事实表和维度表

事实表：

事务事实表：（->明细事实表->聚合事实表）

记录的是事务层面的事实，保存的是最原子的数据，也叫做“原子事实表”。事务事实表中的数据在事务事件发生后产生，数据的粒度通常是每个事务一条记录。一旦事务被提交，事实表数据被插入，数据就不再进行更改，其更新方式为增量更新。

明细事实表（单事件事实表，流程事实表）

一般位于DWD层，该层事实表设计不进行聚合，汇总等动作，仅做数据规范化，数据降维动作，同时数据保持业务事务粒度，确保数据信息无丢失。

单事件事实表：

1.更方便跟踪业务流程细节数据，针对特殊的业务分析场景比较方便和灵活，数据处理上也更加灵活；
2.不方便的地方就是数仓中需要管理太多的事实表，同时跟踪业务流转不够直观；

流程事实表

能够更直观的跟踪业务流转和当前状态，流程事实集中，方便大部分的通用分析应用场景，由于和业务侧的数据模型设计思路一致，也是目前最常用的事实表设计；但是细节数据跟踪不到位，特殊场景的分析不够灵活；

聚合事实表

相对于明细事实表，聚合事实表通常是在明细事实表的基础上，按照一定的粒度粗细进行的汇总、聚合操作，它的粒度较明细数据粒度粗，同时伴随着细节信息的丢失。聚合事实表一般位于DWS层，聚合事实表的数据来源可以是两种明细事实表中的任一种。

• 通用汇总层：封装底层计算逻辑，做通用汇总，避免上层直接访问下层明细数据，应用广泛
• 日粒度：数据范围一般为T-1天的数据
• 周期性积累：用于周期性分析，数据来源于通用汇总层，或者日粒度
• 历史积累：历史数据积累的大表，用于用户画像，特征提取，经营分析等场景，计算比较耗时。

  周期快照事实表

  以一定的周期间隔来记录事实，它是在事务事实表之上建立的聚集表，记录的事实是这一段时间的聚集事实值。eg：销售日快照表，库存日快照表

  积累快照事实表

  存储的不确定周期的事务数据的快照信息。

  维度表

  维度表：存放详细的数据信息，有唯一的主键ID。如上面的商品表、用户表等等。
  维度表示你要对数据进行分析时所用的一个量, 比如你要分析产品销售情况, 你可以选择按类别来进行分析,或按区域来分析. 这样的按..分析就构成一个维度。前面的示例就可以有两个维度：类型和区域。另外每个维度还可以有子维度（称为属性），例如类别可以有子类型，产品名等属性。

  稳定维度表

  部分维度表的维度是在维度表产生以后，属性是稳定的，无变化的。eg：时间维度，区域维度，

  缓慢渐变维度表（拉链表）

  维度数据会随着时间发生变化，变化速度非常缓慢。eg：电商平台的用户维度表，用户的收货地址是缓慢变化的。

总而总之：事实表有三种（事务事实表，周期快照事实表，历史快照事实表），维度表有两种（稳定维度维表，渐变维度维表）。

一文搞定数据仓库之拉链表，流水表，全量表，增量表

1. 全量表：每天的所有的最新状态的数据，
2. 增量表：每天的新增数据，增量数据是上次导出之后的新数据。
3. 拉链表：维护历史状态，以及最新状态数据的一种表，拉链表根据拉链粒度的不同，实际上相当于快照，只不过做了优化，去除了一部分不变的记录而已,通过拉链表可以很方便的还原出拉链时点的客户记录。
4. 流水表： 对于表的每一个修改都会记录，可以用于反映实际记录的变更。

拉链表通常是对账户信息的历史变动进行处理保留的结果，流水表是每天的交易形成的历史；
流水表用于统计业务相关情况，拉链表用于统计账户及客户的情况
数据仓库之拉链表（原理、设计以及在Hive中的实现）

在有些情况下，为了保持历史的一些状态，需要用拉链表来做，这样做目的在可以保留所有状态的情况下可以节省空间。

拉链表适用于以下几种情况吧

数据量有点大，表中某些字段有变化，但是呢变化的频率也不是很高，业务需求呢又需要统计这种变化状态，每天全量一份呢，有点不太现实，

不仅浪费了存储空间，有时可能业务统计也有点麻烦，这时，拉链表的作用就提现出来了，既节省空间，又满足了需求。

一般在数仓中通过增加begin_date,en_date来表示，如下例，后两列是start_date和end_date.

