點擊上方藍色字體，選擇“設為星標”

回復”資源“獲取更多資源

大數據技術與架構

點擊右側關注，大數據開發領域最強公眾號！

大數據真好玩

點擊右側關注，大數據真好玩！

?????????????目錄

一、ETL基礎

1. 數據倉庫架構中的ETL

2. 數據抽取

（1）邏輯抽取

（2）物理抽取

（3）變化數據捕獲

3. 數據轉換

4. 數據裝載

5. 開發ETL系統的方法

二、ETL工具

1. ETL工具的產生

2. ETL工具的功能

（1）連接

（2）平臺獨立

（3）數據規模

（4）設計靈活性

（5）復用性

（6）擴展性

（7）數據轉換

（8）測試和調試

（9）血統和影響分析

（10）日志和審計

三、Kettle簡介

1. Kettle設計原則

2. 轉換

（1）步驟

（2）轉換的跳

（3）并行

（4）數據行

（5）數據類型轉換

3. 作業

（1）作業項

（2）作業的跳

（3）多路徑和回溯

（4）并行執行

（5）作業項結果

4. 數據庫連接

（1）一般選項

（2）特殊選項

5. 連接與事務

6. 元數據與資源庫

7. 工具

（1）Spoon

（2）Kitchen和Pan

（3）Carte

8. 虛擬文件系統

四、小結?????????????

我在2017年寫了一本名為《Hadoop構建數據倉庫實踐》的書。在這本書中，較為詳細地講解了如何利用Hadoop（Cloudera's Distribution Including Apache Hadoop，CDH）生態圈組件構建傳統數據倉庫。例如，使用Sqoop從關系數據庫全量或增量抽取數據到Hadoop系統，使用Hive進行數據轉換和裝載處理等等。作為進階，書中還說明了數據倉庫技術中的漸變維、代理鍵、角色扮演維度、層次維度、退化維度、無事實事實表、遲到事實、累計度量等常見問題在Hadoop上的處理。它們都是通過Hive SQL來實現的，其中有些SQL語句邏輯復雜，可讀性也不是很好。

對于那些不太熟悉SQL語言的大數據開發人員來說，有沒有可能使用一種GUI（Graphical User Interface，圖形用戶界面）工具實現上述所有功能呢？下面我就準備開辟一個專題，通過一系列文章，介紹并演示如何用Kettle完成這一工作。主要內容包括：
（一）ETL與Kettle的基本概念
（二）Kettle及其使用環境的安裝與配置
（三）Kettle對Hadoop的支持
（四）建立ETL示例模型
（五）數據抽取
（六）數據轉換與裝載
（七）定期自動執行ETL作業
（八）維度表技術（增加列、維度子集、角色扮演維度、層次維度、退化維度、雜項維度、維度合并、分段維度）?
（九）事實表技術（周期快照、累計維度、無事實的事實表、遲到的事實、累積度量）
（十）Kettle數據分發與多線程
（十一）Kettle集群與數據分片
（十二）Kettle元數據管理

對于每一種技術，先要理解相關的概念和它之所以出現的原因，這對于我們繼續深入學習其技術細節大有裨益。源于一貫的學習方法和習慣，讓我們還是先從基本概念開始吧。

一、ETL基礎

ETL一詞是Extract、Transform、Load三個英文單詞的首字母縮寫，中文意為抽取、轉換、裝載。

• 抽取——從操作型數據源獲取數據。

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

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

ETL是建立數據倉庫最重要的處理過程，也是最體現工作量的環節，一般會占到整個數據倉庫項目工作量的一半以上。建立一個數據倉庫，就是要把來自于多個異構的源系統的數據整合在一起，放置于一個集中的位置用于數據分析。如果一開始這些源系統數據就是兼容的當然最好，但實際情況往往不是這樣。ETL系統的工作就是要把異構的數據轉換成同構的。如果沒有ETL，很難對異構數據進行程序化的分析。

1. 數據倉庫架構中的ETL

可以把數據倉庫架構理解成構成數據倉庫的組件及其之間的關系，那么就有了下面的數據倉庫架構圖。

圖1-1 數據倉庫架構

圖1-1中顯示的整個數據倉庫環境包括操作型系統和數據倉庫系統兩大部分。操作型系統的數據由各種形式的業務數據組成，這其中可能包含關系數據庫、TXT或CSV文件、HTML或XML文檔，還可能存在外部系統的數據，比如網絡爬蟲抓取來的互聯網數據等。數據可能是結構化、半結構化或非結構化的。這些數據經過ETL過程進入數據倉庫系統。

這里把ETL分成了抽取和轉換裝載兩個部分。抽取過程負責從操作型系統獲取數據，該過程一般不做數據聚合和匯總，但是會按照主題進行集成，物理上是將操作型系統的數據全量或增量復制到數據倉庫系統的RDS中。Hadoop生態圈中的主要數據抽取工具是Sqoop。Sqoop被設計成支持在關系數據庫和Hadoop之間傳輸數據。

轉換裝載過程將數據進行清洗、過濾、匯總、統一格式化等一系列轉換操作，使數據轉為適合查詢的格式，然后裝載進數據倉庫系統的TDS中。傳統數據倉庫的基本模式是用一些過程將操作型系統的數據抽取到文件，然后另一些過程將這些文件轉化成MySQL或Oracle這樣的關系數據庫的記錄。最后，第三部分過程負責把數據導入進數據倉庫。

RDS（Raw Data Stores）是原始數據存儲的意思。將原始數據保存到數據倉庫里是個不錯的想法。ETL過程的bug或系統中的其它錯誤是不可避免的，保留原始數據使得追蹤并修改這些錯誤成為可能。有時數據倉庫的用戶會有查詢細節數據的需求，這些細節數據的粒度與操作型系統的相同。有了RDS，這種需求就很容易實現，用戶可以查詢RDS里的數據而不必影響業務系統的正常運行。這里的RDS實際上是起到了操作型數據存儲（Operational Data Store，ODS）的作用。

TDS（Transformed Data Stores）意為轉換后的數據存儲，是真正的數據倉庫中的數據。大量用戶會在經過轉換的數據集上處理他們的日常查詢。如果前面的工作做得好，這些數據的構建方式將保證最重要的和最頻繁的查詢能夠快速執行。

這里的原始數據存儲和轉換后的數據存儲是邏輯概念，它們可能物理存儲在一起，也可能分開。當原始數據存儲和轉換后的數據存儲物理上分開時，它們不必使用同樣的軟硬件。傳統數據倉庫中，原始數據存儲通常是本地文件系統，數據被組織進相應的目錄中，這些目錄是基于數據從哪里抽取或何時抽取建立（例如以日期作為文件或目錄名稱的一部分）。轉換后的數據存儲一般使用某種關系數據庫。在Hadoop生中，可以這兩類數據邏輯上分開，物理上通過在Hive中建立兩個不同的數據庫來實現，最終所有數據都被分布存儲到HDFS上。

自動化調度組件的作用是自動定期重復執行ETL過程。不同角色的數據倉庫用戶對數據的更新頻率要求也會有所不同，例如財務主管需要每月的營收匯總報告，而銷售人員想看到每天的產品銷售數據。作為通用需求，所有數據倉庫系統都應該能夠建立周期性自動執行的工作流作業。ETL過程自動化是數據倉庫成功的重要衡量標準。傳統數據倉庫一般利用操作系統自帶的調度功能（如Linux的cron或Windows的計劃任務）實現作業自動執行。Hadoop生態圈中有一個叫做Oozie的工具，它是一個Hadoop的工作流調度系統，可以使用它將ETL過程封裝進工作流自動執行。

