數(shù)據(jù)倉(cāng)庫(kù)實(shí)踐-拉鏈表設(shè)計(jì)

1 寫(xiě)在開(kāi)頭的話(huà)

拉鏈表，學(xué)名叫緩慢變化維（Slowly Changing Dimensions），簡(jiǎn)稱(chēng)漸變維（SCD），俗稱(chēng)拉鏈表，是為了記錄關(guān)鍵字段的歷史變化而設(shè)計(jì)出來(lái)的一種數(shù)據(jù)存儲(chǔ)模型，常見(jiàn)于維度表設(shè)計(jì)，在數(shù)據(jù)倉(cāng)庫(kù)相關(guān)的面試中，也經(jīng)常有被問(wèn)到。但是在工程實(shí)踐中，拉鏈表真是太麻煩了，而且是在模型設(shè)計(jì)、初始化、ETL 開(kāi)發(fā)、運(yùn)維、日常取數(shù)等各個(gè)環(huán)節(jié)都很麻煩，而麻煩的設(shè)計(jì)通常都容易出錯(cuò)，或者對(duì)團(tuán)隊(duì)成員能力要求高些。

使用拉鏈表，需要考慮的問(wèn)題很多，我先簡(jiǎn)單列幾個(gè)，大家可以先思考下，真的必須用拉鏈表嗎？

1. 新建的拉鏈表，歷史數(shù)據(jù)要不要補(bǔ)充；

2. 新建的拉鏈表，主鍵怎么設(shè)置，需要引入代理鍵嗎；

3. 構(gòu)建好的拉鏈表，更新的時(shí)候只能逐天往后計(jì)算，中間有一天計(jì)算錯(cuò)誤，后續(xù)的都得重刷；

4. 運(yùn)維的時(shí)候，更新的時(shí)候如果部分?jǐn)?shù)據(jù) update 錯(cuò)誤，如何更正？

5. 關(guān)系型數(shù)據(jù)庫(kù)還好可以 update，那大數(shù)據(jù)環(huán)境下呢，如何處理增量數(shù)據(jù)？

6. 使用的時(shí)候，什么時(shí)候取最新快照，什么時(shí)候取歷史某一時(shí)刻的數(shù)據(jù)？

7. 使用的時(shí)候，事實(shí)表關(guān)聯(lián)拉鏈表，join 該怎么寫(xiě)，會(huì)不會(huì)寫(xiě)錯(cuò)？

2 先分享一篇類(lèi)似的文章

漫談數(shù)據(jù)倉(cāng)庫(kù)之拉鏈表（原理、設(shè)計(jì)以及在Hive中的實(shí)現(xiàn)）

https://blog.csdn.net/zhaodedong/article/details/54177686

上邊是木東居士在前些年分享在 CSDN 的一篇文章，目前已有 3.9 萬(wàn)瀏覽。寫(xiě)的非常棒，思路清晰、簡(jiǎn)單易懂，也是是網(wǎng)絡(luò)上流傳的常規(guī)拉鏈表設(shè)計(jì)思路。

3 對(duì)于變化數(shù)據(jù)的處理方案

我們常說(shuō)，數(shù)據(jù)模型設(shè)計(jì)一定要切合實(shí)際業(yè)務(wù)需求。對(duì)于變化數(shù)據(jù)的處理，常見(jiàn)需求有以下三種：

需求一：保護(hù)第一個(gè)值

在廣告投放的業(yè)務(wù)場(chǎng)景中，有個(gè)很重要的概念叫廣告歸因，這就是一個(gè)典型的必須保護(hù)第一個(gè)值的案例。就是說(shuō)一個(gè)安裝歸屬到渠道 1 后，就應(yīng)該永遠(yuǎn)綁定在該渠道上。

該需求實(shí)現(xiàn)最簡(jiǎn)單，只需要追加新數(shù)據(jù)就好了。

需求二：保留最新值

當(dāng)我們不需要記錄歷史變化的時(shí)候，就可以只保留最新值。比如用戶(hù)修改了出生日期，有可能之前給的是系統(tǒng)默認(rèn)值。

