0x00 前言

过了半年时间,对数据仓库的理解又有了一些不同的认识,翻出来之前写的关于拉链表的内容,稍作修改重新发出来。本篇将会谈一谈在数据仓库中拉链表相关的内容,包括它的原理、设计、以及在我们大数据场景下的实现方式。

内容

全文由下面几个部分组成:

  1. 先分享一下拉链表的用途、什么是拉链表。
  2. 举一个具体的应用场景,来设计并实现一份拉链表,最后并通过一些例子说明如何使用我们设计的这张表(因为现在 Hive 的大规模使用,我们会以 Hive 场景下的设计为例)。
  3. 分析一下拉链表的优缺点,并对前面的提到的一些内容进行补充说明,比如说拉链表和流水表的区别。

0x01 什么是拉链表

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

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

注册日期 用户编号 手机号码 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 使用双备份或者三备份的话就更大一些。
  2. 表中的部分字段会被 Update 更新操作,如用户联系方式,产品的描述信息,订单的状态等等。
  3. 需要查看某一个时间点或者时间段的历史快照信息,比如,查看某一个订单在历史某一个时间点的状态。
  4. 表中的记录变化的比例和频率不是很大,比如,总共有 10 亿的用户,每天新增和发生变化的有 200 万左右,变化的比例占的很小。

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

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

为什么使用拉链表

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

方案一

这种方案就不用多说了,实现起来很简单,每天 Drop 掉前一天的数据,重新抽一份最新的。

优点很明显,节省空间,一些普通的使用也很方便,不用在选择表的时候加一个时间分区什么的。

缺点同样明显,没有历史数据,先翻翻旧账只能通过其它方式,比如从流水表里面抽。

方案二

每天一份全量的切片是一种比较稳妥的方案,而且历史数据也在。

缺点就是存储空间占用量太大太大了,如果对这边表每天都保留一份全量,那么每次全量中会保存很多不变的信息,对存储是极大的浪费。

当然我们也可以做一些取舍,比如只保留近一个月的数据。但是,需求是无耻的,数据的生命周期不是我们能完全左右的,你会发现,存储周期可能会从 30 天变为 90 天,然后再从 90 天变为 1 年,然后需要永久保存。

拉链表

拉链表在使用上基本兼顾了我们的需求。

首先它在空间上做了一个取舍,虽说不像方案一那样占用量那么小,但是它每日的增量可能只有方案二的千分之一甚至是万分之一。

其实它能满足方案二所能满足的需求,既能获取最新的数据,也能添加筛选条件也获取历史的数据。所以在一些场景下,拉链表是能解决很多问题的。

0x02 拉链表的设计和实现

如何设计一张拉链表

下面我们来举个栗子详细聊一下拉链表。以用户资料表为例,我们先看一下在关系型数据库里的 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'。(此处要好好理解,是拉链表比较重要的一块。

在Hive中实现拉链表

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

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

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

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

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

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

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

0x03 补充

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

拉链表和流水表

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

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

查询性能

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

  1. 在一些查询引擎中,我们对 start_date 和 end_date 做索引,这样能提高不少性能。这种方法其实在 Hive 中行不通,因为 Hive 相当于没有索引,不过在其它系统中可以考虑。
  2. 保留部分历史数据,比如说我们一张表里面存放全量的拉链表数据,然后再对外暴露一张只提供近 3 个月数据的拉链表。

淘汰机制

关于淘汰机制,其实和性能也是有关系的,一方面是因为所有数据的积累会导致计算越来越慢,另一方面是业务侧其实对历史数据的需求也有一定的优先级的。

因此在设计拉链表的时候可以制定一些数据的淘汰机制。淘汰的数据不一定要删除,比如我们建立两张拉链表,一张拉链表中只保存最新的十条数据,其它的数据会存入一张历史拉链表中。

其它

在使用中还有了一些心得,补充进来:

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

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

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

0xFF 总结

随便吐个槽,感觉自己了解的东西越多,就越不敢随便写博客了。

最开始写博客其实挺随心所欲的,胆子很大,自己有想法就敢写出来,对错也不太在意。

然后写着写着就会收到很多的反馈,有互相交流问题的,有指出毛病的,感觉比起之前来讲,更对自己写的内容负责了,所以写起来就谨慎了很多。

再接着发现,自己之前的理解其实有很多不对的地方,会很心虚,而且互联网是个舆论很可怕的地方,自己一些理解的错误、或者没有考虑周到的地方,有些人会把你喷成一坨狗屎,看了有些评论后感觉还是自己写了不要发出来丢人了。这时候会觉得写博客要更慎重的,不把一个东西理解的很深,就不敢写出来。

不过,心态还是要放平和,毕竟收到的正反馈还是远远多于负反馈的,写博客就当是自己对学习的记录,顺便和世界分享一下自己的想法。有压力才能更有动力学习,对内容负责才能更深入理解。

results matching ""

    No results matching ""