基於MaxCompute的拉鍊表設計

背景資訊：

1. 資料量比較大;
2. 表中的部分欄位會被update,如使用者的地址，產品的描述資訊，訂單的狀態、手機號碼等等;
3. 需要檢視某一個時間點或者時間段的歷史快照資訊。（比如，檢視某一個訂單在歷史某一個時間點的狀態，比如，檢視某一個使用者在過去某一段時間內，更新過幾次等等）
4. 變化的比例和頻率不是很大，比如，總共有1000萬的會員，每天新增和發生變化的有10萬左右;如果對這邊表每天都保留一份全量，那麼每次全量中會儲存很多不變的資訊，對儲存是極大的浪費;

Demo資料

• 同一天中同一訂單隻有一個狀態發生；
• 基於20150821及之前的資料並沒有同一個訂單有兩個狀態的最簡單場景模擬；
• 且資料來源在阿里雲RDS for Mysql中。且表明為orders。

20150821以及之前的歷史訂單資料：

20150822訂單資料：

20150823的訂單資料：

CREATE TABLE ods_orders_inc_d
(
 orderid BIGINT
 ,createtime STRING
 ,modifiedtime STRING
 ,o_status STRING
)
PARTITIONED BY (dt STRING)
LIFECYCLE 7;

CREATE TABLE dw_orders_his_d
(
 orderid BIGINT COMMENT `訂單ID`
 ,createtime STRING COMMENT `訂單建立時間`
 ,modifiedtime STRING COMMENT `訂單修改時間`
 ,o_status STRING COMMENT `訂單修改時間`
 ,dw_start_date STRING COMMENT `訂單生命週期開始時間`
 ,dw_end_date STRING COMMENT `訂單生命週期結束時間`
);

• 全量初始化：將2015-08-21及以前的全量歷史資料通過全量方式同步至ODS並刷進DW層。
• 增量更新：將2015-08-22、2015-08-23的全天增量資料以增量方式刷入下游資料。

全量初始化

1. 建立節點任務：資料同步
2. 選擇排程型別：手動排程
3. 配置資料同步任務：Mysql：Orders–>ODPS：ods_orders_inc_d
4. where條件配置：modifiedtime <= `20150821`
5. 分割槽值dt=20150821

INSERT overwrite TABLE dw_orders_his_d
SELECT orderid,createtime,modifiedtime,o_status,createtime AS dw_start_date,`99991231` AS dw_end_date
FROM ods_orders_inc_d
WHERE dt = `20150821`;

通過以上步驟可以將2015-08-21及以前的歷史全量資料一次性刷入DW和ODS中。

增量抽取並生成拉鍊表

1. 建立工作流任務並選擇週期性排程。
2. 依次拖入資料同步節點任務和SQL任務。
3. 在資料同步任務中where條件配置為：modifiedtime=${bdp.system.bizdate}
4. 目標表ods_orders_inc_d分割槽配置為dt=${bdp.system.bizdate}
5. 配置SQL節點，且為資料同步節點的下游節點。

--通過DW歷史資料和ODS增量資料重新整理DW表
insert overwrite table dw_orders_his_d 
SELECT a0.orderid, a0.createtime, a0.modifiedtime, a0.o_status, a0.dw_start_date, a0.dw_end_date
FROM (
	-- 對orderid進行開窗然後按照生命週期結束時間倒序排，支援重跑
	SELECT a1.orderid, a1.createtime, a1.modifiedtime, a1.o_status, a1.dw_start_date, a1.dw_end_date
	, ROW_NUMBER() OVER (distribute BY a1.orderid,a1.createtime, a1.modifiedtime,a1.o_status sort BY a1.dw_end_date DESC) AS nums
	FROM (
		-- 用歷史資料與增量22日的資料進行匹配，當發現在22日新增資料中存在且end_date > 當前日期的就表示資料狀態發生過變化，然後修改生命週期
		-- 修改昨日已經生命截止的資料並union最新增量資料到DW
		SELECT a.orderid, a.createtime, a.modifiedtime, a.o_status, a.dw_start_date  
			, CASE 
				WHEN b.orderid IS NOT NULL AND a.dw_end_date > ${bdp.system.bizdate} THEN ${yesterday}
				ELSE a.dw_end_date
			END AS dw_end_date
		FROM dw_orders_his_d a
		LEFT OUTER JOIN (
			SELECT *
			FROM ods_orders_inc_d
			WHERE dt = ${bdp.system.bizdate}
		) b
		ON a.orderid = b.orderid
		UNION ALL
		--2015-08-22的增量資料重新整理到DW
		SELECT orderid, createtime, modifiedtime, o_status, modifiedtime AS dw_start_date
			, `99991231` AS dw_end_date
		FROM ods_orders_inc_d
		WHERE dt = ${bdp.system.bizdate}
	) a1
) a0 
-- 開視窗後對某個訂單中生命週期為`9999-12-31`的取值並寫入，防止重跑資料情況。
WHERE a0.nums = 1
order by a0.orderid,a0.dw_start_date;

備註：測試執行的時候，選擇業務日期為20150822。也可以通過補資料方式，直接把20150822和20150823兩天的增量資料刷入DW中。上面SQL中${yesterday}為自定義變數，其賦值為${yyyymmdd-1}

如何使用拉鍊表

• 檢視某一天的全量歷史快照資料。

SELECT *
FROM dw_orders_his_d
WHERE dw_start_date <= `20150822`
	AND dw_end_date >= `20150822`
ORDER BY orderid
LIMIT 10000;

• 取一段時間的變化記錄集合，如在20150822-20150823變化的記錄。

SELECT *
FROM dw_orders_his_d
WHERE dw_start_date <= `20150823`
	AND dw_end_date >= `20150822`
ORDER BY orderid
LIMIT 10000;

• 檢視某一訂單歷史變化情況。

SELECT *
FROM dw_orders_his_d
WHERE orderid = 8
ORDER BY dw_start_date;

• 取最新的資料。

SELECT *
FROM dw_orders_his_d
WHERE dw_end_date = `99991231`