數據目錄有時也被稱為元數據存儲，它可以提供一份數據倉庫中數據的清單。用戶通過它應該可以快速解決這些問題：什么類型的數據被存儲在哪里，數據集的構建有何區別，數據最后的訪問或更新時間等。此外還可以通過數據目錄感知數據是如何被操作和轉換的。一個好的數據目錄是讓用戶體驗到系統易用性的關鍵。Hadoop生態圈中主要的數據目錄工具是HCatalog，它是Hadoop上的一個表和存儲管理層。

查詢引擎組件負責實際執行用戶查詢。傳統數據倉庫中，它可能是存儲轉換后數據的Oracle、MySQL等關系數據庫系統內置的查詢引擎，還可能是以固定時間間隔向其導入數據的OLAP立方體，如Essbase cube。Hadoop生態圈中的主要SQL查詢引擎有基于MapReduce的Hive、基于RDD的SparkSQL和基于MPP的Impala等。

用戶界面指的是最終用戶所使用的接口程序?？赡苁且粋€GUI軟件，如BI套件的中的客戶端軟件，也可能就只是一個瀏覽器。Hadoop生態圈中比較知名的數據可視化工具是Hue和Zeppelin。

本專題的（三）Kettle對Hadoop的支持 將詳細介紹如何在Kettle中使用Hadoop相關組件。

2. 數據抽取

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

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

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

（1）邏輯抽取

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

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

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

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

（2）物理抽取

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

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

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

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

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

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

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

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

（3）變化數據捕獲

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

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

常用的變化數據捕獲方法有時間戳、快照、觸發器和日志四種。相信熟悉數據庫的用戶對這些方法都不會陌生。時間戳方法需要源系統有相應的數據列表示最后的數據變化?？煺辗椒梢允褂脭祿煜到y自帶的機制實現，如Oracle的物化視圖技術，也可以自己實現相關邏輯，但會比較復雜。觸發器是關系數據庫系統具有的特性，源表上建立的觸發器會在對該表執行insert、update、delete等語句時被觸發，觸發器中的邏輯用于捕獲數據的變化。日志可以使用應用日志或系統日志，這種方式對源系統不具有侵入性，但需要額外的日志解析工作。關于這四種方案的特點，將會在本專題（五）數據抽取 中具體說明。

3. 數據轉換

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

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

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

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

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

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

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

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

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

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

• 行列轉置。

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

• 合并重復列。

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

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

4. 數據裝載

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

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

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

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

5. 開發ETL系統的方法

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

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

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

二、ETL工具

1. ETL工具的產生

ETL工具出現之前，人們使用手工編寫程序的方式來完成不同數據源的數據整合工作，常見的程序語言如COBOL、Perl或PL/SQL等。盡管這種數據整合方案由來已久，但直至今天仍有ETL工作使用這種手工編程/腳本的方式來完成。在還沒有太多開源ETL工具的年代，相對價格昂貴的ETL工具而言，手工編程還有一定意義。手工編程的主要缺點在于：

• 容易出錯

• 開發周期長

• 不易于維護

• 缺少元數據

• 缺乏一致性的日志和錯誤處理

最初的ETL工具為克服這些問題而被開發，方法是依據設計好的ETL工作流來自動生成所需代碼。隨之出現了Prism、Carlton、ETI等產品。代碼生成最大的弊端是大多數代碼生成僅能用于有限的特定數據庫。不久之后，就在代碼生成技術廣泛應用之時，新的基于引擎架構的ETL工具出現了。新一代ETL工具可以執行幾乎所有的數據處理流程，還可以將數據庫連接和轉換規則作為元數據存儲起來。因為引擎有標準的工作方式，所有的轉換在邏輯上是獨立的，無論是相對于數據源還是數據目標?；谝娴腅TL工具通常比代碼生成的方式更具通用性。Kettle就是一個基于引擎ETL工具的典型例子。在這個領域，還有一些其它熟悉的名字，比如Informatica Powercenter以及SQL Server Information Services等。

無論是代碼生成器還是基于引擎的工具，都能幫助我們發現數據源的底層架構，以及這些架構之間的關系。但它們都需要開發目標數據模型，或者先行開發，或者在設計數據轉換步驟時開發。設計階段過后，還必須進行目標數據模型與源數據模型的映射，而整個過程是相當耗時的。所以后來還隨之出現了模型驅動的數據倉庫工具。模型驅動架構（Model-Driven Architecture，MDA）工具試圖自動化實現數據倉庫的設計過程，讀取源數據模型，生成目標數據模型與需求數據之間的映射，以便向目標表填充數據，但市場上的相關工具并不多。當然MDA工具也不可能解決所有的數據集成問題，并且仍然需要具備一定技能的數據倉庫開發人員才能發揮其作用。

2. ETL工具的功能

下面描述一般ETL工具必備的通用功能，以及Kettle如何提供這些功能。

（1）連接

任何ETL工具都應該有能力連接到類型廣泛的數據源和數據格式。對于最常用的關系型數據庫系統，還要提供本地的連接方式（如Oracle的OCI），ETL應該能夠提供下面最基本的功能：

• 連接到普通關系型數據庫并獲取數據，如常見的Oracle、MS SQL Server、IBM DB/2、Ingres、MySQL和PostgreSQL等。

• 從有分隔符或固定格式的ASCII文件中獲取數據。

• 從XML文件中獲取數據。

• 從流行的辦公軟件中獲取數據，如Access數據庫和Excel電子表格。

• 使用FTP、SFTP、SSH方式獲取數據（最好不用腳本）。

除了上述這些功能，還要能從Web Services或RSS中獲取數據。如果還需要一些ERP系統里的數據，如Oracle E-Business Suite、SAP/R3、PeopleSoft或JD/Edwards，ETL工具也應該提供到這些系統的連接。

除了將通用的關系數據庫和文本格式的文件作為數據源，Kettle也提供Salesforce.com和SAP/R3的輸入步驟，但不是套件內，需要額外安裝。對于其它ERP和財務系統的數據抽取還需要其它解決方法。

（2）平臺獨立

一個ETL工具應該能在任何平臺上甚至是不同平臺的組合上運行。例如，一個32位的操作系統可能在開發的初始階段運行很好，但是當數據量越來越大時，就需要一個更強大的64位操作系統。再比如，開發一般是在Windows或Mac機上進行的，而生產環境一般是Linux系統或集群，ETL解決方案應該可以無縫地在這些系統間切換。Kettle是用Java開發的，可以運行在任何安裝了Java虛擬機的計算機上。

（3）數據規模

ETL解決方案應該能處理逐年增長的數據。一般ETL能通過下面三種方式處理大數據。

• 并發：ETL過程能夠同時處理多個數據流，以便利用現代多核的硬件架構。

• 分區：ETL能夠使用特定的分區模式，將數據分發到并發的數據流中。

• 集群：ETL過程能夠分配在多臺機器上聯合完成。

Kettle轉換里的每個步驟都是以并發的方式來執行，并且可以多線程并行，這樣加快了處理速度。Kettle在運行轉換時，根據用戶的設置，可以將數據以分發和復制兩種方式發送到多個數據流中。分發是以輪流的方式將每行數據只發給一個數據流，復制是將一行數據發給所有數據流。本專題的（十）Kettle數據分發與多線程 將論述Kettle并行機制。

