1.4 抽取-轉換-裝載

前面已經多次提到了ETL一詞，它是Extract、Transform、Load三個英文單詞首字母的簡寫，中文意為抽取、轉換、裝載。ETL是建立數據倉庫最重要的處理過程，也是最體現工作量的環節，一般會占到整個數據倉庫項目工作量的一半以上。

● 抽取：從操作型數據源獲取數據。

● 轉換：轉換數據，使之轉變為適用于查詢和分析的形式和結構。

● 裝載：將轉換后的數據導入到最終的目標數據倉庫。

建立一個數據倉庫，就是要把來自于多個異構的源系統的數據集成在一起，放置于一個集中的位置用于數據分析。如果一開始這些源系統數據就是兼容的當然最好，但情況往往不是這樣。ETL系統的工作就是要把異構的數據轉換成同構的。如果沒有ETL，不可能對異構的數據進行程序化的分析。

1.4.1 數據抽取

抽取操作從源系統獲取數據給后續的數據倉庫環境使用。這是ETL處理的第一步，也是最重要的一步。數據被成功抽取后，才可以進行轉換并裝載到數據倉庫中。能否正確地獲取數據直接關系到后面步驟的成敗。數據倉庫典型的源系統是事務處理應用，例如，一個銷售分析數據倉庫的源系統之一，可能是一個訂單錄入系統，其中包含當前銷售訂單相關操作的全部記錄。

設計和建立數據抽取過程，在ETL處理乃至整個數據倉庫處理過程中，一般是較為耗時的任務。源系統很可能非常復雜并且缺少相應的文檔，因此只是決定需要抽取哪些數據可能就已經非常困難了。通常數據都不是只抽取一次，而是需要以一定的時間間隔反復抽取，通過這樣的方式把數據的所有變化提供給數據倉庫，并保持數據的及時性。除此之外，源系統一般不允許外部系統對它進行修改，也不允許外部系統對它的性能和可用性產生影響，數據倉庫的抽取過程要能適應這樣的需求。如果已經明確了需要抽取的數據，下一步就該考慮從源系統抽取數據的方法了。

對抽取方法的選擇高度依賴于源系統和目標數據倉庫環境的業務需要。一般情況下，不可能因為需要提升數據抽取的性能，而在源系統中添加額外的邏輯，也不能增加這些源系統的工作負載。有時，用戶甚至都不允許增加任何“開箱即用”的外部應用系統，這叫做對源系統具有侵入性。下面分別從邏輯和物理兩方面介紹數據抽取方法。

1．邏輯抽取

有兩種邏輯抽取類型：全量抽取和增量抽取。

（1）全量抽取

源系統的數據全部被抽取。因為這種抽取類型影響源系統上當前所有有效的數據，所以不需要跟蹤自上次成功抽取以來的數據變化。源系統只需要原樣提供現有的數據而不需要附加的邏輯信息（比如時間戳等）。一個全表導出的數據文件或者一個查詢源表所有數據的SQL語句，都是全量抽取的例子。

（2）增量抽取

只抽取某個事件發生的特定時間點之后的數據。通過該事件發生的時間順序能夠反映數據的歷史變化，它可能是最后一次成功抽取，也可能是一個復雜的業務事件，如最后一次財務結算等。必須能夠標識出特定時間點之后所有的數據變化。這些發生變化的數據可以由源系統自身來提供，例如能夠反映數據最后發生變化的時間戳列，或者是一個原始事務處理之外的，只用于跟蹤數據變化的變更日志表。大多數情況下，使用后者意味著需要在源系統上增加抽取邏輯。

在許多數據倉庫中，抽取過程不含任何變化數據捕獲技術。取而代之的是，把源系統中的整個表抽取到數據倉庫過渡區，然后用這個表的數據和上次從源系統抽取得到的表數據作比對，從而找出發生變化的數據。雖然這種方法不會對源系統造成很大的影響，但顯然需要考慮給數據倉庫處理增加的負擔，尤其是當數據量很大的時候。

2．物理抽取

依賴于選擇的邏輯抽取方法和能夠對源系統所做的操作和所受的限制，存在兩種物理數據抽取機制：直接從源系統聯機抽取或者間接從一個脫機結構抽取數據。這個脫機結構有可能已經存在，也可能需要由抽取程序生成。

（1）聯機抽取

數據直接從源系統抽取。抽取進程或者直連源系統數據庫，訪問它們的數據表，或者連接到一個存儲快照日志或變更記錄表的中間層系統。注意這個中間層系統并不需要必須和源系統物理分離。

（2）脫機抽取

