拉链表的应用

数据仓库和事务数据库不一样的地方，在于数据仓库需要对历史数据进行分析。而在数据仓库海量的数据里，如何既节约存储空间，又能满足对历史变更数据的查询，就成为一个要解决的问题。这时候就可以使用拉链表来完成这种需求。

什么是拉链表

以订单这种场景为例，原始订单表orders有如下几个字段：订单ID、创建时间、修改时间、订单状态

4月1号当天的订单

orderid	createtime	modifiedtime	status
1	2018-04-01	2018-04-01	create
2	2018-04-01	2018-04-01	create
3	2018-04-01	2018-04-01	create

4月2号当天的订单表数据

orderid	createtime	modifiedtime	status
1	2018-04-01	2018-04-02	paid
2	2018-04-01	2018-04-02	finish-closed
3	2018-04-01	2018-04-02	paid
4	2018-04-02	2018-04-02	create

这一天，订单1、3由create变成paid状态，订单2由create变为paid，然后变为finish状态(假设现在只关心订单的最新状态)，订单4为新订单。

这时候，在数据仓库中，可以设计一张表来保存每天的订单变化情况。增加两个字段：dw_start_date和dw_end_date，dw_start_date表示该条记录的生命周期开始时间，dw_end_date表示该条记录的生命周期结束时间。现在表就变成了下面这个样子：

orderid	createtime	modifiedtime	status	dw_start_date	dw_end_date
1	2018-04-01	2018-04-01	create	2018-04-01	2018-04-01
1	2018-04-01	2018-04-02	paid	2018-04-02	9999-12-31
2	2018-04-01	2018-04-01	create	2018-04-01	2018-04-01
2	2018-04-01	2018-04-02	finish-closed	2018-04-02	9999-12-31
3	2018-04-01	2018-04-01	create	2018-04-01	2018-04-01
3	2018-04-01	2018-04-02	paid	2018-04-02	9999-12-31
4	2018-04-02	2018-04-02	create	2018-04-02	9999-12-31

这个表就叫拉链表 orders_his

如果要查询当前有效的记录，那么如下：

1 2	SELECT * FROM orders_his WHERE dw_end_date='9999-12-31';

如果要查询4月1号的历史快照，如下：

1 2	SELECT * FROM orders_his WHERE dw_start_date <= '2018-04-01' AND dw_end_date >= '2018-04-01'

实现拉链表

准备

现在有三张表：原系统订单表orders、数据仓库ODS层增量数据表orders_inc、数据仓库DW层拉链表orders_his

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

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

CREATE TABLE orders_his (
    orderid INT,
    createtime STRING,
    modifiedtime STRING,
    status STRING,
    dw_start_date STRING,
    dw_end_date STRING
) stored AS textfile;

其中，增量表可以有如下几个方式获取：

监控MySQL的数据变化，例如使用Canal、或者ChangeDataCapture，获取最后一条更新的数据
每天获取一份切片数据，然后对比两天的切片数据获得增量
源数据库的流水表，例如XXX_log等

下面是如何实现拉链表的步骤

1. 全量初始化

假设数据仓库从4月1号起启用，那么首先需要在4月1号这一天做全量初始化，需要将4月1号以前(含)的所有数据都抽取并刷新到数据仓库中。

INSERT overwrite TABLE orders_inc PARTITION (day = '2018-04-01')
SELECT orderid, createtime, modifiedtime, status
FROM orders
WHERE createtime <= '2018-04-01';

然后从ODS层抽取到DW层：

INSERT overwrite TABLE orders_his
SELECT orderid,c reatetime, modifiedtime, status,
    createtime AS dw_start_date,
    '9999-12-31' AS dw_end_date
FROM orders_inc
WHERE day = '2018-04-01';

到此，全量初始化的工作完成，现在DW拉链表的数据如下，

orderid	createtime	modifiedtime	status	dw_start_date	dw_end_date
1	2018-04-01	2018-04-01	create	2018-04-01	9999-12-31
2	2018-04-01	2018-04-01	create	2018-04-01	9999-12-31
3	2018-04-01	2018-04-01	create	2018-04-01	9999-12-31

2. 增量抽取

4月2号了，

从源系统订单表中，将前一天的增量数据抽取到ODS层的增量数据表。这里的增量数据使用订单表中的创建时间和修改时间来确定。

note：订单表中数据同一天有多次状态更新，取每天的最后一个状态为当天的最终状态

-- ${day} = '2018-04-02'
INSERT overwrite TABLE orders_inc PARTITION (day = '${day}')
SELECT orderid, createtime, modifiedtime, status
FROM orders
WHERE createtime = '${day}' OR modifiedtime = '${day}';

3. 放到拉链表中

先将增量数据放到临时表中，然后插入DW拉链表

DROP TABLE IF EXISTS orders_his_tmp;  
CREATE TABLE orders_his_tmp AS  
SELECT orderid,  
    createtime,  
    modifiedtime,  
    status,  
    dw_start_date,  
    dw_end_date  
FROM ( 
    -- updated order 
    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 '2018-04-01' 
             ELSE a.dw_end_date 
        END AS dw_end_date  
    FROM orders_his a  
    LEFT JOIN (SELECT * FROM orders_inc WHERE day = '2018-04-02') b  
    ON (a.orderid = b.orderid)  
    
    UNION ALL  
    -- new order
    SELECT orderid,  
        createtime,  
        modifiedtime,  
        status,  
        modifiedtime AS dw_start_date,  
        '9999-12-31' AS dw_end_date  
    FROM orders_inc  
    WHERE day = '2018-04-02'  
) x  
ORDER BY orderid,dw_start_date;  
   
INSERT overwrite TABLE dw_orders_his  
SELECT * FROM orders_his_tmp;

拉链表就是如下了：

orderid	createtime	modifiedtime	status	dw_start_date	dw_end_date
1	2018-04-01	2018-04-01	create	2018-04-01	2018-04-01
1	2018-04-01	2018-04-02	paid	2018-04-02	9999-12-31
2	2018-04-01	2018-04-01	create	2018-04-01	2018-04-01
2	2018-04-01	2018-04-02	finish-closed	2018-04-02	9999-12-31
3	2018-04-01	2018-04-01	create	2018-04-01	2018-04-01
3	2018-04-01	2018-04-02	paid	2018-04-02	9999-12-31
4	2018-04-02	2018-04-02	create	2018-04-02	9999-12-31

查询性能优化

数据仓库随着时间发展，拉链表也会越来越大，为了查询性能不会受影响，需要定期对拉链表中历史数据进行归档。另外，除了增加两个起始时间和结束时间外，还可以增加一个当前行状态字段isActive字段，可以迅速找到可用的状态行。

参考

下面的一些思路参考木东居士

使用拉链表的时候可以不加t_end_date，即失效日期，但是加上之后，能优化很多查询。

可以加上当前行状态标识，能快速定位到当前状态。

在拉链表的设计中可以加一些内容，因为我们每天保存一个状态，如果我们在这个状态里面加一个字段，比如当天修改次数，那么拉链表的作用就会更大。