ETL概念

ETL是資料抽取（Extract）、清洗（Cleaning）、轉換（Transform）、裝載（Load）的過程。是構建資料倉庫的重要一環，使用者從資料來源抽取出所需的資料，經過資料清洗,最終按照預先定義好的資料倉庫模型，將資料載入到資料倉庫中去。

一、資料的抽取（Extract）

這一部分需要在調研階段做大量的工作，首先要搞清楚資料是從幾個業務系統中來,各個業務系統的資料庫伺服器運行什麼DBMS,是否存在手工資料，手工資料量有多大，是否存在非結構化的資料等等，當收集完這些資訊之後才可以進行資料抽取的設計。

1、對於與存放DW的資料庫系統相同的資料來源處理方法

這一類資料來源在設計上比較容易。一般情況下，DBMS（SQLServer、Oracle）都會提供資料庫連結功能，在DW資料庫伺服器和原業務系統之間建立直接的連結關係就可以寫Select語句直接訪問。

2、對於與DW資料庫系統不同的資料來源的處理方法

對於這一類資料來源，一般情況下也可以通過ODBC的方式建立資料庫連結——如 SQLServer和Oracle之間。如果不能建立資料庫連結，可以有兩種方式完成，一種是通過工具將源資料匯出成.txt或者是.xls檔案，然後再將這些源系統檔案匯入到ODS中。另外一種方法是通過程式介面來完成。

3、對於檔案類型資料來源（.txt,.xls）

可以培訓業務人員利用資料庫工具將這些資料匯入到指定的資料庫，然後從指定的資料庫中抽取。或者還可以藉助工具實現，如SQLServer2005的SSIS服務的平面資料來源和平面目標等元件匯入ODS中去。

4、增量更新的問題

對於資料量大的系統，必須考慮增量抽取。一般情況下，業務系統會記錄業務發生的時間，我們可以用來做增量的標誌,每次抽取之前首先判斷ODS中記錄最大的時間，然後根據這個時間去業務系統取大於這個時間所有的記錄。利用業務系統的時間戳，一般情況下，業務系統沒有或者部分有時間戳。

二、資料的清洗轉換（Cleaning）

一般情況下，資料倉庫分為ODS、DW兩部分。通常的做法是從業務系統到ODS做清洗，將髒資料和不完整資料過濾掉，在從ODS到DW的過程中轉換，進行一些業務規則的計算和聚合。

1、資料清洗

資料清洗的任務是過濾那些不符合要求的資料，將過濾的結果交給業務主管部門，確認是否過濾掉還是由業務單位修正之後再進行抽取。不符合要求的資料主要是有不完整的資料、錯誤的資料、重複的資料三大類。

（1）不完整的資料：

這一類資料主要是一些應該有的資訊缺失，如供應商的名稱、分公司的名稱、客戶的區域資訊缺失、業務系統中主表與明細表不能匹配等。對於這一類資料過濾出來，按缺失的內容分別寫入不同Excel檔案向客戶提交，要求在規定的時間內補全。補全後才寫入資料倉庫。

（2）錯誤的資料：

這一類錯誤產生的原因是業務系統不夠健全，在接收輸入後沒有進行判斷直接寫入後臺資料庫造成的，比如數值資料輸成全形數字字元、字元串資料後面有一個回車操作、日期格式不正確、日期越界等。這一類資料也要分類，對於類似於全形字元、資料前後有不可見字元的問題，只能通過寫SQL語句的方式找出來，然後要求客戶在業務系統修正之後抽取。日期格式不正確的或者是日期越界的這一類錯誤會導致ETL運行失敗，這一類錯誤需要去業務系統資料庫用SQL的方式挑出來，交給業務主管部門要求限期修正，修正之後再抽取。

（3）重複的資料：

對於這一類資料——特別是維表中會出現這種情況——將重複資料記錄的所有欄位匯出來，讓客戶確認並整理。

資料清洗是一個反覆的過程，不可能在幾天內完成，只有不斷地發現問題，解決問題。

對於是否過濾，是否修正一般要求客戶確認，對於過濾掉的資料，寫入Excel檔案或者將過濾資料寫入資料表，在ETL開發的初期可以每天向業務單位傳送過濾資料的郵件，促使他們儘快地修正錯誤,同時也可以做為將來驗證資料的依據。資料清洗需要注意的是不要將有用的資料過濾掉，對於每個過濾規則認真進行驗證，並要使用者確認。

三、資料轉換（Transform）

資料轉換的任務主要進行不一致的資料轉換、資料粒度的轉換，以及一些商務規則的計算。

（1）不一致資料轉換：

這個過程是一個整合的過程，將不同業務系統的相同類型的資料統一，比如同一個供應商在結算系統的編碼是XX0001,而在CRM中編碼是YY0001，這樣在抽取過來之後統一轉換成一個編碼。

（2）資料粒度的轉換：

業務系統一般儲存非常明細的資料，而資料倉庫中資料是用來分析的，不需要非常明細的資料。一般情況下，會將業務系統資料按照資料倉庫粒度進行聚合。

（3）商務規則的計算：

不同的企業有不同的業務規則、不同的資料指標，這些指標有的時候不是簡單地加加減減就能完成，這個時候需要在ETL中將這些資料指標計算好了之後儲存在資料倉庫中，以供分析使用。

四、資料載入（Load）

把資料載入至目的地，比如資料倉庫中。通常的做法是，將處理好的資料寫成特定格式（如parquet、csv等）的檔案，然後再把檔案掛載到指定的表分區上。也有些表的資料量很小，不會採用分區表，而是直接生成最終的資料表。

ETL開發工作流程

資料倉庫和ODS並存方案

經過調研，發現大體上有三種解法：

1、業務資料 － ODS － 資料倉庫

優點：

這樣做的好處是ODS的資料與資料倉庫的資料高度統一；開發成本低，至少開發一次並應用到ODS即可；可見ODS是發揮承上啟下的作用，調研阿里巴巴的資料部門也是這麼實現的。

缺點：

資料倉庫需要的所有資料都需要走ODS，那麼ODS的靈活性必然受到影響，甚至不利於擴展、系統的靈活性差

2、OB － ODS

優點：

結構簡單。一般的初創資料分析團隊都是類似的結構，比如我們部門就應該歸結到這一範疇

缺點：

這樣所有資料都歸結到ODS，長期資料決策分析能力差，軟硬體成本高，模組劃分不清晰，通用性差

3、資料倉庫和ODS並行

可見這個模型兼顧了上面提高的各自優點，且便於擴展，ODS和資料倉庫各做各的，形成優勢互補！可以解決現在網際網路公司遇到的快速變化、快速開發等特點！特別是對於那些剛剛創建資料團隊，資料開發人員緊缺的公司，可以嘗試使用這個資料架構解決問題！

Data Mart 資料集市

為了特定的應用目的或應用範圍，而從資料倉庫中獨立出來的一部分資料，也可稱為部門資料或主題資料（subjectarea）。在資料倉庫的實施過程中往往可以從一個部門的資料集市著手，以後再用幾個資料集市組成一個完整的資料倉庫。需要注意的就是在實施不同的資料集市時，同一含義的欄位定義一定要相容，這樣以後實施資料倉庫時才不會造成大麻煩。

作者：GTW_Zeus
連結：https://juejin.cn/post/6989530429644603429
來源：掘金