什么是拉链表
拉链表是针对数据仓库中表存储数据的方式而定义的。
所谓拉链,就是记录历史,记录一个事物从开始,一直到当前状态的所有变化的信息。
下面的示例就是一张拉链表,存储的是用户的基本信息以及每条记录的生命周期。我们可以使用这张表拿到当天的最新数据以及之前的历史数据。
register_date | user_id | phone_number | t_start_date | t_end_date |
---|---|---|---|---|
2017-09-01 | 0001 | 13620321111 | 2017-09-01 | 9999-12-31 |
2017-09-01 | 0002 | 13620321112 | 2017-09-01 | 2017-09-01 |
2017-09-01 | 0002 | 13620321113 | 2017-09-02 | 9999-12-31 |
2017-09-01 | 0003 | 13620321114 | 2017-09-01 | 9999-12-31 |
2017-09-01 | 0004 | 13620321115 | 2017-09-01 | 2017-09-01 |
2017-09-01 | 0004 | 13620321116 | 2017-09-02 | 2017-09-02 |
2017-09-01 | 0004 | 13620321117 | 2017-09-03 | 9999-12-31 |
2017-09-02 | 0005 | 13620321118 | 2017-09-02 | 2017-09-07 |
2017-09-02 | 0005 | 13620321119 | 2017-09-08 | 9999-12-31 |
2017-09-03 | 0006 | 13620321110 | 2017-09-03 | 9999-12-31 |
拉链表的使用场景
在数据仓库的模型设计过程中,经常会遇到下面这种表的设计:
- 有一些表的数据量很大,比如一张用户表,大约10亿条记录,50个字段。这种表,及即使用ORC压缩,单张表的存储也会超过100G,在HDFS上使用双备份或三备份的话,所占用的看空间会更大。
- 表中的部分字段会被update更新操作,如用户联系方式,产品描述信息,订单状态等。
- 需要查看某一个时间点或时间段的历史快照信息,比如,查看某一个订单在历史某一个时间点的状态。
- 表中的记录变化的比例和频率不是很大,比如,总共有10亿的用户,每天新增和发生变化的有200万左右,变化的比例占的很小。
那么,对于这种表该如何设计呢,下面有几种方案:
- 方案一:每天只留最新的一份,比如每天用Sqoop抽取最新的一份全量数据到Hive中
- 方案二:每天保留一份全量的切片数据
- 方案三:使用拉链表
为什么使用拉链表
方案一:
实现起来简单,每天drop掉前一天的数据,重新抽一份最新的。
优点:节省空间,一些普通的使用也很方便,不用在选择表的时候加一个时间分区什么的。
缺点:没有历史数据,想翻翻旧账只能通过其它方式,比如从流水表里面抽。
方案二:
每天一份全量的切片是一种比较稳妥的方案,而且历史数据也在。
缺点:存储空间占用量太大,如果每天都保留一份全量,那么每次全量中会保存很多不变的信息,对存储是极大的浪费。
当然也可以做一些取舍,比如只保留近一个月的数据。但是,需求是无耻的,数据的生命周期不是我们能完全左右的,你会发现,存储周期可能会从30天变为90天,然后再从90天变为1年,然后需要永久保存。
拉链表:
拉链表在使用上基本兼顾了我们的需求。
首先它在空间上做了一个取舍,虽说不像方案一那样占用量那么小,但是它每天的增量可能只有方案二的千分之一,甚至是万分之一。
它能满足方案二的需求,既能获取最新的数据,也能添加筛选条件获取历史数据,所以在一些场景下,拉链表是能解决很多问题的。
拉链表的设计和实现
如何设计一张拉链表
以用户资料表为例,我们先看一下在关系型数据库里的user表中的信息变化。
在2017-09-01这一天表中的数据是:
register_date | user_id | phone_number |
---|---|---|
2017-09-01 | 0001 | 13620321111 |
2017-09-01 | 0002 | 13620321112 |
2017-09-01 | 0003 | 13620321114 |
2017-09-01 | 0004 | 13620321115 |
在2017-09-02这一天表中的数据是,用户0002和0004的资料进行了修改,0005是新增用户:
register_date | user_id | phone_number | remark |
---|---|---|---|
2017-09-01 | 0001 | 13620321111 | |
2017-09-01 | 0002 | 13620321113 | 13620321112 => 13620321113 |
2017-09-01 | 0003 | 13620321114 | |
2017-09-01 | 0004 | 13620321116 | 13620321115 => 13620321116 |
2017-09-02 | 0005 | 13620321118 | 2017-09-02新增 |
在2019-09-03这一天表中的数据是,用户0004和0005资料进行了修改,0006是新增用户:
register_date | user_id | phone_number | remark |
---|---|---|---|
2017-09-01 | 0001 | 13620321111 | |
2017-09-01 | 0002 | 13620321113 | |
2017-09-01 | 0003 | 13620321114 | |
2017-09-01 | 0004 | 13620321117 | 13620321116 => 13620321117 |
2017-09-02 | 0005 | 13620321119 | 13620321118 = > 13620321119 |
2017-09-03 | 0006 | 13620321110 | 2017-09-03新增 |
如果在数据仓库中设计成历史拉链表保存改变,则会有下面这样一张表,这是最新一天(即2017-09-03)的数据:
register_date | user_id | phone_number | t_start_date | t_end_date |
---|---|---|---|---|
2017-09-01 | 0001 | 13620321111 | 2017-09-01 | 9999-12-31 |
2017-09-01 | 0002 | 13620321112 | 2017-09-01 | 2017-09-01 |
2017-09-01 | 0002 | 13620321113 | 2017-09-02 | 9999-12-31 |
2017-09-01 | 0003 | 13620321114 | 2017-09-01 | 9999-12-31 |
2017-09-01 | 0004 | 13620321115 | 2017-09-01 | 2017-09-01 |
2017-09-01 | 0004 | 13620321116 | 2017-09-02 | 2017-09-02 |
2017-09-01 | 0004 | 13620321117 | 2017-09-03 | 9999-12-31 |
2017-09-02 | 0005 | 13620321118 | 2017-09-02 | 2017-09-07 |
2017-09-02 | 0005 | 13620321119 | 2017-09-08 | 9999-12-31 |
2017-09-03 | 0006 | 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表
用户资料切片表的建表语句:
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'; |
ODS层的user_update表
我们还需要一张用户每日更新表:
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'; |
实现SQL语句
现在假设已经初始化了2017-09-01的数据,然后需要更新2017-09-02那一天的数据,那么每日更新的SQL语句如下:
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 |
如果需要更新其它日期的数据,把两个日期设置为变量就可以了。
拉链表和流水表
流水表存放的是一个用户的变更记录,比如在一张流水表中,一天的数据中,会存放一个用户的每条修改记录,但是在拉链表中只有一条记录。
这是拉链表设计时需要注意的一个粒度问题。我们当然也可以设置的粒度更小一些,一般按天就足够。
查询性能
拉链表当然也会遇到查询性能的问题,比如说我们存放了5年的拉链数据,那么这张表势必会比较大,当查询的时候性能就比较低了,有两种解决思路:
- 在一些查询引擎中,我们对 start_date 和 end_date 做索引,这样能提高不少性能。这种方法其实在 Hive 中行不通,因为 Hive 相当于没有索引,不过在其它系统中可以考虑。
- 保留部分历史数据,比如说我们一张表里面存放全量的拉链表数据,然后再对外暴露一张只提供近 3 个月数据的拉链表。
淘汰机制
关于淘汰机制,其实和性能也是有关系的,一方面是因为所有数据的积累会导致计算越来越慢,另一方面是业务侧其实对历史数据的需求也有一定的优先级的。
因此在设计拉链表的时候可以制定一些数据的淘汰机制。淘汰的数据不一定要删除,比如我们建立两张拉链表,一张拉链表中只保存最新的十条数据,其它的数据会存入一张历史拉链表中。
总结
拉链表使用心得:
- 使用拉链表的时候可以不加 t_end_date,即失效日期,但是加上之后,能优化很多查询。
- 可以加上当前行状态标识,能快速定位到当前状态。
- 在拉链表的设计中可以加一些内容,因为我们每天保存一个状态,如果我们在这个状态里面加一个字段,比如如当天修改次数,那么拉链表的作用就会更大。