数据仓库之拉链表

背景：在业务数据中存在会改变状态的数据，需要做历史分析，比如订单状态、登录位置等。

问题：如何找到历史某一天的所有主体的状态。

针对上述问题有三种解决方案：

1. 每天保存全量数据快照：每天都做数据全量备份，可直接查询历史某个节点的全量数据。
2. 每天只做增量数据抽取：查询的时候需要按用户分组并过滤更新时间最近的数据。
3. 每天做增量数据且完成拉链：查询指定开始和结束时间即可。

上述三种方案的利弊：

1. 全量数据不够合理，当数据变化少数据量大时资源浪费，但实现简单，使用方便。
2. 只做增量抽取使用复杂，不够直观。
3. 拉链：数据占用空间只是左右数据变化的历史不会重复存储，使用逻辑简单。

综上所述，在数据仓库建设的过程中如果需要保存数据的历史状态可考虑拉链表，下面具体介绍拉链表是如何保存历史且占用空间较少的。

下面是一张订单明细表：订单号，创建时间，更改时间，订单状态

001   2016-08-20  2016-08-20  创建
002   2016-08-20  2016-08-20  创建
003   2016-08-20  2016-08-20  创建
001   2016-08-20  2016-08-21  支付
002   2016-08-20  2016-08-21  完成
004   2016-08-21  2016-08-21  创建
001   2016-08-20  2016-08-22  完成
003   2016-08-20  2016-08-22  支付
004   2016-08-21  2016-08-22  支付
005   2016-08-22  2016-08-22  创建

拉链表通过添加状态的开始时间，结束时间来进行状态标记 start_date,end_date

我们希望看到拉链表数据为：

001  2016-08-20  2016-08-20  创建 2016-08-20  2016-08-20
001  2016-08-20  2016-08-21  支付 2016-08-21  2016-08-21
001  2016-08-20  2016-08-22  完成 2016-08-22  9999-12-31
002  2016-08-20  2016-08-20  创建 2016-08-20  2016-08-20
002  2016-08-20  2016-08-21  完成 2016-08-21  9999-12-31
003  2016-08-20  2016-08-20  创建 2016-08-20  2016-08-21
003  2016-08-20  2016-08-22  支付 2016-08-22  9999-12-31
004  2016-08-21  2016-08-21  创建 2016-08-21  2016-08-21
004  2016-08-21  2016-08-22  支付 2016-08-22  9999-12-31
005  2016-08-22  2016-08-22  创建 2016-08-22  9999-12-31

这样我们在查看2016-08-21这一天所有订单的状态时可以把条件写为start_date<='2016-08-21' and end_date => '2016-08-21'

 

下面介绍如何实现拉链表，以Hive为例：

第一步建表：

CREATE TABLE orders (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
) row format delimited fields terminated by '\t'
 
 
CREATE TABLE ods_orders_inc (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
) PARTITIONED BY (day STRING)
row format delimited fields terminated by '\t'
 
 
CREATE TABLE dw_orders_his (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING,
dw_start_date STRING,
dw_end_date STRING
) row format delimited fields terminated by '\t' ;

第二步历史数据全量接入：以时间节点为2016-08-20

INSERT overwrite TABLE ods_orders_inc PARTITION (day = '2016-08-20')
SELECT orderid,createtime,modifiedtime,status
FROM orders
WHERE createtime < '2016-08-21' and modifiedtime <'2016-08-21';

--刷新至拉链表中

INSERT overwrite TABLE dw_orders_his
SELECT orderid,createtime,modifiedtime,status,
createtime AS dw_start_date,
'9999-12-31' AS dw_end_date
FROM ods_orders_inc
WHERE day = '2016-08-20';

如下结果：

select * from dw_orders_his;
OK
001  2016-08-20  2016-08-20  创建 2016-08-20  9999-12-31
002  2016-08-20  2016-08-20  创建 2016-08-20  9999-12-31
003  2016-08-20  2016-08-20  创建 2016-08-20  9999-12-31

第三步接入增量：2016-08-21

INSERT overwrite TABLE ods_orders_inc PARTITION (day = '2016-08-21')
SELECT orderid,createtime,modifiedtime,status
FROM orders
WHERE (createtime = '2016-08-21'  and modifiedtime = '2016-08-21') OR modifiedtime = '2016-08-21';
 
select * from ods_orders_inc where day='2016-08-21';
OK
001  2016-08-20  2016-08-21  支付 2016-08-21
002  2016-08-20  2016-08-21  完成 2016-08-21
004  2016-08-21  2016-08-21  创建 2016-08-21

第四步拉链增量与历史数据：

逻辑解析：

• 1）判断历史数据是否更新状态，如果更新则将end_date改为前一天时间，无更新不用变
• 2）将增量数据union并把end_date写为「9999-12-31」表示为正在执行状态
• 3）重写原拉链表

 

DROP TABLE IF EXISTS dw_orders_his_tmp;
CREATE TABLE 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 > '2016-08-21' THEN '2016-08-20' ELSE a.dw_end_date END AS dw_end_date
    FROM dw_orders_his a
    left outer join (SELECT * FROM ods_orders_inc WHERE day = '2016-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 ods_orders_inc
    WHERE day = '2016-08-21'
    
) x
ORDER BY orderid,dw_start_date;
 
INSERT overwrite TABLE dw_orders_his
SELECT * FROM dw_orders_his_tmp;

按操作继续完成2016-08-22日的新增数据拉链可以看到如下结构：

select * from dw_orders_his;
OK
001  2016-08-20  2016-08-20  创建 2016-08-20  2016-08-20
001  2016-08-20  2016-08-21  支付 2016-08-21  2016-08-21
001  2016-08-20  2016-08-22  完成 2016-08-22  9999-12-31
002  2016-08-20  2016-08-20  创建 2016-08-20  2016-08-20
002  2016-08-20  2016-08-21  完成 2016-08-21  9999-12-31
003  2016-08-20  2016-08-20  创建 2016-08-20  2016-08-21
003  2016-08-20  2016-08-22  支付 2016-08-22  9999-12-31
004  2016-08-21  2016-08-21  创建 2016-08-21  2016-08-21
004  2016-08-21  2016-08-22  支付 2016-08-22  9999-12-31
005  2016-08-22  2016-08-22  创建 2016-08-22  9999-12-31

下一篇讨论拉链的其他实现方式和效率问题。