1 背景
本文前面的内容时参考了'lxw的大数据田地',具体可查看最后的'参考文章',个人加入了'拉链表的回滚'部分的内容sql,如果有实践的,可以互相交流学习,谢谢
在数据仓库的数据模型设计过程中,经常会遇到这样的需求:
1.1 数据量比较大;
1.2 表中的部分字段会被update,如用户的地址,产品的描述信息,订单的状态等等;
1.3 需要查看某一个时间点或者时间段的历史快照信息,比如,查看某一个订单在历史某一个时间点的状态,比如,查看某一个用户在过去某一段时间内,更新过几次等等;
1.4 变化的比例和频率不是很大,比如,总共有1000万的会员,每天新增和发生变化的有10万左右;如果对这边表每天都保留一份全量,那么每次全量中会保存很多不变的信息,对存储是极大的浪费;
综上所述:引入'拉链历史表',既能满足反应数据的历史状态,又可以最大程度的节省存储;
2 具体表结构
2.1 例如
有一张订单表,6月20号有3条记录:
订单创建日期 | 订单编号 | 订单状态 |
2012-06-20 | 001 | 创建订单 |
2012-06-20 | 002 | 创建订单 |
2012-06-20 | 003 | 支付完成 |
到6月21日,表中有5条记录:
订单创建日期 | 订单编号 | 订单状态 |
2012-06-20 | 001 | 支付完成(从创建到支付) |
2012-06-20 | 002 | 创建订单 |
2012-06-20 | 003 | 支付完成 |
2012-06-21 | 004 | 创建订单 |
2012-06-21 | 005 | 创建订单 |
到6月22日,表中有6条记录:
订单创建日期 | 订单编号 | 订单状态 |
2012-06-20 | 001 | 支付完成(从创建到支付) |
2012-06-20 | 002 | 创建订单 |
2012-06-20 | 003 | 已发货(从支付到发货) |
2012-06-21 | 004 | 创建订单 |
2012-06-21 | 005 | 支付完成(从创建到支付) |
2012-06-22 | 006 | 创建订单 |
2.2 常用的解决方案以及存在的问题:
1 快照表:只保留一份全量,则数据和6月22日的记录一样,如果需要查看6月21日订单001的状态,则无法满足;
2 全量历史表:每天都保留一份全量,则数据仓库中的该表共有14条记录,但好多记录都是重复保存,没有任务变化,如订单002,004,数据量大了,会造成很大的存储浪费;
2.3 如果在数据仓库中设计成历史拉链表保存该表,则会有下面这样一张表:
订单创建日期 | 订单编号 | 订单状态 | dw_begin_date | dw_end_date |
2012-06-20 | 001 | 创建订单 | 2012-06-20 | 2012-06-20 |
2012-06-20 | 001 | 支付完成 | 2012-06-21 | 9999-12-31 |
2012-06-20 | 002 | 创建订单 | 2012-06-20 | 9999-12-31 |
2012-06-20 | 003 | 支付完成 | 2012-06-20 | 2012-06-21 |
2012-06-20 | 003 | 已发货 | 2012-06-22 | 9999-12-31 |
2012-06-21 | 004 | 创建订单 | 2012-06-21 | 9999-12-31 |
2012-06-21 | 005 | 创建订单 | 2012-06-21 | 2012-06-21 |
2012-06-21 | 005 | 支付完成 | 2012-06-22 | 9999-12-31 |
2012-06-22 | 006 | 创建订单 | 2012-06-22 | 9999-12-31 |
说明:
2.3.1 dw_begin_date表示该条记录的生命周期开始时间(周期快照时的状态),dw_end_date表示该条记录的生命周期结束时间;
2.3.2 dw_end_date = '9999-12-31'表示该条记录目前处于有效状态;
2.3.3 如果查询当前所有有效的记录,则select * from order_his where dw_end_date = '9999-12-31'
2.3.4 如果查询2012-06-21的历史快照,则select * from order_his where dw_begin_date <= '2012-06-21' and end_date >='2012-06-21',这条语句会查询到以下记录:
订单创建日期 | 订单编号 | 订单状态 | dw_begin_date | dw_end_date |
2012-06-20 | 001 | 支付完成 | 2012-06-21 | 9999-12-31 |
2012-06-20 | 002 | 创建订单 | 2012-06-20 | 9999-12-31 |
2012-06-20 | 003 | 支付完成 | 2012-06-20 | 2012-06-21 |
2012-06-21 | 004 | 创建订单 | 2012-06-21 | 9999-12-31 |
2012-06-21 | 005 | 创建订单 | 2012-06-21 | 2012-06-21 |
和源表在6月21日的记录完全一致:
订单创建日期 | 订单编号 | 订单状态 |
2012-06-20 | 001 | 支付完成(从创建到支付) |
2012-06-20 | 002 | 创建订单 |
2012-06-20 | 003 | 支付完成 |
2012-06-21 | 004 | 创建订单 |
2012-06-21 | 005 | 创建订单 |
可以看出,这样的历史拉链表,既能满足对历史数据的需求,又能很大程度的节省存储资源;
3 拉链表更新方案
假设:
3.1 前提:
3.1.1 数据仓库中订单历史表的刷新频率为一天,当天更新前一天的增量数据;
3.1.2 如果一个订单在一天内有多次状态变化,则只会记录最后一个状态的历史;
3.1.3 订单状态包括三个:创建、支付、完成;
3.1.4 创建时间和修改时间只取到天,如果源订单表中没有状态修改时间,那么抽取增量就比较麻烦,需要有个机制来确保能抽取到每天的增量数据;
-- 例如DB中的binlog解析,或者通过sqoop同步,只同步有过修改的数据(新增 or 修改)
3.1.5 本文中的表和SQL都使用Hive的HQL语法;
3.1.6 源系统中订单表结构为:
CREATE TABLE orders ( orderid INT, createtime STRING, modifiedtime STRING, status STRING ) stored AS textfile;
3.2 在数据仓库的ODS层,有一张订单的增量数据表,按天分区,存放每天的增量数据:
CREATE TABLE t_ods_orders_inc ( orderid INT, createtime STRING, modifiedtime STRING, status STRING ) PARTITIONED BY (day STRING) stored AS textfile;
3.3 在数据仓库的DW层,有一张订单的历史数据拉链表,存放订单的历史状态数据:
CREATE TABLE t_dw_orders_his ( orderid INT, createtime STRING, modifiedtime STRING, status STRING, dw_start_date STRING, dw_end_date STRING ) stored AS textfile;
3.4 2015-08-21至2015-08-23,每天原系统订单表的数据如下,红色标出的为当天发生变化的订单,即增量数据:
3.5 具体步骤:
在数据从源业务系统每天正常抽取和刷新到DW订单历史表之前,需要做一次全量的初始化,就是从源订单表中昨天以前的数据全部抽取到ODW,并刷新到DW。
以上面的数据为例,比如在2015-08-21这天做全量初始化,那么我需要将包括2015-08-20之前的所有的数据都抽取并刷新到DW:
3.5.1 抽取全量数据到ODS:
INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = ‘2015-08-20′) SELECT orderid,createtime,modifiedtime,status FROM orders WHERE createtime <= ‘2015-08-20′;
3.5.2 从ODS刷新到DW:
INSERT overwrite TABLE t_dw_orders_his SELECT orderid,createtime,modifiedtime,status, createtime AS dw_start_date, ‘9999-12-31′ AS dw_end_date FROM t_ods_orders_inc WHERE day = ‘2015-08-20′;
完成后,DW订单历史表中数据如下:
spark-sql> select * from t_dw_orders_his; 1 2015-08-18 2015-08-18 创建 2015-08-18 9999-12-31 2 2015-08-18 2015-08-18 创建 2015-08-18 9999-12-31 3 2015-08-19 2015-08-21 支付 2015-08-19 9999-12-31 4 2015-08-19 2015-08-21 完成 2015-08-19 9999-12-31 5 2015-08-19 2015-08-20 支付 2015-08-19 9999-12-31 6 2015-08-20 2015-08-20 创建 2015-08-20 9999-12-31 7 2015-08-20 2015-08-21 支付 2015-08-20 9999-12-31 Time taken: 2.296 seconds, Fetched 7 row(s)
3.5.3 增量抽取
每天,从源系统订单表中,将前一天的增量数据抽取到ODS层的增量数据表。
这里的增量需要通过订单表中的创建时间和修改时间来确定:
INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = '${day}') SELECT orderid,createtime,modifiedtime,status FROM orders WHERE createtime = '${day}' OR modifiedtime = '${day}';
注意:在ODS层按天分区的增量表,最好保留一段时间的数据,比如半年,为了防止某一天的数据有问题而回滚重做数据。
3.5.4 增量刷新历史数据
从2015-08-22开始,需要每天正常刷新前一天(2015-08-21)的增量数据到历史表。
3.5.4.1 通过增量抽取,将2015-08-21的数据抽取到ODS:
INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = '2015-08-21') SELECT orderid,createtime,modifiedtime,status FROM orders WHERE createtime = '2015-08-21' OR modifiedtime = '2015-08-21';
ODS增量表中2015-08-21的数据如下:
-
spark-sql> select * from t_ods_orders_inc where day = '2015-08-21'; 3 2015-08-19 2015-08-21 支付 2015-08-21 4 2015-08-19 2015-08-21 完成 2015-08-21 7 2015-08-20 2015-08-21 支付 2015-08-21 8 2015-08-21 2015-08-21 创建 2015-08-21 Time taken: 0.437 seconds, Fetched 4 row(s)
3.5.4.2 通过DW历史数据(数据日期为2015-08-20),和ODS增量数据(2015-08-21),刷新历史表:
先把数据放到一张临时表中:
-
DROP TABLE IF EXISTS t_dw_orders_his_tmp; CREATE TABLE t_dw_orders_his_tmp AS SELECT orderid, createtime, modifiedtime, status, dw_start_date, dw_end_date FROM (SELECT a.orderid, a.createtime, a.modifiedtime, a.status, a.dw_start_date, CASE WHEN b.orderid IS NOT NULL AND a.dw_end_date > '2015-08-21' THEN '2015-08-20' ELSE a.dw_end_date END AS dw_end_date FROM t_dw_orders_his a left outer join (SELECT * FROM t_ods_orders_inc WHERE day = '2015-08-21') b ON (a.orderid = b.orderid) UNION ALL SELECT orderid, createtime, modifiedtime, status, modifiedtime AS dw_start_date, '9999-12-31' AS dw_end_date FROM t_ods_orders_inc WHERE day = '2015-08-21' ) x ORDER BY orderid,dw_start_date;
其中:
UNION ALL的两个结果集中,第一个是用历史表left outer join 日期为 ${yyy-MM-dd} 的增量,能关联上的,并且dw_end_date > ${yyy-MM-dd},说明状态有变化,则把原来的dw_end_date置为(${yyy-MM-dd} – 1), 关联不上的,说明状态无变化,dw_end_date无变化。
第二个结果集是直接将增量数据插入历史表。
3.5.5 最后把临时表中数据插入历史表:
INSERT overwrite TABLE t_dw_orders_his SELECT * FROM t_dw_orders_his_tmp;
刷新完后,历史表中数据如下:
-
spark-sql> select * from t_dw_orders_his order by orderid,dw_start_date; 1 2015-08-18 2015-08-18 创建 2015-08-18 9999-12-31 2 2015-08-18 2015-08-18 创建 2015-08-18 9999-12-31 3 2015-08-19 2015-08-21 支付 2015-08-19 2015-08-20 3 2015-08-19 2015-08-21 支付 2015-08-21 9999-12-31 4 2015-08-19 2015-08-21 完成 2015-08-19 2015-08-20 4 2015-08-19 2015-08-21 完成 2015-08-21 9999-12-31 5 2015-08-19 2015-08-20 支付 2015-08-19 9999-12-31 6 2015-08-20 2015-08-20 创建 2015-08-20 9999-12-31 7 2015-08-20 2015-08-21 支付 2015-08-20 2015-08-20 7 2015-08-20 2015-08-21 支付 2015-08-21 9999-12-31 8 2015-08-21 2015-08-21 创建 2015-08-21 9999-12-31 Time taken: 0.717 seconds, Fetched 11 row(s)
由于在2015-08-21做了8月20日以前的数据全量初始化,而订单3、4、7在2015-08-21的增量数据中也存在,因此都有两条记录,但不影响后面的查询。
4 拉链表回滚
4.1 具体操作方案
假设恢复到t天之前的数据,即未融合t天数据之前的拉链表,假设标记的开始日期和结束日期分别为s、t,具体分析如下:
1 当t-1>e时,s数据、e数据在t天之前产生,保留即可 2 当t-1=e时,e数据在t天产生,需修改 3 当s<t<=e时,e数据在t+n天产生,需修改 4 当s>=t时,s数据、e数据在t+n天产生,删除即可
具体例子:
spark-sql> select * from t_dw_orders_his order by orderid,dw_start_date; 1 2015-08-18 2015-08-18 创建 2015-08-18 2015-08-21 1 2015-08-18 2015-08-22 支付 2015-08-22 2015-08-22 1 2015-08-18 2015-08-23 完成 2015-08-23 9999-12-31 2 2015-08-18 2015-08-18 创建 2015-08-18 2015-08-21 2 2015-08-18 2015-08-22 完成 2015-08-22 9999-12-31 3 2015-08-19 2015-08-21 支付 2015-08-19 2015-08-20 3 2015-08-19 2015-08-21 支付 2015-08-21 2015-08-22 3 2015-08-19 2015-08-23 完成 2015-08-23 9999-12-31 4 2015-08-19 2015-08-21 完成 2015-08-19 2015-08-20 4 2015-08-19 2015-08-21 完成 2015-08-21 9999-12-31 5 2015-08-19 2015-08-20 支付 2015-08-19 2015-08-22 5 2015-08-19 2015-08-23 完成 2015-08-23 9999-12-31 6 2015-08-20 2015-08-20 创建 2015-08-20 2015-08-21 6 2015-08-20 2015-08-22 支付 2015-08-22 9999-12-31 7 2015-08-20 2015-08-21 支付 2015-08-20 2015-08-20 7 2015-08-20 2015-08-21 支付 2015-08-21 9999-12-31 8 2015-08-21 2015-08-21 创建 2015-08-21 2015-08-21 8 2015-08-21 2015-08-22 支付 2015-08-22 2015-08-22 8 2015-08-21 2015-08-23 完成 2015-08-23 9999-12-31 9 2015-08-22 2015-08-22 创建 2015-08-22 9999-12-31 10 2015-08-22 2015-08-22 支付 2015-08-22 9999-12-31 11 2015-08-23 2015-08-23 创建 2015-08-23 9999-12-31 12 2015-08-23 2015-08-23 创建 2015-08-23 9999-12-31 13 2015-08-23 2015-08-23 支付 2015-08-23 9999-12-31
比如在插入2015-08-23的数据后,回滚2015-08-22的数据,使拉链表与2015-08-21的一致,具体操作过程如下
1 增加临时表t_dw_orders_his_tmp1,用来记录t-1>e的数据 CREATE TABLE t_dw_orders_his_tmp1 AS SELECT orderid, createtime, modifiedtime, status, dw_start_date, dw_end_date FROM t_dw_orders_his WHERE dw_end_date < '2015-08-21'
3 2015-08-19 2015-08-21 支付 2015-08-19 2015-08-20 4 2015-08-19 2015-08-21 完成 2015-08-19 2015-08-20 7 2015-08-20 2015-08-21 支付 2015-08-20 2015-08-20
2 增加临时表t_dw_orders_his_tmp2,用来记录t-1=e的数据 CREATE TABLE t_dw_orders_his_tmp2 AS SELECT orderid, createtime, modifiedtime, status, dw_start_date, '9999-12-31' AS dw_end_date FROM t_dw_orders_his WHERE dw_end_date = '2015-08-21'
1 2015-08-18 2015-08-18 创建 2015-08-18 9999-12-31 2 2015-08-18 2015-08-18 创建 2015-08-18 9999-12-31 6 2015-08-20 2015-08-20 创建 2015-08-20 9999-12-31 8 2015-08-21 2015-08-21 创建 2015-08-21 9999-12-31
3 增加临时表t_dw_orders_his_tmp3,用来记录s<t<=e的数据 CREATE TABLE t_dw_orders_his_tmp3 AS SELECT orderid, createtime, modifiedtime, status, dw_start_date, '9999-12-31' dw_end_date FROM t_dw_orders_his WHERE dw_start_date < '2015-08-22' AND dw_end_date >= '2015-08-22'
3 2015-08-19 2015-08-21 支付 2015-08-21 9999-12-31 4 2015-08-19 2015-08-21 完成 2015-08-21 9999-12-31 5 2015-08-19 2015-08-20 支付 2015-08-19 9999-12-31 7 2015-08-20 2015-08-21 支付 2015-08-21 9999-12-31
4 所有数据插入新表t_dw_orders_his_new CREATE TABLE t_dw_orders_his_new AS SELECT * FROM t_dw_orders_his_tmp1 UNION ALL SELECT * FROM t_dw_orders_his_tmp2 UNION ALL SELECT * FROM t_dw_orders_his_tmp3
1 2015-08-18 2015-08-18 创建 2015-08-18 9999-12-31 2 2015-08-18 2015-08-18 创建 2015-08-18 9999-12-31 3 2015-08-19 2015-08-21 支付 2015-08-19 2015-08-20 3 2015-08-19 2015-08-21 支付 2015-08-21 9999-12-31 4 2015-08-19 2015-08-21 完成 2015-08-19 2015-08-20 4 2015-08-19 2015-08-21 完成 2015-08-21 9999-12-31 5 2015-08-19 2015-08-20 支付 2015-08-19 9999-12-31 6 2015-08-20 2015-08-20 创建 2015-08-20 9999-12-31 7 2015-08-20 2015-08-21 支付 2015-08-20 2015-08-20 7 2015-08-20 2015-08-21 支付 2015-08-21 9999-12-31 8 2015-08-21 2015-08-21 创建 2015-08-21 9999-12-31
与原数据一致,验证无错
4.2 备用方案
可以采用备份的方案,保证无误和可行。(保存增量数据,并对t_dw_orders_his表每个月备份一次全量数据。如需回滚,最多重跑30天数据即可)