漫谈数据仓库之拉链表（原理、设计以及在Hive中的实现）

小强签名设计

已于 2023-05-15 10:27:13 修改

阅读量992

点赞数 1

分类专栏：大数据面试文章标签： hive 数据仓库 hadoop

于 2023-05-11 22:20:37 首次发布

原文链接：https://www.cnblogs.com/lxbmaomao/p/9821128.html

版权

大数据面试专栏收录该内容

32 篇文章 16 订阅

订阅专栏

本文介绍了拉链表在数据仓库中的应用，包括其原理、设计方法以及在Hive环境中的实现。拉链表用于记录数据的历史变化，以节省存储空间并允许查询历史快照。文章提供了两种在Hive中实现拉链表的方案，并讨论了拉链表与流水表的区别以及查询性能的考虑。此外，还提及了拉链表的回滚策略。

摘要由CSDN通过智能技术生成

文章目录

一、前言

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

先分享一下拉链表的用途、什么是拉链表。
通过一些小的使用场景来对拉链表做近一步的阐释，以及拉链表和常用的切片表的区别。
举一个具体的应用场景，来设计并实现一份拉链表，最后并通过一些例子说明如何使用我们设计的这张表（因为现在Hive的大规模使用，我们会以Hive场景下的设计为例）。
分析一下拉链表的优缺点，并对前面的提到的一些内容进行补充说明，比如说拉链表和流水表的区别。

二、什么是拉链表

拉链表是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。

我们先看一个示例，这就是一张拉链表，存储的是用户的最基本信息以及每条记录的生命周期。我们可以使用这张表拿到最新的当天的最新数据以及之前的历史数据。

注册日期	用户编号	手机号码	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

1. 拉链表的使用场景

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

有一些表的数据量很大，比如一张用户表，大约10亿条记录，50个字段，这种表，即使使用 ORC 压缩，单张表的存储也会超过100G，在 HDFS 使用双备份或者三备份的话就更大一些。
表中的部分字段会被 update 更新操作，如用户联系方式，产品的描述信息，订单的状态等等。
需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态。
表中的记录变化的比例和频率不是很大，比如，总共有10亿的用户，每天新增和发生变化的有200万左右，变化的比例占的很小。

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

方案一：每天只留最新的一份，比如我们每天用Sqoop抽取最新的一份全量数据到Hive中。
方案二：每天保留一份全量的切片数据。
方案三：使用拉链表。

2. 为什么使用拉链表

现在我们对前面提到的三种进行逐个的分析。

方案一：

这种方案就不用多说了，实现起来很简单，每天 drop 掉前一天的数据，重新抽一份最新的。
优点很明显，节省空间，一些普通的使用也很方便，不用在选择表的时候加一个时间分区什么的。
缺点同样明显，没有历史数据，想翻翻旧账只能通过其它方式，比如从流水表里面抽。

方案二：

每天一份全量的切片是一种比较稳妥的方案，而且历史数据也在。
缺点就是存储空间占用量太大太大了，如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费，这点我感触还是很深的…
当然我们也可以做一些取舍，比如只保留近一个月的数据？但是，需求是无耻的，数据的生命周期不是我们能完全左右的。

方案三：拉链表

拉链表在使用上基本兼顾了我们的需求。
首先它在空间上做了一个取舍，虽说不像方案一那样占用量那么小，但是它每日的增量可能只有方案二的千分之一甚至是万分之一。
其实它能满足方案二所能满足的需求，既能获取最新的数据，也能添加筛选条件也获取历史的数据。

所以我们还是很有必要来使用拉链表的。

三、拉链表的设计和实现

1. 如何设计一张拉链表

下面我们来举个栗子详细看一下拉链表。

我们接上在《漫谈数据仓库之维度建模》中的电商网站的例子，现在以用户的拉链表来说明。

我们先看一下在 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

说明：

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’。（此处要好好理解，是拉链表比较重要的一块。 where条件筛选当前有效数据，开始日期小于等于当前日期并且结束日期大于等于当前日期，则为有效。）

2. 在Hive中实现拉链表

在现在的大数据场景下，大部分的公司都会选择以 Hdfs 和 Hive 为主的数据仓库架构。目前的 Hdfs 版本来讲，其文件系统中的文件是不能做改变的，也就是说 Hive 的表只能进行删除和添加操作，而不能进行 update。基于这个前提，我们来实现拉链表。

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

我们需要一张ODS层的用户全量表。至少需要用它来初始化。
每日的用户更新表。

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

另外，补充一下每日的用户更新表该怎么获取，据笔者的经验，有3种方式拿到或者间接拿到每日的用户增量，因为它比较重要，所以详细说明：

