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

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

數(shù)據(jù)架構及流程

MaxCompute包含臨時層、基礎數(shù)據(jù)層、應用層三個層次，數(shù)據(jù)上云后將數(shù)據(jù)源中的數(shù)據(jù)先傳輸?shù)組axCompute里的臨時層中，并將數(shù)據(jù)進行處理，接著將數(shù)據(jù)經(jīng)過簡單的轉(zhuǎn)換傳輸?shù)交A數(shù)據(jù)層，最后將數(shù)據(jù)進一步匯總到應用層進而提供服務。三個層次的具體介紹如下：

• 臨時層：臨時層包含增量數(shù)據(jù)和全量數(shù)據(jù)。

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

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

ETL算法

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

更新插入（主表）算法

更新插入（主表）算法適用于保留最新狀態(tài)表的處理。它是指根據(jù)主鍵（或指定字段）進行數(shù)據(jù)對比，如果目標表存在記錄，則更新，否則插入數(shù)據(jù)。由于MaxCompute中不支持update/delete，因此需使用full outer Join實現(xiàn)。?
在使用full outer Join實現(xiàn)過程中，當主鍵（Source Table）為NULL，主鍵（Target Table FULL）為NOT NULL時，OUTER JOIN 選取結果為不變數(shù)據(jù)；當主鍵（Source Table）為NOT NULL，主鍵（ Target Table FULL）為NULL時，OUTER JOIN 選取結果為新增數(shù)據(jù)；當主鍵（ Source Table）和主鍵（Target Table FULL）都為NOT NULL時，OUTER JOIN 選取結果為變化數(shù)據(jù)。

直接追加算法

直接追加算法是指增量數(shù)據(jù)直接追加到目標表中，此算法適合流水、交易、事件、話單等增量且不修改的數(shù)據(jù)。

全量歷史表算法

全量歷史表中必須包含開始日期（s_date）、結束日期（e_date）這兩個字段，通過這兩個字段歷史表記錄了數(shù)據(jù)的變動軌跡。開始日期（s_date）即數(shù)據(jù)開始存在的日期，初始加載時，如果業(yè)務表中沒有日期字段對應，則填最小日期；結束日期（e_date）即數(shù)據(jù)失效或繼續(xù)有效的日期，且初始加載時需填最大日期。?
對全量歷史表算法進行兩加載四數(shù)據(jù)說明，兩加載包含初始加載和日常加載，初始加載是指直接把全量數(shù)據(jù)加載到歷史表中，其中開始日期為業(yè)務日期或最小日期，結束日期為最大日期；日常加載是指除開始日期、結束日期外的所有字段比對，通過Full Outer Join生成新增、失效、不變?nèi)糠謹?shù)據(jù)，直接從歷史表中找出已經(jīng)失效的數(shù)據(jù)。四數(shù)據(jù)是指當前新增數(shù)據(jù)、當前失效數(shù)據(jù)、當前不變數(shù)據(jù)、已經(jīng)失效數(shù)據(jù)，當前新增數(shù)據(jù)是指開始日期為數(shù)據(jù)日期，結束日期為最大日期；當前失效數(shù)據(jù)是指開始日期不變，結束日期為數(shù)據(jù)日期；當前不變數(shù)據(jù)是指開始日期、結束日期都不變；已經(jīng)失效數(shù)據(jù)像當前不變數(shù)據(jù)一樣是指開始日期、結束日期都不變，但不同點在于已經(jīng)失效數(shù)據(jù)的數(shù)據(jù)已經(jīng)無效。以上四部分數(shù)據(jù)可直接插入到新歷史表中。

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

NULL值處理?。?！

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

ETL

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

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

ETL任務映射

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

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

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

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

ETL開發(fā)步驟

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

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

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

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

原文鏈接

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