数据仓库：拉链表 | VinxC's blog

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

拉链表的使用场景

在数据仓库的模型设计过程中，经常会遇到下面这种表的设计：

有一些表的数据量很大，比如一张用户表，大约10亿条记录，50个字段。这种表，及即使用ORC压缩，单张表的存储也会超过100G，在HDFS上使用双备份或三备份的话，所占用的看空间会更大。

表中的部分字段会被update更新操作，如用户联系方式，产品描述信息，订单状态等。

需要查看某一个时间点或时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态。

表中的记录变化的比例和频率不是很大，比如，总共有10亿的用户，每天新增和发生变化的有200万左右，变化的比例占的很小。

那么，对于这种表该如何设计呢，下面有几种方案：

方案二：每天保留一份全量的切片数据

方案三：使用拉链表

为什么使用拉链表

方案一：

实现起来简单，每天drop掉前一天的数据，重新抽一份最新的。

优点：节省空间，一些普通的使用也很方便，不用在选择表的时候加一个时间分区什么的。

缺点：没有历史数据，想翻翻旧账只能通过其它方式，比如从流水表里面抽。

每天一份全量的切片是一种比较稳妥的方案，而且历史数据也在。

缺点：存储空间占用量太大，如果每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费。

当然也可以做一些取舍，比如只保留近一个月的数据。但是，需求是无耻的，数据的生命周期不是我们能完全左右的，你会发现，存储周期可能会从30天变为90天，然后再从90天变为1年，然后需要永久保存。

拉链表在使用上基本兼顾了我们的需求。

首先它在空间上做了一个取舍，虽说不像方案一那样占用量那么小，但是它每天的增量可能只有方案二的千分之一，甚至是万分之一。

它能满足方案二的需求，既能获取最新的数据，也能添加筛选条件获取历史数据，所以在一些场景下，拉链表是能解决很多问题的。

拉链表的设计和实现

如何设计一张拉链表

以用户资料表为例，我们先看一下在关系型数据库里的user表中的信息变化。

在2017-09-01这一天表中的数据是：

register_date user_id phone_number 2017-09-01 13620321111 2017-09-01 13620321112 2017-09-01 13620321114 2017-09-01 13620321115

在2017-09-02这一天表中的数据是，用户0002和0004的资料进行了修改，0005是新增用户：

register_date user_id phone_number remark 2017-09-01 13620321111 2017-09-01 13620321113 13620321112 => 13620321113 2017-09-01 13620321114 2017-09-01 13620321116 13620321115 => 13620321116 2017-09-02 13620321118 2017-09-02新增

在2019-09-03这一天表中的数据是，用户0004和0005资料进行了修改，0006是新增用户：

register_date user_id phone_number remark 2017-09-01 13620321111 2017-09-01 13620321113 2017-09-01 13620321114 2017-09-01 13620321117 13620321116 => 13620321117 2017-09-02 13620321119 13620321118 = > 13620321119 2017-09-03 13620321110 2017-09-03新增

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

register_date user_id phone_number t_start_date t_end_date 2017-09-01 13620321111 2017-09-01 9999-12-31 2017-09-01 13620321112 2017-09-01 2017-09-01 2017-09-01 13620321113 2017-09-02 9999-12-31 2017-09-01 13620321114 2017-09-01 9999-12-31 2017-09-01 13620321115 2017-09-01 2017-09-01 2017-09-01 13620321116 2017-09-02 2017-09-02 2017-09-01 13620321117 2017-09-03 9999-12-31 2017-09-02 13620321118 2017-09-02 2017-09-07 2017-09-02 13620321119 2017-09-08 9999-12-31 2017-09-03 13620321110 2017-09-03 9999-12-31


     t_start_date

表示该条记录的生命周期开始时间，


     t_end_date

表示该条记录的生命周期结束时间。


     t_end_date = '9999-12-31'

表示该条记录目前处于有效状态。

如果查询当前所有有效的记录，则是


     select * from user where t_end_date = '9999-12-31'

。

如果查询2017-09-02的快照，则是


     select * from user where t_start_date <= '2017-09-02' and t_end_date >= '2017-09-02'

。

Hive实现拉链表

大部分公司都会选择以HDFS和Hive为主的数据仓库架构，在Hive的0.14版本之前，Hive的表只能进行删除和添加，不能进行update。

以上面的用户表为例，我们要实现用户的拉链表，首先确定数据源：

需要一张ODS层的用户全量表，用来初始化数据

每天的用户更新表

还要确定拉链表的时间粒度，比如说，拉链表每天只取一个状态，也就是说如果一天有3个状态变更，我们只取最后一个状态，这种天的粒度的表其实已经能解决大部分的问题。

获取每天的用户更新和增量信息的3种方式：

监听MySQL数据的变化，比如说用Canal，最后合并每日的变化，获取到最新的一个状态。

假设每天都会获得一份切片数据，可以通过取两天切片数据的不同来作为每日更新表，这种情况下可以对所有的字段先进行concat，再取md5，这样就ok了。

流水表，有每日的变更流水表。

ODS层的user表

用户资料切片表的建表语句：