為了更精確控制數據，Kettle還使用了分區模式，通過分區可以將同一特征的數據發送到同一個數據流。這里的分區只是概念上類似于數據庫的分區，Kettle并沒有針對數據庫分區有什么功能，一般認為數據庫應該比ETL更適合完成數據分區。集群是有效的規模擴展方式，可以使Kettle將工作負載按需分配到多臺機器上。本專題的（十一）Kettle集群與數據分片 部分深入講解這兩種規模擴展方式。

（4）設計靈活性

一個ETL工具應該留給開發人員足夠的自由度來使用，而不能通過一種固定的方式限制用戶的創造力和設計的需求。ETL工具可以分為基于過程的和基于映射的?；谟成涞墓ぞ咧辉谠春湍康臄祿g提供一組固定的步驟，嚴重限制了設計工作的自由度?；谟成涞墓ぞ咭话阋子谑褂?#xff0c;可快速上手，但是對于更復雜的任務，基于過程的工具才是最好的選擇。使用像Kettle這樣基于過程的工具，根據實際的數據和業務需求，可以創建自定義的步驟和轉換。

（5）復用性

設計完的ETL轉換應該可以被復用，這也是ETL工具的一個不可或缺的特征。復制和粘貼已存在的轉換步驟是最常見的一種復用，但這還不是真正意義上的復用。復用一詞是指定義了一個轉換或步驟，從其它地方可以調用這些轉換或步驟。Kettle里有一個“映射(子轉換)”步驟，可以完成轉換的復用，該步驟可以將一個轉換作為其它轉換的子轉換。另外轉換還可以在多個作業里多次使用，同樣作業也可以作為其它作業的子作業。

（6）擴展性

ETL工具必須要有擴展功能的方法。幾乎所有的ETL工具都提供了腳本，以編程的方式來解決工具本身不能解決的問題。另外有些ETL工具可以通過API或其它方式來為工具增加組件。第三種方法是使用腳本語言寫函數，函數可以被其它轉換或腳本調用。

Kettle提供了上述所有功能?！癑avaScript代碼”步驟可以用來開發Java腳本，把這個腳本保存為一個轉換，再通過映射(子轉換)步驟，又可以變為一個標準的可以復用的函數。實際上并不限于腳本，每個轉換都可以通過這種映射(子轉換)方式來復用，如同創建了一個組件。Kettle在設計上就是可擴展的，它提供了一個插件平臺。這種插件架構允許第三方為Kettle平臺開發插件。Kettle里的所有組件都是插件，即使是默認提供的組件。

（7）數據轉換

ETL項目很大一部分工作都是在做數據轉換。在輸入和輸出之間，數據要經過檢驗、連接、分割、合并、轉置、排序、歸并、克隆、排重、刪除、替換或者其它操作。常用的ETL工具（包括Kettle）都提供了下面一些最基本的轉換功能：

• 緩慢變更維度（Slowly Changing Dimension，SCD）

• 查詢值

• 行列轉置

• 條件分割

• 排序、合并、連接

• 聚集

（8）測試和調試

測試和調試的重要性不言而喻。ETL的設計過程和直接用開發語言寫程序很相似，也就是說在寫程序時用到的一些步驟或過程同樣也適用于ETL設計。測試也是ETL設計的一部分。為了完成測試工作，我們通常需要假設下面幾種失敗場景，并要給出相應的處理方法：

• 如果ETL過程沒有按時完成數據轉換的任務怎么辦？

• 如果轉換過程異常終止怎么辦？

• 目標是非空列的數據抽取到的數據為空怎么辦？

• 轉換后的行數和抽取到的數據行數不一致怎么辦（數據丟失）？

• 轉換后計算的數值和另一個系統的數值不一致怎么辦（邏輯錯誤）？

測試可分為黑盒測試（也叫功能測試）和白盒測試（也叫結構測試）。對于前者，ETL轉換就被認為是一個黑盒子，測試者并不了解黑盒子內的功能，只知道輸入和期望的輸出。白盒測試要求測試者知道轉換內部的工作機制并依此設計測試用例來檢查特定的轉換是否有特定的結果。

調試實際是白盒測試中的一部分，通過調試可以讓開發者或測試者一步一步地運行一個轉換，并找出問題的所在。Kettle為作業和轉換都提供了單步逐行調試功能特性。

（9）血統和影響分析

任何ETL工具都應該有一個重要的功能：讀取轉換的元數據，抽取由不同轉換構成的數據流的信息。血統分析和影響分析是基于元數據的兩個相關的特性。血統是一種回溯性的機制，它可以查看到數據的來源。例如，“價格”和“數量”字段作為輸入字段，在轉換中根據這兩個字段計算出“收入”字段。即使在后面的處理流程里過濾了“價格”個“數量”字段，血統分析也能分析出“收入”字段是基于“價格”和“數量”字段的。

影響分析是基于元數據的另種分析方法，該方法可以分析源數據字段對隨后的轉換以及目標表的影響。在本專題的（十二）Kettle元數據管理 中將詳細講述這一主題。

（10）日志和審計

數據倉庫的目的就是要提供一個準確的信息源，因此數據倉庫里的數據應該是可靠和可信的。為了保證這種可靠性，同時保證可以記錄下所有的數據轉換操作，ETL工具應該提供日志和審計功能。日志可以記錄下在轉換過程中執行了哪些步驟，包括每個步驟開始和結束時間時間戳。審計可以追蹤到對數據做的所有操作，包括讀行數、轉換行數、寫行數。在這方面Kettle在ETL工具市場處于領先地位。

傳統大的軟件廠商一般都提供ETL工具軟件，如Oracle的OWB和ODI、微軟的SQL Server Integration Services、SAP的Data Integrator、IBM的InfoSphere DataStage、Informatica等。下面介紹本專題的主角，開源的ETL工具中的佼佼者——Kettle。

三、Kettle簡介

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

Kettle是使用Java語言開發的。它最初的作者Matt Casters原是一名C語言程序員，在著手開發Kettle時還是一名Java小白，但是他僅用了一年時間就開發出了Kettle的第一個版本。雖然有很多不足，但這版畢竟是可用的。使用自己并不熟悉的語言，僅憑一己之力在很短的時間里就開發出了復雜的ETL系統工具，作者的開發能力和實踐精神令人十分佩服。后來Pentaho公司獲得了Kettle源代碼的版權，Kettle也隨之更名為Pentaho Data Integration，簡稱PDI。

1. Kettle設計原則

Kettle工具在設計之初就考慮到了一些設計原則，這些原則也借鑒了以前使用過的其它一些ETL工具積累下的經驗和教訓。

• 易于開發

Kettle認為，作為ETL開發者，應該把時間用在創建應用解決方案上。任何用于軟件安裝、配置的時間都是一種浪費。例如，為了創建數據庫連接，很多和Kettle類似的Java工具都要求用戶手工輸入數據驅動類名和JDBC URL連接串，這明顯把用戶的注意力轉移到了技術方面而非業務方面。Kettle盡量避免這類問題的發生。

• 避免自定義開發

一般ETL工具提供了標準化的構建組件來實現ETL開發人員不斷重復的需求。當然可以通過手工編寫Java代碼或Java腳本來實現一些功能，但增加的每一行代碼都給項目增加了復雜度和維護成本。所以Kettle盡量避免手工開發，而是提供組件及其各種組合來完成任務。

• 所有功能都通過用戶界面完成

