拉链表.docx
《拉链表.docx》由会员分享,可在线阅读,更多相关《拉链表.docx(20页珍藏版)》请在冰豆网上搜索。
拉链表
在数据仓库的数据模型设计过程中,经常会遇到这样的需求:
1.数据量比较大;
2.表中的部分字段会被update,如用户的地址,产品的描述信息,订单的状态等等;
3.需要查看某一个时间点或者时间段的历史快照信息,比如,查看某一个订单在历史某一个时间点的状态,
比如,查看某一个用户在过去某一段时间内,更新过几次等等;
4.变化的比例和频率不是很大,比如,总共有1000万的会员,每天新增和发生变化的有10万左右;
5.如果对这边表每天都保留一份全量,那么每次全量中会保存很多不变的信息,对存储是极大的浪费;
拉链历史表,既能满足反应数据的历史状态,又可以最大程度的节省存储;
举个简单例子,比如有一张订单表,6月20号有3条记录:
到6月21日,表中有5条记录:
到6月22日,表中有6条记录:
数据仓库中对该表的保留方法:
1.只保留一份全量,则数据和6月22日的记录一样,如果需要查看6月21日订单001的状态,则无法满足;
2.每天都保留一份全量,则数据仓库中的该表共有14条记录,但好多记录都是重复保存,没有任务变化,如订单002,004,数据量大了,会造成很大的存储浪费;
如果在数据仓库中设计成历史拉链表保存该表,则会有下面这样一张表:
说明:
1.dw_begin_date表示该条记录的生命周期开始时间,dw_end_date表示该条记录的生命周期结束时间;
2.dw_end_date='9999-12-31'表示该条记录目前处于有效状态;
3.如果查询当前所有有效的记录,则select*fromorder_hiswheredw_end_date='9999-12-31'
4.如果查询2012-06-21的历史快照,则select*fromorder_hiswheredw_begin_date<='2012-06-21'andend_date>='2012-06-21',这条语句会查询到以下记录:
和源表在6月21日的记录完全一致:
可以看出,这样的历史拉链表,既能满足对历史数据的需求,又能很大程度的节省存储资源;
数据仓库中历史拉链表的更新方法
关键字:
数据仓库、极限存储、历史拉链表、更新
在之前介绍过数据仓库中的历史拉链表《数据仓库数据模型之:
极限存储–历史拉链表》,
使用这种方式即可以记录历史,而且最大程度的节省存储。
这里简单介绍一下这种历史拉链表的更新方法。
本文中假设:
1.数据仓库中订单历史表的刷新频率为一天,当天更新前一天的增量数据;
2.如果一个订单在一天内有多次状态变化,则只会记录最后一个状态的历史;
3.订单状态包括三个:
创建、支付、完成;
4.创建时间和修改时间只取到天,如果源订单表中没有状态修改时间,那么抽取增量就比较麻烦,需要有个机制来确保能抽取到每天的增量数据;
5.本文中的表和SQL都使用Hive的HQL语法;
6.源系统中订单表结构为:
CREATETABLEorders(
orderidINT,
createtimeSTRING,
modifiedtimeSTRING,
statusSTRING
)storedAStextfile;
7.在数据仓库的ODS层,有一张订单的增量数据表,按天分区,存放每天的增量数据:
CREATETABLEt_ods_orders_inc(
orderidINT,
createtimeSTRING,
modifiedtimeSTRING,
statusSTRING
)PARTITIONEDBY(daySTRING)
storedAStextfile;
8.在数据仓库的DW层,有一张订单的历史数据拉链表,存放订单的历史状态数据:
CREATETABLEt_dw_orders_his(
orderidINT,
createtimeSTRING,
modifiedtimeSTRING,
statusSTRING,
dw_start_dateSTRING,
dw_end_dateSTRING
)storedAStextfile;
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)
华丽的分割线:
您可以关注lxw的大数据田地,或者加入邮件列表,随时接收博客更新的通知邮件。
增量抽取
每天,从源系统订单表中,将前一天的增量数据抽取到ODS层的增量数据表。
这里的增量需要通过订单表中的创建时间和修改时间来确定:
INSERToverwriteTABLEt_ods_orders_incPARTITION(day=‘${day}‘)
SELECTorderid,createtime,modifiedtime,status
FROMorders
WHEREcreatetime=‘${day}’ORmodifiedtime=‘${day}';
注意:
在ODS层按天分区的增量表,最好保留一段时间的数据,比如半年,为了防止某一天的数据有问题而回滚重做数据。
增量刷新历史数据
从2015-08-22开始,需要每天正常刷新前一天(2015-08-21)的增量数据到历史表。
第一步,通过增量抽取,将2015-08-21的数据抽取到ODS:
INSERToverwriteTABLEt_ods_orders_incPARTITION(day=‘2015-08-21′)
SELECTorderid,createtime,modifiedtime,status
FROMorders
WHEREcreatetime=‘2015-08-21′ORmodifiedtime=‘2015-08-21′;
ODS增量表中2015-08-21的数据如下:
1.spark-sql>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>'2015-08-21'THEN'2015-08-20'ELSEa.dw_end_dateENDASdw_end_date
16.FROMt_dw_orders_hisa
17.leftouterjoin(SELECT*FROMt_ods_orders_incWHEREday='2015-08-21')b
18.ON(a.orderid=b.orderid)
19.UNIONALL
20.SELECTorderid,
21.createtime,
22.modifiedtime,
23.status,
24.modifiedtimeASdw_start_date,
25.'9999-12-31'ASdw_end_date
26.FROMt_ods_orders_inc
27.WHEREday='2015-08-21'
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无变化。
第二个结果集是直接将增量数据插入历史表。
最后把临时表中数据插入历史表:
INSERToverwriteTABLEt_dw_orders_his
SELECT*FROMt_dw_orders_his_tmp;
华丽的分割线:
您可以关注lxw的大数据田地,或者加入邮件列表,随时接收博客更新的通知邮件。
刷新完后,历史表中数据如下:
1.spark-sql>select*fromt_dw_orders_hisorderbyorderid,dw_start_date;
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-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='2015-08-22'ORmodifiedtime='2015-08-22';
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>'2015-08-22'THEN'2015-08-21'ELSEa.dw_end_dateENDASdw_end_date
21.FROMt_dw_orders_hisa
22.leftouterjoin(SELECT*FROMt_ods_orders_incWHEREday='2015-08-22')b
23.ON(a.orderid=b.orderid)
24.UNIONALL
25.SELECTorderid,
26.createtime,
27.modifiedtime,
28.status,
29.modifiedtimeASdw_start_date,
30.'9999-12-31'ASdw_end_date
31.FROMt_ods_orders_inc
32.WHEREday='2015-08-22'
33.)x
34.ORDERBYorderid,dw_start_date;
35.
36.
37.INSERToverwriteTABLEt_dw_orders_his
38.SELECT*FROMt_dw_orders_his_tmp;
39.
刷新完后历史表数据如下:
1.spark-sql>select*fromt_dw_orders_hisorderbyorderid,dw_start_date;
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.
华丽的分割线:
您可以关注lxw的大数据田地,或者加入邮件列表,随时接收博客更新的通知邮件。
查看2015-08-21的历史快照数据:
1.spark-sql>select*fromt_dw_orders_hiswheredw_start_date<='2015-08-21'anddw_end_date>='2015-08-21';
2.12015-08-182015-08-18创建2015-08-182015-08-21
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
6.52015-08-192015-08-20支付2015-08-199999-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的增量数据:
按照上面的方法刷新完后,历史表数据如下:
1.spark-sql>select*fromt_dw_orders_hisorderbyorderid,dw_start_date;
2.12015-08-182015-08-18创建2015-08-182015-08-21
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