数据仓库之拉链表（二）使用拉链表更新数据

关注

转载最新推荐文章于 2026-05-19 10:03:31 发布 1.8k 阅读 AI 写同款· GEO 优化 ›

图解数据仓库之拉链表，超简单！图解数据仓库之拉链表，超简单！ 1.背景分析 2.图解拉链表形成过程 1）下图为业务后台数据库中每日的数据变化情况，每天只保留客户最新手机号码。 2）根据最后修改时间字段从业务后台数据库抽取最新被修改数据，形成分区表。 3.过程分析 4.评价写在最后 ... 阅读详情

前提假设：

数据仓库中订单历史表的刷新频率为一天，当天更新前一天的增量数据；
如果一个订单在一天内有多次状态变化，则只会记录最后一个状态的历史；
订单状态包括三个：创建、支付、完成
创建时间和修改时间只取到天，如果原订单表中没有状态修改时间，那么抽取增量就比较麻烦，需要有个机制来确保能抽取到每天的增量数据；
原系统中订单表结构为：
CREATE TABLE orders (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
) stored AS textfile;
在数据仓库的ODS层，有一张订单的增量数据表，按天分区，存放每天的增量数据：
CREATE TABLE t_ods_orders_inc (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
) PARTITIONED BY (day STRING)
stored AS textfile;
在数据仓库的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;
暂未考虑Hive上表的查询性能问题，只实现功能；

每天的原系统订单表的数据如下（红色标出的为当天发生变化的订单，即增量数据）：
2015-08-21订单表数据：

订单编号	订单创建时间	订单修改时间	订单状态
orderid	createtime	modifiedtime	status
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	创建

2015-08-22订单表数据：

订单编号	订单创建时间	订单修改时间	订单状态
orderid	createtime	modifiedtime	status
1	2015-08-18	2015-08-22	支付
2	2015-08-18	2015-08-22	完成
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-22	支付
7	2015-08-20	2015-08-21	支付
8	2015-08-21	2015-08-22	支付
9	2015-08-22	2015-08-22	创建
10	2015-08-22	2015-08-22	支付

2015-08-23订单表数据：

订单编号	订单创建时间	订单修改时间	订单状态
orderid	createtime	modifiedtime	status
1	2015-08-18	2015-08-23	完成
2	2015-08-18	2015-08-22	完成
3	2015-08-19	2015-08-23	完成
4	2015-08-19	2015-08-21	完成
5	2015-08-19	2015-08-23	完成
6	2015-08-20	2015-08-22	支付
7	2015-08-20	2015-08-21	支付
8	2015-08-21	2015-08-23	完成
9	2015-08-22	2015-08-22	创建
10	2015-08-22	2015-08-22	支付
11	2015-08-23	2015-08-23	创建
12	2015-08-23	2015-08-23	创建
13	2015-08-23	2015-08-23	支付

全量初始化

在数据从原业务系统每天正常抽取和刷新到dw订单历史表之前，需要做一次全量的初始化，就是从原订单表中昨天以前的数据全部抽取到ods，并刷新到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订单历史表中数据如下：

<span style="color:#000000"><code>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
</code></span>

增量抽取

每天，从原系统订单表中，将前一天的增量数据抽取到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的数据如下：

<span style="color:#000000"><code>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
</code></span>

第二步，通过dw历史数据（数据日期为2015-08-20）和ods增量数据（2015-08-21）刷新历史表：
先把数据放到一张临时表中：

<span style="color:#000000"><code>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
    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;
</code></span>

其中：
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;

刷新完后，历史表中数据如下：

<span style="color:#000000"><code>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
</code></span>

由于在2015-08-21做了8月20日以前的数据全量初始化，而订单3，4，7在2015-08-21的增量数据中也存在，因此都有两条记录，但不影响后面的查询。

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

<span style="color:#000000"><code>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;
</code></span>

刷新完成后历史拉链表数据如下：

<span style="color:#000000"><code>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
</code></span>

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

<span style="color:#000000"><code>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
</code></span>

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

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

<span style="color:#000000"><code>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
</code></span>

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

<span style="color:#000000"><code>//查看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</code></span>

收录于