漫談數(shù)據(jù)倉庫之拉鏈表（原理、設(shè)計(jì)以及在Hive中的實(shí)現(xiàn)）
本文將會(huì)談一談在數(shù)據(jù)倉庫中拉鏈表相關(guān)的內(nèi)容，包括它的原理、設(shè)計(jì)、以及在我們大數(shù)據(jù)場(chǎng)景下的實(shí)現(xiàn)方式。
全文由下面幾個(gè)部分組成：
- 先分享一下拉鏈表的用途、什么是拉鏈表。
- 通過一些小的使用場(chǎng)景來對(duì)拉鏈表做近一步的闡釋，以及拉鏈表和常用的切片表的區(qū)別。
- 舉一個(gè)具體的應(yīng)用場(chǎng)景，來設(shè)計(jì)并實(shí)現(xiàn)一份拉鏈表，最后并通過一些例子說明如何使用我們?cè)O(shè)計(jì)的這張表(因?yàn)楝F(xiàn)在Hive的大規(guī)模使用，我們會(huì)以Hive場(chǎng)景下的設(shè)計(jì)為例)。
- 分析一下拉鏈表的優(yōu)缺點(diǎn)，并對(duì)前面的提到的一些內(nèi)容進(jìn)行補(bǔ)充說明，比如說拉鏈表和流水表的區(qū)別。
0x01 什么是拉鏈表
拉鏈表是針對(duì)數(shù)據(jù)倉庫設(shè)計(jì)中表存儲(chǔ)數(shù)據(jù)的方式而定義的，顧名思義，所謂拉鏈，就是記錄歷史。記錄一個(gè)事物從開始，一直到當(dāng)前狀態(tài)的所有變化的信息。
我們先看一個(gè)示例，這就是一張拉鏈表，存儲(chǔ)的是用戶的最基本信息以及每條記錄的生命周期。我們可以使用這張表拿到最新的當(dāng)天的最新數(shù)據(jù)以及之前的歷史數(shù)據(jù)。
我們暫且不對(duì)這張表做細(xì)致的講解，后文會(huì)專門來闡述怎么來設(shè)計(jì)、實(shí)現(xiàn)和使用它。
拉鏈表的使用場(chǎng)景
在數(shù)據(jù)倉庫的數(shù)據(jù)模型設(shè)計(jì)過程中，經(jīng)常會(huì)遇到下面這種表的設(shè)計(jì)：
- 有一些表的數(shù)據(jù)量很大，比如一張用戶表，大約10億條記錄，50個(gè)字段，這種表，即使使用ORC壓縮，單張表的存儲(chǔ)也會(huì)超過100G，在HDFS使用雙備份或者三備份的話就更大一些。
- 表中的部分字段會(huì)被update更新操作，如用戶聯(lián)系方式，產(chǎn)品的描述信息，訂單的狀態(tài)等等。
- 需要查看某一個(gè)時(shí)間點(diǎn)或者時(shí)間段的歷史快照信息，比如，查看某一個(gè)訂單在歷史某一個(gè)時(shí)間點(diǎn)的狀態(tài)。
- 表中的記錄變化的比例和頻率不是很大，比如，總共有10億的用戶，每天新增和發(fā)生變化的有200萬左右，變化的比例占的很小。
那么對(duì)于這種表我該如何設(shè)計(jì)呢?下面有幾種方案可選：
- 方案一：每天只留最新的一份，比如我們每天用Sqoop抽取最新的一份全量數(shù)據(jù)到Hive中。
- 方案二：每天保留一份全量的切片數(shù)據(jù)。
- 方案三：使用拉鏈表。
為什么使用拉鏈表
現(xiàn)在我們對(duì)前面提到的三種進(jìn)行逐個(gè)的分析。
方案一
這種方案就不用多說了，實(shí)現(xiàn)起來很簡(jiǎn)單，每天drop掉前一天的數(shù)據(jù)，重新抽一份最新的。
優(yōu)點(diǎn)很明顯，節(jié)省空間，一些普通的使用也很方便，不用在選擇表的時(shí)候加一個(gè)時(shí)間分區(qū)什么的。
缺點(diǎn)同樣明顯，沒有歷史數(shù)據(jù)，要想翻舊賬只能通過其它方式，比如從流水表里面抽。
方案二
每天一份全量的切片是一種比較穩(wěn)妥的方案，而且歷史數(shù)據(jù)也在。
缺點(diǎn)就是存儲(chǔ)空間占用量太大太大了，如果對(duì)這邊表每天都保留一份全量，那么每次全量中會(huì)保存很多不變的信息，對(duì)存儲(chǔ)是極大的浪費(fèi)，這點(diǎn)我感觸還是很深的……
當(dāng)然我們也可以做一些取舍，比如只保留近一個(gè)月的數(shù)據(jù)?但是，需求是無恥的，數(shù)據(jù)的生命周期不是我們能完全左右的。
拉鏈表
拉鏈表在使用上基本兼顧了我們的需求。
首先它在空間上做了一個(gè)取舍，雖說不像方案一那樣占用量那么小，但是它每日的增量可能只有方案二的千分之一甚至是萬分之一。
其實(shí)它能滿足方案二所能滿足的需求，既能獲取最新的數(shù)據(jù)，也能添加篩選條件也獲取歷史的數(shù)據(jù)。
所以我們還是很有必要來使用拉鏈表的。
0x02 拉鏈表的設(shè)計(jì)和實(shí)現(xiàn)
如何設(shè)計(jì)一張拉鏈表
下面我們來舉個(gè)栗子詳細(xì)看一下拉鏈表。
我們先看一下在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è)計(jì)成歷史拉鏈表保存該表，則會(huì)有下面這樣一張表，這是最新一天(即2017-01-03)的數(shù)據(jù)：
說明
- t_start_date表示該條記錄的生命周期開始時(shí)間，t_end_date表示該條記錄的生命周期結(jié)束時(shí)間。
- 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’。(*where條件篩選當(dāng)前有效數(shù)據(jù)，開始日期小于等于當(dāng)前日期并且結(jié)束日期大于等于當(dāng)前日期，則為有效*)
在Hive中實(shí)現(xiàn)拉鏈表
在現(xiàn)在的大數(shù)據(jù)場(chǎng)景下，大部分的公司都會(huì)選擇以Hdfs和Hive為主的數(shù)據(jù)倉庫架構(gòu)。目前的Hdfs版本來講，其文件系統(tǒng)中的文件是不能做改變的，也就是說Hive的表只能進(jìn)行刪除和添加操作，而不能進(jìn)行update。基于這個(gè)前提，我們來實(shí)現(xiàn)拉鏈表。
還是以上面的用戶表為例，我們要實(shí)現(xiàn)用戶的拉鏈表。在實(shí)現(xiàn)它之前，我們需要先確定一下我們有哪些數(shù)據(jù)源可以用。
- 我們需要一張ODS層的用戶全量表。至少需要用它來初始化。
- 每日的用戶更新表。
而且我們要確定拉鏈表的時(shí)間粒度，比如說拉鏈表每天只取一個(gè)狀態(tài)，也就是說如果一天有3個(gè)狀態(tài)變更，我們只取最后一個(gè)狀態(tài)，這種天粒度的表其實(shí)已經(jīng)能解決大部分的問題了。
另外，補(bǔ)充一下每日的用戶更新表該怎么獲取，據(jù)筆者的經(jīng)驗(yàn)，有以下方式拿到或者間接拿到每日的用戶增量，因?yàn)樗容^重要，所以詳細(xì)說明：
- 我們可以監(jiān)聽Mysql數(shù)據(jù)的變化，比如說用Canal，最后合并每日的變化，獲取到最后的一個(gè)狀態(tài)。
- 假設(shè)我們每天都會(huì)獲得一份切片數(shù)據(jù)，我們可以通過取兩天切片數(shù)據(jù)的不同來作為每日更新表，這種情況下我們可以對(duì)所有的字段先進(jìn)行concat，再取md5，這樣就ok了。
- 流水表!有每日的變更流水表。
- 通過etl工具對(duì)操作型數(shù)據(jù)庫按照時(shí)間字段增量抽取到ods或者數(shù)據(jù)倉庫(每天抽取前一天的數(shù)據(jù))，形成每天的增量數(shù)據(jù)(實(shí)際中使用最多的情形)。
拉鏈表實(shí)現(xiàn)方式一：
ods層的user表
現(xiàn)在我們來看一下我們ods層的用戶資料切片表的結(jié)構(gòu)：
- CREATE EXTERNAL TABLE ods.user (
- user_num STRING COMMENT '用戶編號(hào)',
- mobile STRING COMMENT '手機(jī)號(hào)碼',
- reg_date STRING COMMENT '注冊(cè)日期'
- 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 '用戶編號(hào)',
- mobile STRING COMMENT '手機(jī)號(hào)碼',
- reg_date STRING COMMENT '注冊(cè)日期'
- 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 '用戶編號(hào)',
- mobile STRING COMMENT '手機(jī)號(hào)碼',
- reg_date STRING COMMENT '用戶編號(hào)',
- 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';
- )
實(shí)現(xiàn)sql語句
然后初始化的sql就不寫了，其實(shí)就相當(dāng)于是拿一天的ods層用戶表過來就行，我們寫一下每日的更新語句。
現(xiàn)在我們假設(shè)我們已經(jīng)已經(jīng)初始化了2017-01-01的日期，然后需要更新2017-01-02那一天的數(shù)據(jù)，我們有了下面的Sql。
然后把兩個(gè)日期設(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
拉鏈表實(shí)現(xiàn)方式二：
操作型數(shù)據(jù)庫的用戶表結(jié)構(gòu)：
- CREATE EXTERNAL TABLE ods.user (
- user_num STRING COMMENT '用戶編號(hào)',
- mobile STRING COMMENT '手機(jī)號(hào)碼',
- reg_date STRING COMMENT '注冊(cè)日期' ,
- last_modify_date STRING COMMENT '‘最后修改時(shí)間’
- COMMENT '用戶資料表'
- PARTITIONED BY (dt string)
- ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
- STORED AS ORC
- LOCATION '/ods/user';
這里我們假設(shè)ods.user表的業(yè)務(wù)主鍵為user_num+mobile作為聯(lián)合主鍵。
每天增量抽取的用戶表結(jié)構(gòu)和抽取條件：
1）表結(jié)構(gòu)和上面的表結(jié)構(gòu)保持一致，我們?nèi)”砻麨閛ds.user_update
2）增量抽取條件：select * from ods.user where last_modify_date = '$date'
拉鏈表
現(xiàn)在我們創(chuàng)建一張拉鏈表：
- CREATE EXTERNAL TABLE dws.user_his (
- user_num STRING COMMENT '用戶編號(hào)',
- mobile STRING COMMENT '手機(jī)號(hào)碼',
- reg_date STRING COMMENT '用戶編號(hào)',
- last_modify_date STRING COMMENT '‘最后修改時(shí)間’
- 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';
- )
實(shí)現(xiàn)sql
1）
merge into dws.user_his tar
using
(
select user_num,mobile from ods.user_update
) sou on tar.user_num=sou.user_num and tar.mobile=sou.mobile and tar.t_start_date < '$date' and tar.t_end_date > '$date'
when matched then
update set tar.t_end_date='9999-12-31'
按照主鍵篩選，在dws.user_his表中出現(xiàn)過的并且現(xiàn)在為有效數(shù)據(jù)的，全部更新為閉鏈數(shù)據(jù)。
2)
- INSERT TABLE dws.user_his
- SELECT
- C.user_num,
- C.mobile,
- C.reg_date,
- c.last_modify_date
- '2017-01-02' AS t_start_time,
- '9999-12-31' AS t_end_time
- FROM ods.user_update AS C
比如我們要1月2號(hào)的數(shù)據(jù)，取出來的數(shù)據(jù)為
select from user where t_start_date <= ‘2017-01-02’ and t_end_date >= ‘2017-01-02’
與1月2號(hào)數(shù)據(jù)完全一致。
0x03 補(bǔ)充
好了，我們分析了拉鏈表的原理、設(shè)計(jì)思路、并且在Hive環(huán)境下實(shí)現(xiàn)了一份拉鏈表，下面對(duì)拉鏈表做一些小的補(bǔ)充。
拉鏈表和流水表
流水表存放的是一個(gè)用戶的變更記錄，比如在一張流水表中，一天的數(shù)據(jù)中，會(huì)存放一個(gè)用戶的每條修改記錄，但是在拉鏈表中只有一條記錄。
這是拉鏈表設(shè)計(jì)時(shí)需要注意的一個(gè)粒度問題。我們當(dāng)然也可以設(shè)置的粒度更小一些，一般按天就足夠。
查詢性能
拉鏈表當(dāng)然也會(huì)遇到查詢性能的問題，比如說我們存放了5年的拉鏈數(shù)據(jù)，那么這張表勢(shì)必會(huì)比較大，當(dāng)查詢的時(shí)候性能就比較低了，個(gè)人認(rèn)為兩個(gè)思路來解決：
- 在一些查詢引擎中，我們對(duì)start_date和end_date做索引，這樣能提高不少性能。
- 保留部分歷史數(shù)據(jù)，比如說我們一張表里面存放全量的拉鏈表數(shù)據(jù)，然后再對(duì)外暴露一張只提供近3個(gè)月數(shù)據(jù)的拉鏈表。
4 拉鏈表回滾
4.1 具體操作方案
假設(shè)恢復(fù)到t天之前的數(shù)據(jù)，即未融合t天數(shù)據(jù)之前的拉鏈表，假設(shè)標(biāo)記的開始日期和結(jié)束日期分別為s、t，具體分析如下：
1 當(dāng)t-1>e時(shí)，s數(shù)據(jù)、e數(shù)據(jù)在t天之前產(chǎn)生，保留即可 2 當(dāng)t-1=e時(shí)，e數(shù)據(jù)在t天產(chǎn)生，需修改 3 當(dāng)s<t<=e時(shí)，e數(shù)據(jù)在t+n天產(chǎn)生，需修改 4 當(dāng)s>=t時(shí)，s數(shù)據(jù)、e數(shù)據(jù)在t+n天產(chǎn)生，刪除即可
具體例子：
spark-sql> select * from t_dw_orders_his order by orderid,dw_start_date; 1 2015-08-18 2015-08-18 創(chuàng)建 2015-08-18 2015-08-21 1 2015-08-18 2015-08-22 支付 2015-08-22 2015-08-22 1 2015-08-18 2015-08-23 完成 2015-08-23 9999-12-31 2 2015-08-18 2015-08-18 創(chuàng)建 2015-08-18 2015-08-21 2 2015-08-18 2015-08-22 完成 2015-08-22 9999-12-31 3 2015-08-19 2015-08-21 支付 2015-08-19 2015-08-20 3 2015-08-19 2015-08-21 支付 2015-08-21 2015-08-22 3 2015-08-19 2015-08-23 完成 2015-08-23 9999-12-31 4 2015-08-19 2015-08-21 完成 2015-08-19 2015-08-20 4 2015-08-19 2015-08-21 完成 2015-08-21 9999-12-31 5 2015-08-19 2015-08-20 支付 2015-08-19 2015-08-22 5 2015-08-19 2015-08-23 完成 2015-08-23 9999-12-31 6 2015-08-20 2015-08-20 創(chuàng)建 2015-08-20 2015-08-21 6 2015-08-20 2015-08-22 支付 2015-08-22 9999-12-31 7 2015-08-20 2015-08-21 支付 2015-08-20 2015-08-20 7 2015-08-20 2015-08-21 支付 2015-08-21 9999-12-31 8 2015-08-21 2015-08-21 創(chuàng)建 2015-08-21 2015-08-21 8 2015-08-21 2015-08-22 支付 2015-08-22 2015-08-22 8 2015-08-21 2015-08-23 完成 2015-08-23 9999-12-31 9 2015-08-22 2015-08-22 創(chuàng)建 2015-08-22 9999-12-31 10 2015-08-22 2015-08-22 支付 2015-08-22 9999-12-31 11 2015-08-23 2015-08-23 創(chuàng)建 2015-08-23 9999-12-31 12 2015-08-23 2015-08-23 創(chuàng)建 2015-08-23 9999-12-31 13 2015-08-23 2015-08-23 支付 2015-08-23 9999-12-31
比如在插入2015-08-23的數(shù)據(jù)后，回滾2015-08-22的數(shù)據(jù)，使拉鏈表與2015-08-21的一致，具體操作過程如下
1 增加臨時(shí)表t_dw_orders_his_tmp1，用來記錄t-1>e的數(shù)據(jù) CREATE TABLE t_dw_orders_his_tmp1 AS SELECT orderid, createtime, modifiedtime, status, dw_start_date, dw_end_date FROM
t_dw_orders_his WHERE
dw_end_date < '2015-08-21'
3 2015-08-19 2015-08-21 支付 2015-08-19 2015-08-20 4 2015-08-19 2015-08-21 完成 2015-08-19 2015-08-20 7 2015-08-20 2015-08-21 支付 2015-08-20 2015-08-20
2 增加臨時(shí)表t_dw_orders_his_tmp2，用來記錄t-1=e的數(shù)據(jù)
CREATE TABLE t_dw_orders_his_tmp2
AS
SELECT
orderid,
createtime,
modifiedtime,
status,
dw_start_date,
'9999-12-31' AS dw_end_date
FROM
t_dw_orders_his WHERE
dw_end_date = '2015-08-21'
1 2015-08-18 2015-08-18 創(chuàng)建 2015-08-18 9999-12-31 2 2015-08-18 2015-08-18 創(chuàng)建 2015-08-18 9999-12-31 6 2015-08-20 2015-08-20 創(chuàng)建 2015-08-20 9999-12-31 8 2015-08-21 2015-08-21 創(chuàng)建 2015-08-21 9999-12-31
3 增加臨時(shí)表t_dw_orders_his_tmp3，用來記錄s<t<=e的數(shù)據(jù) CREATE TABLE t_dw_orders_his_tmp3 AS SELECT orderid, createtime, modifiedtime, status, dw_start_date, '9999-12-31' dw_end_date FROM
t_dw_orders_his WHERE
dw_start_date < '2015-08-22' AND dw_end_date >= '2015-08-22'
3 2015-08-19 2015-08-21 支付 2015-08-21 9999-12-31 4 2015-08-19 2015-08-21 完成 2015-08-21 9999-12-31 5 2015-08-19 2015-08-20 支付 2015-08-19 9999-12-31 7 2015-08-20 2015-08-21 支付 2015-08-21 9999-12-31
4 所有數(shù)據(jù)插入新表t_dw_orders_his_new CREATE TABLE t_dw_orders_his_new AS SELECT * FROM t_dw_orders_his_tmp1 UNION ALL SELECT * FROM t_dw_orders_his_tmp2 UNION ALL SELECT * FROM t_dw_orders_his_tmp3
1 2015-08-18 2015-08-18 創(chuàng)建 2015-08-18 9999-12-31 2 2015-08-18 2015-08-18 創(chuàng)建 2015-08-18 9999-12-31
3 2015-08-19 2015-08-21 支付 2015-08-19 2015-08-20
3 2015-08-19 2015-08-21 支付 2015-08-21 9999-12-31
4 2015-08-19 2015-08-21 完成 2015-08-19 2015-08-20
4 2015-08-19 2015-08-21 完成 2015-08-21 9999-12-31
5 2015-08-19 2015-08-20 支付 2015-08-19 9999-12-31
6 2015-08-20 2015-08-20 創(chuàng)建 2015-08-20 9999-12-31
7 2015-08-20 2015-08-21 支付 2015-08-20 2015-08-20
7 2015-08-20 2015-08-21 支付 2015-08-21 9999-12-31
8 2015-08-21 2015-08-21 創(chuàng)建 2015-08-21 9999-12-31
與原數(shù)據(jù)一致，驗(yàn)證無錯(cuò)
4.2 備用方案
可以采用備份的方案，保證無誤和可行。（保存增量數(shù)據(jù)，并對(duì)t_dw_orders_his表每個(gè)月備份一次全量數(shù)據(jù)。如需回滾，最多重跑30天數(shù)據(jù)即可）
0xFF 總結(jié)
我們?cè)谶@篇文章里面詳細(xì)地分享了一下和拉鏈表相關(guān)的知識(shí)點(diǎn)，但是仍然會(huì)有一會(huì)遺漏。歡迎交流。
在后面的使用中又有了一些心得，補(bǔ)充進(jìn)來：
- 使用拉鏈表的時(shí)候可以不加t_end_date，即失效日期，但是加上之后，能優(yōu)化很多查詢。
- 可以加上當(dāng)前行狀態(tài)標(biāo)識(shí)，能快速定位到當(dāng)前狀態(tài)。
- 在拉鏈表的設(shè)計(jì)中可以加一些內(nèi)容，因?yàn)槲覀兠刻毂４嬉粋€(gè)狀態(tài)，如果我們?cè)谶@個(gè)狀態(tài)里面加一個(gè)字段，比如如當(dāng)天修改次數(shù)，那么拉鏈表的作用就會(huì)更大。
作者： liangxb
出處：http://www.rzrgm.cn/lxbmaomao/
本文版權(quán)歸作者和博客園共有，歡迎轉(zhuǎn)載，但未經(jīng)作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接，否則保留追究法律責(zé)任的權(quán)利。
浙公網(wǎng)安備 33010602011771號(hào)