Kettle直接把所有功能通過界面的方式提供給用戶，節約開發人員或用戶的時間。當然專家級的ETL用戶還是要去學習隱藏在界面后的一些特性。在Kettle里，ETL元數據可以通過XML格式表現，或通過資源庫，或通過使用Java API。無論ETL元數據以哪種形式提供，都可以百分之百通過圖形用戶界面來編輯。

• 沒有命名限制

ETL轉換里有各種各樣的名稱，如數據庫連接、轉換、步驟、數據字段、作業等都要有一個名稱。如果還要在命名時考慮一些如長度或字符限制，就會給工作帶來一定麻煩。Kettle具備足夠的智能化來處理ETL開發人員設置的各種名稱。最終ETL解決方案應該可以盡可能地自描述，這樣可以部分減少文檔的需求，進而減少項目維護成本。

• 透明

Kettle不需要用戶了解轉換中某一部分工作是如何完成的，但允許用戶看到ETL過程中各部分的運行狀態。這樣可以加快開發速度、降低維護成本。

• 靈活的數據通道

Kettle從設計之初就在數據的發送、接收方式上盡可能靈活。Kettle可以在文本文件、關系數據庫等不同目標之間復制和分發數據，從不同數據源合并數據也是內核引擎的一部分，同樣很簡單。

• 只映射需要的字段

在一些ETL工具里經?？梢钥吹綌蛋傩械妮斎牒洼敵鲇成?#xff0c;對于維護人員來說這是一個噩夢。在ETL開發過程中，字段要經常變動，這樣的大量映射也會增加維護成本。Kettle的一個重要核心原則就是，在ETL流程中所有未指定的字段都自動被傳遞到下一個組件。也就是說輸入中的字段會自動出現在輸出中，除非中間過程特別設置了終止某個字段的傳遞。

• 可視化編程

Kettle可以被歸類為可視化編程語言（Visual Programming Languages，VPL），因為Kettle可以使用圖形化的方式定義復雜的ETL程序和工作流。Kettle里的圖就是轉換和作業?？梢暬幊桃恢笔荎ettle里的核心概念，它可以讓用戶快速構建復雜的ETL作業，并降低維護工作量。Kettle中的設計開發工作幾乎都可以通過簡單的拖拽來完成。它通過隱藏很多技術細節，使IT領域更接近于業務領域。

2. 轉換

轉換（transformation）是Kettle ETL解決方案中最主要的部分，它處理抽取、轉換、裝載各階段各種對數據行的操作。轉換包括一個或多個步驟（step），如讀取文件、過濾輸出行、數據清洗或將數據裝載到數據庫等等。

轉換里的步驟通過跳（hop）來連接，跳定義了一個單向通道，允許數據從一個步驟向另一個步驟步驟流動。在Kettle里，數據的單位是行，數據流就是數據行從一個步驟到另一個步驟的移動。

圖1-2所示的轉換從數據庫讀取數據并寫入文本文件。除了步驟和跳，轉換還包括了注釋（note）。注釋是一個文本框，可以放在轉換流程圖的任何位置。注釋的主要目的是使轉換文檔化。

（1）步驟

步驟是轉換的基本組成部分，它以圖標的方式圖形化地展現，圖1-2中顯示了兩個步驟，“表輸入”和“文本文件輸出”。一個步驟有幾個關鍵特性：

• 步驟需要有一個名字，這個名字在轉換范圍內唯一。

• 每個步驟都會讀寫數據行。唯一例外是“生成記錄”步驟，該步驟只寫數據。在本專題的（四）建立ETL示例模型 中將看到如何使用“生成記錄”步驟生成日期維度數據。

• 步驟將數據寫到與之相連的一個或多個輸出跳（outgoing hops），再傳送到跳的另一端的步驟。對另一端的步驟來說，這個跳就是一個輸入跳（incoming hops），步驟通過輸入跳接收數據。

• 大多數步驟可以有多個輸出跳。一個步驟的數據發送可以被設置為輪流發送或復制發送。輪流發送是將數據行依次發給每個輸出跳，復制發送是將全部數據行發送給所有輸出跳。

• 在運行轉換時，一個線程運行一個步驟或步驟的一份拷貝，如圖1-2中“表輸入”步驟左上角的X4，表示4個線程執行該步驟，數據行將復制4份。所有步驟的線程幾乎同時運行，數據行連續地流過步驟之間的跳。

（2）轉換的跳

跳（hop）就是步驟間帶箭頭的連線，跳定義了步驟之間的數據通路。跳實際上是兩個步驟之間的被稱為行級（row set）的數據行緩存。行集的大小可以在轉換的設置里定義，Kettle 8.3 默認為10000行。當行集滿了，向行集寫數據的步驟將停止寫入，直到行集里又有了空間。當行集空了，從行集讀取數據的步驟停止讀取，直到行集里又有可讀的數據行。注意，跳在轉換里不能循環，因為在轉換里每個步驟都依賴于前一個步驟獲取字段。

（3）并行

跳的這種基于行集緩存的規則允許每個步驟都由一個獨立的線程運行，這樣并發程度最高。這一規則也允許以最小消耗內存的數據流的方式來處理。在數據分析中，我們經常要處理大量數據，所以這種并發低耗內存的方式也是ETL工具的核心需求。

對于Kettle轉換，不可能定義一個步驟在另一個步驟之后執行，因為所有步驟都以并發方式執行：當轉換啟動后，所有步驟都同時開始，從它們的輸入跳中讀取數據，并把處理過的數據寫到輸出跳，直到輸入跳不再有數據，就中止步驟的運行。當所有的步驟都中止了，整個轉換就中止了。從功能的角度看，轉換具有明確的起點和終點。例如，圖1-2里顯示的轉換起點是“表輸入”步驟，因為這個步驟生成數據行。終點是“文本文件輸出”步驟，因為這個步驟將數據寫到文件，而且后面不再有其它節點。

前面關于步驟并發執行與起點、終點的描述看似自相矛盾，實際上只是看問題的角度不同。一方面，可以想象數據沿著轉換里的步驟移動，形成一條行頭到尾的數據通路。另一方面，轉換里的步驟幾乎是同時啟動的，所以不可能判斷出哪個步驟是第一個啟動的步驟。如果想要一個任務沿著指定的順序執行，就要使用后面介紹的“作業”了。

（4）數據行

數據以數據行的形式沿著步驟移動。一個數據行是零到多個字段的集合，字段包括這里所列的幾種數據類型。

• String：字符類型數據。

• Number：雙精度浮點數。

• Integer：帶符號64位長整型。

• BigNumber：任意精度數值。

• Date：毫秒精度的日期時間值。

• Boolean：取值為true或false的布爾值。

• Binary：二進制類型，可以包括圖形、音視頻或其它類型的二進制數據。

每個步驟在輸出數據行時都有對字段的描述，這種描述就是數據行的元數據，通常包括下面一些信息：

• 名稱：行里的字段名應該是唯一的。

• 數據類型：字段的數據類型。

• 長度：字符串的長度或BigNumber類型的長度。

• 精度：BigNumber數據類型的十進制精度。

• 掩碼：數據顯示的格式（轉換掩碼）。如果要把數值型（Number、Integer、BigNumber）或日期類型轉換成字符串類型就需要用到掩碼，例如在圖形界面中預覽數值型、日期型數據，或者把這些數據保存成文本或XML格式時。

• 小數點：十進制數據的小數點格式。不同文化背景下小數點符號是不同的，一般是點（.）或逗號（,）。

