拉链表

在数据仓库的数据模型设计过程中，经常会遇到这样的需求：
1. 数据量比较大;
2. 表中的部分字段会被update,如用户的地址，产品的描述信息，订单的状态等等;
3. 需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态，
比如，查看某一个用户在过去某一段时间内，更新过几次等等;
4. 变化的比例和频率不是很大，比如，总共有1000万的会员，每天新增和发生变化的有10万左右;
5. 如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费; 拉链历史表，既能满足反应数据的历史状态，又可以最大程度的节省存储;
举个简单例子，比如有一张订单表,6月20号有3条记录：
到6月21日，表中有5条记录：
到6月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 end_date >= '2012-06-21'，这条语句会查询到以下记录：
和源表在6月21日的记录完全一致：
可以看出，这样的历史拉链表，既能满足对历史数据的需求，又能很大程度的节省存储资源；
数据仓库中历史拉链表的更新方法
关键字：数据仓库、极限存储、历史拉链表、更新
在之前介绍过数据仓库中的历史拉链表《数据仓库数据模型之：极限存储–历史拉链表》，
使用这种方式即可以记录历史，而且最大程度的节省存储。

这里简单介绍一下这种历史拉链表的更新方法。

本文中假设：
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订单历史表中数据如下：
华丽的分割线：您可以关注lxw的大数据田地，或者加入邮件列表，随时接收博客更新的通知邮件。

增量抽取
每天，从源系统订单表中，将前一天的增量数据抽取到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的数据如下：
第二步，通过DW历史数据(数据日期为2015-08-20)，和ODS增量数据(2015-08-21)，刷新历史表：
先把数据放到一张临时表中：
其中：
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;
华丽的分割线：您可以关注lxw的大数据田地，或者加入邮件列表，随时接收博客更新的通知邮件。

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

再看将2015-08-22的增量数据刷新到历史表：
刷新完后历史表数据如下：
华丽的分割线：您可以关注lxw的大数据田地，或者加入邮件列表，随时接收博客更新的通知邮件。

查看2015-08-21的历史快照数据：
订单1在2015-08-21的时候还处于创建的状态，在2015-08-22的时候状态变为支付。

再刷新2015-08-23的增量数据：
按照上面的方法刷新完后，历史表数据如下：
订单1从20号-23号，状态变化了三次，历史表中有三条记录。

实际业务中，有可能某一天的数据有问题，需要回滚或重做，这点有点麻烦，后续文章再介绍。

数据仓库数据模型之：极限存储–历史拉链表
在数据仓库的数据模型设计过程中，经常会遇到这样的需求：
1.数据量比较大;
2. 表中的部分字段会被update,如用户的地址，产品的描述信息，订单的状态等等;
3. 需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一
个时间点的状态，
比如，查看某一个用户在过去某一段时间内，更新过几次等等;
4. 变化的比例和频率不是很大，比如，总共有1000万的会员，每天新增和发生变化的有
10万左右;
5. 如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是
极大的浪费;拉链历史表，既能满足反应数据的历史状态，又可以最大程度的节省存储;举个简单例子，比如有一张订单表,6月20号有3条记录：
订单创建日期订单编号订单状态
2012-06-20 001 创建订单
2012-06-20 002 创建订单
2012-06-20 003 支付完成
到6月21日，表中有5条记录：
订单创建日期订单编号订单状态
2012-06-20 001 支付完成（从创建到支付）2012-06-20 002 创建订单
2012-06-20 003 支付完成
2012-06-21 004 创建订单
2012-06-21 005 创建订单
到6月22日，表中有6条记录：
订单创建日期订单编号订单状态
2012-06-20 001 支付完成（从创建到支付）2012-06-20 002 创建订单
2012-06-20 003 已发货（从支付到发货）
2012-06-21 004 创建订单
2012-06-21 005 支付完成（从创建到支付）2012-06-22 006 创建订单
数据仓库中对该表的保留方法：
1.只保留一份全量，则数据和6月22日的记录一样，如果需要查看6月21日订单001的
状态，则无法满足；
2.每天都保留一份全量，则数据仓库中的该表共有14条记录，但好多记录都是重复保存，
没有任务变化，如订单002,004，数据量大了，会造成很大的存储浪费；
如果在数据仓库中设计成历史拉链表保存该表，则会有下面这样一张表：
订单创建日期订单编号订单状态dw_begin_date dw_end_date 2012-06-20 001 创建订单2012-06-20 2012-06-20 2012-06-20 001 支付完成2012-06-21 9999-12-31 2012-06-20 002 创建订单2012-06-20 9999-12-31 2012-06-20 003 支付完成2012-06-20 2012-06-21
2012-06-20 003 已发货2012-06-22 9999-12-31 2012-06-21 004 创建订单2012-06-21 9999-12-31 2012-06-21 005 创建订单2012-06-21 2012-06-21 2012-06-21 005 支付完成2012-06-22 9999-12-31 2012-06-22 006 创建订单2012-06-22 9999-12-31
说明：
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 end_date >= ‘2012-06-21’，这条语句会查询到以下记录：
订单创建日期订单编号订单状态dw_begin_date dw_end_date 2012-06-20 001 支付完成2012-06-21 9999-12-31 2012-06-20 002 创建订单2012-06-20 9999-12-31
2012-06-20 003 支付完成2012-06-20 2012-06-21 2012-06-21 004 创建订单2012-06-21 9999-12-31 2012-06-21 005 创建订单2012-06-21 2012-06-21
和源表在6月21日的记录完全一致：
订单创建日期订单编号订单状态
2012-06-20 001 支付完成（从创建到支付）2012-06-20 002 创建订单
2012-06-20 003 支付完成
2012-06-21 004 创建订单
2012-06-21 005 创建订单
可以看出，这样的历史拉链表，既能满足对历史数据的需求，又能很大程度的节省存储资源；
关于这种历史拉链表的etl刷新策略和方法，下次再谈吧。