該需求處理會(huì)稍微復(fù)雜，需要 update 用戶(hù)維表，同時(shí)如果有對(duì)于用戶(hù)年齡相關(guān)的分析，還要重刷相關(guān)的事實(shí)表數(shù)據(jù)。

需求三：記錄歷史變化

我們需要回溯主體歷史某一時(shí)點(diǎn)的狀態(tài)的時(shí)候，就必須記錄歷史變化了。比如某一天，某業(yè)務(wù)員轉(zhuǎn)崗了，那么部門(mén)業(yè)績(jī)?cè)露葏R總的時(shí)候，就需要知道該業(yè)務(wù)員過(guò)去在哪些部門(mén)待過(guò)以及起始日期。

需求三處理起來(lái)比較麻煩，方案如下：

• 方案一：每天記錄一份快照，快照在木東居士文章里稱(chēng)為切片。

• 方案二：增加新的列，比如只需要存最近 3 次變化，那么我們新增三列就好了。

• 方案三：增加新的行，核心屬性變化一次，新增一條，同時(shí)新增 2 列（數(shù)據(jù)開(kāi)始日期、數(shù)據(jù)截止日期）。

方案一：

好處是寫(xiě)入和查詢(xún)特別方便。但如果數(shù)據(jù)量巨大，數(shù)倉(cāng)場(chǎng)景，您至少得存三年吧，由此帶來(lái)存儲(chǔ)、計(jì)算成本，都將是非常巨大的。

互聯(lián)網(wǎng)時(shí)代的快餐模式，大家都沒(méi)時(shí)間建模了，同時(shí)主流大數(shù)據(jù)數(shù)倉(cāng)組件基本不支持 Update ，或者目前的存儲(chǔ)還吃的消，又或者數(shù)據(jù)量沒(méi)那么大，因此該方案被采用的還是比多的。

方案二：? ? ?

對(duì)于某些特定的使用場(chǎng)景，該方案還是蠻香的。再次強(qiáng)調(diào)，數(shù)據(jù)開(kāi)發(fā)者一定要懂業(yè)務(wù)，許多技術(shù)上實(shí)現(xiàn)非常復(fù)雜的，換一種業(yè)務(wù)角度會(huì)簡(jiǎn)單太多了，

方案三：? ? ? ? ? ?

這是多數(shù)人都能想到的處理思路，即拉鏈表。適用場(chǎng)景必須是緩慢變化，例如一張表有 10 億數(shù)據(jù)，每天變化的只有幾萬(wàn)、幾十萬(wàn)才能稱(chēng)為緩慢變化，反之如果 10 億的表每天有 7 億都會(huì)發(fā)生變化，那這還適合用拉鏈表嗎？

拉鏈表的優(yōu)點(diǎn)是，相對(duì)于快照表可以極大的節(jié)省存儲(chǔ)空間，缺點(diǎn)也很明顯就是太麻煩了。

4 實(shí)現(xiàn)方法

大數(shù)據(jù)數(shù)倉(cāng)不支持 Update ，因此跟傳統(tǒng)數(shù)倉(cāng)實(shí)現(xiàn)還是有區(qū)別的。（當(dāng)然這是個(gè)偽命題，因?yàn)?ODPS 從 2021 年 3 月份已經(jīng)開(kāi)始支持 Update，雖然是試用階段但未來(lái)可期。）

另外，有些需求，純 SQL 實(shí)現(xiàn)確實(shí)很難。大家不要太迷戀 SQL，時(shí)代不同了，拉鏈表的計(jì)算，有時(shí)候?qū)?MR 反而更容易理解。有時(shí)候多寫(xiě)幾個(gè) UDF、UDAF、UDTF，SQL 寫(xiě)起來(lái)反而更方便、執(zhí)行效率反而會(huì)更好。

4.1 數(shù)據(jù)模型設(shè)計(jì)-傳統(tǒng)數(shù)倉(cāng)設(shè)計(jì)方案

因?yàn)閿?shù)量不大，通常也就幾萬(wàn)幾十萬(wàn)的數(shù)據(jù)量，業(yè)務(wù)系統(tǒng)和數(shù)倉(cāng) ODS 層也不太需要啟用數(shù)據(jù)刪除策略。因此不用考慮分區(qū)設(shè)計(jì)。

