数仓--DW--Hadoop数仓实践Case-18-拉链表实践

拉链表在数仓的实际开发中应用广泛，切实解决优化存储
重点是对变化的数据进行统一管理，和缓慢变化维的处理还是不一样的。注意对比学习

拉链表概述

拉链表是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。
我们先看一个示例，这就是一张拉链表，存储的是用户的最基本信息以及每条记录的生命周期。我们可以使用这张表拿到最新的当天的最新数据以及之前的历史数据。

数仓拉链表示例.PNG
从上图中可以看出，用户的手机号码是会进行改变的。可以根据开始日期和结束日期进行判断。

拉链表使用场景

1. 数据量比较大;
1. 表中的部分字段会被update,如用户的地址，产品的描述信息，订单的状态等等;
1. 需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态，比如，查看某一个用户在过去某一段时间内，更新过几次等等;
1. 变化的比例和频率不是很大，比如，总共有1000万的会员，每天新增和发生变化的有10万左右;
1. 如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费;
拉链历史表，既能满足反应数据的历史状态，又可以最大程度的节省存储;
案例
在数据仓库的数据模型设计过程中，经常会遇到下面这种表的设计：
- 有一些表的数据量很大，比如一张用户表，大约10亿条记录，50个字段，这种表，即使使用ORC压缩，单张表的存储也会超过100G，在HDFS使用双备份或者三备份的话就更大一些。
- 表中的部分字段会被update更新操作，如用户联系方式，产品的描述信息，订单的状态等等。
- 需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态。
- 表中的记录变化的比例和频率不是很大，比如，总共有10亿的用户，每天新增和发生变化的有200万左右，变化的比例占的很小。

那么对于这种表我该如何设计呢？下面有几种方案可选:

方案一：每天只留最新的一份，比如我们每天用Sqoop抽取最新的一份全量数据到Hive中。
方案二：每天保留一份全量的切片数据。
方案三：使用拉链表。

如何设计拉链表

案例参考-漫谈数据仓库之拉链表（原理、设计以及在Hive中的实现）
我们先看一下在Mysql关系型数据库里的user表中信息变化。
在2017-01-01这一天表中的数据是：

注册日期	用户编号	手机号码
2017-01-01	001	111111
2017-01-01	002	222222
2017-01-01	003	333333
2017-01-01	004	444444

在2017-01-02这一天表中的数据是，用户002和004资料进行了修改，005是新增用户：

注册日期	用户编号	手机号码	备注
2017-01-01	001	111111
2017-01-01	002	233333	由222222变成2333333
2017-01-01	003	333333
2017-01-01	004	43434343	由444444变成43434343
2017-01-02	005	555555	2017-01-02新增

在2017-01-03这一天表中的数据是，用户004和005资料进行了修改，006是新增用户：

注册日期	用户编号	手机号码	备注
2017-01-01	001	111111
2017-01-01	002	233333	由222222变成2333333
2017-01-01	003	333333
2017-01-01	004	654321	由43434343变成654321
2017-01-02	005	511111	由555555变成511111
2017-01-03	006	666666	2017-01-03新增

如果在数据仓库中设计成历史拉链表保存该表，则会有下面这样一张表，这是最新一天（即2017-01-03）的数据：

拉链表设计.PNG

说明：

t_start_date表示该条记录的生命周期开始时间，t_end_date表示该条记录的生命周期结束时间。
t_end_date = '9999-12-31'表示该条记录目前处于有效状态。
如果查询当前所有有效的记录，则select * from user where t_end_date = '9999-12-31'。
如果查询2017-01-02的历史快照，则select * from user where t_start_date <= '2017-01-02' and t_end_date >= '2017-01-02'。（此处要好好理解，是拉链表比较重要的一块。）

Hive实现拉链表

只考虑实现，不考虑性能
-- 时间粒度：天 day，建模之前需要按照Kimball思想"四步走"战略
以订单表为例，数据如下，每天的订单明细：

orderid createtime modifiedtime status
1   2016-08-20  2016-08-20  创建
2   2016-08-20  2016-08-20  创建
3   2016-08-20  2016-08-20  创建
1   2016-08-20  2016-08-21  支付
2   2016-08-20  2016-08-21  完成
4   2016-08-21  2016-08-21  创建
1   2016-08-20  2016-08-22  完成
3   2016-08-20  2016-08-22  支付
4   2016-08-21  2016-08-22  支付
5   2016-08-22  2016-08-22  创建

根据拉链表，我们先想得到

1  2016-08-20  2016-08-20  创建 2016-08-20  2016-08-20
1  2016-08-20  2016-08-21  支付 2016-08-21  2016-08-21
1  2016-08-20  2016-08-22  完成 2016-08-22  9999-12-31
2  2016-08-20  2016-08-20  创建 2016-08-20  2016-08-20
2  2016-08-20  2016-08-21  完成 2016-08-21  9999-12-31
3  2016-08-20  2016-08-20  创建 2016-08-20  2016-08-21
3  2016-08-20  2016-08-22  支付 2016-08-22  9999-12-31
4  2016-08-21  2016-08-21  创建 2016-08-21  2016-08-21
4  2016-08-21  2016-08-22  支付 2016-08-22  9999-12-31
5  2016-08-22  2016-08-22  创建 2016-08-22  9999-12-31

