微信搜索bigdata029 | 邀请体验:数阅–数据管理、OLAP分析与可视化平台 | 订阅本站 | 赞助作者:赞助作者

数据仓库中历史拉链表的更新方法

数据仓库/数据库 lxw1234@qq.com 9111℃ 5评论

关键字:数据仓库、极限存储、历史拉链表、更新

在之前介绍过数据仓库中的历史拉链表《数据仓库数据模型之:极限存储–历史拉链表》,

使用这种方式即可以记录历史,而且最大程度的节省存储。这里简单介绍一下这种历史拉链表的更新方法。

本文中假设:

  1. 数据仓库中订单历史表的刷新频率为一天,当天更新前一天的增量数据;
  2. 如果一个订单在一天内有多次状态变化,则只会记录最后一个状态的历史;
  3. 订单状态包括三个:创建、支付、完成;
  4. 创建时间和修改时间只取到天,如果源订单表中没有状态修改时间,那么抽取增量就比较麻烦,需要有个机制来确保能抽取到每天的增量数据;
  5. 本文中的表和SQL都使用Hive的HQL语法;
  6. 源系统中订单表结构为:

CREATE TABLE orders (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
) stored AS textfile;

7.在数据仓库的ODS层,有一张订单的增量数据表,按天分区,存放每天的增量数据:

CREATE TABLE t_ods_orders_inc (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
) PARTITIONED BY (day STRING)
stored AS textfile;

8. 在数据仓库的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;

9. 暂未考虑Hive上表的查询性能问题,只实现功能;

华丽的分割线:您可以关注 lxw的大数据田地 ,或者 加入邮件列表 ,随时接收博客更新的通知邮件。

 

10. 2015-08-21至2015-08-23,每天原系统订单表的数据如下,红色标出的为当天发生变化的订单,即增量数据:

历史拉链表

历史拉链表

历史拉链表

全量初始化

在数据从源业务系统每天正常抽取和刷新到DW订单历史表之前,需要做一次全量的初始化,就是从源订单表中昨天以前的数据全部抽取到ODW,并刷新到DW。

以上面的数据为例,比如在2015-08-21这天做全量初始化,那么我需要将包括2015-08-20之前的所有的数据都抽取并刷新到DW:

第一步,抽取全量数据到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′;

第二步,从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)
华丽的分割线:您可以关注 lxw的大数据田地 ,或者 加入邮件列表 ,随时接收博客更新的通知邮件。

 

增量抽取

每天,从源系统订单表中,将前一天的增量数据抽取到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层按天分区的增量表,最好保留一段时间的数据,比如半年,为了防止某一天的数据有问题而回滚重做数据。

增量刷新历史数据

从2015-08-22开始,需要每天正常刷新前一天(2015-08-21)的增量数据到历史表。

第一步,通过增量抽取,将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)

第二步,通过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无变化。
第二个结果集是直接将增量数据插入历史表。

最后把临时表中数据插入历史表:
INSERT overwrite TABLE t_dw_orders_his
SELECT * FROM t_dw_orders_his_tmp;

华丽的分割线:您可以关注 lxw的大数据田地 ,或者 加入邮件列表 ,随时接收博客更新的通知邮件。

 

刷新完后,历史表中数据如下:

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的增量数据中也存在,因此都有两条记录,但不影响后面的查询。

再看将2015-08-22的增量数据刷新到历史表:

INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = '2015-08-22') 
SELECT orderid,createtime,modifiedtime,status 
FROM orders 
WHERE createtime = '2015-08-22' OR modifiedtime = '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 (
    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-22' THEN '2015-08-21' 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-22') 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-22' 
) x 
ORDER BY orderid,dw_start_date;


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      2015-08-21
1       2015-08-18      2015-08-22      支付    2015-08-22      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      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      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      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
Time taken: 0.66 seconds, Fetched 17 row(s)

华丽的分割线:您可以关注 lxw的大数据田地 ,或者 加入邮件列表 ,随时接收博客更新的通知邮件。

 

查看2015-08-21的历史快照数据:

spark-sql> select * from t_dw_orders_his where dw_start_date <= '2015-08-21' and dw_end_date >= '2015-08-21';
1       2015-08-18      2015-08-18      创建    2015-08-18      2015-08-21
2       2015-08-18      2015-08-18      创建    2015-08-18      2015-08-21
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
6       2015-08-20      2015-08-20      创建    2015-08-20      2015-08-21
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