數據不從源系統直接抽取，而是從一個源系統以外的過渡區抽取。過渡區可能已經存在（例如數據庫備份文件、關系數據庫系統的重做日志、歸檔日志等），或者抽取程序自己建立。應該考慮以下的存儲結構：

● 數據庫備份文件。一般需要數據還原操作才能使用。

● 備用數據庫。如Oracle的DataGuard和MySQL的數據復制等技術。

● 平面文件。數據定義成普通格式，關于源對象的附加信息（列名、數據類型等）需要另外處理。

● 導出文件。關系數據庫大都自帶數據導出功能，如Oracle的exp/expdp程序和MySQL的mysqldump程序，都可以用于生成導出數據文件。

● 重做日志和歸檔日志。每種數據庫系統都有自己的日志格式和解析工具。

3．變化數據捕獲

抽取處理需要重點考慮增量抽取，也被稱為變化數據捕獲，簡稱CDC。假設一個數據倉庫系統，在每天夜里的業務低峰時間從操作型源系統抽取數據，那么增量抽取只需要過去24小時內發生變化的數據。變化數據捕獲也是建立準實時數據倉庫的關鍵技術。

當你能夠識別并獲得最近發生變化的數據時，抽取及其后面的轉換、裝載操作顯然都會變得更高效，因為要處理的數據量會小很多。遺憾的是，很多源系統很難識別出最近變化的數據，或者必須侵入源系統才能做到。變化數據捕獲是數據抽取中典型的技術挑戰。

常用的變化數據捕獲方法有時間戳、快照、觸發器和日志四種。相信熟悉數據庫的讀者對這些方法都不會陌生。時間戳方法需要源系統有相應的數據列表示最后的數據變化。快照方法可以使用數據庫系統自帶的機制實現，如Oracle的物化視圖技術，也可以自己實現相關邏輯，但會比較復雜。觸發器是關系數據庫系統具有的特性，源表上建立的觸發器會在對該表執行insert、update、delete等語句時被觸發，觸發器中的邏輯用于捕獲數據的變化。日志可以使用應用日志或系統日志，這種方式對源系統不具有侵入性，但需要額外的日志解析工作。關于這4種方案的特點，將會在本書第7章“數據抽取”具體說明。

1.4.2 數據轉換

數據從操作型源系統獲取后，需要進行多種轉換操作。如統一數據類型、處理拼寫錯誤、消除數據歧義、解析為標準格式等。數據轉換通常是最復雜的部分，也是ETL開發中用時最長的一步。數據轉換的范圍極廣，從單純的數據類型轉化到極為復雜的數據清洗技術。

在數據轉換階段，為了能夠最終將數據裝載到數據倉庫中，需要在已經抽取來的數據上應用一系列的規則和函數。有些數據可能不需要轉換就能直接導入到數據倉庫。

數據轉換一個最重要的功能是清洗數據，目的是只有“合規”的數據才能進入目標數據倉庫。這步操作在不同系統間交互和通信時尤其必要，例如，一個系統的字符集在另一個系統中可能是無效的。另一方面，由于某些業務和技術的需要，也需要進行多種數據轉換，例如下面的情況：

● 只裝載特定的數據列。例如，某列為空的數據不裝載。

● 統一數據編碼。例如，性別字段，有些系統使用的是1和0，有些是‘M’和‘F'，有些是‘男’和‘女’，統一成‘M’和‘F'。

● 自由值編碼。例如，將‘Male’改成‘M'。

● 預計算。例如，產品單價 * 購買數量 = 金額。

● 基于某些規則重新排序以提高查詢性能。

● 合并多個數據源的數據并去重。

● 預聚合。例如，匯總銷售數據。

● 行列轉置。

● 將一列轉為多列。例如，某列存儲的數據是以逗號作為分隔符的字符串，將其分割成多列的單個值。

● 合并重復列。

● 預連接。例如，查詢多個關聯表的數據。

● 數據驗證。針對驗證的結果采取不同的處理，通過驗證的數據交給裝載步驟，驗證失敗的數據或直接丟棄，或記錄下來做進一步檢查。

1.4.3 數據裝載

ETL的最后步驟是把轉換后的數據裝載進目標數據倉庫。這步操作需要重點考慮兩個問題，一是數據裝載的效率問題，二是一旦裝載過程中途失敗了，如何再次重復執行裝載過程。

