漫谈数据仓库之拉链表使用场景、原理和在Hive中的实现示例

小鱼儿 2023-02-13 08:07 57阅读 0赞

## 一、全量表-增量表-拉链表-流水表-介绍 ##

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

### 1、拉链表和流水表的区别 ###

> 流水表存放的是一个用户的变更记录，比如在一张流水表中，一天的数据中，会存放一个用户的每条修改记录，但是在拉链表中只有一条记录。
> 
> 拉链表通常是对账户信息的历史变动进行处理保留的结果，流水表是每天的交易形成的历史；  
> 流水表用于统计业务相关情况，拉链表用于统计账户及客户的情况。
> 
> 这是拉链表设计时需要注意的一个粒度问题。我们当然也可以设置的粒度更小一些，一般按天就足够。

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

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

比如：

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

这时，拉链表的作用就提现出来了，既节省空间，又满足了需求。

> 拉链表的使用场景——数据量很大，每天新增和变化的不大，并且需要查看历史某一个时间点或者时间段的历史快照

## 二、拉链表的使用场景举例 ##

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

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

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

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

### 1、为什么使用拉链表 ###

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

**方案一**

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

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

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

**方案二**

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

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

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

**拉链表**

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

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

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

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

## 三、拉链表使用示例 ##

### 1、背景和拉链表查询介绍 ###

下面这就是一张拉链表，存储的是用户订单的状态以及每条记录的生命周期。我们可以使用这张表拿到最新的当天的最新数据以及之前的历史数据。

一般在数仓中通过增加begin\_date,en\_date来表示，如下面拉链表order\_his所示，后两列是start\_date和end\_date.

> orderid createtime   modifiedtime   status start\_date end\_date  
> 1  2019-08-20  2019-08-20  创建 2019-08-20  2019-08-20  
> 1  2019-08-20  2019-08-21  支付 2019-08-21  2019-08-21  
> 1  2019-08-20  2019-08-22  完成 2019-08-22  9999-12-31  
> 2  2019-08-20  2019-08-20  创建 2019-08-20  2019-08-20  
> 2  2019-08-20  2019-08-21  完成 2019-08-21  9999-12-31  
> 3  2019-08-20  2019-08-20  创建 2019-08-20  2019-08-21  
> 3  2019-08-20  2019-08-22  支付 2019-08-22  9999-12-31  
> 4  2019-08-21  2019-08-21  创建 2019-08-21  2019-08-21  
> 4  2019-08-21  2019-08-22  支付 2019-08-22  9999-12-31  
> 5  2019-08-22  2019-08-22  创建 2019-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'

**如果查询2019-08-21的历史快照，则**

> select \* from order\_his where begin\_date <= '2019-08-21' and end\_date >= '2019-08-21'

**再简单介绍一下拉链表的更新：**  
   
**假设以天为维度，以每天的最后一个状态为当天的最终状态。**

### 2、具体示例  ###

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

> 1   2019-08-20  2019-08-20  创建  
> 2   2019-08-20  2019-08-20  创建  
> 3   2019-08-20  2019-08-20  创建  
> 1   2019-08-20  2019-08-21  支付  
> 2   2019-08-20  2019-08-21  完成  
> 4   2019-08-21  2019-08-21  创建  
> 1   2019-08-20  2019-08-22  完成  
> 3   2019-08-20  2019-08-22  支付  
> 4   2019-08-21  2019-08-22  支付  
> 5   2019-08-22  2019-08-22  创建

根据拉链表我们希望得到的是

> 1  2019-08-20  2019-08-20  创建 2019-08-20  2019-08-20  
> 1  2019-08-20  2019-08-21  支付 2019-08-21  2019-08-21  
> 1  2019-08-20  2019-08-22  完成 2019-08-22  9999-12-31  
> 2  2019-08-20  2019-08-20  创建 2019-08-20  2019-08-20  
> 2  2019-08-20  2019-08-21  完成 2019-08-21  9999-12-31  
> 3  2019-08-20  2019-08-20  创建 2019-08-20  2019-08-21  
> 3  2019-08-20  2019-08-22  支付 2019-08-22  9999-12-31  
> 4  2019-08-21  2019-08-21  创建 2019-08-21  2019-08-21  
> 4  2019-08-21  2019-08-22  支付 2019-08-22  9999-12-31  
> 5  2019-08-22  2019-08-22  创建 2019-08-22  9999-12-31