我们可以监听 Mysql 数据的变化，比如说用 Canal，最后合并每日的变化，获取到最后的一个状态。
假设我们每天都会获得一份切片数据，我们可以通过取两天切片数据的不同来作为每日更新表，这种情况下我们可以对所有的字段先进行 concat，再取 md5，这样就 ok 了。
流水表！有每日的变更流水表。
通过 etl 工具对操作型数据库按照时间字段增量抽取到 ods 或者数据仓库（每天抽取前一天的数据），形成每天的增量数据（实际中使用最多的情形）。

（1）拉链表实现方式一

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

（2）拉链表实现方式二

操作型数据库的用户表结构：

CREATE EXTERNAL TABLE ods.user ( 
  user_num STRING COMMENT '用户编号', 
  mobile STRING COMMENT '手机号码', 
  reg_date STRING COMMENT '注册日期' ,
  last_modify_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';

每天增量抽取的用户表结构和抽取条件：

1）表结构和上面的表结构保持一致，我们取表名为 ods.user_update

2）增量抽取条件：select * from ods.user where last_modify_date = '$date'

拉链表：

现在我们创建一张拉链表：

CREATE EXTERNAL TABLE dws.user_his ( 
  user_num STRING COMMENT '用户编号', 
  mobile STRING COMMENT '手机号码', 
  reg_date STRING COMMENT '用户编号',
  last_modify_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：

1）

merge into dws.user_his tar 
using
(
　　select user_num,mobile from ods.user_update 
) sou on tar.user_num=sou.user_num and tar.mobile=sou.mobile and tar.t_start_date < '$date' and tar.t_end_date >  '$date' 
when matched then 
update set tar.t_end_date='9999-12-31';

按照主键筛选，在 dws.user_his 表中出现过的并且现在为有效数据的，全部更新为闭链数据。

2）

INSERT  TABLE dws.user_his 
SELECT 
　　C.user_num, 
        C.mobile, 
        C.reg_date, 
         c.last_modify_date
         '2017-01-02' AS t_start_time, 
         '9999-12-31' AS t_end_time 
  FROM ods.user_update AS C;

比如我们要1月2号的数据，取出来的数据为 select from user where t_start_date <= ‘2017-01-02’ and t_end_date >= ‘2017-01-02’；

注册日期	用户编号	手机号	t_start_date	t_end_date
2017-01-01	001	111111	2017-01-01	9999-12-31
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	432432	2017-01-02	2017-01-02
2017-01-01	005	555555	2017-01-02	2017-01-02

与1月2号数据完全一致。

四、补充

好了，我们分析了拉链表的原理、设计思路、并且在 Hive 环境下实现了一份拉链表，下面对拉链表做一些小的补充。

1. 拉链表和流水表

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

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

2. 查询性能

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

在一些查询引擎中，我们对start_date和end_date做索引，这样能提高不少性能。
保留部分历史数据，比如说我们一张表里面存放全量的拉链表数据，然后再对外暴露一张只提供近3个月数据的拉链表。

五、拉链表回滚

1. 具体操作方案

假设恢复到 t 天之前的数据，即未融合 t 天数据之前的拉链表，假设标记的开始日期和结束日期分别为 s、t，具体分析如下：

当 t-1>e 时，s数据、e 数据在 t 天之前产生，保留即可
当 t-1=e 时，e数据在 t 天产生，需修改
当 s<t<=e 时，e数据在 t+n 天产生，需修改
当 s>=t 时，s数据、e数据在 t+n 天产生，删除即可

具体例子：

spark-sql> select * from t_dw_orders_his order by orderid,dw_start_date;
1       2015-08-18      2015-08-18      创建    2015-08-18      2015-08-21
1       2015-08-18      2015-08-22      支付    2015-08-22      2015-08-22
1       2015-08-18      2015-08-23      完成    2015-08-23      9999-12-31
2       2015-08-18      2015-08-18      创建    2015-08-18      2015-08-21
2       2015-08-18      2015-08-22      完成    2015-08-22      9999-12-31
3       2015-08-19      2015-08-21      支付    2015-08-19      2015-08-20
3       2015-08-19      2015-08-21      支付    2015-08-21      2015-08-22
3       2015-08-19      2015-08-23      完成    2015-08-23      9999-12-31
4       2015-08-19      2015-08-21      完成    2015-08-19      2015-08-20
4       2015-08-19      2015-08-21      完成    2015-08-21      9999-12-31
5       2015-08-19      2015-08-20      支付    2015-08-19      2015-08-22
5       2015-08-19      2015-08-23      完成    2015-08-23      9999-12-31
6       2015-08-20      2015-08-20      创建    2015-08-20      2015-08-21
6       2015-08-20      2015-08-22      支付    2015-08-22      9999-12-31
7       2015-08-20      2015-08-21      支付    2015-08-20      2015-08-20
7       2015-08-20      2015-08-21      支付    2015-08-21      9999-12-31
8       2015-08-21      2015-08-21      创建    2015-08-21      2015-08-21
8       2015-08-21      2015-08-22      支付    2015-08-22      2015-08-22
8       2015-08-21      2015-08-23      完成    2015-08-23      9999-12-31
9       2015-08-22      2015-08-22      创建    2015-08-22      9999-12-31
10      2015-08-22      2015-08-22      支付    2015-08-22      9999-12-31
11      2015-08-23      2015-08-23      创建    2015-08-23      9999-12-31
12      2015-08-23      2015-08-23      创建    2015-08-23      9999-12-31
13      2015-08-23      2015-08-23      支付    2015-08-23      9999-12-31

比如在插入 2015-08-23 的数据后，回滚 2015-08-22 的数据，使拉链表与 2015-08-21 的一致，具体操作过程如下：

（1）增加临时表 t_dw_orders_his_tmp1，用来记录 t-1>e 的数据：

CREATE TABLE t_dw_orders_his_tmp1
AS
SELECT 
　　orderid,
　　createtime,
　　modifiedtime,
　　status,
　　dw_start_date,
　　dw_end_date
FROM 
　　t_dw_orders_his
WHERE 
　　dw_end_date < '2015-08-21'
3       2015-08-19      2015-08-21      支付    2015-08-19      2015-08-20
4       2015-08-19      2015-08-21      完成    2015-08-19      2015-08-20
7       2015-08-20      2015-08-21      支付    2015-08-20      2015-08-20

（2）增加临时表 t_dw_orders_his_tmp2，用来记录 t-1=e 的数据：

CREATE TABLE t_dw_orders_his_tmp2 
AS 
SELECT 　　
　　orderid,
　　createtime, 　　
　　modifiedtime, 　　
　　status, 　　
　　dw_start_date, 　　
　　'9999-12-31' AS dw_end_date 
FROM 
　　t_dw_orders_his
WHERE 
　　dw_end_date = '2015-08-21'
1       2015-08-18      2015-08-18      创建    2015-08-18      9999-12-31
2       2015-08-18      2015-08-18      创建    2015-08-18      9999-12-31
6       2015-08-20      2015-08-20      创建    2015-08-20      9999-12-31
8       2015-08-21      2015-08-21      创建    2015-08-21      9999-12-31

（3）增加临时表 t_dw_orders_his_tmp3，用来记录 s<t<=e 的数据：

CREATE TABLE t_dw_orders_his_tmp3
AS
SELECT 
　　orderid,
　　createtime,
　　modifiedtime,
　　status,
　　dw_start_date,
　　'9999-12-31' dw_end_date
FROM 
　　t_dw_orders_his
WHERE 
　　dw_start_date < '2015-08-22' AND dw_end_date >= '2015-08-22'
3       2015-08-19      2015-08-21      支付    2015-08-21      9999-12-31
4       2015-08-19      2015-08-21      完成    2015-08-21      9999-12-31
5       2015-08-19      2015-08-20      支付    2015-08-19      9999-12-31
7       2015-08-20      2015-08-21      支付    2015-08-21      9999-12-31

（4）所有数据插入新表 t_dw_orders_his_new：

CREATE TABLE t_dw_orders_his_new
AS
SELECT * FROM t_dw_orders_his_tmp1
UNION ALL
SELECT * FROM t_dw_orders_his_tmp2
UNION ALL
SELECT * FROM t_dw_orders_his_tmp3
1       2015-08-18      2015-08-18      创建    2015-08-18      9999-12-31
2       2015-08-18      2015-08-18      创建    2015-08-18      9999-12-31
3       2015-08-19      2015-08-21      支付    2015-08-19      2015-08-20
3       2015-08-19      2015-08-21      支付    2015-08-21      9999-12-31
4       2015-08-19      2015-08-21      完成    2015-08-19      2015-08-20
4       2015-08-19      2015-08-21      完成    2015-08-21      9999-12-31
5       2015-08-19      2015-08-20      支付    2015-08-19      9999-12-31 
6       2015-08-20      2015-08-20      创建    2015-08-20      9999-12-31
7       2015-08-20      2015-08-21      支付    2015-08-20      2015-08-20
7       2015-08-20      2015-08-21      支付    2015-08-21      9999-12-31
8       2015-08-21      2015-08-21      创建    2015-08-21      9999-12-31