数据仓库-拉链表

在数据仓库的数据模型设计过程中，经常会遇到这样的需求：

1.数据量比较大;

2.表中的部分字段会被update,如用户的地址，产品的描述信息，订单的状态等等;

3.需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态， 比如，查看某一个用户在过去某一段时间内，更新过几次等等;

4.变化的比例和频率不是很大，比如，总共有1000万的会员，每天新增和发生变化的有10万左右;

5.如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费;遇到些问题在没解决吗？

拉链历史表，既能满足反应数据的历史状态，又可以最大程度的节省存储。

拉链表案例：

1、有一张订单表，2019-06-20 这天里面有三条订单数据：

到了2019-06-21这天，表中有5条记录：

到了2019-06-22日，表中有6条记录：

这种存储方式有什么问题呢？

1、只保留一份全量，则数据和6月22日的记录一样，如果需要查看6月21日订单001的状态，则无法满足；

2、每天都保留一份全量，则数据仓库中的该表共有14条记录，但好多记录都是重复保存，没有任务变化，如订单002,004，数据量大了，会造成很大的存储浪费；

如果设计成历史拉链表，如下：

拉链表解释：

说明：

1.dw_begin_date表示该条记录的生命周期开始时间，dw_end_date表示该条记录的生命周期结束时间；

2.dw_end_date = '9999-12-31'表示该条记录目前处于有效状态；

3.如果查询当前所有有效的记录，则select * from order_his where dw_end_date = '9999-12-31'

4.如果查询2012-06-21的历史快照，则select * from order_his where dw_begin_date <= '2012-06-21' and dw_end_date >= '2012-06-21'，这条语句会查询到以下记录：

可以看出，这样的历史拉链表，既能满足对历史数据的需求，又能很大程度的节省存储资源；

拉链表实战：

源系统中订单表结构为：

<code>CREATE TABLE orders_20190821 (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
stored AS textfile;

CREATE TABLE orders_20190822 (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
stored AS textfile;

CREATE TABLE orders_20190823 (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
stored AS textfile;/<code>

数据：

<code>2019-08-21订单数据表(order)
1,2019-08-18,2019-08-18,创建
2,2019-08-18,2019-08-18,创建
3,2019-08-19,2019-08-21,支付
4,2019-08-19,2019-08-21,完成
5,2019-08-19,2019-08-20,支付
6,2019-08-20,2019-08-20,创建
7,2019-08-20,2019-08-21,支付
8,2019-08-21,2019-08-21,创建

2019-08-22订单数据表(order)
*1,2019-08-18,2019-08-22,支付 

*2,2019-08-18,2019-08-22,完成
3,2019-08-19,2019-08-21,支付
4,2019-08-19,2019-08-21,完成
5,2019-08-19,2019-08-20,支付
*6,2019-08-20,2019-08-22,支付
7,2019-08-20,2019-08-21,支付
*8,2019-08-21,2019-08-22,支付
*9,2019-08-22,2019-08-22,创建
*10,2019-08-22,2019-08-22,支付


2019-08-23订单数据表
1,2019-08-18,2019-08-23,完成
2,2019-08-18,2019-08-22,完成
3,2019-08-19,2019-08-23,完成
4,2019-08-19,2019-08-21,完成
5,2019-08-19,2019-08-23,完成
6,2019-08-20,2019-08-22,支付
7,2019-08-20,2019-08-21,支付
8,2019-08-21,2019-08-23,完成
9,2019-08-22,2019-08-22,创建
10,2019-08-22,2019-08-22,支付
11,2019-08-23,2019-08-23,创建
12,2019-08-23,2019-08-23,创建
13,2019-08-23,2019-08-23,支付/<code>

<code>load data  inpath '/user/root/csii/orders_20190821' into table orders_20190821;
load data  inpath '/user/root/csii/orders_20190822' into table orders_20190822;
load data  inpath '/user/root/csii/orders_20190823' into table orders_20190823;/<code>

ODS层设计：

在数据仓库的ODS层，有一张订单的增量数据表，按天分区，存放每天的增量数据：

<code>CREATE TABLE ods_orders_inc (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
)PARTITIONED BY (day STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
stored AS textfile;/<code> 

DW层设计：

在数据仓库的DW层，有一张订单的历史数据拉链表，存放订单的历史状态数据：

<code>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 ','
stored AS textfile;/<code>

第一步，抽取全量数据到ODS：

<code>INSERT overwrite TABLE ods_orders_inc PARTITION (day = '2019-08-20')
SELECT orderid,createtime,modifiedtime,status
FROM orders_20190821
WHERE createtime <= '2019-08-20';/<code>

第二步，从ODS刷新到DW：

<code>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 = '2019-08-20';/<code>

完成后，DW订单历史表中数据:

<code>hive> select * from dw_orders_his;
1       2019-08-18      2019-08-18      创建    2019-08-18      9999-12-31      
2       2019-08-18      2019-08-18      创建    2019-08-18      9999-12-31
3       2019-08-19      2019-08-21      支付    2019-08-19      9999-12-31
4       2019-08-19      2019-08-21      完成    2019-08-19      9999-12-31
5       2019-08-19      2019-08-20      支付    2019-08-19      9999-12-31
6       2019-08-20      2019-08-20      创建    2019-08-20      9999-12-31
7       2019-08-20      2019-08-21      支付    2019-08-20      9999-12-31/<code> 

增量抽取

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

<code>INSERT overwrite TABLE ods_orders_inc PARTITION (day = '${day}')
SELECT orderid,createtime,modifiedtime,status
FROM orders
WHERE createtime = '${day}' OR modifiedtime = '${day}';/<code>

增量刷新历史数据

从2019-08-22开始，需要每天正常刷新前一天（2019-08-21）的增量数据到历史表。

第一步，通过增量抽取，将2019-08-21的数据抽取到ODS：

<code>INSERT overwrite TABLE ods_orders_inc PARTITION (day = '2019-08-21')
SELECT orderid,createtime,modifiedtime,status
FROM orders_20190821
WHERE modifiedtime = '2019-08-21' or createtime = '2019-08-21';/<code>

ODS增量表中2019-08-21的数据如下：

<code>hive> select * from ods_orders_inc where day = '2019-08-21';
3       2019-08-19      2019-08-21      支付    2019-08-21
4       2019-08-19      2019-08-21      完成    2019-08-21
7       2019-08-20      2019-08-21      支付    2019-08-21
8       2019-08-21      2019-08-21      创建    2019-08-21/<code>

第二步，通过DW历史数据(数据日期为2019-08-20)，和ODS增量数据(2019-08-21)

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

<code>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 > '2019-08-21' THEN '2019-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 = '2019-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 = '2019-08-21' 
) x 
ORDER BY orderid,dw_start_date;/<code>

最后把临时表中数据插入历史表：

<code>INSERT overwrite TABLE dw_orders_his
SELECT * FROM dw_orders_his_tmp;/<code>

查看拉链表数据：

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

<code>第一步，通过增量抽取，将2019-08-22的数据抽取到ODS：

INSERT overwrite TABLE ods_orders_inc PARTITION (day = '2019-08-22') 
SELECT orderid,createtime,modifiedtime,status 
FROM orders_20190822
WHERE createtime = '2019-08-22' OR modifiedtime = '2019-08-22';

第二步，通过DW历史数据(数据日期为2019-08-21)，和ODS增量数据(2019-08-22)
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 > '2019-08-22' THEN '2019-08-21' 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 = '2019-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 ods_orders_inc 
    WHERE day = '2019-08-22' 
) x 
ORDER BY orderid,dw_start_date;
 
第三步：最后把临时表中数据插入历史表： 

INSERT overwrite TABLE dw_orders_his 
SELECT * FROM dw_orders_his_tmp;/<code>

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

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

<code>select * from dw_orders_his where dw_start_date <= '2019-08-21' and dw_end_date >= '2019-08-21';/<code>

将2019-08-23的增量数据刷新到历史表:

<code>第一步，通过增量抽取，将2019-08-22的数据抽取到ODS：

INSERT overwrite TABLE ods_orders_inc PARTITION (day = '2019-08-23') 
SELECT orderid,createtime,modifiedtime,status 
FROM orders_20190823
WHERE createtime = '2019-08-23' OR modifiedtime = '2019-08-23';

第二步，通过DW历史数据(数据日期为2019-08-22)，和ODS增量数据(2019-08-23)
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 > '2019-08-23' THEN '2019-08-22' 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 = '2019-08-23') 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 = '2019-08-23' 
) x 
ORDER BY orderid,dw_start_date;

