本文共 9875 字，大约阅读时间需要 32 分钟。
什么是拉链表
拉链表是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。
我们先看一个示例，这就是一张拉链表，存储的是用户的最基本信息以及每条记录的生命周期。我们可以使用这张表拿到最新的当天的最新数据以及之前的历史数据。
| 注册日期 | 用户编号 | 手机号码 | t_start_date | t_end_date |
|---|---|---|---|---|
| 2017-01-01 | 001 | 111111 | 2017-01-01 | 9999-12-31 |
| 2017-01-01 | 002 | 222222 | 2017-01-01 | 2017-01-01 |
| 2017-01-01 | 002 | 233333 | 2017-01-02 | 9999-12-31 |
| 2017-01-01 | 003 | 333333 | 2017-01-01 | 9999-12-31 |
| 2017-01-01 | 004 | 444444 | 2017-01-01 | 2017-01-01 |
| 2017-01-01 | 004 | 432432 | 2017-01-02 | 2017-01-02 |
| 2017-01-01 | 004 | 432432 | 2017-01-03 | 9999-12-31 |
| 2017-01-02 | 005 | 555555 | 2017-01-02 | 2017-01-02 |
| 2017-01-02 | 005 | 115115 | 2017-01-03 | 9999-12-31 |
| 2017-01-03 | 006 | 666666 | 2017-01-03 | 9999-12-31 |
我们暂且不对这张表做细致的讲解，后文会专门来阐述怎么来设计、实现和使用它。
拉链表的使用场景
在数据仓库的数据模型设计过程中，经常会遇到下面这种表的设计：
那么对于这种表我该如何设计呢？下面有几种方案可选：
为什么使用拉链表
现在我们对前面提到的三种进行逐个的分析。
方案一
这种方案就不用多说了，实现起来很简单，每天drop掉前一天的数据，重新抽一份最新的。
优点很明显，节省空间，一些普通的使用也很方便，不用在选择表的时候加一个时间分区什么的。
缺点同样明显，没有历史数据，先翻翻旧账只能通过其它方式，比如从流水表里面抽。
方案二
每天一份全量的切片是一种比较稳妥的方案，而且历史数据也在。
缺点就是存储空间占用量太大太大了，如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费，这点我感触还是很深的......
当然我们也可以做一些取舍，比如只保留近一个月的数据？但是，需求是无耻的，数据的生命周期不是我们能完全左右的。
拉链表
拉链表在使用上基本兼顾了我们的需求。
首先它在空间上做了一个取舍，虽说不像方案一那样占用量那么小，但是它每日的增量可能只有方案二的千分之一甚至是万分之一。
其实它能满足方案二所能满足的需求，既能获取最新的数据，也能添加筛选条件也获取历史的数据。
所以我们还是很有必要来使用拉链表的。
拉链表的设计和实现
下面我们来举个栗子详细看一下拉链表。
我们接上在中的电商网站的例子，现在以用户的拉链表来说明。
我们先看一下在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变成233333） |
| 2017-01-01 | 003 | 333333 | |
| 2017-01-01 | 004 | 432432 | （由444444变成432432） |
| 2017-01-02 | 005 | 555555 | （2017-01-02新增） |
在2017-01-03这一天表中的数据是， 用户004和005资料进行了修改，006是新增用户：
| 注册日期 | 用户编号 | 手机号码 | 备注 |
|---|---|---|---|
| 2017-01-01 | 001 | 111111 | |
| 2017-01-01 | 002 | 233333 | |
| 2017-01-01 | 003 | 333333 | |
| 2017-01-01 | 004 | 654321 | （由432432变成654321） |
| 2017-01-02 | 005 | 115115 | （由555555变成115115） |
| 2017-01-03 | 006 | 666666 | （2017-01-03新增） |
如果在数据仓库中设计成历史拉链表保存该表，则会有下面这样一张表，这是最新一天（即2017-01-03）的数据：
| 注册日期 | 用户编号 | 手机号码 | t_start_date | t_end_date |
|---|---|---|---|---|
| 2017-01-01 | 001 | 111111 | 2017-01-01 | 9999-12-31 |
| 2017-01-01 | 002 | 222222 | 2017-01-01 | 2017-01-01 |
| 2017-01-01 | 002 | 233333 | 2017-01-02 | 9999-12-31 |
| 2017-01-01 | 003 | 333333 | 2017-01-01 | 9999-12-31 |
| 2017-01-01 | 004 | 444444 | 2017-01-01 | 2017-01-01 |
| 2017-01-01 | 004 | 432432 | 2017-01-02 | 2017-01-02 |
| 2017-01-01 | 004 | 654321 | 2017-01-03 | 9999-12-31 |
| 2017-01-02 | 005 | 555555 | 2017-01-02 | 2017-01-02 |
| 2017-01-02 | 005 | 115115 | 2017-01-03 | 9999-12-31 |
| 2017-01-03 | 006 | 666666 | 2017-01-03 | 9999-12-31 |
说明
在Hive中实现拉链表
在现在的大数据场景下，大部分的公司都会选择以Hdfs和Hive为主的数据仓库架构。目前的Hdfs版本来讲，其文件系统中的文件是不能做改变的，也就是说Hive的表只能进行删除和添加操作，而不能进行update。基于这个前提，我们来实现拉链表。
还是以上面的用户表为例，我们要实现用户的拉链表。在实现它之前，我们需要先确定一下我们有哪些数据源可以用。
而且我们要确定拉链表的时间粒度，比如说拉链表每天只取一个状态，也就是说如果一天有3个状态变更，我们只取最后一个状态，这种天粒度的表其实已经能解决大部分的问题了。
另外，补充一下每日的用户更新表该怎么获取，据笔者的经验，有3种方式拿到或者间接拿到每日的用户增量，因为它比较重要，所以详细说明：
ods层的user表
现在我们来看一下我们ods层的用户资料切片表的结构：
CREATE EXTERNAL TABLE ods.user ( user_num STRING COMMENT '用户编号', mobile STRING COMMENT '手机号码', reg_date STRING COMMENT '注册日期'COMMENT '用户资料表'PARTITIONED BY (dt string)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'STORED AS ORCLOCATION '/ods/user';)
ods层的user_update表
然后我们还需要一张用户每日更新表，前面已经分析过该如果得到这张表，现在我们假设它已经存在。
CREATE EXTERNAL TABLE ods.user_update ( user_num STRING COMMENT '用户编号', mobile STRING COMMENT '手机号码', reg_date STRING COMMENT '注册日期'COMMENT '每日用户资料更新表'PARTITIONED BY (dt string)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'STORED AS ORCLOCATION '/ods/user_update';)
拉链表
现在我们创建一张拉链表：
CREATE EXTERNAL TABLE dws.user_his ( user_num STRING COMMENT '用户编号', mobile STRING COMMENT '手机号码', reg_date STRING COMMENT '用户编号', t_start_date , t_end_dateCOMMENT '用户资料拉链表'ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'STORED AS ORCLOCATION '/dws/user_his';)
实现sql语句
然后初始化的sql就不写了，其实就相当于是拿一天的ods层用户表过来就行，我们写一下每日的更新语句。
现在我们假设我们已经已经初始化了2017-01-01的日期，然后需要更新2017-01-02那一天的数据，我们有了下面的Sql。
然后把两个日期设置为变量就可以了。
INSERT OVERWRITE TABLE dws.user_hisSELECT * FROM( SELECT A.user_num, A.mobile, A.reg_date, A.t_start_time, CASE WHEN A.t_end_time = '9999-12-31' AND B.user_num IS NOT NULL THEN '2017-01-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_num = B.user_numUNION SELECT C.user_num, C.mobile, C.reg_date, '2017-01-02' AS t_start_time, '9999-12-31' AS t_end_time FROM ods.user_update AS C) AS T
好了，我们分析了拉链表的原理、设计思路、并且在Hive环境下实现了一份拉链表，下面对拉链表做一些小的补充。
流水表存放的是一个用户的变更记录，比如在一张流水表中，一天的数据中，会存放一个用户的每条修改记录，但是在拉链表中只有一条记录。
这是拉链表设计时需要注意的一个粒度问题。我们当然也可以设置的粒度更小一些，一般按天就足够。
拉链表当然也会遇到查询性能的问题，比如说我们存放了5年的拉链数据，那么这张表势必会比较大，当查询的时候性能就比较低了，个人认为两个思路来解决：
我们在这篇文章里面详细地分享了一下和拉链表相关的知识点，但是仍然会有一会遗漏。欢迎交流。
在后面的使用中又有了一些心得，补充进来：
使用拉链表的时候可以不加t_end_date，即失效日期，但是加上之后，能优化很多查询。
可以加上当前行状态标识，能快速定位到当前状态。
在拉链表的设计中可以加一些内容，因为我们每天保存一个状态，如果我们在这个状态里面加一个字段，比如如当天修改次数，那么拉链表的作用就会更大。
假如我们有一个账号account表，我们需要在hive中存储（数据是从线上mysql读取binlog同步来的，是有明细变化的）
account表结构：account_id, username, followers_count, modified_at
我们经常使用的存储方式有快照表和流水表。快照表就是以时间为粒度（比如天），生成每个时间的全量数据快照；流水表则是记录数据的每一条具体的改变。
现在有一个需求：需要记录账号的历史变更情况
快照表实现 这里以天为粒度，对每天账号最终的状态进行存储即可。在hive中，以天为分区存储，我们需要访问某天的历史状态，直接指定分区即可访问
-- 访问20190801时某个账号的状态select * from account_snapshot where ds = "20190801" and account_id = xxx
快照表的缺点是：当单表的数据量比较大时，每天存储全量的快照，会导致不必要的资源开支
流水表实现
流水表记录数据的每一条变化，来一条插入一条
这种存储方法对数据的使用者不太友好
-- 查询20190801时某个账号的状态select * from ( select *, row_number over(partition by account_id order by modified_at desc) as ro from account where modified_at <= "2019-08-02 00:00:00" and account_id = xxx) where ro = 1
以上的两种方式，多多少少都存在问题，接下来介绍拉链表的使用
拉链表
拉链表是维护历史状态、以及最新状态的一种方式。拉链表对快照表进行了优化，根据拉链粒度（一般为时间）的不同，去除了在粒度范围内不变的数据。
拉链表可以维护两个时间（start_time, end_time），来标识当前记录是否还有效，以及更好的定位历史数据
实现前提：
首先要有某一时刻的全量数据，作为起始表其次要有流水表或者快照表两者其一，作为变化的依据
实现：
-- 原始数据create table account( account int , username varchar, followers_count int , modified_at timestamp )-- 创建拉链表create table account_zip( account int , username varchar, followers_count int , modified_at timestamp , start_time timestamp, -- 记录的有效起始时间 end_time timestamp, -- 记录的有效结束时间)
今天是8.1，我们从7.31号的数据开始记录
首先我们将7.31号的数据导入我们的拉链表中
insert into account_zipselect *, "2019-07-31 00:00:00" as start_time , "9999-12-31 00:00:00" as end_timefrom account ;
这里的start_time指的是这条记录是在7.31改变生效的，end_time是指这条记录在9999-12-31前是有效的。导入拉链表后，表内的记录如下所示，
接下来，我们在8.1的时候，对账号进行修改和新增左边是7.31的数据，右边是8.1的数据
我们可以看到8.1进行了一条记录的修改（修改mwf的followers_account）和一条记录的新增（新增account_id为5的用户）
针对修改来说：
在拉链中已经存在mwf的信息，8.1对他进行修改，我们可以将之前那条记录的end_time修改为8.1，表示他在8.1之后失效了
然后将8.1的这次操作写入拉链表，他的start_time为8.1，end_time为9999-12-31
针对新增来说：
我们直接将它写入拉链表，start_time为8.1，end_time为9999-12-318.1过后，我们的拉链表变为了如下版本：
以上我们就实现了一个拉链表
查询记录
查询当前的有效记录select * from account_zip where end_time = "9999-12-31 00:00:00"
如查询2019-07-31时的历史快照
-- 在7.31号前开始生效，且在7.31号当天时还没有失效， 此处通过两个时间刚好限定了范围select * from account_zipwhere start_time <= "2019-07-31 00:00:00" and end_time >= "2019-07-31 00:00:00"
基于快照表生成拉链表
insert into account_zip_tmp -- 联合两个表，写入临时的拉链表中select * from ( -- 改变原有拉链表中 失效的数据 -- 这里用到了md5来比较数据是否相同 select bak.account_id, bak.username , bak.followers_count , bak.modified_at, bak.start_time case when bak.end_time = "9999-12-31 00:00:00" and md5(concat( coalesce(bak.username, 'NULL'), coalesce(bak.followers_count, 'NULL'), coalesce(bak.modified_at, 'NULL') )) != md5(concat( coalesce(new.username, 'NULL'), coalesce(new.followers_count, 'NULL'), coalesce(new.modified_at, 'NULL') )) then "2019-07-31 00:00:00" else bak.end_time end as end_time from account_zip as bak left join ( select * from account_snapshot where ds = "20190801" ) as new on bak.account_id = new.account_id union -- 写入修改或新增的数据 select a.account_id, a.username , a.followers_count , a.modified_at, "2019-07-31 00:00:00" as start_time, "9999-12-31 00:00:00" as end_time from ( select * from account_snapshot where ds = "20190801" ) as a left join ( select * from account_zip where end_time = "9999-12-31 00:00:00" ) on a.account_id = b.account_id where md5(concat( coalesce(a.username, 'NULL'), coalesce(a.followers_count, 'NULL'), coalesce(a.modified_at, 'NULL') )) != md5(concat( coalesce(b.username, 'NULL'), coalesce(b.followers_count, 'NULL'), coalesce(b.modified_at, 'NULL') )) )；-- 将临时拉链表写回拉链表insert overwrite table account_zipselect * from account_zip_tmp
转载地址：http://jgpmi.baihongyu.com/