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

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


http://www.cnblogs.com/wujin/p/6121754.html


http://www.jianshu.com/p/799252156379


http://lxw1234.com/archives/2015/04/20.htm

--------------------- 

作者：mtj66

来源：CSDN

原文：https://blog.csdn.net/mtj66/article/details/78019370?utm_source=copy 
      

https://blog.csdn.net/baidu_21088863/article/details/77802758