数据仓库中历史拉链表的更新方法

 

使用这种方式即可以记录历史，而且最大程度的节省存储。这里简单介绍一下这种历史拉链表的更新方法。

本文中假设：

1. 数据仓库中订单历史表的刷新频率为一天，当天更新前一天的增量数据；

2. 如果一个订单在一天内有多次状态变化，则只会记录最后一个状态的历史；

3. 订单状态包括三个：创建、支付、完成；

4. 创建时间和修改时间只取到天，如果源订单表中没有状态修改时间，那么抽取增量就比较麻烦，需要有个机制来确保能抽取到每天的增量数据；

5. 本文中的表和SQL都使用Hive的HQL语法；

6. 源系统中订单表结构为：

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

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

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

8. 在数据仓库的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;

9. 暂未考虑Hive上表的查询性能问题，只实现功能；

华丽的分割线：您可以关注  lxw的大数据田地 ，或者  加入邮件列表 ，随时接收博客更新的通知邮件。 

 

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

全量初始化 

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

1. spark-sql> select * from t_dw_orders_his;

2. 1       2015-08-18      2015-08-18      创建    2015-08-18      9999-12-31      

3. 2       2015-08-18      2015-08-18      创建    2015-08-18      9999-12-31

4. 3       2015-08-19      2015-08-21      支付    2015-08-19      9999-12-31

5. 4       2015-08-19      2015-08-21      完成    2015-08-19      9999-12-31

6. 5       2015-08-19      2015-08-20      支付    2015-08-19      9999-12-31

7. 6       2015-08-20      2015-08-20      创建    2015-08-20      9999-12-31

8. 7       2015-08-20      2015-08-21      支付    2015-08-20      9999-12-31

9. Time taken: 2.296 seconds, Fetched 7 row(s)

华丽的分割线：您可以关注  lxw的大数据田地 ，或者  加入邮件列表 ，随时接收博客更新的通知邮件。 

 

增量抽取 

每天，从源系统订单表中，将前一天的增量数据抽取到ODS层的增量数据表。
这里的增量需要