資料倉儲之拉鍊表設計

落花桂發表於2021-08-02

目錄

一、拉鍊表的使用場景

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

  那麼對於這種表我該如何設計呢？下面有幾種方案可選：

  • 方案一：每天只留最新的一份，比如我們每天用Sqoop抽取最新的一份全量資料到Hive中。

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

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

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

  • 方案三：使用拉鍊表。

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

二、拉鍊表的設計和實現

1、資料需求

  我們先看一下在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	666666	（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	654321	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

2、拉鍊表設計說明

  • t_start_date表示該條記錄的生命週期開始時間，t_end_date表示該條記錄的生命週期結束時間。

  • t_end_date = '9999-12-31’表示該條記錄目前處於有效狀態。

  • 如果查詢當前所有有效的記錄，則

select * from user where t_end_date = ‘9999-12-31’
  • 如果查詢2017-01-02的歷史快照，判斷生效時間小於等於2017-01-02，失效時間大於等於2017-01-02，則
select * from user where t_start_date <= ‘2017-01-02’ and t_end_date >= ‘2017-01-02’

三、在Hive中實現拉鍊表

1、建立ods層和dw層表

  • ods層的ods.user表
    以日期作為分割槽欄位，壓縮格式為ORC
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層的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';
)
  • 在dw層建立拉鍊表：dws.user_his
    在這張表中沒有分割槽，但壓縮格式依舊是ORC
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';
)

2、增量的sql實現

  假設我們已經已經初始化了2017-01-01的日期，然後需要更新2017-01-02那一天的資料
  實現思路：（對需要修改的資料進行update） UNION ALL （新增的資料）

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 ALL
    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

3、查詢效能

  假如存放了5年的拉鍊資料，那麼這張表勢必會比較大，當查詢的時候效能就比較低了，個人認為兩個思路來解決：

  • 在一些查詢引擎中，我們對start_date和end_date做索引，這樣能提高不少效能。

  • 保留部分歷史資料，比如說我們一張表裡面存放全量的拉鍊表資料，然後再對外暴露一張只提供近3個月資料的拉鍊表。

四、拉鍊表的sparkSQL整合hive實現

  現在介紹的這種設計方案與上面的方案大同小異，主要區別在於資料的儲存格式，以及分割槽的設計，增量抽取資料時增加了臨時表作為儲存。

0、拉鍊表的資料效果圖


1、拉鍊表設計具體步驟

  • 1）第一次全量匯入
    • 所有的ODS層資料全量匯入到拉鍊歷史記錄表中
  • 2）增量匯入（某天，例如：2021-07-21）
    • 增量匯入某天的資料到ODS分割槽
    • 合併歷史資料（通過連線查詢的方式進行更新）

2、MySQL和ods層以及dw層資料說明

  • MySQL層資料和ods層資料保持一致

  • 在ods層使用dt作為日期，對每日增量資料進行分割槽儲存，即每天都會增加一個分割槽

  • ods層資料都是用parquet格式儲存，snappy壓縮格式，對於sparkSQL的支援是最好的

  • 在dw層使用拉鍊表技術，不分割槽

  • dw層每張表都有一張對應的臨時中間表

3、資料匯入

  • 使用sparkSQL進行資料操作全量匯入（資料日期是20190909號之前的）


  modifyTime，為資料的修改時間
  dw_start_date，為資料的生效時間，只要當modifyTime不為空，就等於modifyTime的時間，否則就等於createTime建立時間的值
  dw_end_date，為資料的失效時間，第一次全量匯入時全部都初始化為"9999-12-31"，寓意永久不失效

  • 增量匯入（將20190910號的資料匯入到拉鍊表中）

  1）使用kettle將20190910的資料抽取到ods

  2）編寫spark-sql更新歷史資料（從ods層的20190910號分割槽獲取資料）


  dw_start_date，為資料的生效時間，此時不會做改變
  dw_end_date，為資料的失效時間，如果dw_end_date依舊為"9999-12-31"，並且ods層的20190910號分割槽中goodsId（即兩張表中都用此商品Id時），把dw_end_date更新為昨天的日期"2019-09-09"，否則日期不變

  3）編寫spark-sql獲取當日資料


  dw_start_date，為資料的生效時間，只要當modifyTime不為空，就等於modifyTime的時間，否則就等於createTime建立時間的值
  dw_end_date，為資料的失效時間，此時不會做改變

  4）先把資料合併到臨時表中，第5步在把臨時表中的資料插入到dw層的表中



  5）將歷史資料和當日資料匯入到歷史拉鍊表中及查詢資料

相關文章