• 分組符號（數字里的分割符號）：數值類型數據的分組符號，不同文化背景下數字里的分組符號也是不同的，一般是逗號（,）或點（.）或單引號（'）。

• 初始步驟：Kettle在元數據里還記錄了字段是由哪個步驟創建的，可以讓用戶快速定位字段是由轉換里的哪個步驟最后一次修改或創建。

當設計轉換時有幾個數據類型的規則需要注意：

• 行集里的所有行都應該有同樣的數據結構。當從多個步驟向一個步驟里寫數據時，多個步驟輸出的數據行應該有相同的結構，即字段名、數據類型、字段順序都相同。

• 字段元數據不會在轉換中發生變化。字符串不會自動截去長度以適應指定的長度，浮點數也不會自動取整以適應指定的精度。這些功能必須通過一些指定的步驟來完成。

• 默認情況下，空字符串被認為與NULL相等，但可以通過kettle.properties文件中的kettle_empty_string_differs_from_null參數來設置。

（5）數據類型轉換

既可以顯式地轉換數據類型，如在“字段選擇”步驟中直接選擇要轉換的數據類型，也可以隱式地轉換數據類型，如將數值數據寫入數據庫的varchar類型字段。這兩種形式的數據轉換實際上是完全一樣的，都是使用了數據和對數據的描述。

• Date和String的轉換

Kettle內部的Date類型里包含了足夠的信息，可以用這些信息來表現任何毫秒精度的日期、時間值。如果要在String和Date類型之間轉換，唯一要指定的就是日期格式掩碼。表1-1顯示的是幾個日期轉換例子。

表1-1 日期轉換例子

• Numeric和String的轉換

Numeric數據（包括Number、Integer、BigNumber）和String類型之間的轉換用到的幾個字段元數據是：轉換掩碼、小數點符號、分組符號和貨幣符號。這些轉換掩碼只是決定了一個文本格式的字符串如何轉換為一個數值，而與數值本身的實際精度和舍入無關。表1-2顯示了幾個常用的例子。

表1-2 數值轉換掩碼的例子

• 其它轉換

表1-3提供了Boolean和String之間、整型與日期類型之間數據類型轉換的列表。

表1-3 其它數據類型轉換

3. 作業

大多數ETL項目都需要完成各種各樣的維護任務。例如，當運行中發生錯誤，要做哪些操作；如何傳送文件；驗證數據庫表是否存在等等。這些操作要按照一定順序完成，就需要一個可以串行執行的作業來處理。

一個作業包括一個或多個作業項，這些作業項以某種順序來執行。作業執行順序由作業項之間的跳（job hop）和每個作業項的執行結果來決定。圖1-3顯示了一個典型的裝載數據倉庫的作業。

（1）作業項

作業項是作業的基本構成部分。如同轉換的步驟，作業項也可以使用圖標的方式圖形化展示。但是作業項有一些地方不同于步驟：

• 步驟的名字在轉換中是唯一的，但作業項可以有影子拷貝（shadow copies），如圖1-3中的“錯誤郵件”。這樣可以把一個作業項放在多個不同的位置。這些影子拷貝里的信息都是相同的，編輯了一份拷貝，其它拷貝也會隨之修改。

• 在作業項之間可以傳遞一個結果對象（result object）。這個結果對象里包含了數據行，它們不是以流的方式傳遞的，而是等一個作業項執行完了，再傳遞給下一個作業項。

• 默認情況下，所有的作業項都以串行方式執行，只是在特殊的情況下以并行方式執行。

因為作業順序執行作業項，所以必須定義一個起點，如圖中的“start”作業項，就定義了一個起點。一個作業只能定義一個開始作業項。

（2）作業的跳

如同轉換中各步驟之間的跳，作業的跳是作業項之間的連接線，它定義了作業的執行路徑。作業里每個作業項的不同運行結果決定了作業的不同執行路徑。對作業項的運行結果的判斷如下：

• 無條件執行：不論上一個作業項執行成功還是失敗，下一個作業項都會執行。這是一種黑色的連接線，上面有一個鎖的圖標，如圖1-3中 “start”到“傳送數據”作業項之間的連線。

• 當運行結果為真時執行：當上一個作業項的執行結果為真時，執行下一個作業項，通常在需要無錯誤執行的情況下使用。這是一種綠色連接線，上面有一個對鉤號圖標，如圖1-3中橫向的三個連線。

• 當運行結果為假時執行：當上一個作業項的執行結果為假或沒有成功時，執行下一個作業項。這是一種紅色的連接線，上面有一個紅色的叉子圖標。

? ? ? ? 在作業跳的右鍵菜單上可以設置以上這三種判斷方式。

（3）多路徑和回溯

Kettle使用一種回溯算法來執行作業里的所有作業項，而且作業項的運行結果（真或假）也決定執行路徑?；厮菟惴ㄊ且环N深度遍歷：假設執行到了圖里的一條路徑的某個節點時，要依次執行這個節點的所有子路徑，直到沒有再可以執行的子路徑，就返回該節點的上一節點，再反復這個過程。

圖1-4 使用回溯算法串行執行多個路徑

例如，圖1-4里的A、B、C三個作業項的執行順序為：

首先“Start”作業項搜索所有下一個節點作業項，找到了“A”和“C”。

執行“A”

搜索“A”后面的作業項，發現了“B”。

執行“B”。

搜索“B”后面的作業項，沒有找到任何作業項。

回到“A”，也沒有發現其它作業項。

回到“Start”，發現另一個要執行的作業項“C”。

執行“C”。

搜索“C”后面的作業項，沒有找到任何作業項。

回到“Start”，沒有找到任何作業項。

作業結束。

因為沒有定義執行順序，所以這個例子的執行順序除了ABC，還可以是CAB。這種回溯算法有兩個重要特征：

• 因為作業可以是嵌套的，除了作業項有運行結果，作業也需要一個運行結果，因為一個作業可以是另一個作業的作業項。一個作業的運行結果，來自于它最后一個執行的作業項。這個例子里作業的執行順序可能是ABC，也可能是CAB，所以不能保證作業項C的結果就是作業的結果。

• 作業里允許循環。當在作業里創建了一個循環，一個作業項就會被執行多次，作業項的多次運行結果會保存在內存里，便于以后使用。

（4）并行執行

一個作業項能以并發的方式執行它后面的作業項，如圖1-5中的作業所示。在這個例子里，作業項A和C幾乎同時啟動。

圖1-5 并行執行的作業項

需要注意的是，如果A和C是順序執行的多個作業項，那么這兩組作業項也是并行執行的，如圖1-6所示。

圖1-6 兩組同時執行的作業項

在這個例子中，作業項[A、B、寫日志]和[C、D、清空表]是在兩個線程里并行執行的。通常設計者也是希望以這樣的方式執行。但有時候，設計者希望一部分作業項并行執行，然后再串行執行其它作業項。這就需要把并行的作業項放到一個新的作業里，然后作為另一個作業的作業項，如圖1-7所示。

圖1-7 并行加載作業作為另一個作業的作業項

（5）作業項結果

作業執行結果不僅決定了作業的執行路徑，而且還向下個作業項傳遞了一個結果對象。結果對象包括了這里所示的一些信息。

• 一組數據行：在轉換里使用“復制記錄到結果”步驟可以設置這組數據行。與之對應，使用“從結果獲取記錄”步驟可以獲取這組數據行。在一些作業項里，如“Shell”、“轉換”、“作業”的設置里有一個選項可以循環執行這組數據行，這樣可以通過參數化來控制轉換和作業。

