資料倉儲之拉鍊表

柯廣發表於2020-12-16

拉鍊表是針對資料倉儲設計中表儲存資料的方式而定義的，顧名思義，所謂拉鍊，就是記錄歷史。記錄一個事物從開始，一直到當前狀態的所有變化的資訊。

下面就是一張拉鍊表，儲存的是使用者的最基本資訊以及每條記錄的生命週期。我們可以使用這張表拿到最新的當天的最新資料以及之前的歷史資料。

註冊日期 使用者編號 手機號碼 t_start_date t_end_date
2017-01-01 001 111111 2017-01-01 9999-12-31
2017-01-01 002 222222 2017-01-01 2017-01-01
2017-01-01 002 233333 2017-01-02 9999-12-31
2017-01-01 003 333333 2017-01-01 9999-12-31
2017-01-01 004 444444 2017-01-01 2017-01-01
2017-01-01 004 432432 2017-01-02 2017-01-02
2017-01-01 004 432432 2017-01-03 9999-12-31
2017-01-02 005 555555 2017-01-02 2017-01-02
2017-01-02 005 115115 2017-01-03 9999-12-31
2017-01-03 006 666666 2017-01-03 9999-12-31

說明：

  • t_start_date 表示該條記錄的生命週期開始時間，t_end_date 表示該條記錄的生命週期結束時間；
  • t_end_date = '9999-12-31' 表示該條記錄目前處於有效狀態；
  • 如果查詢當前所有有效的記錄，則select * from user where t_end_date = '9999-12-31'
  • 如果查詢2017-01-01的歷史快照，則select * from user where t_start_date <= '2017-01-01' and end_date >= '2017-01-01'，這條語句會查詢到以下記錄：

拉鍊表的使用場景

在資料倉儲的資料模型設計過程中，經常會遇到下面這種表的設計：

  1. 有一些表的資料量很大，比如一張使用者表，大約10億條記錄，50個欄位，這種表，即使使用ORC壓縮，單張表的儲存也會超過100G，在HDFS使用雙備份或者三備份的話就更大一些。
  2. 表中的部分欄位會被update更新操作，如使用者聯絡方式，產品的描述資訊，訂單的狀態等等。
  3. 需要檢視某一個時間點或者時間段的歷史快照資訊，比如，檢視某一個訂單在歷史某一個時間點的狀態。
  4. 表中的記錄變化的比例和頻率不是很大，比如，總共有10億的使用者，每天新增和發生變化的有200萬左右，變化的比例佔的很小。

對於這種表的設計？下面有幾種方案可選：

  • 方案一：每天只留最新的一份，比如我們每天用datax抽取最新的一份全量資料到Hive中。
  • 方案二：每天保留一份全量的切片資料。
  • 方案三：使用拉鍊表。

為什麼使用拉鍊表

方案一：每天只留最新的一份

這種方案就不用多說了，實現起來很簡單，每天drop掉前一天的資料，重新抽一份最新的。
優點很明顯，節省空間，一些普通的使用也很方便，不用在選擇表的時候加一個時間分割槽什麼的。
缺點同樣明顯，沒有歷史資料，先翻翻舊賬只能通過其它方式，比如從流水錶裡面抽。

方案二：每天保留一份全量的切片資料

每天一份全量的切片是一種比較穩妥的方案，而且歷史資料也在。
缺點就是儲存空間佔用量太大太大了，如果對這邊表每天都保留一份全量，那麼每次全量中會儲存很多不變的資訊，對儲存是極大的浪費。
當然我們也可以做一些取捨，比如只保留近一個月的資料？但是，需求是無恥的，資料的生命週期不是我們能完全左右的。

方案三：拉鍊表

拉鍊表在使用上基本兼顧了我們的需求。
首先它在空間上做了一個取捨，雖說不像方案一那樣佔用量那麼小，但是它每日的增量可能只有方案二的千分之一甚至是萬分之一。
其實它能滿足方案二所能滿足的需求，既能獲取最新的資料，也能新增篩選條件也獲取歷史的資料。
所以我們還是很有必要來使用拉鍊表的。

拉鍊表的設計

