数据仓库--拉链表

需求

数据量有点大，表中某些字段有变化，但是呢变化的频率也不是很高，业务需求呢又需要统计这种变化状态，每天全量一份呢，有点不太现实，不仅浪费了存储空间，有时可能业务统计也有点麻烦

可选方案

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

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

什么是拉链表

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

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

   注册日期    | 用户编号   |   手机号码   | t_start_date | t_end_date 
--------------------------------------------------------------------
 2018-05-01   | u100001   | 13800000001 | 2018-05-01   | 2018-05-01 
 2018-05-01   | u100002   | 13800000002 | 2018-05-01   | 2018-05-01 
 2018-05-01   | u100003   | 13800000003 | 2018-05-01   | 2018-05-01 
 2018-05-01   | u100004   | 13800000004 | 2018-05-01   | 9999-12-31 
 2018-05-01   | u100001   | 13800001001 | 2018-05-02   | 9999-12-31 
 2018-05-02   | u100005   | 13800005005 | 2018-05-02   | 2018-05-02 
 2018-05-01   | u100003   | 13800003333 | 2018-05-02   | 2018-05-02 
 2018-05-01   | u100002   | 13822220000 | 2018-05-03   | 2018-05-03 
 2018-05-01   | u100003   | 13800003300 | 2018-05-03   | 2018-05-03 
 2018-05-01   | u100002   | 13822220022 | 2018-05-04   | 9999-12-31 
 2018-05-01   | u100005   | 13800550055 | 2018-05-05   | 9999-12-31 
 2018-05-01   | u100003   | 13833003333 | 2018-05-05   | 9999-12-31

使用场景

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

拉链表的设计与实现

准备数据

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

我们需要一张ODS层的用户表。至少需要用它来初始化(该表可以使用sqoop或者其他ETL工具抽取数据源到ods层)。
每日的用户更新表。

而且我们要确定拉链表的时间粒度，比如说拉链表每天只取一个状态，也就是说如果一天有3个状态变更，我们只取最后一个状态，这种天粒度的表其实已经能解决大部分的问题了，一天的粒度不够的，那么我们可以取小时的，但是如果取小时的话，那么我们需要将按照小时去定时执行计算拉链表的程序，并且检索条件或者分区也需要考虑设置成小时粒度的。

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

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

这里我们给出ODS的用户表、用户更新表、拉链表的建表语句和目标数据：

用户表:

