拉链表Word下载.docx
- 文档编号:5311509
- 上传时间:2023-05-04
- 格式:DOCX
- 页数:20
- 大小:684.61KB
拉链表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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 拉链