MaxCompute資料倉儲在更新插入、直接載入、全量歷史表三大演算法中的資料轉換實踐

資料架構及流程 

MaxCompute包含臨時層、基礎資料層、應用層三個層次，資料上雲後將資料來源中的資料先傳輸到MaxCompute裡的臨時層中，並將資料進行處理，接著將資料經過簡單的轉換傳輸到基礎資料層，最後將資料進一步彙總到應用層進而提供服務。 三個層次的具體介紹如下：

• 臨時層：臨時層包含增量資料和全量資料。
  

• 基礎資料層：基礎資料層的優點是可以永久性的儲存資料，它包含核心模型和通用匯總，其中核心模型又包含客戶、商品、事件、渠道、程式碼等資料。基礎資料層使用資料倉儲的實體、屬性命名規範來建立模型表，基礎資料層表可分為主表、歷史表和追加表，且具有儲存歷史資料、高效地使用、方便的設計原則。
  

• 應用層：應用層包含資料集市，即包含客戶分析、銷售分析、商品庫存分析。它不像基礎資料層那樣可以永久性的儲存資料，而是僅儲存需要的資料，但它像基礎資料層那樣適應於使用資料倉儲的實體、屬性命名規範來建立模型表的原則。
  

ETL演算法

ETL載入轉換策略有M1全表覆蓋、M2更新插入、M3直接載入、M4全量歷史拉鍊、M5增量歷史拉鍊五種策略，在ETL演算法中主要介紹M2更新插入（主表）演算法、M3直接載入演算法、M4全量歷史表演算法三種演算法。

更新插入（主表）演算法

更新插入（主表）演算法適用於保留最新狀態表的處理。它是指根據主鍵（或指定欄位）進行資料對比，如果目標表存在記錄，則更新，否則插入資料。由於MaxCompute中不支援update/delete，因此需使用full outer Join實現。 

在使用full outer Join實現過程中，當主鍵（Source Table）為NULL，主鍵（Target Table FULL）為NOT NULL時，OUTER JOIN 選取結果為不變資料；當主鍵（Source Table）為NOT NULL，主鍵（ Target Table FULL）為NULL時，OUTER JOIN 選取結果為新增資料；當主鍵（ Source Table）和主鍵（Target Table FULL）都為NOT NULL時，OUTER JOIN 選取結果為變化資料。

直接追加演算法

直接追加演算法是指增量資料直接追加到目標表中，此演算法適合流水、交易、事件、話單等增量且不修改的資料。

全量歷史表演算法

全量歷史表中必須包含開始日期（s_date）、結束日期（e_date）這兩個欄位，透過這兩個欄位歷史表記錄了資料的變動軌跡。開始日期（s_date）即資料開始存在的日期，初始載入時，如果業務表中沒有日期欄位對應，則填最小日期；結束日期（e_date）即資料失效或繼續有效的日期，且初始載入時需填最大日期。 

對全量歷史表演算法進行兩載入四資料說明，兩載入包含初始載入和日常載入，初始載入是指直接把全量資料載入到歷史表中，其中開始日期為業務日期或最小日期，結束日期為最大日期；日常載入是指除開始日期、結束日期外的所有欄位比對，透過Full Outer Join生成新增、失效、不變三部分資料，直接從歷史表中找出已經失效的資料。四資料是指當前新增資料、當前失效資料、當前不變資料、已經失效資料，當前新增資料是指開始日期為資料日期，結束日期為最大日期；當前失效資料是指開始日期不變，結束日期為資料日期；當前不變資料是指開始日期、結束日期都不變；已經失效資料像當前不變資料一樣是指開始日期、結束日期都不變，但不同點在於已經失效資料的資料已經無效。以上四部分資料可直接插入到新歷史表中。

在上圖中，左側是全量源資料表A，右側是歷史表當前資料B，1代表新增資料，2代表當前未變化資料，3代表當前失效資料。透過A FULL OUTER JOIN B後生成新增資料、當前未變化資料、當前失效資料三種資料，再加上原有的歷史已經失效的資料，總共四種資料構成NEW H。

NULL值處理！！！

NULL是一個SQL關鍵字，代表著未知的資料或值，它既不具備資料型別也不具備資料特徵，任何值與NULL的比較都返回false，結果為空。在OLTP系統中，大多數表欄位都存在NULL。 

在使用包含NULL值的欄位做表關聯或欄位聚合時，可能會出現與業務人員期望不一致的

結果；因此，在資料進入資料倉儲表時，建議對欄位的NULL值進行非NULL的處理，但特

殊情況例外。同時，NULL問題屬於資料庫技術處理的範疇，由於NULL值的存在可能會運算出與業務人員需求不一致的結果，因此NULL值在進行SQL和資料分析時需要特別注意。

ETL

統一的ETL指令碼開發

ETL程式從MaxCompute後設資料表中讀取表的column schema時，可根據column schema生成統一的指令碼。由於ETL邏輯固定，因此可以使用ETL程式生成相應的演算法指令碼，然後對指令碼NULL處理部分內容進行修改即可。在安裝Python、安裝python odps外掛的前提下，將程式命名為scripts_gen.py，並設定odps配置檔案、目標表名、源表名、主鍵欄位、ETL演算法引數，透過引數的配置生成名為”.sql”的指令碼檔案。

ETL任務對映

在進行ETL轉換任務開發之前，為了方便進行任務的開發及相關進度記錄，需先整理好任務之間的對映關係；在開發過程或開發完成後，為了方便對任務的統一管理維護，需要對欄位級的對映及轉換進行詳細的文件對映記錄。

ETL轉換任務開發-舉例

如上圖所示，根目錄應為02_資料轉換格式，DataWorks任務目錄結構應按主題劃分子目錄，儲存主題表的任務指令碼，且任務名稱為表名。

如上圖所示，在任務開發過程中，具體操作流程為點選主題目錄→滑鼠右鍵→新建任務→填寫任務名稱→建立任務→在出現的任務指令碼中將轉換指令碼複製進行儲存→在任務指令碼頁面使用執行或提交→測試執行進行任務測試→在右上角點選排程配置相關排程屬性。

ETL開發步驟

ETL開發步驟可分為ETL指令碼生成、Dataworks任務建立、測試上線三大步，具體流程如下：

• 執行scriptsGen.py指令碼生成器程式，根據ETL演算法輸入相應的引數，生成統一的ETL指令碼檔案，並對指令碼檔案NULL值處理部分進行修改。
  

• 在Dataworks資料開發頁面，建立相應的目錄、任務，將相應的指令碼檔案SQL複製到新建的任務中。
  

• 測試執行，然後設定排程配置，點選提交。
  

ETL開發經過以上三大步後，任務就可以日常自動執行了。

【作者：彬甫，阿里雲資料技術專家】