拉链表--实现、更新及回滚的具体实现

最新推荐文章于 2024-02-20 11:15:50 发布

hepy11

最新推荐文章于 2024-02-20 11:15:50 发布

阅读量1.6k

点赞数 1

分类专栏：拉链表文章标签：数据库

原文链接：https://blog.csdn.net/guicaizhou/article/details/82021735?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522160670311419721942295304%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=160670311419721942295304&biz_id=

版权

拉链表专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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天数据即可）