即使經過了轉換、過濾和清洗，去掉了部分噪聲數據，但需要裝載的數據量還是很大的。執行一次數據裝載可能需要幾個小時的時間，同時需要占用大量的系統資源。要提高裝載的效率，加快裝載速度，可以從以下幾方面入手。首先保證足夠的系統資源。數據倉庫存儲的都是海量數據，所以要配置高性能的服務器，并且要獨占資源，不要與別的系統共用。在進行數據裝載時，要禁用數據庫約束（唯一性、非空性，檢查約束等）和索引，當裝載過程完全結束后，再啟用這些約束，重建索引，這種方法會很大的提高裝載速度。在數據倉庫環境中，一般不使用數據庫來保證數據的參考完整性，即不使用數據庫的外鍵約束，它應該由ETL工具或程序來維護。

數據裝載過程可能由于多種原因而失敗，比如裝載過程中某些源表和目標表的結構不一致而導致失敗，而這時已經有部分表裝載成功了。在數據量很大的情況下，如何能在重新執行裝載過程時只裝載失敗的部分是一個不小的挑戰。對于這種情況，實現可重復裝載的關鍵是要記錄下失敗點，并在裝載程序中處理相關的邏輯。還有一種情況，就是裝載成功后，數據又發生了改變（比如有些滯后的數據在ETL執行完才進入系統，就會帶來數據的更新或新增），這時需要重新再執行一遍裝載過程，已經正確裝載的數據可以被覆蓋，但相同數據不能重復新增。簡單的實現方式是先刪除再插入，或者用replace into、merge into等類似功能的操作。

裝載到數據倉庫里的數據，經過匯總、聚合等處理后交付給多維立方體或數據可視化、儀表盤等報表工具、BI工具做進一步的數據分析。

1.4.4 開發ETL系統的方法

ETL系統一般都會從多個應用系統整合數據，典型的情況是這些應用系統運行在不同的軟硬件平臺上，由不同的廠商所支持，各個系統的開發團隊也是彼此獨立的，隨之而來的數據多樣性增加了ETL系統的復雜性。

開發一個ETL系統，常用的方式是使用數據庫標準的SQL及其程序化語言，如Oracle的PL/SQL和MySQL的存儲過程、用戶自定義函數（UDF）等。還可以使用Kettle這樣的ETL工具，這些工具都提供多種數據庫連接器和多種文件格式的處理能力，并且對ETL處理進行了優化。使用工具的最大好處是減少編程工作量，提高工作效率。如果遇到特殊需求或特別復雜的情況，可能還是需要使用Shell、Java、Python等編程語言開發自己的應用程序。

ETL過程要面對大量的數據，因此需要較長的處理時間。為了提高ETL的效率，通常這三步操作會并行執行。當數據被抽取時，轉換進程同時處理已經收到的數據。一旦某些數據被轉換過程處理完，裝載進程就會將這些數據導入目標數據倉庫，而不會等到前一步工作執行完才開始。

1.4.5 常見ETL工具

傳統大的軟件廠商一般都提供ETL工具軟件，如Oracle的OWB和ODI、微軟的SQL Server Integration Services、SAP的Data Integrator、IBM的InfoSphere DataStage、Informatica等。這里簡單介紹另外一種開源的ETL工具——Kettle。

Kettle是Pentaho公司的數據整合產品，它可能是現在世界上最流行的開源ETL工具，經常被用于數據倉庫環境。Kettle的使用場景包括：在應用或數據庫間遷移數據、把數據庫中的數據導出成平面文件、向數據庫大批量導入數據、數據轉換和清洗、應用整合等。

Kettle里主要有“轉換”和“作業”兩個功能模塊。轉換是ETL解決方案中最主要的部分，它處理ETL各階段各種對數據的操作。轉換有輸入、輸出、檢驗、映射、加密、腳本等很多分類，每個分類中包括多個步驟，如輸入轉換中就有表輸入、CSV文件輸入、文本文件輸入等很多步驟。轉換里的步驟通過跳（hop）來連接，跳定義了一個單向通道，允許數據從一個步驟流向另外一個步驟。在Kettle里，數據的單位是行，數據流就是數據行從一個步驟到另一個步驟的移動。

轉換是以并行方式執行的，而作業則是以串行方式處理的，驗證數據表是否存在這樣的操作就需要作業來完成。一個作業包括一個或多個作業項，作業項是以某種順序來執行的，作業執行順序由作業項之間的跳（hop）和每個作業項的執行結果決定。和轉換一樣，作業也有很多分類，每個分類中包括多個作業項，如轉換就是一個通用分類里的作業項。作業項也可以是一個作業，此時稱該作業為子作業。

Kettle非常容易使用，其所有的功能都通過用戶界面完成，不需要任何編碼工作。你只需要告訴它做什么，而不用指示它怎么做，這大大提高了ETL過程的開發效率。本書第5章將會詳細說明怎樣使用Kettle操作Hadoop數據。