4.2 數(shù)據(jù)模型設(shè)計(jì)-大數(shù)據(jù)數(shù)倉(cāng)設(shè)計(jì)方案

網(wǎng)絡(luò)上分享出來(lái)的文章，還是沿用關(guān)系型數(shù)據(jù)的模型設(shè)計(jì)思路。所有數(shù)據(jù)都放一個(gè)分區(qū)或者干脆不建分區(qū)，往往會(huì)帶來(lái)一系列問(wèn)題。比如：

1. 隨著存儲(chǔ)時(shí)間的拉長(zhǎng)，這張表勢(shì)必會(huì)越來(lái)越大，查詢(xún)效率會(huì)越來(lái)越底，然而大部分查詢(xún)場(chǎng)景只需要查詢(xún)快照或者最近一段時(shí)間的歷史變化。

2. 如果某次更新，由于誤操作造成拉鏈表數(shù)據(jù)錯(cuò)誤，已經(jīng)存放五年歷史變化的拉鏈表該怎么恢復(fù)？存儲(chǔ)備份肯定是不可能的，如果我們每次都將全量數(shù)據(jù)寫(xiě)入新的分區(qū)，至少得存近三天的全量拉鏈表數(shù)據(jù)吧？這又會(huì)帶來(lái)存儲(chǔ)空間的消耗。

例如，

有這么一個(gè)場(chǎng)景，需要存儲(chǔ) SDK 上報(bào)的手機(jī)硬件信息，主鍵是設(shè)備 ID，關(guān)鍵的設(shè)備屬性大概 30 個(gè)，設(shè)備數(shù)量 40 億，在只存儲(chǔ)一份快照的情況下，需占用 400 G 存儲(chǔ)空間，一開(kāi)始用的是快照表方式，考慮存儲(chǔ)開(kāi)銷(xiāo)我們只存最近 7 天快照，帶來(lái)的問(wèn)題是設(shè)備歷史變化的 imei 、mac、os、品牌、機(jī)型等重要屬性都會(huì)丟失。所以，最好的方案應(yīng)該是使用拉鏈表。由于數(shù)據(jù)已經(jīng)累積了三四年，使用拉鏈表數(shù)據(jù)的話(huà)，數(shù)據(jù)條數(shù)會(huì)從 40 億膨脹到 60 億，需占 600 G 存儲(chǔ)空間。

==========設(shè)計(jì)思路、更新辦法=======================

分區(qū)列：

1. day comment '生成日期。如果 is_latest_row=0，則 day=t_end_date。如果 is_latest_row=1，則day='99991231'。如果day=t_start_date，則說(shuō)明該用戶(hù)是今日新增的。'

2. is_latest_row comment '是否最新一條數(shù)據(jù)。1是0否。如果標(biāo)記為 0 說(shuō)明該條數(shù)據(jù)不會(huì)再被更新'

20170101 這一天的數(shù)據(jù)

相比于前一天，用戶(hù)2、3沒(méi)變化，用戶(hù)4更新了手機(jī)號(hào)。

用戶(hù)2、用戶(hù)3沒(méi)變化，直接從前一天的分區(qū)里移過(guò)來(lái)放到當(dāng)天的 is_latest_row='1' 分區(qū)下。

用戶(hù)4 修改了手機(jī)號(hào)碼，更新庫(kù)里已有的那條數(shù)據(jù) t_end_date='20170101'，然后放入當(dāng)天的 is_latest_row='0' 分區(qū)下，說(shuō)明該條數(shù)據(jù)因?yàn)槭П粴w檔了。新增的那條用戶(hù)4 數(shù)據(jù) t_start_date='20170101' ,t_end_date='99991231',放入當(dāng)天的 is_latest_row='1' 分區(qū)下。

20170102 這一天生成的數(shù)據(jù)

用戶(hù)5是新增的，該條數(shù)據(jù)的失效日期是永久，所以 is_latest_row = '1'。? ? ?相比于前一天，新增了用戶(hù)5，同時(shí)更新了用戶(hù)2的手機(jī)號(hào)碼，用戶(hù)3、4無(wú)變化。