• 一組文件名：在作業項的執行過程中可以獲得一些文件名。這組文件名是所有與作業項發生過交互的文件的名稱。例如，一個轉換讀取和處理了10個XML文件，這些文件名就會保留在結果對象里。使用轉換里的“從結果獲取文件”步驟可以獲取到這些文件名，除了文件名還能獲取到文件類型?！耙话恪鳖愋褪侵杆械妮斎胼敵鑫募?#xff0c;“日志”類型是指Kettle日志文件。

• 讀、寫、輸入、輸出、更新、刪除、拒絕的行數和轉換里的錯誤數。

• 腳本作業項的退出狀態：根據腳本執行后的狀態碼，判斷腳本的執行狀態，再執行不同的作業流程。

4. 數據庫連接

Kettle里的轉換和作業使用數據庫連接來連接到關系型數據庫。Kettle數據庫連接實際是數據庫連接的描述，也就是建立實際連接需要的參數。實際連接只是在運行時才建立，定義一個Kettle的數據庫連接并不真正打開一個數據庫的連接。各種數據庫的行為彼此不同，圖1-8所示的數據庫連接窗口里有很多種數據庫。

圖1-8?數據庫連接窗口

（1）一般選項

在數據庫連接窗口中主要設置三個選項：

• 連接名稱：設定一個在作業或轉換范圍內唯一的名稱。

• 連接類型：從數據庫列表中選擇要連接的數據庫類型。根據選中數據庫的類型不同，要設置的訪問方式和連接參數也不同，某些Kettle步驟或作業項生成SQL語句時使用的方言也不同。

• 訪問方式：在列表里可以選擇可用的訪問方式，一般都使用JDBC連接，不過也可以使用ODBC數據源、JNDI數據源、Oracle的OCI連接（使用Oracle命名服務）等。

根據選擇的數據庫不同，右側面板的連接參數設置也不同。例如圖1-8中，只有Oracle數據庫可以設置表空間選項。一般常用的連接參數為：

• 主機名：數據庫服務器的主機名或IP地址。

• 數據庫名：要訪問的數據庫名。

• 端口號：默認是選中的數據庫服務器的默認端口號。

• 用戶名和密碼：連接數據庫服務器的用戶名和密碼。

（2）特殊選項

對于大多數用戶來說，使用數據庫連接窗口的“一般”標簽就足夠了。但偶爾也可能需要設置對話框里的“高級”標簽的內容，如圖1-9所示。

圖1-9?數據庫連接窗口中的“高級”標簽

• 支持Boolean數據類型：對Boolean（bit）數據類型，大多數數據庫的處理方式都不相同，即使同一個數據庫的不同版本也可能不同。許多數據庫根本不支持Boolean數據類型，如Oracle和MySQL，所以默認情況下，Kettle使用一個char(1)字段的不同值（如Y或N）來代替Boolean字段。如果選中了這個選項，Kettle就會為支持Boolean類型的數據庫生成正確的SQL方言。

• 雙引號分割標識符：強迫SQL語句里的所有標識符（如列名、表名）加雙引號，一般用于區分大小寫的數據庫，或者Kettle里定義的關鍵字列表和實際數據庫不一致的情況。

• 強制轉為小寫：將所有表名和列名轉為小寫。

• 強制轉為大寫：將所有表名和列名轉為大寫。

• 默認模式名：當不明確指定模式名時默認的模式名。

• 連接后要執行的SQL語句：一般用于建立連接后，修改某些數據庫參數，如session級的變量或調試信息等。

除了這些高級選項，在連接對話框的 “選項”標簽下，還可以設置數據庫特定的參數，如一些連接參數。為了便于使用，對于某些數據庫（如MySQL），Kettle提供了一些默認的連接參數和值。有幾種數據庫類型，Kettle還提供了連接參數的幫助文檔，通過單擊“選項”標簽中的“幫助”按鈕可以打開對應數據庫的幫助頁面。

還可以選擇Apache的通用數據庫連接池選項。如果運行了很多小的轉換或作業，這些轉換或作業里又定義了生命期短的數據庫連接，連接池選項就顯得有意義了。連接池選項不會限制并發數據庫連接的數量。

當一個大數據庫不能再滿足需求時，就會考慮用很多小的數據庫來處理數據。通常可以使用數據分區技術（注意不是數據庫系統本身自帶分區特性）來分散數據裝載。這種方法可以將一個大數據集分為幾個數據分區，每個分區都保存在獨立的數據庫實例中。這種方法的優點顯而易見，能夠大幅減少每個表或每個數據庫實例的行數?？梢栽跀祿爝B接對話框的“集群”標簽下設置分區，詳見本專題的（十一）Kettle集群與數據分片。

關系數據庫在數據的連接、合并、排序等方面有著突出的優勢。和基于流的數據處理引擎，如Kettle相比，它的一大優點是，數據庫使用的數據都存儲在磁盤中。當關系型數據庫進行連接或排序操作時，直接使用這些數據即可，而不用把這些數據裝載到內存里，這就體現出明顯的性能方面的優勢。但缺點也是很明顯的，把數據裝載到關系數據庫里也可能會產生性能的瓶頸。

對ETL開發者而言，要盡可能利用數據庫自身的性能優勢，來完成連接或排序這樣的操作。如果不能在數據庫里進行連接這樣的操作，如數據的來源不同，也應該在數據庫里排序，以便在ETL里做連接操作。

5. 連接與事務

數據庫連接只在執行作業或轉換時使用。在作業里，每一個作業項都打開和關閉一個獨立的數據庫連接。轉換也是如此，但是因為轉換里的步驟是并行的，每個步驟都打開一個獨立的數據庫連接并開始一個事務。盡管這樣在很多情況下會提高性能，但當不同步驟更新同一個表時，也會帶來鎖和參照完整性問題。

為了解決打開多個數據庫連接而產生的問題，Kettle可以在一個事務中完成轉換。在轉換設置對話框的 “雜項”標簽中，設置“使用唯一連接”，可以完成此功能。當選中了這個選項，所有步驟里的數據庫連接都使用同一個數據庫連接。只有所有步驟都正確，轉換正確執行，才提交事務，否則回滾事務。

6. 元數據與資源庫

轉換和作業是Kettle的核心組成部分。在介紹Kettle設計原則時曾經討論過，它們可以用XML格式來表示，可以保存在資料庫里，也可以用Java API的形式來表示。它們的這些表示方式，都依賴于這里所列的元數據。

• 名字：轉換或作業的名字。不論是在一個ETL工程內還是在多個ETL工程內，都應該盡可能使用唯一的名字，這樣在遠程執行時或多個ETL工程共用一個資源庫時都會有幫助。

• 文件名：轉換或作業所在的文件名或URL。只有當轉換或作業是以XML文件的形式存儲時，才需要設置這個屬性。當從資源庫加載時，不必設置這個屬性。

• 目錄：這個目錄是指在Kettle資源庫里的目錄。當轉換或作業保存在資源庫里時設置，保存為XML文件時不用設置。

• 描述：這是一個可選屬性，用來設置作業或轉換的簡短的描述信息。如果使用了資源庫，這個描述屬性也會出現在資源庫瀏覽窗口的文件列表中。

• 擴展描述：也是一個可選屬性，用來設置作業或轉換的詳細描述信息。

