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

 

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-31AS 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的数据如下:

  1. 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),刷新历史表:

先把数据放到一张临时表中:

  1. 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;

 

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

  1. 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天数据即可)

 
 
参考文章:
  http://lxw1234.com/archives/2015/04/20.htm

 

posted on 2017-03-07 17:31  醉方休  阅读(19238)  评论(1编辑  收藏  举报

导航