用戶(hù)3、4沒(méi)變化，直接從前一天的分區(qū)里移過(guò)來(lái)放到當(dāng)天的 is_latest_row='1' 分區(qū)下。

用戶(hù)2 修改了手機(jī)號(hào)碼，更新庫(kù)里已有的那條數(shù)據(jù) t_end_date='20170102'，然后放入當(dāng)天的 is_latest_row='0' 分區(qū)下，說(shuō)明該條數(shù)據(jù)因?yàn)槭П粴w檔了。新增的那條用戶(hù)2 數(shù)據(jù) t_start_date='20170102' ,t_end_date='99991231',放入當(dāng)天的 is_latest_row='1' 分區(qū)下。

==========使用方法=========

假如數(shù)據(jù)已經(jīng)更新到了 20170102 這一天。

1. is_latest_row = '0' 的分區(qū)絕對(duì)不允許刪除，保證歷史變化都能記錄下來(lái)。

2. is_latest_row = '1' 的分區(qū)只保留最近 7 天或最近 3 天的數(shù)據(jù)，節(jié)省存儲(chǔ)空間的同時(shí)，就是某一天更新錯(cuò)誤也能很快的修正數(shù)據(jù)。

3. 可以查最新快照：

select * from dim_user_history where day='20170102' and is_latest_row='1' ?;

4. 可以查歷史任意一天[20161002]的快照：

select t.*

from

(

select t.*

,row_number() over (partition by user_id order by t_end_date) rn

from dim_user_history

where (day>='20161002' and is_latest_row='0') ?or (day='20170102' and is_latest_row='1')

) t

where t.rn=1

;

5. 可以查指定時(shí)間范圍內(nèi)的[20161002-20161101]的所有狀態(tài)：

select t.*

from dim_user_history

where (day<'20161101' and is_latest_row='0' ?and ?t_start_date>'20161002') ?

or (day='20170102' and is_latest_row='1' and t_start_date>'20161002')

;

拉鏈表雖然能解決很多問(wèn)題，但是，只要一個(gè)日期卡錯(cuò)，就會(huì)出問(wèn)題。使用起來(lái)真的太太太難了。。。。

4.3 歷史數(shù)據(jù)初始化

上邊，我們了解到，拉鏈表的使用有多麻煩。這一節(jié)我們接著討論下寫(xiě)入。

如果我們構(gòu)建拉鏈表的時(shí)候，歷史數(shù)據(jù)已經(jīng)沉淀一段時(shí)間了，那么大概率我們是需要全量加工處理，并一次性寫(xiě)入進(jìn)來(lái)的。當(dāng)然，我們可以從第一天開(kāi)始、一天一天的往后計(jì)算。

但是，總覺(jué)得吧，這不是我們技術(shù)該干的事兒，因?yàn)檫@也太 lower 了吧。一天一天算，那得等多久啊，技術(shù)不能提高效率，要技術(shù)干嘛？

這個(gè)時(shí)候 SQL Boy 該上場(chǎng)了。有啥事情是一條 SQL 搞不定的呢？如果有，那就兩條吧。哈哈哈。。。

接下來(lái)先說(shuō)一下思路吧：

增量更新相對(duì)簡(jiǎn)單些，我們直接拿上一次統(tǒng)計(jì)周期的全量快照，關(guān)聯(lián)本次統(tǒng)計(jì)周期的變化量即可。

歷史數(shù)據(jù)初始化，由于存在某一個(gè)業(yè)務(wù)主鍵對(duì)應(yīng)的屬性可能會(huì)變化多次的情況，處理起來(lái)就會(huì)復(fù)雜很多：

1. 相鄰兩個(gè)統(tǒng)計(jì)周期的數(shù)據(jù)如果沒(méi)有變化，需要去重。

2. 剩下的數(shù)據(jù)，需要按時(shí)間正序排列，第一條的數(shù)據(jù)止期=第二條的數(shù)據(jù)起期、第二條的數(shù)據(jù)止期=第三條的數(shù)據(jù)起期，以此類(lèi)推。

3. 而 SQL 對(duì)于行間數(shù)據(jù)的處理常常無(wú)能為力，那我們能否把行間數(shù)據(jù)計(jì)算轉(zhuǎn)化成行內(nèi)數(shù)據(jù)計(jì)算呢？

