一文搞定資料倉庫之拉連結清單，流水表，全量表，增量表

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