数据仓库拉链表（原理、Hive中的设计与实现）

如果该表设计为在数据仓库中保存为历史 zip 表，则会出现下表，这是最新日期（例如 2017-01 -03) 信息：

解释t_start_date代表记录的生命周期的开始时间，t_end_date =记录的生命周期的结束时间-112-- 31'表示该记录当前在有效的空间中。

拉链表在Hive中的实现

当前大数据场景中基于HDFS和Hive的数据仓库架构。在当前版本的HDFS中，文件系统中的文件是不可变的，这意味着Hive表可以智能删除和添加，但不能更新。基于这个出发点，实现了拉链列表。

还是以上面的用户表为例，我们需要实现一个用户拉链表。在实施之前，我们需要确定我们有哪些可用的数据源。

1. 我们需要一份完整的ODS层用户列表。至少应该用于初始化。
2. 每日用户更新表。

并且我们需要确定拉链表的时间精度。比如拉链桌每天就只有一个空间。换句话说，如果每天有 3 个状态变化，我们只取最后一个状态。这样的日常准确度表其实可以解决大部分问题。

另外，我想补充一下如何获取每日用户更新表。根据笔者的经验，有3种方式可以获得或间接获得每日用户津贴。既然比较重要，我就详细解释一下：

1. 我们可以使用例如Canal来跟踪Mysql数据的变化，最后结合每天的变化得到最后的状态。
2. 假设我们每天都会收到切片数据。我们可以将两天的分片数据的差值作为每日更新表。这种情况我们可以先将所有字段组合起来，然后取md5就可以了。
3. 流量计！每日更换时间表有效。

Ods层用户表

现在我们看一下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 ORC
LOCATION '/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 ORC
LOCATION '/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_date
COMMENT '用户资料拉链表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION '/dws/user_his';
)

执行sql命令

那么初始化sql就不写了。其实相当于用了ods级别的用户表一天。我们在这里写每日更新。

现在假设我们已经初始化了日期2017-01-01，那么数据需要更新到2017-01-02。我们有以下 SQL。

然后仅设置两个日期作为变量。

INSERT OVERWRITE TABLE dws.user_his
SELECT * 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_num
UNION
    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

0x03补充

好了，我们分析了拉链表的原理和设计思路，并在Hive环境下实现了拉链表。让我们对拉链表进行一些小的添加。

Zip 表和流表

流表存储用户变更记录。例如，在流表中，一天的数据存储了每个用户的更改记录，但在拉链表中，只有一条记录。

这是设计拉链表时需要解决的精度问题。当然我们也可以把分辨率设置低一些，一般每天就够了。

查询性能

当然，zip表也面临查询性能问题。比如我们保存zip数据5年，这张表必然会比较大，查询时性能会比较低。个人认为有两个思路可以解决：

1. 在一些查询引擎中，我们对开始日期和结束日期建立索引，这样可以大大提高性能。
2. 保留一些历史信息。例如，我们将整个 zip 表的数据存储在一个表中，然后公开一个仅包含最近 3 个月数据的 zip 表。

0xFF 总结

我们在本文中详细分享了与拉链列表相关的信息点，但仍然缺少一些内容。欢迎交流。

从后来的使用中积累了经验，所以我补充一下：

1. 在使用zipper表时，不需要添加t_end_date，即最后一个有效期，但是添加后可以优化很多查询。
2. 您可以添加当前线路状态标记，以便快速查找当前线路状态。
3. 您可以在拉链桌设计中添加内容，因为我们每天都节省空间。如果我们在这个空间添加一个字段，比如每天的变化次数，拉链表会更有用。