? ? ?

結(jié)合以上分析，實(shí)現(xiàn)步驟如下（以統(tǒng)計(jì)周期為天來(lái)舉例）：

1. 原始數(shù)據(jù)表。

2. 按更新時(shí)間，每天只保留最后一條數(shù)據(jù)，數(shù)據(jù)起期為當(dāng)天，止期為無(wú)限大。

create table dws.user_his_mid_01 as

select user_id,user_name,update_day b_date,'99990101' e_date

,row_number() over (partition by user_id order by update_day ) rn

from

(

select update_day,user_id,user_name

,row_number() over (partition by update_day,user_id order by update_time desc ) rn

from ods.user

) t

where rn=1

；

前兩條數(shù)據(jù)會(huì)只留下一條

3. 修正數(shù)據(jù)起止期。

create table dws.user_his_mid_02 as

select t1.user_id,t1.user_name

,t1.b_date

,nvl(t2.b_date,t1.e_date) e_date

from dws.user_his_mid_01 t1

left join dws.user_his_mid t2 on t1.user_id=t2.user_id and t1.rn=t2.rn-1

;

4. 相鄰兩條數(shù)據(jù)，屬性無(wú)變化的去重。

上表數(shù)據(jù)，會(huì)合并為三條。

好吧。歷史數(shù)據(jù)初始化，當(dāng)時(shí)是有寫(xiě)過(guò) SQL 的，好多年過(guò)去實(shí)在想不起來(lái)，當(dāng)時(shí)的 SQL 也找不到了。

本想重現(xiàn)當(dāng)時(shí)的 SQL，不過(guò)寫(xiě)到第三條實(shí)在寫(xiě)不動(dòng)了，因?yàn)樘y了。

換做現(xiàn)在的我，其實(shí)更愿意寫(xiě) MR 或者 UDAF 去實(shí)現(xiàn)這一業(yè)務(wù)邏輯的。思路特簡(jiǎn)單，就是將相同業(yè)務(wù)主鍵的數(shù)據(jù)放到一個(gè) Reduce 里，按 update_time 排序后，循環(huán)遍歷，返回結(jié)果。

4.4 增量更新

木東居士這條 SQL 寫(xiě)的非常簡(jiǎn)介、實(shí)用，借過(guò)來(lái)給大家看看。

ods.user_update 表應(yīng)該存的是前一天的變化量（新增 + Update）。

這是關(guān)系型數(shù)據(jù)庫(kù)的寫(xiě)法，具體到大數(shù)據(jù)場(chǎng)景，大家還得參照上文，加上分區(qū)列，直接 overwrite 總感覺(jué)心里不踏實(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

；

下邊是我之前寫(xiě)的，每月計(jì)算 IP 地址經(jīng)緯度歷史變化的拉鏈表。

牽涉到部分計(jì)算邏輯，會(huì)稍微有點(diǎn)復(fù)雜，大家看核心代碼段即可。

第一條 SQL 是，這個(gè)月的變化量，關(guān)聯(lián)上個(gè)月的全量快照，更新這個(gè)月變化量的起止日期，暫時(shí)放到這個(gè)月的全量快照分區(qū)里（類(lèi)似上邊 SQL 的 ods.user_update ?作用）。

第二條 SQL 是，上個(gè)月的全量快照，關(guān)聯(lián)這個(gè)月的變化量，得到這個(gè)月的全量快照+這個(gè)月失效的數(shù)據(jù)（數(shù)據(jù)止期='$1'）。

奧，看了好久，下邊 SQL 的數(shù)據(jù)止期有問(wèn)題。因?yàn)楫?dāng)時(shí)的需求跟拉鏈表的不太一樣。數(shù)據(jù)止期用的不是一個(gè)無(wú)限大的日期，而是（數(shù)據(jù)止期='$1'） 。意味著，如果某ip只在其中一個(gè)月份出現(xiàn)過(guò)，那么起止日期都是一樣的，如果連續(xù)出現(xiàn)過(guò)2個(gè)月，數(shù)據(jù)起期是第一月，數(shù)據(jù)止期是第二月。