CREATE EXTERNAL TABLE ods.user (
  user_num STRING COMMENT '用户编号',
  mobile STRING COMMENT '手机号码',
  reg_date STRING COMMENT '创建日期'
COMMENT '用户资料表'
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION '/ods/user';

用户表的数据:

创建日期      |用户编号|手机号码
---------------------------------
2018-05-01   |u100001|13800000001
2018-05-01   |u100002|13800000002
2018-05-01   |u100003|13800000003
2018-05-01   |u100004|13800000004

用户更新表:

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

用户更新表的数据:

 创建日期      |   用户编号 |    手机号码  | 备注
 ----------------------------------------------------------------------
 2018-05-02   | u100001   | 13800001001 | 由13800000001更换为13800001001
 2018-05-02   | u100005   | 13800005005 | 创建新用户
 2018-05-02   | u100003   | 13800003333 | 更换为成都移动
 2018-05-03   | u100002   | 13822220000 | 
 2018-05-03   | u100003   | 13800003300 | 更新为新的靓号
 2018-05-04   | u100002   | 13822220022 |  
 2018-05-05   | u100005   | 13800550055 | 更换不需要理由
 2018-05-05   | u100003   | 13833003333 |

拉链表：

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

拉链表的结果数据应该如下所示，接下来我们会慢慢讲解如何生成拉链表:

   注册日期    | 用户编号   |   手机号码   | t_start_date | t_end_date 
--------------------------------------------------------------------
 2018-05-01   | u100001   | 13800000001 | 2018-05-01   | 2018-05-01 
 2018-05-01   | u100002   | 13800000002 | 2018-05-01   | 2018-05-01 
 2018-05-01   | u100003   | 13800000003 | 2018-05-01   | 2018-05-01 
 2018-05-01   | u100004   | 13800000004 | 2018-05-01   | 9999-12-31 
 2018-05-01   | u100001   | 13800001001 | 2018-05-02   | 9999-12-31 
 2018-05-02   | u100005   | 13800005005 | 2018-05-02   | 2018-05-02 
 2018-05-01   | u100003   | 13800003333 | 2018-05-02   | 2018-05-02 
 2018-05-01   | u100002   | 13822220000 | 2018-05-03   | 2018-05-03 
 2018-05-01   | u100003   | 13800003300 | 2018-05-03   | 2018-05-03 
 2018-05-01   | u100002   | 13822220022 | 2018-05-04   | 9999-12-31 
 2018-05-01   | u100005   | 13800550055 | 2018-05-05   | 9999-12-31 
 2018-05-01   | u100003   | 13833003333 | 2018-05-05   | 9999-12-31

拉链表实现

初始化拉链表:

我们需要通过用户表去初始化拉链表，这个的目的是首次加载完所有的历史数据，后续的部分即可增量处理修改记录，例如将拉链表初始化为2018-05-01之前的所有数据，后续的增量可以从2018-05-02、2018-05-03.....等每天去增量记录当天的修改记录(细粒度为每天的情况下)

INSERT overwrite TABLE dws.user_his
SELECT user_num,
       mobile,
       reg_date,
       t_start_date,
       '9999-12-31' t_end_date,
FROM ods.user
WHERE reg_date <= '2018-05-01';

增量处理拉链表

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 FROM_UNIXTIME(UNIX_TIMESTAMP(),'YYYY-MM-dd')
                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,
           FROM_UNIXTIME(UNIX_TIMESTAMP(),'YYYY-MM-dd') AS t_start_time,
           '9999-12-31' AS t_end_time
    FROM ods.user_update AS C
) AS T;

SQL描述：
将拉链表作为左表，更新表为右表，做左连接，连接关键字为用户ID(可以考虑换成其他的唯一关键字或者组合关键字)，因为这里右表是在某一个细粒度下，所以右表在主键唯一的情况下，某个粒度的状态肯定唯一(对于状态不唯一的，可考虑取最后的一条更新记录或者平均值，具体的要根据业务场景来决定)，然后对于t_end_time(拉链闭合时间)是非9999-12-31(非9999-12-31表示拉链已经闭合，那么不需要再修改时间)的，时间不用改变，对于拉链非闭合的，且更新表有更新的(B.user_num IS NOT NULL表示左连接时，右表有数据，即有数据能连接上)，这部分将拉链闭合时间设置成程序的当前时间(这里粒度是天，如果粒度是小时，那么我们需要将时间设置成小时，并且调度也需要考虑为小时为周期),这里主要的目的是修改user_his(上个周期更新的状态记录)能关联上的拉链时间;然后我们需要union一下当前最后一次更新状态的记录，并把时间设置成未拉链时间,这是为了下一次增量的时候检测出未拉链时间并更改时间的；然后将这两个数据源合并后插入到拉链表即可。

补充

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

拉链表和流水表：流水表存放的是一个用户的变更记录，比如在一张流水表中，一天的数据中，会存放一个用户的每条修改记录，但是在拉链表中只有一条记录。
这是拉链表设计时需要注意的一个粒度问题。我们当然也可以设置的粒度更小一些，一般按天就足够。
查询性能：拉链表当然也会遇到查询性能的问题，比如说我们存放了5年的拉链数据，那么这张表势必会比较大，当查询的时候性能就比较低了，个人认为两个思路来解决：
- 在一些查询引擎中，我们对start_date和end_date做索引，这样能提高不少性能。
- 保留部分历史数据，比如说我们一张表里面存放全量的拉链表数据，然后再对外暴露一张只提供近3个月数据的拉链表。

参考文献

posted @ 2019-06-26 18:39 shenjie2017 阅读(844) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

shenjie2017