CREATE EXTERNAL TABLE ods.user (
  user_id STRING COMMENT '用户编号',
  phone_number STRING COMMENT '手机号码',
  register_date STRING COMMENT '注册日期'
)
COMMENT '用户资料表'
PARTITIONED BY (dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION '/ods/user';

ODS层的user_update表

我们还需要一张用户每日更新表：

CREATE EXTERNAL TABLE ods.user_update (
  user_id STRING COMMENT '用户编号',
  phone_number STRING COMMENT '手机号码',
  register_date STRING COMMENT '注册日期'
)
COMMENT '每日用户资料更新表'
PARTITIONED BY (dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION '/ods/user_update';

拉链表

创建一张拉链表：

CREATE EXTERNAL TABLE dws.user_his (
  user_id STRING COMMENT '用户编号',
  phone_number STRING COMMENT '手机号码',
  register_date STRING COMMENT '用户编号',
  t_start_date ,
  t_end_date
)
COMMENT '用户资料拉链表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION '/dws/user_his';

实现SQL语句

现在假设已经初始化了2017-09-01的数据，然后需要更新2017-09-02那一天的数据，那么每日更新的SQL语句如下：

INSERT OVERWRITE TABLE dws.user_his
SELECT * FROM
(
    SELECT A.user_id,
           A.phone_number,
           A.register_date,
           A.t_start_time,
           CASE
                WHEN A.t_end_time = '9999-12-31' AND B.user_num IS NOT NULL THEN '2017-09-01'
                ELSE A.t_end_time
           END AS t_end_time
    FROM dws.user_his AS A
    LEFT JOIN ods.user_update AS B
    ON A.user_id = B.user_id
UNION
    SELECT C.user_id,
           C.phone_number,
           C.register_date,
           '2017-09-02' AS t_start_time,
           '9999-12-31' AS t_end_time
    FROM ods.user_update AS C
) AS T

如果需要更新其它日期的数据，把两个日期设置为变量就可以了。

拉链表和流水表

流水表存放的是一个用户的变更记录，比如在一张流水表中，一天的数据中，会存放一个用户的每条修改记录，但是在拉链表中只有一条记录。

这是拉链表设计时需要注意的一个粒度问题。我们当然也可以设置的粒度更小一些，一般按天就足够。

查询性能

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

在一些查询引擎中，我们对 start_date 和 end_date 做索引，这样能提高不少性能。这种方法其实在 Hive 中行不通，因为 Hive 相当于没有索引，不过在其它系统中可以考虑。

保留部分历史数据，比如说我们一张表里面存放全量的拉链表数据，然后再对外暴露一张只提供近 3 个月数据的拉链表。

淘汰机制

关于淘汰机制，其实和性能也是有关系的，一方面是因为所有数据的积累会导致计算越来越慢，另一方面是业务侧其实对历史数据的需求也有一定的优先级的。

因此在设计拉链表的时候可以制定一些数据的淘汰机制。淘汰的数据不一定要删除，比如我们建立两张拉链表，一张拉链表中只保存最新的十条数据，其它的数据会存入一张历史拉链表中。

总结

拉链表使用心得：

1	CREATE EXTERNAL TABLE ods.user (
2	user_id STRING COMMENT '用户编号',
3	phone_number STRING COMMENT '手机号码',
4	register_date STRING COMMENT '注册日期'
5	)
6	COMMENT '用户资料表'
7	PARTITIONED BY (dt string)
8	ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
9	STORED AS ORC
10	LOCATION '/ods/user';

1	CREATE EXTERNAL TABLE ods.user_update (
2	user_id STRING COMMENT '用户编号',
3	phone_number STRING COMMENT '手机号码',
4	register_date STRING COMMENT '注册日期'
5	)
6	COMMENT '每日用户资料更新表'
7	PARTITIONED BY (dt string)
8	ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
9	STORED AS ORC
10	LOCATION '/ods/user_update';

1	CREATE EXTERNAL TABLE dws.user_his (
2	user_id STRING COMMENT '用户编号',
3	phone_number STRING COMMENT '手机号码',
4	register_date STRING COMMENT '用户编号',
5	t_start_date ,
6	t_end_date
7	)
8	COMMENT '用户资料拉链表'
9	ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
10	STORED AS ORC
11	LOCATION '/dws/user_his';

1	INSERT OVERWRITE TABLE dws.user_his
2	SELECT * FROM
3	(
4	SELECT A.user_id,
5	A.phone_number,
6	A.register_date,
7	A.t_start_time,
8	CASE
9	WHEN A.t_end_time = '9999-12-31' AND B.user_num IS NOT NULL THEN '2017-09-01'
10	ELSE A.t_end_time
11	END AS t_end_time
12	FROM dws.user_his AS A
13	LEFT JOIN ods.user_update AS B
14	ON A.user_id = B.user_id
15	UNION
16	SELECT C.user_id,
17	C.phone_number,
18	C.register_date,
19	'2017-09-02' AS t_start_time,
20	'9999-12-31' AS t_end_time
21	FROM ods.user_update AS C
22	) AS T