拉链表--实现、更新及回滚的具体实现
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.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天数据即可）
