2018“MaxCompute開發者交流”釘釘群直播分享，由阿裡雲資料技術專家彬甫帶來以“MaxCompute資料倉庫資料轉換實踐”為題的演講。本文首先介紹了MaxCompute的資料架構和流程，其次介紹了ETL算法中的三大算法，即更新插入算法、直接加載算法、全量曆史表算法，再次介紹了在OLTP系統中怎樣處理NULL值，最後對ETL相關知識進行了詳細地介紹。
數十款阿裡雲産品限時折扣中，
趕快點選這裡 ，領券開始雲上實踐吧! 直播視訊回顧 PPT下載下傳請點選 以下内容根據現場分享整理而成。資料架構及流程
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開發經過以上三大步後，任務就可以日常自動運作了。