拉链表的创建、查询和回滚
概述
使用这种方式即可以记录历史,而且最大程度的节省存储。这里简单介绍一下这种历史拉链表的更新方法。此文参考链接
本文中假设:
- 数据仓库中订单历史表的刷新频率为一天,当天更新前一天的增量数据;
- 如果一个订单在一天内有多次状态变化,则只会记录最后一个状态的历史;
- 订单状态包括三个:创建、支付、完成;
- 创建时间和修改时间只取到天,如果源订单表中没有状态修改时间,那么抽取增量就比较麻烦,需要有个机制来确保能抽取到每天的增量数据;
- 本文中的表和SQL都使用Hive的HQL语法;
初始化
假设我们有三天数据,【2015-08-20,2015-08-21,2015-08-22】。
数据流向:(原系统表)orders ==> (增量表)t_ods_orders_inc ==> (拉链表)t_dw_orders_his
建表脚本
-- 源系统中订单表 CREATE TABLE orders ( orderid INT, createtime STRING, modifiedtime STRING, status STRING ) stored AS textfile; -- 订单的增量数据表,按天分区,存放每天的增量数据,保留半年左右 CREATE TABLE t_ods_orders_inc ( orderid INT, createtime STRING, modifiedtime STRING, status STRING ) PARTITIONED BY (day STRING) stored AS textfile; -- 订单的历史数据拉链表 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;
数据初始化
-- 1. 源表orders,假设此表为21日状态 insert into orders values (1,'2015-08-18','2015-08-18','创建'), (2,'2015-08-18','2015-08-18','创建'), (3,'2015-08-19','2015-08-21','支付'), (4,'2015-08-19','2015-08-21','完成'), (5,'2015-08-19','2015-08-20','支付'), (6,'2015-08-20','2015-08-20','创建'), (7,'2015-08-20','2015-08-21','支付'), (8,'2015-08-21','2015-08-21','创建'); -- 2. 初始化ODS增量表-21日数据(全量初始化,将21号前的累加到此分区) INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = '2015-08-21') SELECT orderid, createtime, modifiedtime, status FROM orders WHERE createtime <= '2015-08-21'; -- 3. 初始化ODS增量表-22日数据 insert overwrite table t_ods_orders_inc partition(day='2015-08-22') values (1,'2015-08-18','2015-08-22','支付'), (2,'2015-08-18','2015-08-22','完成'), (6,'2015-08-20','2015-08-22','支付'), (8,'2015-08-21','2015-08-22','支付'), (9,'2015-08-22','2015-08-22','创建'), (10,'2015-08-22','2015-08-22','支付'); -- 4. 初始化ODS增量表-23日数据 insert overwrite table t_ods_orders_inc partition(day='2015-08-23') values (1,'2015-08-18','2015-08-23','完成'), (3,'2015-08-19','2015-08-23','完成'), (5,'2015-08-19','2015-08-23','完成'), (8,'2015-08-21','2015-08-23','完成'), (11,'2015-08-23','2015-08-23','创建'), (12,'2015-08-23','2015-08-23','创建'), (13,'2015-08-23','2015-08-23','支付');
拉链表创建
假设
- 跑数时间 T= '${dt1}'
- 拉链表有开始日期(生效日期)和结束日期(失效日期,最新记录此列 = '9999-12-31')
初始化
当21号跑数时,需要全量初始化拉链表,此时,拉链表就是21日的切片数据
-- 初始化拉链表,假设21号的就是原始数据 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-21';
增量更新
当22号跑数时,需要把要处理的所有数据分成两部分,处理思路为:
- 新增,22号增量数据,结束日期= '9999-12-31'
- 更新,历史拉链表与增量表进行比对
- 当增量表中存在记录,开始日期=历史拉链表开始日期,结束日期= date_add('${dt1}',-1)
- 当增量表中不存在此记录,代表不需要更新
具体操作
创建一张临时表保存比对的结果数据。
-- 22号增量数据进来后,与21号的状态数据(t_dw_orders_his)比对,更新拉链表 -- ${dt1} = '2015-08-22' 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 ( -- 22号前需更新状态的数据 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 = '9999-12-31' THEN date_add('${dt1}',-1) -- 把22号前有效的数据失效日期置为dt1的上一日 ELSE a.dw_end_date END AS dw_end_date FROM t_dw_orders_his a left join t_ods_orders_inc b ON a.orderid = b.orderid and b.day = '${dt1}' UNION ALL -- 22号最新状态数据 SELECT orderid, createtime, modifiedtime, status, modifiedtime AS dw_start_date, '9999-12-31' AS dw_end_date FROM t_ods_orders_inc WHERE day = '${dt1}' ) x ORDER BY orderid,dw_start_date; -- 临时数据正式入库 INSERT overwrite TABLE t_dw_orders_his SELECT * FROM t_dw_orders_his_tmp; -- 23号数据入t_dw_orders_his表请重复上述22号的刷新步骤 -- ${dt1} = '2015-08-23' -- ... ...
查询和使用场景
1. 查询拉链表最新状态数据
select * from t_dw_orders_his where dw_end_date='9999-12-31';
2.查询某日所有订单快照
--假设查询22号数据状态 --'${dt1}' = '2021-08-22' select * from t_dw_orders_his where dw_start_date<='${dt1}' and dw_end_date>='${dt1}';
回滚方法
方法
先把拉链表的数据分为三份,分别为 T-N、T、T+N 的数据,T是回滚日期。
那么我们假设,在23号发现数据有问题,需要回滚22号的数据,此时拉链表数据有三块。
其中:
- 对于结束日期为21号及之前的数据,【保留】,下图绿色
- 对于22日有效的数据,【更新】,其数据又分两种
- 一种是结束日期是22日的,把结束日期 = '9999-12-31'即可,下图黄色
- 一种是22日前创建,22日后还有效的数据,把结束日期 = '9999-12-31',下图蓝色
- 对于22日后产生的数据,【删除】,下图红色
所以,拉链表的回滚过程的增删改就如下图所示:
具体操作
创建三个临时表,分别保存 T-N、T日的数据,最后合到一张结果表中。
-- 1. 绿色,保留 DROP TABLE t_dw_orders_his_tmp1; 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-22'; -- 2. 黄色,更新-当日生效的数据 DROP TABLE t_dw_orders_his_tmp2; 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-22'; -- 2. 蓝色,更新-22号前到22号后还生效的数据 DROP TABLE t_dw_orders_his_tmp3; 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'; -- 4. 数据插入到新表 CREATE TABLE t_dw_orders_his_new AS select * from ( SELECT a.* ,'绿色,保留,号前的数据' FROM t_dw_orders_his_tmp1 a UNION ALL SELECT b.*,'黄色,更新-当日生效的数据' FROM t_dw_orders_his_tmp2 b UNION ALL SELECT c.*,'蓝色,更新-22号前到22号后还生效的数据' FROM t_dw_orders_his_tmp3 c ) a ORDER BY a.orderid,a.dw_start_date;
总结
拉链表的出现是为了压缩存储和记录数,针对频繁更新的数据会很有效。但是回滚操作不方便,查询的时候也必须要指定时间才能正确取数,操作成本高,得权衡利弊后再确认是否合适自己使用。