拉链表（原理、设计以及在Hive中的实现）

评论

什么是拉链表

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

  • 记录一个事物从开始，一直到当前状态的所有变化的信息。
  • 我们可以使用这张表拿到最新的当天的最新数据以及之前的历史数据。
  • 既能满足反应数据的历史状态，又可以最大限度地节省存储空间

使用场景

  1. 数据量比较大;
  2. 表中的部分字段会被update,如用户的地址，产品的描述信息，订单的状态等等;
  3. 需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态，
    比如，查看某一个用户在过去某一段时间内，更新过几次等等;
  4. 变化的比例和频率不是很大，比如，总共有1000万的会员，每天新增和发生变化的有10万左右;
  5. 如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费;

这些场景下使用拉链历史表，既可以反映数据的历史状态，又可以最大程度的节省存储。

拉链表：记录一个事物从开始，一直到当前状态的所有变化的信息。

全量表：保存用户所有的数据（包括新增与历史数据）

增量表：只保留当前新增的数据

快照表：按日分区，记录截止数据日期的全量数据

切片表：切片表根据基础表，往往只反映某一个维度的相应数据。其表结构与基础表结构相同，但数据往往只有某一维度，或者某一个事实条件的数据

设计和实现

举例说明，用用户的拉链表来说明。

在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'。（此处要好好理解，是拉链表比较重要的一块。）

在Hive中实现拉链表

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

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

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

而且我们要确定拉链表的时间粒度，比如说拉链表每天只取一个状态，也就是说如果一天有3个状态变更，我们只取最后一个状态，这种天粒度的表其实已经能解决大部分的问题了。
另外，补充一下每日的用户更新表该怎么获取，据笔者的经验，有3种方式拿到或者间接拿到每日的用户增量，因为它比较重要，所以详细说明：

  1. 我们可以监听Mysql数据的变化，比如说用Canal，最后合并每日的变化，获取到最后的一个状态。
  2. 假设我们每天都会获得一份切片数据，我们可以通过取两天切片数据的不同来作为每日更新表，这种情况下我们可以对所有的字段先进行concat，再取md5，这样就ok了。
  3. 流水表！有每日的变更流水表。

ods层的user表

现在我们来看一下我们ods层的用户资料切片表的结构：

1
2
3
4
5
6
7
8
9
10
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表

然后我们还需要一张用户每日更新表，前面已经分析过该如果得到这张表，现在我们假设它已经存在。

1
2
3
4
5
6
7
8
9
10
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';
)

拉链表

1
2
3
4
5
6
7
8
9
10
11
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。
  • 然后把两个日期设置为变量就可以了。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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

拉链表和流水表

  • 流水表存放的是一个用户的变更记录，比如在一张流水表中，一天的数据中，会存放一个用户的每条修改记录，但是在拉链表中只有一条记录。
  • 这是拉链表设计时需要注意的一个粒度问题。我们当然也可以设置的粒度更小一些，一般按天就足够。

查询性能

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

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

拉链表回滚

  • 修正拉链表回滚问题本质就是：
    • 就是找到历史的快照。
  • 历史的快照可以根据起始更新时间，那你就找endtime小于你出错的数据就行了，出错日期的数据就行了。
  • 重新导入数据，将原始拉链表数据过滤到指定日期之前即可。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
举例：
拉链表dwd_userinfo_db,目前时间是2020-12-15，想回滚到2020-11-27,那么拉链表的状态得是2020-11-26


userid		starttime		endtime
1			2020-11-12		2020-11-26
1			2020-11-27		9999-12-31
2			2020-11-16		2020-12-13
2			2020-12-14		9999-12-31


拉链表回滚：过滤starttime<=2020-11-26的数据，将endtime>=2020-11-26的修改为9999-12-31


insert overwrite table dwd_userinfo_db
select
	userid,
	starttime,
	if(endtime>=2020-11-26,'9999-12-31',endtime)
from dwd_userinfo_db
where starttime<=2020-11-26

总结

  • 拉链表不存储冗余的数据，只有某行的数据发生变化，才需要保存下来，相比每次全量同步会节省存储空间
  • 能够查询到历史快照
  • 额外的增加了两列（dw_start_date dw_end_date），为数据行的生命周期
  • 使用拉链表的时候可以不加t_end_date，即失效日期，但是加上之后，能优化很多查询。
  • 可以加上当前行状态标识，能快速定位到当前状态。
  • 在拉链表的设计中可以加一些内容，因为我们每天保存一个状态，如果我们在这个状态里面加一个字段，比如如当天修改次数，那么拉链表的作用就会更大。

参考链接：

https://blog.csdn.net/qq_46893497/article/details/110787881

k12大数据技术

个人简介。