技術交流

ETL 資料擷取、轉換、載入系列(五):常用的 ETL 工具

前面幾篇我們從 ETL(Extract-Transform-Load,提取轉換載入)的概念,談到轉檔步驟、中繼資料庫設計,一直到資料驗證,最終會需要一個工具進行實作。市面上 ETL 工具很多,這些年我們也陸續用過幾套工具,但最常使用的是這兩套:Microsoft SQL Server Integration Services(SSIS)與 Hitachi Vantara 的 Pentaho Data Intergation(PDI)。

ETL 工具免費與付費怎麼選?三大要點

除了一些老牌的 ETL 工具外,現在有越來越多開源解決方案,大部分包含免費的社群版與付費的企業版,各種工具該有的功能都相當完備,但其中有幾項功能是我們在評估 ETL 工具時,需要特別注意的。

1. 擴充性

現行市面上的 ETL 工具從資料源、資料轉換、寫入目的端,都有提供各項元件,但有時也會遇到特殊的資料源或特定需求,若既有功能無法直接達成時,就需要具有自行撰寫擴充元件的功能,以及撰寫元件所使用的程式語言,這部分也要配合專案成員的能力作評估。

2. 環境參數設定

一般系統運行環境,通常會分成開發環境、測試環境、驗證環境、正式環境。因此同一套程式要在不同的環境執行,需要具有參數設定功能,才能隨著環境不同變更適合的參數。

3. 資料庫欄位自動更新

當輸入或輸出資料庫欄位更新時,能夠自動取得最新的格式。我曾經用過一套 ETL 工具,所有功能都很優秀,甚至可以自行開發流程介面,但可惜在資料庫欄位對應功能較弱。當資料源欄位異動時,需要使用者自行在流程的每一個步驟加上欄位定義,這個對於跨系統整合或逐步擴增內容的專案來說,太耗費修正欄位所需的工時。最後我也只能選擇放棄,不再使用那套工具。

ETL 工具介紹

SQL Server Integration Services(SSIS)

SSIS 是微軟提供的 ETL 工具,最大的優點是與微軟其它工具完全整合。它的開發工具是在 Visual Studio 裡面,選擇 Integration Service 的專案類型,微軟也有提供獨立的安裝程式 SQL Server Data Tools(SSDT)可以下載安裝。

整個開發模式與其它微軟的程式語言開發模式類似,利用左側的資料處理元件,在中間的畫布以拖拉方式組合出控制流程與資料流程。其所提供的元件,主要還是以整合微軟 Windows 平台的檔案格式與資料為主,若需要客製可以使用 dot.net 程式語言來開發。

除了在 Visual Studio 中執行外,也可將其直接佈署到 SQL Server 資料庫中,透過 SQL Server Agent 做排程。亦可將其封裝成一個 ISPAC檔,只要有這個檔,就可以在 SQL Server 的環境下透過命令行的方式執行,不需另外安裝執行工具。

SSIS 是其將執行的環境參數,整合到封裝檔內部,因此不同的執行環境參數,需要建置不同的封裝檔。在一些更版過程較嚴謹的企業中,要求過版到正式環境的封裝檔,必須是與驗證環境相同的封裝檔,這一點會有所牴觸。我們的變通做法是將所有環境參數事先設定好,利用批次檔作編譯,同時製作出所有環境的封裝檔,一起申請過版。因為這是微軟 SSIS 的特性,所以使用微軟解決方案的客戶,一般都能接受。

SQL Server Integration Services(SSIS)

Pentaho Data Integration

Pentaho Data Integration(PDI)是 Hitachi Vantara 提供的開源 ETL 工具,舊名叫 Kettle,除了免費的社區版,也可以購買企業版的商業許可證。一般而言,免費的社區版本就已經具備完整且常用的 ETL 元件,其中還包括各種 SQL、NoSQL 資料庫、雲端的資料源與目的端,例如:mongoDB、Hbase、Hadoop、S3 CSV、Salesforce…等,也提供各種資料轉換過程所需要用到的元件。

PDI 提供了一個簡單且友善的開發工具 Spoon,作為資料流程開發的介面。流程分為兩類:控制流程(Job)與資料流程(Transformation),會分別儲存為附檔名 .kjb 與 .ktr 的檔案,它的內容是以 XML 來描述,因此每個流程都是一個獨立的檔案,因此在功能規劃時,儲存目錄的選擇很重要,否則檔案會很凌亂。

Pentaho Data Integration

PDI 有許多資料驗證的元件,例如:讀入兩個異質的資料源,透過簡單的比對元件,就可以找出差異內容,對於作轉檔後的驗證,有很大的助益。當既有元件不足時,可以使用 java 或 JavaScript 自行開發所需的功能,相關討論區或社群也可以找到其它使用者的開發經驗,資源相當豐富。

ETL 流程可透過 spoon 的開發介面直接執行,除了可以看到每個步驟的執行結果外,也可以透過命令列工具 kitchen 啟動流程檔(Job)與資料流程(Transformation),因此可以與各種排程軟體整合。

依執行環境選擇 ETL 工具

上述談到的兩套 ETL 工具 SSIS 與 PDI,我們選用的考量主要是依執行的環境做選擇。若主要執行環境為 Windows 作業平台且資料庫為 SQL Server,開發語言主要為 Dot.net,則我們會選擇使用微軟的 SQL Server Integration Service(SSIS)。其它的執行情境,則我們傾向於使用在 JVM(Java Virtual Machine,Java 虛擬機器)下可以執行的 Pentaho Data Integration,因為它在 windows、linux、Mac 都可以執行,彈性比較大。

好的工具還要配合好的架構設計,兩者相輔相乘,才能發揮最大效益。因此這一系列文章,主要重點還是在於 ETL 的架構設計,對於 ETL 工具我們只做簡單介紹,這方面網路上的資源很多,有興趣的朋友可以自行研讀。

延伸閱讀:系統整合常見問題有哪些?如何因應?

閱讀更多