當ETL項目規模比較大，有很多ETL開發人員在一起工作，開發人員之間的合作就顯得很重要。Kettle以插件的方式靈活定義不同種類的資源庫，但不論是哪種資源庫，它們的基本要素是相同的：它們都使用相同的用戶界面、存儲相同的元數據。目前有三種常見資源庫：數據庫資源庫、Pentaho資源庫和文件資源庫。

• 數據庫資源庫：把所有的ETL信息保存在關系數據庫中。這種資源庫比較容易創建，詳見本專題的（十二）Kettle元數據管理。

• 文件資源庫：在一個文件目錄下定義一個資源庫。因為Kettle使用的是Apache VFS虛擬文件系統，所以這里的文件目錄是一個廣泛的概念，包括zip文件、Web服務、FTP服務等。

• Pentaho資源庫：包含在Kettle企業版中的一個插件。這種資源庫實際是一個內容管理系統（Content Manage System，CMS），它具備一個理想資源庫的所有特性，包括版本控制和依賴完整性檢查。

? ? ? ? 無論哪種資源庫都應該具有下面的特性：

• 中央存儲：在一個中心位置存儲所有的轉換和作業。ETL用戶可以訪問到工程的最新視圖。

• 文件加鎖：防止多個用戶同時修改同一文件。

• 修訂管理：一個理想的資源庫可以存儲一個轉換或作業的所有歷史版本，以便將來參考?？梢源蜷_歷史版本，并查看變更日志。

• 依賴完整性檢查：檢查資源庫轉換或作業之間的相互依賴關系，可以確保資源庫里沒有丟失任何鏈接，沒有丟失任何轉換、作業或數據庫連接。

• 安全性：防止未授權的用戶修改或執行ETL作業。

• 引用：重新組織轉換、作業，或簡單重命名，都是ETL開發人員的常見工作。要做好這些工作，需要完整的轉換或作業的引用。

7. 工具

Kettle是一個獨立的產品，但它包括了ETL開發和部署階段用到的多個工具程序，主要包括：

• Spoon：圖形化工具，用于快速設計和維護復雜的ETL工作流。

• Kitchen：運行作業的命令行工具。

• Pan：運行轉換的命令行工具。

• Carte：輕量級（大概1MB）Web服務器，用來遠程執行轉換或作業。一個運行有Carte進程的機器可以作為從服務器，從服務器是Kettle集群的一部分。

每個工具都有獨立的功能，也多少依賴于其它程序。Kettle的主體框架如圖1-10所示。

圖1-10?Kettle工具程序框架

（1）Spoon

Spoon是Kettle的集成開發環境（IDE）。它基于Java SWT提供了圖形化的用戶接口，主要用于ETL的設計。在Kettle安裝目錄下，有啟動Spoon的腳本，如Windows下的Spoon.bat，類UNIX下的spoon.sh。Windows用戶還可以通過執行Kettle.exe啟動Spoon。Spoon的屏幕截圖如圖1-11所示。

圖1-11 Spoon

圖1-11里可以清楚地看到Spoon的主窗口。主窗口上方有一個菜單條，下方是一個左右分隔的應用窗口。右方面板里有多個標簽面板，每個標簽面板都是一個當前打開的轉換或作業。左方面板是一個樹狀結構步驟或作業項視圖。右方的工作區又可以分為上下兩個部分：上部的畫布和下部的結果面板。

圖1-11的當前選中的畫布標簽里顯示了一個設計好的轉換。設計作業或轉換的過程實際就是往畫布里添加作業項或轉換步驟的圖標這么簡單，向畫布添加圖標的方式為，從左側的樹中拖拽。這些作業項和轉換步驟通過跳來連接。跳就是從一個作業項/步驟的中心連接到另一個作業項/步驟的一條線。在作業里跳定義的是控制流，在轉換里跳定義的是數據流。工作區下方的面板是運行結果面板，其中除了顯示運行結果還顯示運行時日志和運行監控。

工作區左側的樹有“主對象樹”和“核心對象”兩個標簽。主對象樹將當前打開的作業或轉換里的所有作業項或步驟以樹狀結構展現。設計者可以在這里快速地找到某個畫布上的步驟、跳或數據庫連接等資源。核心對象中包含Kettle中所有可用的作業項或步驟，可以在搜索框中輸入文本查找名稱模糊匹配的作業項或步驟。一些調試作業/轉換的工具也集成到了Spoon的圖形界面里，設計者可以在IDE里直接調試作業/轉換。這些調試功能按鈕在畫布上方的工具欄里。

（2）Kitchen和Pan

作業和轉換可以在圖形界面里執行，但這只是在開發、測試和調試階段。在開發完成后，需要部署到實際運行環境中，在部署階段Spoon就很少用到了。部署階段一般需要通過命令行執行，并把命令行放到Shell腳本中，并定時調度這個腳本。Kitchen和Pan命令行工具就是用于這個階段，在實際的生產環境使用。

Kitchen和Pan工具是Kettle的命令行執行程序。實際上，Kitchen和Pan只是在Kettle執行引擎上的封裝。它們只是解釋命令行參數，調用并把這些參數傳遞給Kettle引擎。Kitchen和Pan在概念和用法上都非常相近，這兩個命令的參數也基本是一樣的。唯一不同的是Kitchen用于執行作業，Pan用于執行轉換。在使用命令行執行作業或轉換時，需要重點考慮網絡傳輸的性能。Kettle數據流將數據作為本地行集緩存。如果數據源和目標之間需要通過網絡傳輸大量數據，將Kettle部署于源或目標服務器上會極大提升性能。

Kitchen和Pan都通過腳本的方式啟動，在Windows系統下，腳本名稱是Kitchen.bat和Pan.bat，在類UNIX系統下，腳本名稱是Kitchen.sh和Pan.sh。在執行這些腳本以及Kettle自帶的其它腳本時，要把Kettle目錄切換為控制臺的當前目錄。類UNIX系統的腳本默認情況下是不能執行的，必須使用chmod命令使腳本可執行。

Kettle用Java語言開發，因此在使用Kettle命令行時需要注意匹配Java版本。例如Kettle8.2.0版本需要JDK 1.8的支持。這樣就能在Spoon的圖形界面下進行設計開發調試，然后用命令行執行保存在本地文件或資源庫中的轉換或作業，秉承Java程序一次編譯到處運行的理念。下面是一些命令行的例子。

# 列出所有有效參數Kettle-home> ./kitchen.sh# 運行一個存儲在文件中的作業Kettle-home> ./kitchen.sh /file:/home/foo/daily_load.kjb# 運行一個資源庫里的作業Kettle-home> ./kitchen.sh /rep:pdirepo /user:admin /pass:admin /dir:/ /job:daily_load.kjb# 運行一個存儲在文件中的轉換Kettle-home> ./pan.sh -file:/home/mysql/MongoDB_to_MySQL.ktr

Kitchen和Pan的命令行包含了很多參數，在不使用任何參數的情況下，直接運行Kitchen和Pan會列出所有參數的幫助信息。參數的語法規范為：

[/-]name [[:=]value]

參數以斜線（/）或橫線（-）開頭，后面跟參數名。大部分參數名后面都要有參數值。參數名和參數值之間可以是冒號（:）或等號（=），參數值里如果包含空格，參數值必須用單引號（'）或雙引號（"）引起來。

作業和轉換的命令行參數非常相似，這兩個命令的參數可以分為下面幾類：

• 指定作業或轉換

• 控制日志

• 指定資源庫

