数据仓库中的拉链表（hive实现)_用拉链表记录所有变更hive

数据仓库中的拉链表（hive实现)

前言

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

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

1、什么是拉链表

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

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

可以使用冒号来定义表格的对齐方式，如下：

我们暂且不对这张表做细致的讲解，后文会专门来阐述怎么来设计、实现和使用它。

拉链表的使用场景

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

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

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

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

方案一

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

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

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

方案二

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

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

当然我们也可以做一些取舍，比如只保留近一个月的数据？但是，需求是无耻的，数据的生命周期不是我们能完全左右的。

拉链表

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

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

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

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

0x02 拉链表的设计和实现

如何设计一张拉链表

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

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

我们先看一下在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’。（此处要好好理解，是拉链表比较重要的一块。） 
在Hive中实现拉链表

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

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

我们需要一张ODS层的用户全量表。至少需要用它来初始化。 
每日的用户更新表。 
而且我们要确定拉链表的时间粒度，比如说拉链表每天只取一个状态，也就是说如果一天有3个状态变更，我们只取最后一个状态，这种天粒度的表其实已经能解决大部分的问题了。

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

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

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

1. CREATE EXTERNAL TABLE ods.user (

2. user_num STRING COMMENT '用户编号',

3. mobile STRING COMMENT '手机号码',

4. reg_date STRING COMMENT '注册日期'

5. COMMENT '用户资料表'

6. PARTITIONED BY (dt string)

7. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'

8. STORED AS ORC

9. LOCATION '/ods/user';

10. )

ods层的user_update表

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

1. CREATE EXTERNAL TABLE ods.user_update (

2. user_num STRING COMMENT '用户编号',

3. mobile STRING COMMENT '手机号码',

4. reg_date STRING COMMENT '注册日期'

5. COMMENT '每日用户资料更新表'

6. PARTITIONED BY (dt string)

7. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'

8. STORED AS ORC

9. LOCATION '/ods/user_update';

10. )

拉链表

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

1. CREATE EXTERNAL TABLE dws.user_his (

2. user_num STRING COMMENT '用户编号',

3. mobile STRING COMMENT '手机号码',

4. reg_date STRING COMMENT '用户编号',

5. t_start_date ,

6. t_end_date

7. )COMMENT '用户资料拉链表'

8. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'

9. STORED AS ORC

10. LOCATION '/dws/user_his';

• 实现sql语句

然后初始化的sql就不写了，其实就相当于是拿一天的ods层用户表过来就行，我们写一下每日的更新语句。

现在我们假设我们已经已经初始化了2017-01-01的日期，然后需要更新2017-01-02那一天的数据，我们有了下面的Sql。

然后把两个日期设置为变量就可以了。

1. INSERT OVERWRITE TABLE dws.user_his

2. SELECT * FROM

3. (

4. SELECT A.user_num,

5. A.mobile,

6. A.reg_date,

7. A.t_start_time,

8. CASE

9. WHEN A.t_end_time = '9999-12-31' AND B.user_num IS NOT NULL THEN '2017-01-01'

10. ELSE A.t_end_time

11. END AS t_end_time

12. FROM dws.user_his AS A

13. LEFT JOIN ods.user_update AS B

14. ON A.user_num = B.user_num

15. UNION

16. SELECT C.user_num,

17. C.mobile,

18. C.reg_date,

19. '2017-01-02' AS t_start_time,

20. '9999-12-31' AS t_end_time

21. FROM ods.user_update AS C

22. ) AS T

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

拉链表和流水表

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

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

查询性能

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

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

 

hive数据库设计中的拉链表、增量表、全量表

置顶2018年03月19日 23:03:27

阅读数：1321

1)       存量、流量、增量

（1）存量：系统在某一时点时的所保有的数量；
（2）流量：是指在某一段时间内流入/出系统的数量
（3）增量：则是指在某一段时间内系统中保有数量的变化
（4）增量=流入量--流出量
（5）本期期末存量=上期期末存量+本期内增量

2)       拉链表：注意事项
（1）记录一个事物从开始，一直到当前状态的所有变化的信息；
（2）拉链表每次上报的都是历史记录的最终状态，是记录在当前时刻的历史总量；
（3）当前记录存的是当前时间之前的所有历史记录的最后变化量（总量）；
（4）存量是在某一时刻的总量；
（5）存量一般设计成拉链表（月报-常用、日报）；
（6）流量和存量的区别：流量是增量；存量是总量；
（7）封链时间可以是2999，3000，9999等等比较大的年份；拉链表到期数据要报0；
（8）拉链表和增量表的共同点：表结构基本一样。

3)       拉链表：例子详解

4)       增量表：注意事项
（1）记录每次增加的量，而不是总量；
（2）流量是指在一定时间内的增量；
（3）流量一般设计成增量表（日报-常用、月报）；
（4）流量和存量的区别：流量是增量；存量是总量；
（5）增量表，只报变化量，无变化不用报

5)       增量表：例子详解（增量表只有上报时间，开始时间更改为上报时间）

 

6)       全量表：注意事项
（1）全量表，有无变化，都要报
（2）每次上报的数据都是所有的数据（变化的 + 没有变化的）

7)       全量表：例子详解（全量表只有上报时间，开始时间更改为上报时间）