前言

本文将会谈一谈在数据仓库中拉链表相关的内容，包括它的原理、设计、以及在我们大数据场景下的实现方式。

1. 先分享一下拉链表的用途、什么是拉链表。

2. 通过一些小的使用场景来对拉链表做近一步的阐释，以及拉链表和常用的切片表的区别。

3. 举一个具体的应用场景，来设计并实现一份拉链表，最后并通过一些例子说明如何使用我们设计的这张表（因为现在Hive的大规模使用，我们会以Hive场景下的设计为例）。

4. 分析一下拉链表的优缺点，并对前面的提到的一些内容进行补充说明，比如说拉链表和流水表的区别。

什么是拉链表

拉链表的使用场景

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

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

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

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

• 方案一：每天只留最新的一份，比如我们每天用Sqoop抽取最新的一份全量数据到Hive中。

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

• 方案三：使用拉链表。

为什么使用拉链表

如何设计一张拉链表

• 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中实现拉链表

1. 我们需要一张ODS层的用户全量表。至少需要用它来初始化。

2. 每日的用户更新表。

1. 我们可以监听Mysql数据的变化，比如说用Canal，最后合并每日的变化，获取到最后的一个状态。

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

3. 流水表！有每日的变更流水表。

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语句

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

补充

拉链表和流水表

查询性能

1. 在一些查询引擎中，我们对start_date和end_date做索引，这样能提高不少性能。

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

总结

1. 使用拉链表的时候可以不加t_end_date，即失效日期，但是加上之后，能优化很多查询。

2. 可以加上当前行状态标识，能快速定位到当前状态。

3. 在拉链表的设计中可以加一些内容，因为我们每天保存一个状态，如果我们在这个状态里面加一个字段，比如如当天修改次数，那么拉链表的作用就会更大。

4.