0x00 前言

本文將會談一談在數(shù)據(jù)倉庫中拉鏈表相關(guān)的內(nèi)容，包括它的原理采桃、設(shè)計土浸、以及在我們大數(shù)據(jù)場景下的實現(xiàn)方式。

全文由下面幾個部分組成：

1. 先分享一下拉鏈表的用途蕾殴、什么是拉鏈表笑撞。
2. 通過一些小的使用場景來對拉鏈表做近一步的闡釋，以及拉鏈表和常用的切片表的區(qū)別钓觉。
3. 舉一個具體的應(yīng)用場景茴肥，來設(shè)計并實現(xiàn)一份拉鏈表，最后并通過一些例子說明如何使用我們設(shè)計的這張表（因為現(xiàn)在Hive的大規(guī)模使用荡灾，我們會以Hive場景下的設(shè)計為例）瓤狐。
4. 分析一下拉鏈表的優(yōu)缺點，并對前面的提到的一些內(nèi)容進(jìn)行補充說明批幌，比如說拉鏈表和流水表的區(qū)別础锐。

0x01 什么是拉鏈表

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

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

我們暫且不對這張表做細(xì)致的講解，后文會專門來闡述怎么來設(shè)計左痢、實現(xiàn)和使用它靡羡。

拉鏈表的使用場景

在數(shù)據(jù)倉庫的數(shù)據(jù)模型設(shè)計過程中，經(jīng)常會遇到下面這種表的設(shè)計：

1. 有一些表的數(shù)據(jù)量很大俊性，比如一張用戶表略步，大約10億條記錄，50個字段定页，這種表趟薄，即使使用ORC壓縮，單張表的存儲也會超過100G典徊，在HDFS使用雙備份或者三備份的話就更大一些杭煎。
2. 表中的部分字段會被update更新操作，如用戶聯(lián)系方式卒落，產(chǎn)品的描述信息羡铲，訂單的狀態(tài)等等。
3. 需要查看某一個時間點或者時間段的歷史快照信息儡毕，比如也切，查看某一個訂單在歷史某一個時間點的狀態(tài)。
4. 表中的記錄變化的比例和頻率不是很大妥曲，比如贾费，總共有10億的用戶，每天新增和發(fā)生變化的有200萬左右檐盟，變化的比例占的很小。

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

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

為什么使用拉鏈表

現(xiàn)在我們對前面提到的三種進(jìn)行逐個的分析。

方案一

這種方案就不用多說了磕昼，實現(xiàn)起來很簡單卷雕，每天drop掉前一天的數(shù)據(jù)，重新抽一份最新的票从。

優(yōu)點很明顯漫雕，節(jié)省空間滨嘱，一些普通的使用也很方便，不用在選擇表的時候加一個時間分區(qū)什么的浸间。

缺點同樣明顯太雨，沒有歷史數(shù)據(jù)，先翻翻舊賬只能通過其它方式魁蒜，比如從流水表里面抽囊扳。

方案二

每天一份全量的切片是一種比較穩(wěn)妥的方案，而且歷史數(shù)據(jù)也在兜看。

缺點就是存儲空間占用量太大太大了锥咸，如果對這邊表每天都保留一份全量，那么每次全量中會保存很多不變的信息细移，對存儲是極大的浪費她君，這點我感觸還是很深的......

當(dāng)然我們也可以做一些取舍，比如只保留近一個月的數(shù)據(jù)葫哗？但是缔刹，需求是無恥的，數(shù)據(jù)的生命周期不是我們能完全左右的劣针。

拉鏈表

拉鏈表在使用上基本兼顧了我們的需求校镐。

首先它在空間上做了一個取舍，雖說不像方案一那樣占用量那么小捺典，但是它每日的增量可能只有方案二的千分之一甚至是萬分之一鸟廓。

其實它能滿足方案二所能滿足的需求，既能獲取最新的數(shù)據(jù)襟己，也能添加篩選條件也獲取歷史的數(shù)據(jù)引谜。

所以我們還是很有必要來使用拉鏈表的。

0x02 拉鏈表的設(shè)計和實現(xiàn)

如何設(shè)計一張拉鏈表

下面我們來舉個栗子詳細(xì)看一下拉鏈表擎浴。

我們接上在《漫談數(shù)據(jù)倉庫之維度建脑毖剩》中的電商網(wǎng)站的例子搓扯，現(xiàn)在以用戶的拉鏈表來說明蝶怔。

我們先看一下在Mysql關(guān)系型數(shù)據(jù)庫里的user表中信息變化冠跷。

在2017-01-01這一天表中的數(shù)據(jù)是：

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

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

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

說明

• 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)在的大數(shù)據(jù)場景下楼雹，大部分的公司都會選擇以Hdfs和Hive為主的數(shù)據(jù)倉庫架構(gòu)模孩。目前的Hdfs版本來講，其文件系統(tǒng)中的文件是不能做改變的贮缅，也就是說Hive的表智能進(jìn)行刪除和添加操作榨咐，而不能進(jìn)行update∏垂基于這個前提块茁，我們來實現(xiàn)拉鏈表。

還是以上面的用戶表為例桂肌，我們要實現(xiàn)用戶的拉鏈表数焊。在實現(xiàn)它之前，我們需要先確定一下我們有哪些數(shù)據(jù)源可以用崎场。

1. 我們需要一張ODS層的用戶全量表佩耳。至少需要用它來初始化。
2. 每日的用戶更新表谭跨。

