摘要：?2018“MaxCompute開發(fā)者交流”釘釘群直播分享，由阿里云數據技術專家彬甫帶來以“MaxCompute數據倉庫數據轉換實踐”為題的演講。本文首先介紹了MaxCompute的數據架構和流程，其次介紹了ETL算法中的三大算法，即更新插入算法、直接加載算法、全量歷史表算法，再次介紹了在OLTP系統(tǒng)中怎樣處理NULL值，最后對ETL相關知識進行了詳細地介紹。

2018“MaxCompute開發(fā)者交流”釘釘群直播分享，由阿里云數據技術專家彬甫帶來以“MaxCompute數據倉庫數據轉換實踐”為題的演講。本文首先介紹了MaxCompute的數據架構和流程，其次介紹了ETL算法中的三大算法，即更新插入算法、直接加載算法、全量歷史表算法，再次介紹了在OLTP系統(tǒng)中怎樣處理NULL值，最后對ETL相關知識進行了詳細地介紹。
數十款阿里云產品限時折扣中，趕快點擊這里，領券開始云上實踐吧!
直播視頻回顧
PPT下載請點擊
以下內容根據現(xiàn)場分享整理而成。

數據架構及流程

MaxCompute包含臨時層、基礎數據層、應用層三個層次，數據上云后將數據源中的數據先傳輸到MaxCompute里的臨時層中，并將數據進行處理，接著將數據經過簡單的轉換傳輸到基礎數據層，最后將數據進一步匯總到應用層進而提供服務。三個層次的具體介紹如下：

• 臨時層：臨時層包含增量數據和全量數據。

• 基礎數據層：基礎數據層的優(yōu)點是可以永久性的保存數據，它包含核心模型和通用匯總，其中核心模型又包含客戶、商品、事件、渠道、代碼等數據。基礎數據層使用數據倉庫的實體、屬性命名規(guī)范來創(chuàng)建模型表，基礎數據層表可分為主表、歷史表和追加表，且具有保存歷史數據、高效地使用、方便的設計原則。

• 應用層：應用層包含數據集市，即包含客戶分析、銷售分析、商品庫存分析。它不像基礎數據層那樣可以永久性的保存數據，而是僅保存需要的數據，但它像基礎數據層那樣適應于使用數據倉庫的實體、屬性命名規(guī)范來創(chuàng)建模型表的原則。

ETL算法

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

更新插入（主表）算法

更新插入（主表）算法適用于保留最新狀態(tài)表的處理。它是指根據主鍵（或指定字段）進行數據對比，如果目標表存在記錄，則更新，否則插入數據。由于MaxCompute中不支持update/delete，因此需使用full outer Join實現(xiàn)。?
在使用full outer Join實現(xiàn)過程中，當主鍵（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）即數據開始存在的日期，初始加載時，如果業(yè)務表中沒有日期字段對應，則填最小日期；結束日期（e_date）即數據失效或繼續(xù)有效的日期，且初始加載時需填最大日期。?
對全量歷史表算法進行兩加載四數據說明，兩加載包含初始加載和日常加載，初始加載是指直接把全量數據加載到歷史表中，其中開始日期為業(yè)務日期或最小日期，結束日期為最大日期；日常加載是指除開始日期、結束日期外的所有字段比對，通過Full Outer Join生成新增、失效、不變三部分數據，直接從歷史表中找出已經失效的數據。四數據是指當前新增數據、當前失效數據、當前不變數據、已經失效數據，當前新增數據是指開始日期為數據日期，結束日期為最大日期；當前失效數據是指開始日期不變，結束日期為數據日期；當前不變數據是指開始日期、結束日期都不變；已經失效數據像當前不變數據一樣是指開始日期、結束日期都不變，但不同點在于已經失效數據的數據已經無效。以上四部分數據可直接插入到新歷史表中。

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

NULL值處理！！！

NULL是一個SQL關鍵字，代表著未知的數據或值，它既不具備數據類型也不具備數據特征，任何值與NULL的比較都返回false，結果為空。在OLTP系統(tǒng)中，大多數表字段都存在NULL。?
在使用包含NULL值的字段做表關聯(lián)或字段聚合時，可能會出現(xiàn)與業(yè)務人員期望不一致的
結果；因此，在數據進入數據倉庫表時，建議對字段的NULL值進行非NULL的處理，但特
殊情況例外。同時，NULL問題屬于數據庫技術處理的范疇，由于NULL值的存在可能會運算出與業(yè)務人員需求不一致的結果，因此NULL值在進行SQL和數據分析時需要特別注意。

ETL

統(tǒng)一的ETL腳本開發(fā)

ETL程序從MaxCompute元數據表中讀取表的column schema時，可根據column schema生成統(tǒng)一的腳本。由于ETL邏輯固定，因此可以使用ETL程序生成相應的算法腳本，然后對腳本NULL處理部分內容進行修改即可。在安裝Python、安裝python odps插件的前提下，將程序命名為scripts_gen.py，并設定odps配置文件、目標表名、源表名、主鍵字段、ETL算法參數，通過參數的配置生成名為”.sql”的腳本文件。

ETL任務映射

在進行ETL轉換任務開發(fā)之前，為了方便進行任務的開發(fā)及相關進度記錄，需先整理好任務之間的映射關系；在開發(fā)過程或開發(fā)完成后，為了方便對任務的統(tǒng)一管理維護，需要對字段級的映射及轉換進行詳細的文檔映射記錄。

ETL轉換任務開發(fā)-舉例

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

如上圖所示，在任務開發(fā)過程中，具體操作流程為點擊主題目錄→鼠標右鍵→新建任務→填寫任務名稱→創(chuàng)建任務→在出現(xiàn)的任務腳本中將轉換腳本拷貝進行保存→在任務腳本頁面使用運行或提交→測試運行進行任務測試→在右上角點擊調度配置相關調度屬性。

ETL開發(fā)步驟

ETL開發(fā)步驟可分為ETL腳本生成、Dataworks任務創(chuàng)建、測試上線三大步，具體流程如下：

• 執(zhí)行scriptsGen.py腳本生成器程序，根據ETL算法輸入相應的參數，生成統(tǒng)一的ETL腳本文件，并對腳本文件NULL值處理部分進行修改。

• 在Dataworks數據開發(fā)頁面，創(chuàng)建相應的目錄、任務，將相應的腳本文件SQL拷貝到新建的任務中。

• 測試運行，然后設置調度配置，點擊提交。
  ETL開發(fā)經過以上三大步后，任務就可以日常自動運行了。

原文鏈接

本文為云棲社區(qū)原創(chuàng)內容，未經允許不得轉載。