订单1在2015-08-21的时候还处于创建的状态,在2015-08-22的时候状态变为支付。

再刷新2015-08-23的增量数据:

按照上面的方法刷新完后,历史表数据如下:

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

订单1从20号-23号,状态变化了三次,历史表中有三条记录。

//查看2015-08-22当天的历史快照,可以看出,和上面图中2015-08-22时候订单表中的数据是一样的
spark-sql> select * from t_dw_orders_his where dw_start_date <= '2015-08-22' and dw_end_date >= '2015-08-22';
1       2015-08-18      2015-08-22      支付    2015-08-22      2015-08-22
2       2015-08-18      2015-08-22      完成    2015-08-22      9999-12-31
3       2015-08-19      2015-08-21      支付    2015-08-21      2015-08-22
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
6       2015-08-20      2015-08-22      支付    2015-08-22      9999-12-31
7       2015-08-20      2015-08-21      支付    2015-08-21      9999-12-31
8       2015-08-21      2015-08-22      支付    2015-08-22      2015-08-22
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
Time taken: 0.328 seconds, Fetched 10 row(s)
//查看当前所有订单的最新状态
spark-sql> select * from t_dw_orders_his where dw_end_date = '9999-12-31';                                   
1       2015-08-18      2015-08-23      完成    2015-08-23      9999-12-31
2       2015-08-18      2015-08-22      完成    2015-08-22      9999-12-31
3       2015-08-19      2015-08-23      完成    2015-08-23      9999-12-31
4       2015-08-19      2015-08-21      完成    2015-08-21      9999-12-31
5       2015-08-19      2015-08-23      完成    2015-08-23      9999-12-31
6       2015-08-20      2015-08-22      支付    2015-08-22      9999-12-31
7       2015-08-20      2015-08-21      支付    2015-08-21      9999-12-31
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
Time taken: 0.293 seconds, Fetched 13 row(s)

实际业务中,有可能某一天的数据有问题,需要回滚或重做,这点有点麻烦,后续文章再介绍。

相关阅读:

数据仓库数据模型之:极限存储–历史拉链表

华丽的分割线:您可以关注 lxw的大数据田地 ,或者 加入邮件列表 ,随时接收博客更新的通知邮件。

 

如果觉得本博客对您有帮助,请 赞助作者

转载请注明:lxw的大数据田地 » 数据仓库中历史拉链表的更新方法

喜欢 (24)
分享 (0)
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
(5)个小伙伴在吐槽
  1. 文章写的非常的好,我们现在也在做拉链的表,在你的文章中,有几个地方不太明白: 1.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 说明状态有变化,则把原来的dw_end_date置为(${yyy-MM-dd} – 1), 关联不上的,说明状态无变化,dw_end_date无变化。 这个上面的(${yyy-MM-dd} – 1),我认为是在2015-8-22号做的增量数据,${yyy-MM-dd} – 1) 这个dw_end_date的时间应该为2015-8-21日期,为什么你的语句中是2015-8-20? 2.若是源库有删除的记录,在这个拉链表中如何把删除的置为失效状态?
    bingfeng20042016-04-16 17:58 回复
    • 1.${yyyy-MM-dd}这个时间已经是数据日期了,也就是2015-08-21,这些数据都是在2015-08-21产生的增量,发生的变化,因此要把以前的记录结束日期置为${yyyy-MM-dd,-1d}. 2.文章中确实没有考虑有删除记录的情况,一般业务系统肯定不会把记录物理删除,都是通过记录状态或者移动到另外一张表来实现,这样的话,就单独处理一下删除的这部分记录。如果真是物理删除,那就比较麻烦,需要通过DW历史表和业务系统表关联找到删除的记录,当然不能硬关联。
      lxw1234@qq.com2016-04-20 09:48 回复
  2. 你好!如果数据有问题,怎么实现数据重做呢?能讲解一下吗?谢谢!!!
    polo_suns2016-04-28 13:27 回复
  3. 你好,数据的回滚和重做,有什么好的方法吗?我现在的做法是每天拉链前先备份上一次拉链的结果,这种方式比较傻,只能当天发现问题后修复,第二天再看连备份的数据也是错的了。
    lx_sot2016-06-21 14:17 回复
  4. 非常期待 数据回滚 及重做拉链的 文章
    核动力贝贝2016-06-22 18:51 回复