1 2016-08-20 2016-08-20 创建 2016-08-20 2016-08-20
1 2016-08-20 2016-08-21 支付 2016-08-21 2016-08-21
1 2016-08-20 2016-08-22 完成 2016-08-22 9999-12-31
2 2016-08-20 2016-08-20 创建 2016-08-20 2016-08-20
2 2016-08-20 2016-08-21 完成 2016-08-21 9999-12-31
3 2016-08-20 2016-08-20 创建 2016-08-20 2016-08-21
3 2016-08-20 2016-08-22 支付 2016-08-22 9999-12-31
4 2016-08-21 2016-08-21 创建 2016-08-21 2016-08-21
4 2016-08-21 2016-08-22 支付 2016-08-22 9999-12-31
5 2016-08-22 2016-08-22 创建 2016-08-22 9999-12-31
begin_date表示该条记录的生命周期开始时间，end_date表示该条记录的生命周期结束时间；

end_date = ‘9999-12-31’表示该条记录目前处于有效状态；

如果查询当前所有有效的记录，则select * from order_his where dw_end_date = ‘9999-12-31′

如果查询2016-08-21的历史快照，则select * from order_his where begin_date <= ‘2016-08-21′ and end_date >= ‘2016-08-21’

再简单介绍一下拉链表的更新：

假设以天为维度，以每天的最后一个状态为当天的最终状态。

以一张订单表为例，如下是原始数据，每天的订单状态明细

1 2016-08-20 2016-08-20 创建
2 2016-08-20 2016-08-20 创建
3 2016-08-20 2016-08-20 创建
1 2016-08-20 2016-08-21 支付
2 2016-08-20 2016-08-21 完成
4 2016-08-21 2016-08-21 创建
1 2016-08-20 2016-08-22 完成
3 2016-08-20 2016-08-22 支付
4 2016-08-21 2016-08-22 支付
5 2016-08-22 2016-08-22 创建
根据拉链表我们希望得到的是

1 2016-08-20 2016-08-20 创建 2016-08-20 2016-08-20
1 2016-08-20 2016-08-21 支付 2016-08-21 2016-08-21
1 2016-08-20 2016-08-22 完成 2016-08-22 9999-12-31
2 2016-08-20 2016-08-20 创建 2016-08-20 2016-08-20
2 2016-08-20 2016-08-21 完成 2016-08-21 9999-12-31
3 2016-08-20 2016-08-20 创建 2016-08-20 2016-08-21
3 2016-08-20 2016-08-22 支付 2016-08-22 9999-12-31
4 2016-08-21 2016-08-21 创建 2016-08-21 2016-08-21
4 2016-08-21 2016-08-22 支付 2016-08-22 9999-12-31
5 2016-08-22 2016-08-22 创建 2016-08-22 9999-12-31
可以看出 1，2，3，4每个订单的状态都有，并且也能统计到当前的有效状态。

本例以hive为例，只考虑到实现，与性能无关

首先创建表

CREATE TABLE orders (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
) row format delimited fields terminated by ‘\t’

CREATE TABLE ods_orders_inc (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
) PARTITIONED BY (day STRING)
row format delimited fields terminated by ‘\t’

CREATE TABLE dw_orders_his (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING,
dw_start_date STRING,
dw_end_date STRING
) row format delimited fields terminated by ‘\t’ ;
首先全量更新，我们先到2016-08-20为止的数据。

初始化，先把2016-08-20的数据初始化进去

INSERT overwrite TABLE ods_orders_inc PARTITION (day = ‘2016-08-20’)
SELECT orderid,createtime,modifiedtime,status
FROM orders
WHERE createtime < ‘2016-08-21’ and modifiedtime <’2016-08-21’;
刷到dw中

INSERT overwrite TABLE dw_orders_his
SELECT orderid,createtime,modifiedtime,status,
createtime AS dw_start_date,
‘9999-12-31’ AS dw_end_date
FROM ods_orders_inc
WHERE day = ‘2016-08-20’;

如下结果

select * from dw_orders_his;
OK
1 2016-08-20 2016-08-20 创建 2016-08-20 9999-12-31
2 2016-08-20 2016-08-20 创建 2016-08-20 9999-12-31
3 2016-08-20 2016-08-20 创建 2016-08-20 9999-12-31
剩余需要进行增量更新

INSERT overwrite TABLE ods_orders_inc PARTITION (day = ‘2016-08-21’)
SELECT orderid,createtime,modifiedtime,status
FROM orders
WHERE (createtime = ‘2016-08-21’ and modifiedtime = ‘2016-08-21’) OR modifiedtime = ‘2016-08-21’;

select * from ods_orders_inc where day=’2016-08-21’;
OK
1 2016-08-20 2016-08-21 支付 2016-08-21
2 2016-08-20 2016-08-21 完成 2016-08-21
4 2016-08-21 2016-08-21 创建 2016-08-21
先放到增量表中，然后进行关联到一张临时表中，在插入到新表中