在Mysql關係型資料庫裡的user表中資訊變化

在2017-01-01表中的資料是：

註冊日期 使用者編號 手機號碼
2017-01-01 001 111111
2017-01-01 002 222222
2017-01-01 003 333333
2017-01-01 004 444444

在2017-01-02表中的資料是，使用者002和004資料進行了修改，005是新增使用者:

註冊日期 使用者編號 手機號碼 備註
2017-01-01 001 111111 無
2017-01-01 002 233333 （由222222變成233333）
2017-01-01 003 333333 無
2017-01-01 004 432432 （由444444變成432432）
2017-01-02 005 555555 （2017-01-02新增）

在2017-01-03表中的資料是，使用者004和005資料進行了修改，006是新增使用者:

註冊日期 使用者編號 手機號碼 備註
2017-01-01 001 111111
2017-01-01 002 233333
2017-01-01 003 333333
2017-01-01 004 654321 （由432432 變成 654321）
2017-01-02 005 115115 （由555555 變成 115115）
2017-01-03 006 115115 （2017-01-03 新增）

如果在資料倉儲中設計成歷史拉鍊表儲存該表，則會有下面這樣一張表，這是最新一天（即2017-01-03）的資料：

註冊日期 使用者編號 手機號碼 t_start_date t_end_date
2017-01-01 001 111111 2017-01-01 9999-12-31
2017-01-01 002 222222 2017-01-01 2017-01-01
2017-01-01 002 233333 2017-01-02 9999-12-31
2017-01-01 003 333333 2017-01-01 9999-12-31
2017-01-01 004 444444 2017-01-01 2017-01-01
2017-01-01 004 432432 2017-01-02 2017-01-02
2017-01-01 004 432432 2017-01-03 9999-12-31
2017-01-02 005 555555 2017-01-02 2017-01-02
2017-01-02 005 115115 2017-01-03 9999-12-31
2017-01-03 006 666666 2017-01-03 9999-12-31

說明：

  • t_start_date 表示該條記錄的生命週期開始時間，t_end_date 表示該條記錄的生命週期結束時間；
  • t_end_date = '9999-12-31'表示該條記錄目前處於有效狀態；
  • 如果查詢當前所有有效的記錄，則select * from user where t_end_date = '9999-12-31'
  • 如果查詢2017-01-01的歷史快照，則select * from user where t_start_date <= ‘2017-01-01′ and end_date >= '2017-01-01'。

拉鍊表的實現與更新

Hive中實現拉鍊表

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

而且我們要確定拉鍊表的時間粒度，比如說拉鍊表每天只取一個狀態，也就是說如果一天有3個狀態變更，我們只取最後一個狀態，這種天粒度的表其實已經能解決大部分的問題了。

獲取每日的使用者增量

監聽Mysql資料的變化，比如說用Canal，最後合併每日的變化，獲取到最後的一個狀態。
假設我們每天都會獲得一份切片資料，我們可以通過取兩天切片資料的不同來作為每日更新表，這種情況下我們可以對所有的欄位先進行concat，再取md5，這樣就ok了。
流水錶，有每日的變更流水錶

表結構

ods層的user表

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表

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

拉鍊表

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

更新

假設已經初始化了2017-01-01的日期，然後需要更新2017-01-02那一天的資料

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

補充

拉鍊表和流水錶

流水錶存放的是一個使用者的變更記錄，比如在一張流水錶中，一天的資料中，會存放一個使用者的每條修改記錄，但是在拉鍊表中只有一條記錄。
這是拉鍊表設計時需要注意的一個粒度問題。我們當然也可以設定的粒度更小一些，一般按天就足夠。

查詢效能

連結串列當然也會遇到查詢效能的問題，比如說我們存放了5年的拉鍊資料，那麼這張表勢必會比較大，當查詢的時候效能就比較低了，個人認為兩個思路來解決：

  1. 在一些查詢引擎中，我們對start_date和end_date做索引，這樣能提高不少效能。
  2. 保留部分歷史資料，比如說我們一張表裡面存放全量的拉鍊表資料，然後再對外暴露一張只提供近3個月資料的拉鍊表。

相關文章