拉鏈表
拉鏈表是針對數(shù)據(jù)倉庫設(shè)計中表存儲數(shù)據(jù)的方式而定義的购啄,顧名思義,所謂拉鏈肪获,就是記錄歷史寝凌。記錄一個事物從開始,一直到當(dāng)前狀態(tài)的所有變化的信息贪磺。
下面就是一張拉鏈表硫兰,存儲的是用戶的最基本信息以及每條記錄的生命周期。我們可以使用這張表拿到最新的當(dāng)天的最新數(shù)據(jù)以及之前的歷史數(shù)據(jù)寒锚。
注冊日期 | 用戶編號 | 手機(jī)號碼 | 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 表示該條記錄的生命周期結(jié)束時間;
- t_end_date = ‘9999-12-31’表示該條記錄目前處于有效狀態(tài)刹前;
- 如果查詢當(dāng)前所有有效的記錄泳赋,則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’喇喉,這條語句會查詢到以下記錄:
拉鏈表的使用場景
在數(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è)計萍歉?下面有幾種方案可選:
- 方案一:每天只留最新的一份侣颂,比如我們每天用datax抽取最新的一份全量數(shù)據(jù)到Hive中。
- 方案二:每天保留一份全量的切片數(shù)據(jù)枪孩。
- 方案三:使用拉鏈表憔晒。
為什么使用拉鏈表
方案一:每天只留最新的一份
這種方案就不用多說了,實現(xiàn)起來很簡單蔑舞,每天drop掉前一天的數(shù)據(jù)拒担,重新抽一份最新的。
優(yōu)點很明顯攻询,節(jié)省空間从撼,一些普通的使用也很方便,不用在選擇表的時候加一個時間分區(qū)什么的钧栖。
缺點同樣明顯低零,沒有歷史數(shù)據(jù),先翻翻舊賬只能通過其它方式拯杠,比如從流水表里面抽掏婶。
方案二:每天保留一份全量的切片數(shù)據(jù)
每天一份全量的切片是一種比較穩(wěn)妥的方案,而且歷史數(shù)據(jù)也在潭陪。
缺點就是存儲空間占用量太大太大了雄妥,如果對這邊表每天都保留一份全量,那么每次全量中會保存很多不變的信息依溯,對存儲是極大的浪費老厌。
當(dāng)然我們也可以做一些取舍,比如只保留近一個月的數(shù)據(jù)黎炉?但是梅桩,需求是無恥的,數(shù)據(jù)的生命周期不是我們能完全左右的拜隧。
方案三:拉鏈表
拉鏈表在使用上基本兼顧了我們的需求宿百。
首先它在空間上做了一個取舍,雖說不像方案一那樣占用量那么小洪添,但是它每日的增量可能只有方案二的千分之一甚至是萬分之一垦页。
其實它能滿足方案二所能滿足的需求,既能獲取最新的數(shù)據(jù)干奢,也能添加篩選條件也獲取歷史的數(shù)據(jù)痊焊。
所以我們還是很有必要來使用拉鏈表的。
拉鏈表的設(shè)計
在Mysql關(guān)系型數(shù)據(jù)庫里的user表中信息變化
在2017-01-01表中的數(shù)據(jù)是:
注冊日期 | 用戶編號 | 手機(jī)號碼 |
---|---|---|
2017-01-01 | 001 | 111111 |
2017-01-01 | 002 | 222222 |
2017-01-01 | 003 | 333333 |
2017-01-01 | 004 | 444444 |
在2017-01-02表中的數(shù)據(jù)是忿峻,用戶002和004資料進(jìn)行了修改薄啥,005是新增用戶:
注冊日期 | 用戶編號 | 手機(jī)號碼 | 備注 |
---|---|---|---|
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表中的數(shù)據(jù)是,用戶004和005資料進(jìn)行了修改逛尚,006是新增用戶:
注冊日期 | 用戶編號 | 手機(jī)號碼 | 備注 |
---|---|---|---|
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新增) |
如果在數(shù)據(jù)倉庫中設(shè)計成歷史拉鏈表保存該表垄惧,則會有下面這樣一張表,這是最新一天(即2017-01-03)的數(shù)據(jù):
注冊日期 | 用戶編號 | 手機(jī)號碼 | 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 表示該條記錄的生命周期結(jié)束時間到逊;
- t_end_date = ‘9999-12-31’表示該條記錄目前處于有效狀態(tài);
- 如果查詢當(dāng)前所有有效的記錄滤钱,則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’,這條語句會查詢到以下記錄:
拉鏈表的實現(xiàn)與更新
Hive中實現(xiàn)拉鏈表
- 我們需要一張ODS層的用戶全量表件缸。至少需要用它來初始化铜靶。
- 每日的用戶更新表。
而且我們要確定拉鏈表的時間粒度他炊,比如說拉鏈表每天只取一個狀態(tài)争剿,也就是說如果一天有3個狀態(tài)變更,我們只取最后一個狀態(tài)佑稠,這種天粒度的表其實已經(jīng)能解決大部分的問題了秒梅。
獲取每日的用戶增量
- 監(jiān)聽Mysql數(shù)據(jù)的變化,比如說用Canal舌胶,最后合并每日的變化捆蜀,獲取到最后的一個狀態(tài)。
- 假設(shè)我們每天都會獲得一份切片數(shù)據(jù)幔嫂,我們可以通過取兩天切片數(shù)據(jù)的不同來作為每日更新表辆它,這種情況下我們可以對所有的字段先進(jìn)行concat,再取md5履恩,這樣就ok了锰茉。
- 流水表,有每日的變更流水表
表結(jié)構(gòu)
ods層的user表
CREATE EXTERNAL TABLE ods.user (
user_num STRING COMMENT '用戶編號',
mobile STRING COMMENT '手機(jī)號碼',
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 '手機(jī)號碼',
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 '手機(jī)號碼',
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';
)
更新
假設(shè)已經(jīng)初始化了2017-01-01的日期切心,然后需要更新2017-01-02那一天的數(shù)據(jù)
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
[歷史拉鏈表的更新方法][1]
補充
拉鏈表和流水表
流水表存放的是一個用戶的變更記錄飒筑,比如在一張流水表中片吊,一天的數(shù)據(jù)中,會存放一個用戶的每條修改記錄协屡,但是在拉鏈表中只有一條記錄俏脊。
這是拉鏈表設(shè)計時需要注意的一個粒度問題。我們當(dāng)然也可以設(shè)置的粒度更小一些肤晓,一般按天就足夠爷贫。
查詢性能
鏈表當(dāng)然也會遇到查詢性能的問題,比如說我們存放了5年的拉鏈數(shù)據(jù)补憾,那么這張表勢必會比較大漫萄,當(dāng)查詢的時候性能就比較低了,個人認(rèn)為兩個思路來解決:
- 在一些查詢引擎中盈匾,我們對start_date和end_date做索引腾务,這樣能提高不少性能。
- 保留部分歷史數(shù)據(jù)威酒,比如說我們一張表里面存放全量的拉鏈表數(shù)據(jù)窑睁,然后再對外暴露一張只提供近3個月數(shù)據(jù)的拉鏈表。