DROP TABLE IF EXISTS dw_orders_his_tmp;
CREATE TABLE dw_orders_his_tmp AS
SELECT orderid,
createtime,
modifiedtime,
status,
dw_start_date,
dw_end_date
FROM (
SELECT a.orderid,
a.createtime,
a.modifiedtime,
a.status,
a.dw_start_date,
CASE WHEN b.orderid IS NOT NULL AND a.dw_end_date > ‘2016-08-21’ THEN ‘2016-08-21’ ELSE a.dw_end_date END AS dw_end_date
FROM dw_orders_his a
left outer join (SELECT * FROM ods_orders_inc WHERE day = ‘2016-08-21’) b
ON (a.orderid = b.orderid)
UNION ALL
SELECT orderid,
createtime,
modifiedtime,
status,
modifiedtime AS dw_start_date,
‘9999-12-31’ AS dw_end_date
FROM ods_orders_inc
WHERE day = ‘2016-08-21’
) x
ORDER BY orderid,dw_start_date;

INSERT overwrite TABLE dw_orders_his
SELECT * FROM dw_orders_his_tmp;
在根据上面步骤把2016-08-22号的数据更新进去，最后结果如下

select * from dw_orders_his;
OK
1 2016-08-20 2016-08-20 创建 2016-08-20 2016-08-20
1 2016-08-20 2016-08-21 支付 2016-08-21 2016-08-21
1 2016-08-20 2016-08-22 完成 2016-08-22 9999-12-31
2 2016-08-20 2016-08-20 创建 2016-08-20 2016-08-20
2 2016-08-20 2016-08-21 完成 2016-08-21 9999-12-31
3 2016-08-20 2016-08-20 创建 2016-08-20 2016-08-21
3 2016-08-20 2016-08-22 支付 2016-08-22 9999-12-31
4 2016-08-21 2016-08-21 创建 2016-08-21 2016-08-21
4 2016-08-21 2016-08-22 支付 2016-08-22 9999-12-31
5 2016-08-22 2016-08-22 创建 2016-08-22 9999-12-31
至此，就得到了我们想要的数据。

值得注意的是，订单表中数据同一天有多次状态更新，应以每天的最后一个状态为当天的最终状态。比如一天之内订单状态创建,支付,完成都有，应拉取最终的状态进行拉练表更新，否则后面的数据可能就会出现异常，比如

6 2016-08-22 2016-08-22 创建 2016-08-22 9999-12-31
6 2016-08-22 2016-08-22 支付 2016-08-22 9999-12-31
6 2016-08-22 2016-08-22 完成 2016-08-22 9999-12-31

————————————————
版权声明：本文为CSDN博主「mtj66」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/mtj66/article/details/78019370

漫谈数据仓库之拉链表（原理、设计以及在Hive中的实现）

转载rolin-刘瑞 最后发布于2018-04-25 18:40:59 阅读数 6247 收藏
展开

0x00 前言

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

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

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

   0x01 什么是拉链表

   拉链表是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。
   我们先看一个示例，这就是一张拉链表，存储的是用户的最基本信息以及每条记录的生命周期。我们可以使用这张表拿到最新的当天的最新数据以及之前的历史数据。

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

拉链表的使用场景

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

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

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

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

  为什么使用拉链表

  现在我们对前面提到的三种进行逐个的分析。
  方案一
  这种方案就不用多说了，实现起来很简单，每天drop掉前一天的数据，重新抽一份最新的。
  优点很明显，节省空间，一些普通的使用也很方便，不用在选择表的时候加一个时间分区什么的。
  缺点同样明显，没有历史数据，先翻翻旧账只能通过其它方式，比如从流水表里面抽。
  方案二
  每天一份全量的切片是一种比较稳妥的方案，而且历史数据也在。
  缺点就是存储空间占用量太大太大了，如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费，这点我感触还是很深的……
  当然我们也可以做一些取舍，比如只保留近一个月的数据？但是，需求是无耻的，数据的生命周期不是我们能完全左右的。
  拉链表
  拉链表在使用上基本兼顾了我们的需求。
  首先它在空间上做了一个取舍，虽说不像方案一那样占用量那么小，但是它每日的增量可能只有方案二的千分之一甚至是万分之一。
  其实它能满足方案二所能满足的需求，既能获取最新的数据，也能添加筛选条件也获取历史的数据。
  所以我们还是很有必要来使用拉链表的。

  0x02 拉链表的设计和实现

  如何设计一张拉链表

  下面我们来举个栗子详细看一下拉链表。
  我们接上在《漫谈数据仓库之维度建模》中的电商网站的例子，现在以用户的拉链表来说明。
  我们先看一下在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版本来讲，其文件系统中的文件是不能做改变的，也就是说Hive的表智能进行删除和添加操作，而不能进行update。基于这个前提，我们来实现拉链表。
  还是以上面的用户表为例，我们要实现用户的拉链表。在实现它之前，我们需要先确定一下我们有哪些数据源可以用。

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

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

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

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

实现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
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22

0x03 补充

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

拉链表和流水表

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

查询性能

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

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

   0xFF 总结

   我们在这篇文章里面详细地分享了一下和拉链表相关的知识点，但是仍然会有一会遗漏。欢迎交流。