拉鏈表在數(shù)倉的實際開發(fā)中應(yīng)用廣泛，切實解決優(yōu)化存儲
重點是對變化的數(shù)據(jù)進行統(tǒng)一管理，和緩慢變化維的處理還是不一樣的。注意對比學(xué)習(xí)

拉鏈表概述

• 拉鏈表是針對數(shù)據(jù)倉庫設(shè)計中表存儲數(shù)據(jù)的方式而定義的，顧名思義，所謂拉鏈，就是記錄歷史。記錄一個事物從開始，一直到當(dāng)前狀態(tài)的所有變化的信息。

• 我們先看一個示例，這就是一張拉鏈表，存儲的是用戶的最基本信息以及每條記錄的生命周期。我們可以使用這張表拿到最新的當(dāng)天的最新數(shù)據(jù)以及之前的歷史數(shù)據(jù)。

  
  
  數(shù)倉拉鏈表示例.PNG
• 從上圖中可以看出，用戶的手機號碼是會進行改變的?？梢愿鶕?jù)開始日期和結(jié)束日期進行判斷。

拉鏈表使用場景

• 1. 數(shù)據(jù)量比較大;
• 2. 表中的部分字段會被update,如用戶的地址，產(chǎn)品的描述信息，訂單的狀態(tài)等等;
• 3. 需要查看某一個時間點或者時間段的歷史快照信息，比如，查看某一個訂單在歷史某一個時間點的狀態(tài)，比如，查看某一個用戶在過去某一段時間內(nèi)，更新過幾次等等;
• 4. 變化的比例和頻率不是很大，比如，總共有1000萬的會員，每天新增和發(fā)生變化的有10萬左右;
• 5. 如果對這邊表每天都保留一份全量，那么每次全量中會保存很多不變的信息，對存儲是極大的浪費;
• 拉鏈歷史表，既能滿足反應(yīng)數(shù)據(jù)的歷史狀態(tài)，又可以最大程度的節(jié)省存儲;
• 案例
  在數(shù)據(jù)倉庫的數(shù)據(jù)模型設(shè)計過程中，經(jīng)常會遇到下面這種表的設(shè)計：
  • 有一些表的數(shù)據(jù)量很大，比如一張用戶表，大約10億條記錄，50個字段，這種表，即使使用ORC壓縮，單張表的存儲也會超過100G，在HDFS使用雙備份或者三備份的話就更大一些。
  • 表中的部分字段會被update更新操作，如用戶聯(lián)系方式，產(chǎn)品的描述信息，訂單的狀態(tài)等等。
  • 需要查看某一個時間點或者時間段的歷史快照信息，比如，查看某一個訂單在歷史某一個時間點的狀態(tài)。
  • 表中的記錄變化的比例和頻率不是很大，比如，總共有10億的用戶，每天新增和發(fā)生變化的有200萬左右，變化的比例占的很小。

那么對于這種表我該如何設(shè)計呢？下面有幾種方案可選:

• 方案一：每天只留最新的一份，比如我們每天用Sqoop抽取最新的一份全量數(shù)據(jù)到Hive中。
• 方案二：每天保留一份全量的切片數(shù)據(jù)。
• 方案三：使用拉鏈表。

如何設(shè)計拉鏈表

案例參考-漫談數(shù)據(jù)倉庫之拉鏈表（原理、設(shè)計以及在Hive中的實現(xiàn)）
我們先看一下在Mysql關(guān)系型數(shù)據(jù)庫里的user表中信息變化。
在2017-01-01這一天表中的數(shù)據(jù)是：

在2017-01-02這一天表中的數(shù)據(jù)是， 用戶002和004資料進行了修改，005是新增用戶：

在2017-01-03這一天表中的數(shù)據(jù)是， 用戶004和005資料進行了修改，006是新增用戶：

如果在數(shù)據(jù)倉庫中設(shè)計成歷史拉鏈表保存該表，則會有下面這樣一張表，這是最新一天（即2017-01-03）的數(shù)據(jù)：

拉鏈表設(shè)計.PNG

• t_start_date表示該條記錄的生命周期開始時間，t_end_date表示該條記錄的生命周期結(jié)束時間。
• t_end_date = '9999-12-31'表示該條記錄目前處于有效狀態(tài)。
• 如果查詢當(dāng)前所有有效的記錄，則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實現(xiàn)拉鏈表

只考慮實現(xiàn)，不考慮性能
-- 時間粒度：天 day，建模之前需要按照Kimball思想"四步走"戰(zhàn)略
以訂單表為例，數(shù)據(jù)如下，每天的訂單明細：

orderid createtime modifiedtime status
1   2016-08-20  2016-08-20  創(chuàng)建
2   2016-08-20  2016-08-20  創(chuàng)建
3   2016-08-20  2016-08-20  創(chuàng)建
1   2016-08-20  2016-08-21  支付
2   2016-08-20  2016-08-21  完成
4   2016-08-21  2016-08-21  創(chuàng)建
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  創(chuàng)建