第三步：最后把临时表中数据插入历史表： 

INSERT overwrite TABLE dw_orders_his 
SELECT * FROM dw_orders_his_tmp;/<code>

<code>hive> select * from dw_orders_his order by orderid,dw_start_date;
1       2019-08-18      2019-08-18      创建    2019-08-18      2019-08-21
1       2019-08-18      2019-08-22      支付    2019-08-22      2019-08-22
1       2019-08-18      2019-08-23      完成    2019-08-23      9999-12-31
2       2019-08-18      2019-08-18      创建    2019-08-18      2019-08-21
2       2019-08-18      2019-08-22      完成    2019-08-22      9999-12-31
3       2019-08-19      2019-08-21      支付    2019-08-19      2019-08-20
3       2019-08-19      2019-08-21      支付    2019-08-21      2019-08-22
3       2019-08-19      2019-08-23      完成    2019-08-23      9999-12-31
4       2019-08-19      2019-08-21      完成    2019-08-19      2019-08-20
4       2019-08-19      2019-08-21      完成    2019-08-21      9999-12-31
5       2019-08-19      2019-08-20      支付    2019-08-19      2019-08-22
5       2019-08-19      2019-08-23      完成    2019-08-23      9999-12-31
6       2019-08-20      2019-08-20      创建    2019-08-20      2019-08-21
6       2019-08-20      2019-08-22      支付    2019-08-22      9999-12-31
7       2019-08-20      2019-08-21      支付    2019-08-20      2019-08-20
7       2019-08-20      2019-08-21      支付    2019-08-21      9999-12-31
8       2019-08-21      2019-08-21      创建    2019-08-21      2019-08-21
8       2019-08-21      2019-08-22      支付    2019-08-22      2019-08-22
8       2019-08-21      2019-08-23      完成    2019-08-23      9999-12-31
9       2019-08-22      2019-08-22      创建    2019-08-22      9999-12-31
10      2019-08-22      2019-08-22      支付    2019-08-22      9999-12-31
11      2019-08-23      2019-08-23      创建    2019-08-23      9999-12-31
12      2019-08-23      2019-08-23      创建    2019-08-23      9999-12-31
13      2019-08-23      2019-08-23      支付    2019-08-23      9999-12-31/<code>

查看最新的状态：

<code>hive> select * from dw_orders_his where dw_end_date = '9999-12-31';   
/<code>

閱讀更多 會鹹的魚 的文章

關鍵字: 数据模型 数据仓库 设计