数据仓库之拉链表详解-菜鸟笔记

什么是拉链表

记录历史数据，记录一个事物从开始一直到当前状态的所有变化的信息。

存储的是用户的最基本信息以及每条记录的生命周期。

拉链表的使用场景

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

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

可选方案：

方案一：每天只留最新的一份，比如我们每天用Sqoop抽取最新的一份全量数据到hive中

方案二：每天保留一份全量的切片数据

方案三：使用拉量表

为什么使用拉量表

方案一：

实现起来很简单，每天drop掉前一天的数据，重新抽一份最新数据，优点很明显，节省空间，使用起来也很方便

缺点同样明显，没有历史数据，想要查看历史数据只能通过其他方式，比如从流水表中抽取。

方案二

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

缺点是太占存储空间了，每天存储一份很多不变的全量的数据，对存储是一种极大的浪费。

但是需求是无耻的，数据的生命周期不是我们能完全左右的。

拉链表

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

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

其实它也可以满足方案二所能满足的需求，既能获取最新的数据，也能添加筛选条件也获取历史的数据。

拉链表的设计和实现

举个电商网站的例子

我们先看下在Mysql关系型数据库里的user表中信息变化

在2017-01-01这一天的数据是：

在2017-01-02这一天表中的数据是，用户002和004资料进行了修改，005是新增用户:

在2017-01-03这一天表中的数据是，用户004和005资料进行了修改，006是新增用户：

如果在数据仓库中设计成历史拉链表保存该表，则会有下边这样一张表，这是最新一天（即2017-01-03）的数据：

说明：

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来说，其文件系统中的文件是不能进行update的，目前只能进行删除和添加操作，基于这个前提，我们实现拉链表。

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

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

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

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

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

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

补充：

拉链表和流水表：

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

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

查询性能：

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

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

总结：

本文详细分享了拉链表相关的知识点

使用拉链表的时候可以不加t_end_date,即失效日期，但是加上之后，能优化很多查询。
可以加上当前行状态标识，能快速定位到当前状态。
在拉链表的设计中可以加一些内容，因为我们每天保存一个状态，如果我们在这个状态里面加一个字段，比如当天修改的次数，那么拉链表的作用会更大。

=========================================================================

缓慢变化维是维表设计中常见的一种方式，维度并不是不变的，随时间也会发生缓慢变化。如用户的手机号、邮箱信息可能随用户的状态变化而改变，所以可以考虑用缓慢变化维表来记录这种不同时间点的状态变化。

拉链表是针对缓慢变化维表的一种设计方式，记录一个事物从开始到当前状态的全部状态变化信息。

对于拉链表，可查看某日（如20190801）的快照数据

select *
from dw.cookie_user_zippertable ---拉链表
where start_date<='20190801' and end_date>='20190801'

数据仓库之拉链表详解

拉链表的使用场景

为什么使用拉量表

拉链表的设计和实现

在Hive中实现拉链表：

补充：

总结：

相关推荐

菜鸟笔记

热门教程