可以看出 1，2，3，4每个订单的状态都有，并且也能统计到当前的有效状态。

初始化hive表

-- 创建订单表，使用sqoop进行从mysql中导入hive
CREATE TABLE orders (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
) row format delimited fields terminated by '\t'
 
 -- 订单全量，按天进行分区
CREATE TABLE ods_orders_inc (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
) PARTITIONED BY (day STRING)
row format delimited fields terminated by '\t'
 
 -- 订单拉链表设计
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 '\t' ;

首先全量更新，我们先到2016-08-20为止的数据。
初始化，先把2016-08-20的数据初始化进去。

INSERT overwrite TABLE ods_orders_inc PARTITION (day = '2016-08-20')
SELECT orderid,createtime,modifiedtime,status
FROM orders
WHERE createtime < '2016-08-21' and modifiedtime <'2016-08-21';

刷新到dw中；

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 = '2016-08-20';

结果测试如下：

select * from dw_orders_his;
OK
1  2016-08-20  2016-08-20  创建 2016-08-20  9999-12-31
2  2016-08-20  2016-08-20  创建 2016-08-20  9999-12-31
3  2016-08-20  2016-08-20  创建 2016-08-20  9999-12-31

生于需要增量更新：

INSERT overwrite TABLE ods_orders_inc PARTITION (day = '2016-08-21')
SELECT orderid,createtime,modifiedtime,status
FROM orders
WHERE (createtime = '2016-08-21'  and modifiedtime = '2016-08-21') OR modifiedtime = '2016-08-21';
 
select * from ods_orders_inc where day='2016-08-21';
OK
1  2016-08-20  2016-08-21  支付 2016-08-21
2  2016-08-20  2016-08-21  完成 2016-08-21
4  2016-08-21  2016-08-21  创建 2016-08-21

先放到增量表中，然后进行关联到一张临时表中，在插入到新表中，（同样，可以采用其他语句进行实现）：

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 > '2016-08-21' THEN '2016-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 = '2016-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 = '2016-08-21'
) x
ORDER BY orderid,dw_start_date;
 -- 将数据插入到拉链表中
INSERT overwrite TABLE dw_orders_his
SELECT * FROM dw_orders_his_tmp;

在根据上面步骤把2016-08-22号的数据更新进去，最后结果如下：

select * from dw_orders_his;
OK
1  2016-08-20  2016-08-20  创建 2016-08-20  2016-08-20
1  2016-08-20  2016-08-21  支付 2016-08-21  2016-08-21
1  2016-08-20  2016-08-22  完成 2016-08-22  9999-12-31
2  2016-08-20  2016-08-20  创建 2016-08-20  2016-08-20
2  2016-08-20  2016-08-21  完成 2016-08-21  9999-12-31
3  2016-08-20  2016-08-20  创建 2016-08-20  2016-08-21
3  2016-08-20  2016-08-22  支付 2016-08-22  9999-12-31
4  2016-08-21  2016-08-21  创建 2016-08-21  2016-08-21
4  2016-08-21  2016-08-22  支付 2016-08-22  9999-12-31
5  2016-08-22  2016-08-22  创建 2016-08-22  9999-12-31

至此，得到了我们想要的数据。

拉链表延申补充

流水表与拉链表的区别

流水表存放的是一个用户的变更记录，比如在一张流水表中，一天的数据中，会存放一个用户的每条修改记录，但是在拉链表中只有一个记录。
这是拉链表设计时需要注意的一个粒度问题。我们当然可以设置的粒度更小一些，一般按天足够了。

查询性能

拉链表当然会遇到查询性能的问题。比如我们存放了5年的拉链数据，那么这张表势必会比较大，当查询的时候性能就比较低了，个人认为有两种解决思路：

1.在一些查询引擎中，我们对start_date和end_date做索引，这样能提升不少性能。这种方法其实在hive中行不通，因为hive相当于没有索引，不过在其他系统中可以考虑。
2.保留部分历史数据，比如说我们一张表里面存放全量的拉链表数据，然后再对外暴露一张只提供近3个月数据的拉链表。

淘汰机制

关于淘汰机制，其实和性能也是有关系的，一方面是因为所有数据的积累会导致计算越来越慢，另一方面是业务侧其实对历史数据的需求也有一定的优先级的。
因此再设计拉链表的时候可以制定一些数据的淘汰机制。淘汰的数据不一定要删除，比如我们建立两张拉链表，一张拉链表中只保存最新的十条数据，其他的数据会保存入一张历史拉链表中。

其他心得

1.使用拉链表的时候可以不加t_end_date,即失效日期，但是加上之后，能优化很多查询。
2.可以加上当前行状态标识，能快速定位到当前状态，
3.在拉链表的设计中可以加一些内容，因为我们每天保存一个状态，如果我们在这个状态里面加一个字段，比如当天修改次数，那么拉链表的作用会恒大。

参考博客：
https://www.jianshu.com/p/799252156379 漫谈数据仓库之拉链表（原理、设计以及在Hive中的实现）推荐大家好好的读一下木东居士的文章
http://www.cnblogs.com/wujin/p/6121754.html hive中拉链表
http://lxw1234.com/archives/2015/08/473.htm 数据仓库中历史拉链表的更新方法