insert OVERWRITE table bds_ip_info partition(month='$1',is_latest_row='1')
?select a.ip,
? ? ? ? if(size(split(lgt_list,';'))=1,split(lgt_list,';')[0],if(size(split(lgt_list,';'))=2,(split(lgt_list,';')[0]+split(lgt_list,';')[1])/2,b.lgt_center)) lgt_center,
? ? ? ? if(size(split(ltt_list,';'))=1,split(ltt_list,';')[0],if(size(split(ltt_list,';'))=2,(split(ltt_list,';')[0]+split(ltt_list,';')[1])/2,b.ltt_center)) ltt_center,
? ? ? ? if(size(split(lgt_list,';'))=1,0,if(size(split(lgt_list,';'))=2,lipb_GetDistance(concat(split(lgt_list,';')[0],',',split(ltt_list,';')[0]),concat(split(lgt_list,';')[1],',',split(ltt_list,';')[1]))/2,b.radius)) radius,
? ? ? ? a.b_month,
? ? ? ? a.e_month,
? ? ? ? size(split(a.geo_list,',')) geo_num,
? ? ? ? a.geo_list,
? ? ? ? month_from_list
?from
?(
? ?select t1.ip
? ?,if(t2.ip is null,substring(t1.month,1,6),t2.b_month) b_month
? ?,substring(t1.month,1,6) e_month
? ?,if(t2.ip is null,t1.month,concat(t1.month,';',t2.month_from_list)) month_from_list
? ?,if(t2.ip is null,GetGeoList(time_list,ltt_list,lgt_list)
? ? ? ? ? ,GetGeoLatest(GetGeoList(time_list,ltt_list,lgt_list),t2.geo_list,'500')) geo_list
? ?,split(SplitGeoList(if(t2.ip is null,GetGeoList(time_list,ltt_list,lgt_list)
? ? ? ? ? ,GetGeoLatest(GetGeoList(time_list,ltt_list,lgt_list),t2.geo_list,'500'))),',')[0] ltt_list
? ?,split(SplitGeoList(if(t2.ip is null,GetGeoList(time_list,ltt_list,lgt_list)
? ? ? ? ? ,GetGeoLatest(GetGeoList(time_list,ltt_list,lgt_list),t2.geo_list,'500'))),',')[1] lgt_list
? ?from ods_ip_info_m t1
? ? ?left join
? ? ?(
? ? ? ?select *
? ? ? ?from bds_ip_info t2
? ? ? ?where month=to_char(dateadd(dateadd(dateadd(to_date('$1','yyyymmdd'),1,'dd'),-1,'mm'),-1,'dd'),'yyyymmdd')
? ? ? ?and is_latest_row='1'
? ? ?) t2
? ? ? ?on t1.ip=t2.ip
? ? ?and abs(t1.radius-t2.radius)<=200
? ? ?and lipb_GetDistance(concat(t1.lgt_center,',',t1.ltt_center),concat(t2.lgt_center,',',t2.ltt_center))<=400
? ?where t1.month='$1'
?) a lateral view MapMedianRadius(ltt_list,lgt_list,';') b as ltt_center,lgt_center,radius
;
insert overwrite ?table bds_ip_info partition(month,is_latest_row)
?select a.ip,
? ? ? ? a.lgt_center,
? ? ? ? a.ltt_center,
? ? ? ? a.radius,
? ? ? ? a.b_month,
? ? ? ? a.e_month,
? ? ? ? a.geo_num,
? ? ? ? a.geo_list,
? ? ? ? a.month_from_list,
? ? ? ? '$1' month,
? ? ? ? if(b.ip is null,'1',if(a.e_month<>b.e_month,'0','1')) is_latest_row
?from
?(
? ?select * from bds_ip_info t1
? ?where month=to_char(dateadd(dateadd(dateadd(to_date('$1','yyyymmdd'),1,'dd'),-1,'mm'),-1,'dd'),'yyyymmdd')
? ?and is_latest_row='1'
?) a
? ?left join
? ?(
? ? ?select t1.ip,e_month from bds_ip_info t1 where t1.month='$1' and is_latest_row='1'
? ?) b on a.ip=b.ip
union all
select * from bds_ip_info t1 where t1.month='$1' and is_latest_row='1'
;

