东京热日韩品精久久,亚洲午夜av福利

摘要：簡(jiǎn)單的拉鏈表設(shè)計(jì)

背景信息：

在數(shù)據(jù)倉(cāng)庫(kù)的數(shù)據(jù)模型設(shè)計(jì)過(guò)程中，經(jīng)常會(huì)遇到這樣的需求：?

數(shù)據(jù)量比較大;?

表中的部分字段會(huì)被update,如用戶的地址，產(chǎn)品的描述信息，訂單的狀態(tài)、手機(jī)號(hào)碼等等;?

需要查看某一個(gè)時(shí)間點(diǎn)或者時(shí)間段的歷史快照信息。（比如，查看某一個(gè)訂單在歷史某一個(gè)時(shí)間點(diǎn)的狀態(tài)，比如，查看某一個(gè)用戶在過(guò)去某一段時(shí)間內(nèi)，更新過(guò)幾次等等）?

變化的比例和頻率不是很大，比如，總共有1000萬(wàn)的會(huì)員，每天新增和發(fā)生變化的有10萬(wàn)左右;如果對(duì)這邊表每天都保留一份全量，那么每次全量中會(huì)保存很多不變的信息，對(duì)存儲(chǔ)是極大的浪費(fèi);?

綜上所述：引入’拉鏈歷史表’，既能滿足反應(yīng)數(shù)據(jù)的歷史狀態(tài)，又可以最大程度的節(jié)省存儲(chǔ)。?

（備注：在阿里巴巴內(nèi)部很大程度上是基于存儲(chǔ)換計(jì)算來(lá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ù)并沒有同一個(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)度類型：手動(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)行開窗然后按照生命周期結(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 -- 開窗口后對(duì)某個(gè)訂單中生命周期為'9999-12-31'的取值并寫入，防止重跑數(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}