• 列出可用資源庫和資源庫內容

? ? ? ? 表1-4列出了Kitchen和Pan共有的命令行參數。

表1-4 Kitchen和Pan共有的命令行參數

盡管Kitchen和Pan命令的參數名基本相同，但這兩個命令里的dir參數和listdir參數的含義有一些區別。對Kitchen而言，dir和listdir參數列出的是作業的路徑，Pan命令里的這兩個參數列出的是轉換路徑。除了共有的命令行參數外，Kitchen和Pan自己特有的命令行參數分別見表1-5、表1-6。

表1-5 Kitchen特有的命令行參數

表1-6 Pan特有的命令行參數

（3）Carte

Carte服務用于執行一個作業，就像Kitchen一樣。但和Kitchen不同的是，Carte是一個服務，一直在后臺運行，而Kitchen只是運行完一個作業就退出。當Carte在運行時，一直在某個端口監聽HTTP請求。遠程機器客戶端給Carte發出一個請求，在請求里包含了作業的定義。當Carte接到了這樣的請求后，它驗證請求并執行請求里的作業。Carte也支持其它幾種類型的請求，這些請求用于獲取Carte的執行進度、監控信息等。

Carte是Kettle集群中一個重要的構建模塊。集群可將單個工作或轉換分成幾部分，在Carte服務器所在的多個計算機上并行執行，因此可以分散工作負載。關于Carte以及Kettle集群的配置和使用，詳見本專題的（十一）Kettle集群與數據分片。

8. 虛擬文件系統

靈活而統一的文件處理方式對ETL工具來說非常重要，所以Kettle支持URL形式的文件名。Kettle使用Apache的通用VFS作為文件處理接口，替用戶解決各種文件處理方面的復雜情況。例如，使用Apache VFS可以選中.zip壓縮包內的多個文件，和在一個本地目錄下選擇多個文件一樣方便。表1-7里顯示的是VFS的一些典型的例子。

表1-7 VFS文件規范的例子

四、小結

編程和使用工具是常用的開發ETL應用的方法，而ETL工具又有基于映射和基于引擎之分。面對各種各樣的ETL開發工具，之所以選擇Kettle主要歸結為下面幾點原因。

• 最小化編碼工作

開發ETL系統通常是一個非常復雜的工程，造成這種復雜性的原因很多。數據倉庫的數據來源可能分布在不同的數據庫，不同的地理位置，不同的應用系統之中，而且由于數據形式的多樣性，數據轉換的規則大都極為復雜。如果手工編寫程序抽取數據并做轉換，不可避免地需要大量的設計、編碼、測試、維護等工作。這還不包括熟練掌握編程語言的學習成本。另一方面，Kettle非常容易使用，其所有的功能都通過用戶界面完成，不需要任何編碼工作。用戶只需要告訴它做什么，而不用指示它怎么做，這大大提高了ETL過程的開發效率。在Spoon界面中，用戶通過簡單拖拽就能完成絕大部分ETL設計工作。

• 極簡的多線程與并發執行

顯然多線程并行可以極大提高程序執行效率，然而從編程角度講，多線程比單線程要考慮的問題多得多。在Kettle中設置多線程方式執行非常簡單，只要在步驟的右鍵菜單中選擇“改變開始復制的數量”，然后指定線程數即可，其它工作都交給Kettle處理，實現細節對用戶完全透明。另外再次強調，Kettle轉換中的各個步驟本身就是以數據流的形式并行的。

• 完備的轉換步驟與作業項

Kettle 8.3版本中，轉換的核心對象包含輸入、輸出、應用、轉換、腳本等23個分類，每個分類中又包含大量的步驟。作業的核心對象包含14個分類，同樣每個分類中包含大量作業項。數據庫連接更是支持53種數據庫之多?？梢哉f當前Kettle原生已經幾乎支持所有常見數據源和ETL功能需求，而且步驟、作業項、數據庫種類還會隨著Kettle的版本更新而不斷增加。

• 完全跨平臺

Kettle是基于Java的解決方案，因此天然繼承了Java跨平臺性。用戶可以在自己熟悉的環境中（如Windows、Mac等），通過圖形界面進行ETL設計開發，然后將調試好的轉換或作業保存為外部XML文件，或將元數據存儲在資源庫中。這樣只要有合適的JVM存在，轉換或作業就能運行在任何環境和平臺之上，真正做到與平臺無關。

以我個人的經驗而言，只要是和關系數據庫打交道，很多情況ETL通過SQL就能搞定。但有時面對看似普通的需求，用SQL解決卻相當麻煩。在本篇最后舉一個實際工作中遇到的簡單例子，說明Kettle比SQL更適合的使用場景，同時加深一點對Kettle的直觀印象。下一篇文章從安裝配置開始進入使用Kettle的實操階段。

收到的需求是這樣的：有幾百個文本文件，每個文件內容的格式相同，都是有固定分隔符的兩列，每個文件有數千行記錄?，F在需要把這些文件的內容導入一個表中，除了文件內容中的兩列，還要存一列記錄數據對應的文件名。

向數據庫表中導入數據，本來是一件輕而易舉的事?？捎袔装賯€文件，還要將文件名連同對應的數據一起存入到表中，要手工逐個處理每個文件未免太麻煩了?，F在是Kettle一顯身手的時候了。Kettle的轉換處理數據流，其中有一個“獲取文件名”的輸入步驟，可以使用它在導入文件數據時添加上文件名字段，而且支持正則表達式同時獲取多個文件名，正好適用此場景。下面為在Kettle 8.3中的實現步驟。

1. 新建一個轉換，包含“獲取文件名”、“文本文件輸入”、“表輸出”三個步驟，如圖1-12所示。?

圖1-12 多文件數據導入

2. 設置“獲取文件名”步驟，如圖1-13所示。

圖1-13 “獲取文件名”步驟設置

文件所在目錄為Kettle所在服務器本地的/tmp/data/，通配符采用正則表達式寫法。注意*前面要加一個“.”，否則報錯。這一步驟會將包括文件名在內的文件的13個屬性作為輸出字段傳遞給后面的步驟。

3. 設置“文本文件輸入”步驟，“文件”、“內容”、“字段”標簽分別如圖1-14到圖1-16所示。

圖1-14 “文本文件輸入”步驟的“文件”設置

“選中文件”使用與“獲取文件名”步驟相同的正則表達式?！霸谳斎肜锏淖侄伪划斪鑫募敝行枰顚懮弦徊襟E中作為文件名的字段（默認為filename）。

圖1-15 “文本文件輸入”步驟的“內容”設置

字段分隔符為逗號，格式選擇“Unix”。

圖1-16 “文本文件輸入”步驟的“字段”設置

通過點擊“獲取字段”按鈕，可以自動獲得文本文件中的字段。

4. 設置“表輸出”步驟，如圖1-17所示

圖1-17 “表輸出”步驟設置

將表字段與前面步驟輸出的字段做映射。

該轉換執行后，會將/tmp/data/目錄下所有txt文件的內容，及其對應的文件名同時導入表中。

版權聲明：

本文為大數據技術與架構整理，原作者獨家授權。未經原作者允許轉載追究侵權責任。

編輯｜冷眼丶

微信公眾號｜import_bigdata

歡迎點贊+收藏+轉發朋友圈素質三連

文章不錯？點個【在看】吧！?????

總結

以上是生活随笔為你收集整理的Kettle构建Hadoop ETL实践（一）：ETL与Kettle的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。