5 典型案例

拉鏈表概念來(lái)源于數(shù)倉(cāng)，數(shù)倉(cāng)的面試也經(jīng)常會(huì)被問(wèn)到。拉鏈表也切實(shí)解決了數(shù)倉(cāng)四大特性之一的反應(yīng)歷史變化這一訴求。

但是，拉鏈表在數(shù)倉(cāng)之外是否還有用武之地呢？事實(shí)上，數(shù)倉(cāng)體系內(nèi)的各種方法論、規(guī)范、核心技術(shù)等，在整個(gè)數(shù)據(jù)開(kāi)發(fā)流程內(nèi)始終有著巨大的指導(dǎo)借鑒意義。

數(shù)倉(cāng)人不應(yīng)局限于數(shù)倉(cāng)，可以跳出數(shù)倉(cāng)來(lái)看問(wèn)題。我是數(shù)倉(cāng)人，但我一定要建數(shù)倉(cāng)嗎？我們更應(yīng)該思考的是如何讓組織內(nèi)的數(shù)據(jù)能夠相對(duì)低成本、高效率的使用起來(lái)，發(fā)揮更大的價(jià)值，我們構(gòu)建的是組織內(nèi)的一整套數(shù)據(jù)流轉(zhuǎn)體系。

案例一：記錄設(shè)備庫(kù)核心屬性的歷史變更

上邊提到過(guò)，我們有一個(gè)設(shè)備庫(kù)，需要記錄核心屬性的歷史變更。記錄歷史變更有什么用呢？比如識(shí)別假冒設(shè)備，一部手機(jī)，imei、mac地址經(jīng)常變化，很可能它不是一個(gè)真實(shí)的設(shè)備。

由于設(shè)備庫(kù)非常大，4.2 大數(shù)據(jù)數(shù)倉(cāng)設(shè)計(jì)方案 是更好的選擇。

案例二：記錄商品成本價(jià)格的變化

我們有銷(xiāo)售訂單，訂單里只有銷(xiāo)售價(jià)格，我們想要計(jì)算毛利潤(rùn)，就必須要有對(duì)應(yīng)商品的成本價(jià)格，而商品的成本價(jià)，是隨著每一次進(jìn)貨入庫(kù)實(shí)時(shí)變更的（當(dāng)時(shí)用到一個(gè)移動(dòng)加權(quán)平均算法），比如該筆訂單是昨天下午2點(diǎn)整完成的，那么我必須拿到該商品昨天下午2點(diǎn)整的時(shí)點(diǎn)值價(jià)格。

該場(chǎng)景，我們的數(shù)據(jù)起止日期（t_start_date、t_end_date）就不適用了，因?yàn)槔碚撋希唐穬r(jià)格一天可能會(huì)變更多次，必須改成數(shù)據(jù)起止時(shí)間（t_start_time、t_end_time），由此帶來(lái)的數(shù)據(jù)處理邏輯的變化，上邊 4.4 增量更新的處理邏輯就不適用了，必須改用 4.3 歷史數(shù)據(jù)初始化方式了。

商品成本價(jià)格維表，數(shù)據(jù)量大概也就幾萬(wàn)條數(shù)據(jù)吧，可以采用 4.1 傳統(tǒng)數(shù)倉(cāng)設(shè)計(jì)方案存儲(chǔ)。當(dāng)然也可以使用兩張表，熱表存放近一個(gè)月或近7天的成本價(jià)格數(shù)據(jù)，其它的都?xì)w檔到冷表。

案例三：拉鏈表確實(shí)能解決你的問(wèn)題，但是有沒(méi)有別的方案呢？

? ? ?

上邊是一位網(wǎng)友的問(wèn)題，很快彭總的群里也有人問(wèn)到了拉鏈表的設(shè)計(jì)，風(fēng)大佬還在發(fā)言了，這讓我回憶起曾經(jīng)跟拉鏈表的各種糾葛，聯(lián)想到網(wǎng)上這類(lèi)文章太過(guò)零碎，就想嘗試著寫(xiě)一下。但，寫(xiě)文章真的太難啦，就這簡(jiǎn)單的一個(gè)拉鏈表，從早八點(diǎn)寫(xiě)到凌晨?jī)牲c(diǎn)。。。