而且我們要確定拉鏈表的時間粒度干厚，比如說拉鏈表每天只取一個狀態(tài)，也就是說如果一天有3個狀態(tài)變更螃宙，我們只取最后一個狀態(tài)蛮瞄，這種天粒度的表其實已經(jīng)能解決大部分的問題了。

另外谆扎，補充一下每日的用戶更新表該怎么獲取挂捅，據(jù)筆者的經(jīng)驗，有3種方式拿到或者間接拿到每日的用戶增量燕酷，因為它比較重要籍凝，所以詳細(xì)說明：

1. 我們可以監(jiān)聽Mysql數(shù)據(jù)的變化，比如說用Canal苗缩，最后合并每日的變化，獲取到最后的一個狀態(tài)声诸。
2. 假設(shè)我們每天都會獲得一份切片數(shù)據(jù)酱讶，我們可以通過取兩天切片數(shù)據(jù)的不同來作為每日更新表，這種情況下我們可以對所有的字段先進(jìn)行concat彼乌，再取md5泻肯，這樣就ok了渊迁。
3. 流水表！有每日的變更流水表灶挟。

ods層的user表

現(xiàn)在我們來看一下我們ods層的用戶資料切片表的結(jié)構(gòu)：

CREATE EXTERNAL TABLE ods.user (
  user_num STRING COMMENT '用戶編號',
  mobile STRING COMMENT '手機號碼',
  reg_date STRING COMMENT '注冊日期'
COMMENT '用戶資料表'
PARTITIONED BY (dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION '/ods/user';
)

ods層的user_update表

然后我們還需要一張用戶每日更新表琉朽，前面已經(jīng)分析過該如果得到這張表，現(xiàn)在我們假設(shè)它已經(jīng)存在稚铣。

CREATE EXTERNAL TABLE ods.user_update (
  user_num STRING COMMENT '用戶編號',
  mobile STRING COMMENT '手機號碼',
  reg_date STRING COMMENT '注冊日期'
COMMENT '每日用戶資料更新表'
PARTITIONED BY (dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION '/ods/user_update';
)

拉鏈表

現(xiàn)在我們創(chuàng)建一張拉鏈表：

CREATE EXTERNAL TABLE dws.user_his (
  user_num STRING COMMENT '用戶編號',
  mobile STRING COMMENT '手機號碼',
  reg_date STRING COMMENT '用戶編號',
  t_start_date ,
  t_end_date
COMMENT '用戶資料拉鏈表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION '/dws/user_his';
)

實現(xiàn)sql語句

然后初始化的sql就不寫了箱叁，其實就相當(dāng)于是拿一天的ods層用戶表過來就行，我們寫一下每日的更新語句惕医。

現(xiàn)在我們假設(shè)我們已經(jīng)已經(jīng)初始化了2017-01-01的日期耕漱，然后需要更新2017-01-02那一天的數(shù)據(jù)，我們有了下面的Sql抬伺。

然后把兩個日期設(shè)置為變量就可以了螟够。

INSERT OVERWRITE TABLE dws.user_his
SELECT * FROM
(
    SELECT A.user_num,
           A.mobile,
           A.reg_date,
           A.t_start_time,
           CASE
                WHEN A.t_end_time = '9999-12-31' AND B.user_num IS NOT NULL THEN '2017-01-01'
                ELSE A.t_end_time
           END AS t_end_time
    FROM dws.user_his AS A
    LEFT JOIN ods.user_update AS B
    ON A.user_num = B.user_num
UNION
    SELECT C.user_num,
           C.mobile,
           C.reg_date,
           '2017-01-02' AS t_start_time,
           '9999-12-31' AS t_end_time
    FROM ods.user_update AS C
) AS T

0x03 補充

好了，我們分析了拉鏈表的原理峡钓、設(shè)計思路妓笙、并且在Hive環(huán)境下實現(xiàn)了一份拉鏈表，下面對拉鏈表做一些小的補充能岩。

拉鏈表和流水表

流水表存放的是一個用戶的變更記錄寞宫，比如在一張流水表中，一天的數(shù)據(jù)中捧灰，會存放一個用戶的每條修改記錄淆九，但是在拉鏈表中只有一條記錄。

這是拉鏈表設(shè)計時需要注意的一個粒度問題毛俏。我們當(dāng)然也可以設(shè)置的粒度更小一些炭庙，一般按天就足夠。

查詢性能

拉鏈表當(dāng)然也會遇到查詢性能的問題煌寇，比如說我們存放了5年的拉鏈數(shù)據(jù)焕蹄，那么這張表勢必會比較大，當(dāng)查詢的時候性能就比較低了阀溶，個人認(rèn)為兩個思路來解決：

1. 在一些查詢引擎中腻脏，我們對start_date和end_date做索引，這樣能提高不少性能银锻。
2. 保留部分歷史數(shù)據(jù)永品，比如說我們一張表里面存放全量的拉鏈表數(shù)據(jù)，然后再對外暴露一張只提供近3個月數(shù)據(jù)的拉鏈表击纬。

0xFF 總結(jié)

我們在這篇文章里面詳細(xì)地分享了一下和拉鏈表相關(guān)的知識點鼎姐，但是仍然會有一會遺漏。歡迎交流。

在后面的使用中又有了一些心得炕桨，補充進(jìn)來：

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ù)睡汹，那么拉鏈表的作用就會更大。