**可以看出 1，2，3，4每个订单的状态都有，并且也能统计到当前的有效状态。**  
   
**本例以hive为例，只考虑到实现，与性能无关**

### 3、首先创建表 ###

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

### 4、产出拉链表步骤 ###

首先全量更新，我们先到2019-08-20为止的数据。  
初始化，先把2019-08-20的数据初始化进去

> INSERT overwrite TABLE ods\_orders\_inc PARTITION (day = '2019-08-20')  
> SELECT orderid,createtime,modifiedtime,status  
> FROM orders  
> WHERE createtime < '2019-08-21' and modifiedtime <'2019-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 = '2019-08-20';

如下结果

> select \* from dw\_orders\_his;  
> OK  
> 1  2019-08-20  2019-08-20  创建 2019-08-20  9999-12-31  
> 2  2019-08-20  2019-08-20  创建 2019-08-20  9999-12-31  
> 3  2019-08-20  2019-08-20  创建 2019-08-20  9999-12-31

剩余需要进行增量更新

> INSERT overwrite TABLE ods\_orders\_inc PARTITION (day = '2019-08-21')  
> SELECT orderid,createtime,modifiedtime,status  
> FROM orders  
> WHERE (createtime = '2019-08-21'  and modifiedtime = '2019-08-21') OR modifiedtime = '2019-08-21';

增量更新后验证结果

> select \* from ods\_orders\_inc where day='2019-08-21';  
> OK  
> 1  2019-08-20  2019-08-21  支付 2019-08-21  
> 2  2019-08-20  2019-08-21  完成 2019-08-21  
> 4  2019-08-21  2019-08-21  创建 2019-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 > '2019-08-21' THEN '2019-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 = '2019-08-21'  
>     ) b  
>     ON   
>     a.orderid = b.orderid  
>   UNION  
>     SELECT   
>         orderid,  
>         createtime,  
>         modifiedtime,  
>         status,  
>         modifiedtime AS dw\_start\_date,  
>         '9999-12-31' AS dw\_end\_date  
>     FROM ods\_orders\_inc  
>     WHERE day = '2019-08-21'  
> ) x  
> ORDER BY orderid,dw\_start\_date;  
>  
> 
>   
> INSERT overwrite TABLE dw\_orders\_his  
> SELECT \* FROM dw\_orders\_his\_tmp;

同理，根据上面步骤把2019-08-22号的数据更新进去，最后结果如下

> select \* from dw\_orders\_his;  
> OK  
> 1  2019-08-20  2019-08-20  创建 2019-08-20  2019-08-20  
> 1  2019-08-20  2019-08-21  支付 2019-08-21  2019-08-21  
> 1  2019-08-20  2019-08-22  完成 2019-08-22  9999-12-31  
> 2  2019-08-20  2019-08-20  创建 2019-08-20  2019-08-20  
> 2  2019-08-20  2019-08-21  完成 2019-08-21  9999-12-31  
> 3  2019-08-20  2019-08-20  创建 2019-08-20  2019-08-21  
> 3  2019-08-20  2019-08-22  支付 2019-08-22  9999-12-31  
> 4  2019-08-21  2019-08-21  创建 2019-08-21  2019-08-21  
> 4  2019-08-21  2019-08-22  支付 2019-08-22  9999-12-31  
> 5  2019-08-22  2019-08-22  创建 2019-08-22  9999-12-31

至此，就得到了我们想要的数据。

### 5、值得注意的是 ###

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

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

### 三、拉链表查询性能优化 ###

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

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

另外，也可以考虑以下几点：

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

参考：https://www.cnblogs.com/slhs/p/9773421.html  
           https://www.jianshu.com/p/799252156379  
           https://www.jianshu.com/p/799252156379