? ? ?言歸正傳，簡(jiǎn)單幾句閑聊，隱約感覺(jué)到，這個(gè)需求根本不需要采用拉鏈表的。但本著實(shí)時(shí)求實(shí)的態(tài)度，了解詳情后，給他了他更好的解決方案。經(jīng)得本人同意，脫敏后，特分享給大家。

? ? ?

? ? ?業(yè)務(wù)上有一張貸款詳情表，記錄了大概七八個(gè)屬性狀態(tài)，每一次業(yè)務(wù)事件會(huì)導(dǎo)致?tīng)顟B(tài)發(fā)生變化，其實(shí)吧數(shù)倉(cāng)也可以自己算的，但太麻煩還容易造成數(shù)據(jù)不一致，所以還是每日從業(yè)務(wù)庫(kù)取時(shí)點(diǎn)值。業(yè)務(wù)庫(kù)是主從結(jié)構(gòu)，其中一個(gè)從庫(kù)，當(dāng)天的數(shù)據(jù)同步結(jié)束后會(huì)自動(dòng)斷開(kāi)跟主庫(kù)的連接，零點(diǎn)以后的狀態(tài)變更會(huì)等待 ETL 抽數(shù)完成后重新開(kāi)啟。

? ? ?業(yè)務(wù)庫(kù)貸款詳情表屬性狀態(tài)沒(méi)有更新時(shí)間這個(gè)時(shí)間戳，業(yè)務(wù)系統(tǒng)也不愿意加字段，說(shuō)是該表數(shù)據(jù)量太大，加這個(gè)字段可能會(huì)影響業(yè)務(wù)。這么大一頂帽子扣過(guò)來(lái)，咱也拿他沒(méi)辦法，只能每天全量抽。

? ? ?但是吧，數(shù)據(jù)抽取，每天都是全量抽，后續(xù) ETL 處理不能也也這么干呀。比如每天存一份全量快照，后續(xù)直接從快照出結(jié)果，有時(shí)候還要拿最近好多天的快照去跟別的表關(guān)聯(lián)。好長(zhǎng)一段時(shí)間的快照都得存著，因?yàn)楠?dú)此一份啊，刪了數(shù)據(jù)就丟了。由此帶來(lái)了大量的存儲(chǔ)、計(jì)算資源的開(kāi)銷(xiāo)，并且隨著該表的持續(xù)膨脹，里邊數(shù)據(jù)也沒(méi)有清退機(jī)制，快照會(huì)越滾越大，而且還清貸款的數(shù)據(jù)，所有屬性狀態(tài)是不會(huì)再變動(dòng)的。

? ? ?以上是網(wǎng)友的困惑，為了提高計(jì)算效率，降低存儲(chǔ)成本，他想要使用拉鏈表，記錄歷史變化。

? ? ?說(shuō)實(shí)話(huà)，拉鏈表確實(shí)能解決他的問(wèn)題，但引進(jìn)董卓消滅了外戚，萬(wàn)一袁紹降不住大魔頭咋辦？

? ? ?

下面是不用拉鏈表的問(wèn)題解決思路。以截圖開(kāi)始，就讓我們以截圖結(jié)束吧。

? ? ?

---

《大數(shù)據(jù)成神之路》正在全面PDF化。

你只需要關(guān)注下面名片，后臺(tái)回復(fù)「PDF」就可以看到阿里云盤(pán)下載鏈接了！

目前我把這些文章按照體系全部整理好了。現(xiàn)在你可以在公眾號(hào)方便的進(jìn)行查找：

電子版把他們分類(lèi)做成了下面這個(gè)樣子，并且放在了阿里云盤(pán)提供下載。

我們點(diǎn)開(kāi)一個(gè)文件夾后:

你只需要在后臺(tái)回復(fù)「PDF」就可以看到阿里云盤(pán)下載鏈接了！

Hi，我是王知無(wú)，一個(gè)大數(shù)據(jù)領(lǐng)域的原創(chuàng)作者。?

放心關(guān)注我，獲取更多行業(yè)的一手消息。