拉链表Word下载.docx
《拉链表Word下载.docx》由会员分享，可在线阅读，更多相关《拉链表Word下载.docx（20页珍藏版）》请在冰点文库上搜索。
andend_date>
，这条语句会查询到以下记录：
和源表在6月21日的记录完全一致：
可以看出，这样的历史拉链表，既能满足对历史数据的需求，又能很大程度的节省存储资源；
数据仓库中历史拉链表的更新方法
关键字：
数据仓库、极限存储、历史拉链表、更新
在之前介绍过数据仓库中的历史拉链表《数据仓库数据模型之：
极限存储–历史拉链表》，
使用这种方式即可以记录历史，而且最大程度的节省存储。
这里简单介绍一下这种历史拉链表的更新方法。
本文中假设：
1.数据仓库中订单历史表的刷新频率为一天，当天更新前一天的增量数据；
2.如果一个订单在一天内有多次状态变化，则只会记录最后一个状态的历史；
3.订单状态包括三个：
创建、支付、完成；
4.创建时间和修改时间只取到天，如果源订单表中没有状态修改时间，那么抽取增量就比较麻烦，需要有个机制来确保能抽取到每天的增量数据；
5.本文中的表和SQL都使用Hive的HQL语法；
6.源系统中订单表结构为：
CREATETABLEorders（
orderidINT,
createtimeSTRING,
modifiedtimeSTRING,
statusSTRING
）storedAStextfile;
7.在数据仓库的ODS层，有一张订单的增量数据表，按天分区，存放每天的增量数据：
CREATETABLEt_ods_orders_inc（
）PARTITIONEDBY（daySTRING）
storedAStextfile;
8.在数据仓库的DW层，有一张订单的历史数据拉链表，存放订单的历史状态数据：
CREATETABLEt_dw_orders_his（
statusSTRING,
dw_start_dateSTRING,
dw_end_dateSTRING
9.暂未考虑Hive上表的查询性能问题，只实现功能；
华丽的分割线：
您可以关注lxw的大数据田地，或者加入邮件列表，随时接收博客更新的通知邮件。
10.2015-08-21至2015-08-23，每天原系统订单表的数据如下，红色标出的为当天发生变化的订单，即增量数据：
全量初始化
在数据从源业务系统每天正常抽取和刷新到DW订单历史表之前，需要做一次全量的初始化，就是从源订单表中昨天以前的数据全部抽取到ODW，并刷新到DW。
以上面的数据为例，比如在2015-08-21这天做全量初始化，那么我需要将包括2015-08-20之前的所有的数据都抽取并刷新到DW：
第一步，抽取全量数据到ODS：
INSERToverwriteTABLEt_ods_orders_incPARTITION（day=‘2015-08-20′）
SELECTorderid,createtime,modifiedtime,status
FROMorders
WHEREcreatetime<
=‘2015-08-20′;
第二步，从ODS刷新到DW：
INSERToverwriteTABLEt_dw_orders_his
SELECTorderid,createtime,modifiedtime,status,
createtimeASdw_start_date,
‘9999-12-31′ASdw_end_date
FROMt_ods_orders_inc
WHEREday=‘2015-08-20′;
完成后，DW订单历史表中数据如下：
1.spark-sql>
select*fromt_dw_orders_his;
2.12015-08-182015-08-18创建2015-08-189999-12-31
3.22015-08-182015-08-18创建2015-08-189999-12-31
4.32015-08-192015-08-21支付2015-08-199999-12-31
5.42015-08-192015-08-21完成2015-08-199999-12-31
6.52015-08-192015-08-20支付2015-08-199999-12-31
7.62015-08-202015-08-20创建2015-08-209999-12-31
8.72015-08-202015-08-21支付2015-08-209999-12-31
9.Timetaken:
2.296seconds,Fetched7row（s）
增量抽取
每天，从源系统订单表中，将前一天的增量数据抽取到ODS层的增量数据表。
这里的增量需要通过订单表中的创建时间和修改时间来确定：
INSERToverwriteTABLEt_ods_orders_incPARTITION（day=‘${day}‘）
WHEREcreatetime=‘${day}’ORmodifiedtime=‘${day}'
;
注意：
在ODS层按天分区的增量表，最好保留一段时间的数据，比如半年，为了防止某一天的数据有问题而回滚重做数据。
增量刷新历史数据
从2015-08-22开始，需要每天正常刷新前一天（2015-08-21）的增量数据到历史表。
第一步，通过增量抽取，将2015-08-21的数据抽取到ODS：
INSERToverwriteTABLEt_ods_orders_incPARTITION（day=‘2015-08-21′）
WHEREcreatetime=‘2015-08-21′ORmodifiedtime=‘2015-08-21′;
ODS增量表中2015-08-21的数据如下：
select*fromt_ods_orders_incwhereday='
2015-08-21'
2.32015-08-192015-08-21支付2015-08-21
3.42015-08-192015-08-21完成2015-08-21
4.72015-08-202015-08-21支付2015-08-21
5.82015-08-212015-08-21创建2015-08-21
6.Timetaken:
0.437seconds,Fetched4row（s）
第二步，通过DW历史数据（数据日期为2015-08-20），和ODS增量数据（2015-08-21），刷新历史表：
先把数据放到一张临时表中：
1.DROPTABLEIFEXISTSt_dw_orders_his_tmp;
2.CREATETABLEt_dw_orders_his_tmpAS
3.SELECTorderid,
4.createtime,
5.modifiedtime,
6.status,
7.dw_start_date,
8.dw_end_date
9.FROM（
10.SELECTa.orderid,
11.a.createtime,
12.a.modifiedtime,
13.a.status,
14.a.dw_start_date,
15.CASEWHENb.orderidISNOTNULLANDa.dw_end_date>
'
THEN'
2015-08-20'
ELSEa.dw_end_dateENDASdw_end_date
16.FROMt_dw_orders_hisa
17.leftouterjoin（SELECT*FROMt_ods_orders_incWHEREday='
）b
18.ON（a.orderid=b.orderid）
19.UNIONALL
20.SELECTorderid,
21.createtime,
22.modifiedtime,
23.status,
24.modifiedtimeASdw_start_date,
25.'
ASdw_end_date
26.FROMt_ods_orders_inc
27.WHEREday='
28.）x
29.ORDERBYorderid,dw_start_date;
其中：
UNIONALL的两个结果集中，第一个是用历史表leftouterjoin日期为${yyy-MM-dd}的增量，能关联上的，并且dw_end_date>
${yyy-MM-dd}，说明状态有变化，则把原来的dw_end_date置为（${yyy-MM-dd}–1）,关联不上的，说明状态无变化，dw_end_date无变化。
第二个结果集是直接将增量数据插入历史表。
最后把临时表中数据插入历史表：
SELECT*FROMt_dw_orders_his_tmp;
刷新完后，历史表中数据如下：
select*fromt_dw_orders_hisorderbyorderid,dw_start_date;
2.12015-08-182015-08-18创建2015-08-189999-12-31
4.32015-08-192015-08-21支付2015-08-192015-08-20
5.32015-08-192015-08-21支付2015-08-219999-12-31
6.42015-08-192015-08-21完成2015-08-192015-08-20
7.42015-08-192015-08-21完成2015-08-219999-12-31
8.52015-08-192015-08-20支付2015-08-199999-12-31
9.62015-08-202015-08-20创建2015-08-209999-12-31
10.72015-08-202015-08-21支付2015-08-202015-08-20
11.72015-08-202015-08-21支付2015-08-219999-12-31
12.82015-08-212015-08-21创建2015-08-219999-12-31
13.Timetaken:
0.717seconds,Fetched11row（s）
14.
由于在2015-08-21做了8月20日以前的数据全量初始化，而订单3、4、7在2015-08-21的增量数据中也存在，因此都有两条记录，但不影响后面的查询。
再看将2015-08-22的增量数据刷新到历史表：
1.INSERToverwriteTABLEt_ods_orders_incPARTITION（day='
2015-08-22'
）
2.SELECTorderid,createtime,modifiedtime,status
3.FROMorders
4.WHEREcreatetime='
ORmodifiedtime='
5.
6.DROPTABLEIFEXISTSt_dw_orders_his_tmp;
7.CREATETABLEt_dw_orders_his_tmpAS
8.SELECTorderid,
9.createtime,
10.modifiedtime,
11.status,
12.dw_start_date,
13.dw_end_date
14.FROM（
15.SELECTa.orderid,
16.a.createtime,
17.a.modifiedtime,
18.a.status,
19.a.dw_start_date,
20.CASEWHENb.orderidISNOTNULLANDa.dw_end_date>
21.FROMt_dw_orders_hisa
22.leftouterjoin（SELECT*FROMt_ods_orders_incWHEREday='
23.ON（a.orderid=b.orderid）
24.UNIONALL
25.SELECTorderid,
26.createtime,
27.modifiedtime,
28.status,
29.modifiedtimeASdw_start_date,
30.'
31.FROMt_ods_orders_inc
32.WHEREday='
33.）x
34.ORDERBYorderid,dw_start_date;
35.
36.
37.INSERToverwriteTABLEt_dw_orders_his
38.SELECT*FROMt_dw_orders_his_tmp;
39.
刷新完后历史表数据如下：
2.12015-08-182015-08-18创建2015-08-182015-08-21
3.12015-08-182015-08-22支付2015-08-229999-12-31
4.22015-08-182015-08-18创建2015-08-182015-08-21
5.22015-08-182015-08-22完成2015-08-229999-12-31
6.32015-08-192015-08-21支付2015-08-192015-08-20
7.32015-08-192015-08-21支付2015-08-219999-12-31
8.42015-08-192015-08-21完成2015-08-192015-08-20
9.42015-08-192015-08-21完成2015-08-219999-12-31
10.52015-08-192015-08-20支付2015-08-199999-12-31
11.62015-08-202015-08-20创建2015-08-202015-08-21
12.62015-08-202015-08-22支付2015-08-229999-12-31
13.72015-08-202015-08-21支付2015-08-202015-08-20
14.72015-08-202015-08-21支付2015-08-219999-12-31
15.82015-08-212015-08-21创建2015-08-212015-08-21
16.82015-08-212015-08-22支付2015-08-229999-12-31
17.92015-08-222015-08-22创建2015-08-229999-12-31
18.102015-08-222015-08-22支付2015-08-229999-12-31
19.Timetaken:
0.66seconds,Fetched17row（s）
20.
查看2015-08-21的历史快照数据：
select*fromt_dw_orders_hiswheredw_start_date<
anddw_end_date>
3.22015-08-182015-08-18创建2015-08-182015-08-21
4.32015-08-192015-08-21支付2015-08-219999-12-31
5.42015-08-192015-08-21完成2015-08-219999-12-31
7.62015-08-202015-08-20创建2015-08-202015-08-21
8.72015-08-202015-08-21支付2015-08-219999-12-31
9.82015-08-212015-08-21创建2015-08-212015-08-21
订单1在2015-08-21的时候还处于创建的状态，在2015-08-22的时候状态变为支付。
再刷新2015-08-23的增量数据：
按照上面的方法刷新完后，历史表数据如下：
3.12015-08-182015-08-22支付2015-08-222015-08-22
4.12015-08-182015-08-23完成2015-08-239999-12-31
5.22015-08-182015-08-18创建2015-08-182015-08-21
6.22015-08-182015-08-22完成2015-08-229999-12-31
7.32015-08-192015-08-21支付2015-08-192015-08-20
8.32015-08-192015-08-21支付2015-08-212015-08-22
9.32015-08-192015-08-23完成2015-08-239999-12-31
10.42015-08-192015-08-21完成2015-08-192015-08-20
11.42015-08-192015-08-21完成2015-08-219999-12-31
12.52015-08-192015-08-20支付2015-08-192015-08-22
13.52015-08-192015-08-23完成2015-08-239999-12-31
14.62015-08-202015-08-20创建2015-08-202015-08-21
15.62015-08-202015-08-22支付2015-08-229999-12-31
16.72015-08-202015-08-21支付2015-08-202015-08-20
17.72015-08-202015-08-21支付2015-08-219999-12-31
18.8201
升级会员 