摘要： 簡(jiǎn)單的拉鏈表設(shè)計(jì)
背景信息：
在數(shù)據(jù)倉(cāng)庫(kù)的數(shù)據(jù)模型設(shè)計(jì)過(guò)程中，經(jīng)常會(huì)遇到這樣的需求：?
數(shù)據(jù)量比較大;?
表中的部分字段會(huì)被update,如用戶(hù)的地址，產(chǎn)品的描述信息，訂單的狀態(tài)、手機(jī)號(hào)碼等等;?
需要查看某一個(gè)時(shí)間點(diǎn)或者時(shí)間段的歷史快照信息发乔。（比如，查看某一個(gè)訂單在歷史某一個(gè)時(shí)間點(diǎn)的狀態(tài)宫屠，比如列疗，查看某一個(gè)用戶(hù)在過(guò)去某一段時(shí)間內(nèi)，更新過(guò)幾次等等）?
變化的比例和頻率不是很大浪蹂，比如抵栈，總共有1000萬(wàn)的會(huì)員，每天新增和發(fā)生變化的有10萬(wàn)左右;如果對(duì)這邊表每天都保留一份全量坤次，那么每次全量中會(huì)保存很多不變的信息古劲，對(duì)存儲(chǔ)是極大的浪費(fèi);?
綜上所述：引入’拉鏈歷史表’，既能滿(mǎn)足反應(yīng)數(shù)據(jù)的歷史狀態(tài)缰猴，又可以最大程度的節(jié)省存儲(chǔ)产艾。?
（備注：在阿里巴巴內(nèi)部很大程度上是基于存儲(chǔ)換計(jì)算來(lái)提供開(kāi)發(fā)的效率及易用性，因?yàn)樵诋?dāng)今滑绒，存儲(chǔ)的成本遠(yuǎn)低于CPU和內(nèi)存闷堡。因此在阿里巴巴內(nèi)部會(huì)采用快照的方式將每日的全量數(shù)據(jù)進(jìn)行快照，同時(shí)也會(huì)通過(guò)極限存儲(chǔ)的方式疑故，壓縮率高杠览，在合適的場(chǎng)景下，約能壓縮為原始數(shù)據(jù)的1/30纵势。）
Demo數(shù)據(jù)
以下只是demo如何在MaxCompute中實(shí)現(xiàn)拉鏈表踱阿，所以是基于一些假設(shè)：?
同一天中同一訂單只有一個(gè)狀態(tài)發(fā)生；?
基于20150821及之前的數(shù)據(jù)并沒(méi)有同一個(gè)訂單有兩個(gè)狀態(tài)的最簡(jiǎn)單場(chǎng)景模擬钦铁；?
且數(shù)據(jù)源在阿里云RDS for Mysql中软舌。且表明為orders。
20150821以及之前的歷史訂單數(shù)據(jù)：
20150822訂單數(shù)據(jù)：
20150823的訂單數(shù)據(jù)：
實(shí)現(xiàn)思路
全量初始化：將2015-08-21及以前的全量歷史數(shù)據(jù)通過(guò)全量方式同步至ODS并刷進(jìn)DW層牛曹。?
增量更新：將2015-08-22佛点、2015-08-23的全天增量數(shù)據(jù)以增量方式刷入下游數(shù)據(jù)。
全量初始化
創(chuàng)建節(jié)點(diǎn)任務(wù)：數(shù)據(jù)同步?
選擇調(diào)度類(lèi)型：手動(dòng)調(diào)度?
配置數(shù)據(jù)同步任務(wù)：Mysql：Orders–>ODPS：ods_orders_inc_d?
where條件配置：modifiedtime <= ‘20150821’?
分區(qū)值dt=20150821
提交調(diào)度系統(tǒng)躏仇，待數(shù)據(jù)同步任務(wù)執(zhí)行成功后恋脚，再將ODS數(shù)據(jù)刷入DW腺办。?
創(chuàng)建SQL腳本：
INSERToverwriteTABLEdw_orders_his_dSELECTorderid,createtime,modifiedtime,o_status,createtimeASdw_start_date,'99991231'ASdw_end_dateFROMods_orders_inc_dWHEREdt ='20150821';
數(shù)據(jù)如下：
通過(guò)以上步驟可以將2015-08-21及以前的歷史全量數(shù)據(jù)一次性刷入DW和ODS中焰手。
增量抽取并生成拉鏈表
創(chuàng)建工作流任務(wù)并選擇周期性調(diào)度糟描。?
依次拖入數(shù)據(jù)同步節(jié)點(diǎn)任務(wù)和SQL任務(wù)。?
在數(shù)據(jù)同步任務(wù)中where條件配置為：modifiedtime=bdp.system.bizdate目標(biāo)表odsordersincd分區(qū)配置為dt={bdp.system.bizdate}?
配置SQL節(jié)點(diǎn)书妻，且為數(shù)據(jù)同步節(jié)點(diǎn)的下游節(jié)點(diǎn)船响。
--通過(guò)DW歷史數(shù)據(jù)和ODS增量數(shù)據(jù)刷新DW表insertoverwritetabledw_orders_his_dSELECTa0.orderid, a0.createtime, a0.modifiedtime, a0.o_status, a0.dw_start_date, a0.dw_end_dateFROM(? ? -- 對(duì)orderid進(jìn)行開(kāi)窗然后按照生命周期結(jié)束時(shí)間倒序排，支持重跑SELECTa1.orderid, a1.createtime, a1.modifiedtime, a1.o_status, a1.dw_start_date, a1.dw_end_date? ? , ROW_NUMBER() OVER (distributeBYa1.orderid,a1.createtime, a1.modifiedtime,a1.o_status sortBYa1.dw_end_dateDESC)ASnumsFROM(? ? ? ? -- 用歷史數(shù)據(jù)與增量22日的數(shù)據(jù)進(jìn)行匹配躲履，當(dāng)發(fā)現(xiàn)在22日新增數(shù)據(jù)中存在且end_date > 當(dāng)前日期的就表示數(shù)據(jù)狀態(tài)發(fā)生過(guò)變化见间，然后修改生命周期? ? ? ??
-- 修改昨日已經(jīng)生命截止的數(shù)據(jù)并union最新增量數(shù)據(jù)到DW
SELECTa.orderid, a.createtime, a.modifiedtime, a.o_status, a.dw_start_date? ? ? ? ? ? ? ,CASEWHENb.orderidISNOTNULLANDa.dw_end_date > ${bdp.system.bizdate}THEN${yesterday}ELSEa.dw_end_dateENDASdw_end_dateFROMdw_orders_his_d aLEFTOUTERJOIN(SELECT*FROMods_orders_inc_dWHEREdt = ${bdp.system.bizdate}? ? ? ? ) bONa.orderid = b.orderidUNIONALL--2015-08-22的增量數(shù)據(jù)刷新到DWSELECTorderid, createtime, modifiedtime, o_status, modifiedtimeASdw_start_date? ? ? ? ? ? ,'99991231'ASdw_end_dateFROMods_orders_inc_dWHEREdt = ${bdp.system.bizdate}? ? ) a1) a0 -- 開(kāi)窗口后對(duì)某個(gè)訂單中生命周期為'9999-12-31'的取值并寫(xiě)入，防止重跑數(shù)據(jù)情況工猜。WHEREa0.nums =1orderbya0.orderid,a0.dw_start_date;
備注：測(cè)試運(yùn)行的時(shí)候米诉，選擇業(yè)務(wù)日期為20150822。也可以通過(guò)補(bǔ)數(shù)據(jù)方式篷帅，直接把20150822和20150823兩天的增量數(shù)據(jù)刷入DW中史侣。上面SQL中yesterday為自定義變量，其賦值為{yyyymmdd-1}
通過(guò)如上方式將20150822的增量數(shù)據(jù)刷入DW魏身，如下所示：
通過(guò)同樣的方式將2015-08-23日的數(shù)據(jù)增量輸入DW惊橱，其結(jié)果為：?
關(guān)于基于歷史拉鏈表回滾某一天或一段時(shí)間內(nèi)的數(shù)據(jù)，還是一個(gè)相對(duì)比較復(fù)雜的話(huà)題箭昵，這個(gè)可以下載再談税朴。
閱讀更多干貨好文，請(qǐng)關(guān)注掃描以下二維碼：?