根據(jù)拉鏈表，我們先想得到

1  2016-08-20  2016-08-20  創(chuàng)建 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  創(chuàng)建 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  創(chuàng)建 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  創(chuàng)建 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  創(chuàng)建 2016-08-22  9999-12-31

可以看出 1，2，3，4每個訂單的狀態(tài)都有，并且也能統(tǒng)計到當(dāng)前的有效狀態(tài)。

• 初始化hive表

-- 創(chuàng)建訂單表，使用sqoop進行從mysql中導(dǎo)入hive
CREATE TABLE orders (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
) row format delimited fields terminated by '\t'
 
 -- 訂單全量，按天進行分區(qū)
CREATE TABLE ods_orders_inc (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
) PARTITIONED BY (day STRING)
row format delimited fields terminated by '\t'
 
 -- 訂單拉鏈表設(shè)計
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為止的數(shù)據(jù)。
初始化，先把2016-08-20的數(shù)據(jù)初始化進去。

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

結(jié)果測試如下：

select * from dw_orders_his;
OK
1  2016-08-20  2016-08-20  創(chuàng)建 2016-08-20  9999-12-31
2  2016-08-20  2016-08-20  創(chuàng)建 2016-08-20  9999-12-31
3  2016-08-20  2016-08-20  創(chuàng)建 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  創(chuàng)建 2016-08-21

先放到增量表中，然后進行關(guān)聯(lián)到一張臨時表中，在插入到新表中，（同樣，可以采用其他語句進行實現(xiàn)）：

DROP TABLE IF EXISTS dw_orders_his_tmp;
-- 創(chuàng)建表
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;
 -- 將數(shù)據(jù)插入到拉鏈表中
INSERT overwrite TABLE dw_orders_his
SELECT * FROM dw_orders_his_tmp;

在根據(jù)上面步驟把2016-08-22號的數(shù)據(jù)更新進去，最后結(jié)果如下：

select * from dw_orders_his;
OK
1  2016-08-20  2016-08-20  創(chuàng)建 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  創(chuàng)建 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  創(chuàng)建 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  創(chuàng)建 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  創(chuàng)建 2016-08-22  9999-12-31

至此，得到了我們想要的數(shù)據(jù)。

拉鏈表延申補充

流水表與拉鏈表的區(qū)別

流水表存放的是一個用戶的變更記錄，比如在一張流水表中，一天的數(shù)據(jù)中，會存放一個用戶的每條修改記錄，但是在拉鏈表中只有一個記錄。
這是拉鏈表設(shè)計時需要注意的一個粒度問題。我們當(dāng)然可以設(shè)置的粒度更小一些，一般按天足夠了。

查詢性能

拉鏈表當(dāng)然會遇到查詢性能的問題。比如我們存放了5年的拉鏈數(shù)據(jù)，那么這張表勢必會比較大，當(dāng)查詢的時候性能就比較低了，個人認為有兩種解決思路：

• 1.在一些查詢引擎中，我們對start_date和end_date做索引，這樣能提升不少性能。這種方法其實在hive中行不通，因為hive相當(dāng)于沒有索引，不過在其他系統(tǒng)中可以考慮。
• 2.保留部分歷史數(shù)據(jù)，比如說我們一張表里面存放全量的拉鏈表數(shù)據(jù)，然后再對外暴露一張只提供近3個月數(shù)據(jù)的拉鏈表。

淘汰機制

關(guān)于淘汰機制，其實和性能也是有關(guān)系的，一方面是因為所有數(shù)據(jù)的積累會導(dǎo)致計算越來越慢，另一方面是業(yè)務(wù)側(cè)其實對歷史數(shù)據(jù)的需求也有一定的優(yōu)先級的。
因此再設(shè)計拉鏈表的時候可以制定一些數(shù)據(jù)的淘汰機制。淘汰的數(shù)據(jù)不一定要刪除，比如我們建立兩張拉鏈表，一張拉鏈表中只保存最新的十條數(shù)據(jù)，其他的數(shù)據(jù)會保存入一張歷史拉鏈表中。

其他心得

1.使用拉鏈表的時候可以不加t_end_date,即失效日期，但是加上之后，能優(yōu)化很多查詢。
2.可以加上當(dāng)前行狀態(tài)標(biāo)識，能快速定位到當(dāng)前狀態(tài)，
3.在拉鏈表的設(shè)計中可以加一些內(nèi)容，因為我們每天保存一個狀態(tài)，如果我們在這個狀態(tài)里面加一個字段，比如當(dāng)天修改次數(shù)，那么拉鏈表的作用會恒大。

參考博客：
http://www.itdecent.cn/p/799252156379 漫談數(shù)據(jù)倉庫之拉鏈表（原理、設(shè)計以及在Hive中的實現(xiàn)）推薦大家好好的讀一下木東居士的文章
http://www.cnblogs.com/wujin/p/6121754.html hive中拉鏈表
http://lxw1234.com/archives/2015/08/473.htm 數(shù)據(jù)倉庫中歷史拉鏈表的更新方法