shenxiangl's blog

    在昨天的帖子里面，我介紹了一些ETL初級的東西，不知道大家是否已經掌握，我現在介紹一些Kettle應用中，比較有幫助的一些地方。

1，Kettle跨平臺使用。
    例如：在AIX下（AIX是IBM商用UNIX操作系統，此處在LINUX/UNIX同樣適用），運行Kettle的相關步驟如下：
    1）進入到Kettle部署的路徑
    2）執行 chmod *.sh，將所有shell文件添加可執行權限
    3）在Kettle路徑下，如果要執行transformation，就運行./pan.sh -file=?.ktr -debug=debug -log=log.log
    其中。-file說明你要運行的transformation文件所在的路徑；-debug說明日志輸出的級別；-log說明日志輸出的路徑
    4）同理，對于job的執行，請將./pan.sh更換成./kitchen.sh，其他部分說明不變。

2，Kettle環境變量使用。
    在transformation中，Core Objects-->Job-->Set Variables，可疑設置環境變量，對于絕對路徑和相對路徑的轉換很有幫助，Kettle的跨平臺很大程度依靠他的

3，其它功能的使用。
    其它功能包括DB存儲過程調用，流查詢，值映射，聚合記錄等，各位自行摸索，有問題可以和我聯系：）

4，Kettle定時功能。
    在Job下的start模塊，有一個定時功能，可以每日，每周等方式進行定時，對于周期性的ETL，很有幫助。

5,Kettle經驗之日志。
    Kettle對于日志的處理，存在一個BUG，看過上一篇的人或許已經看到了我的留言，Kettle對于日志處理有一個BUG，當日志多于49M（不是50M，也不是49M），Kettle就會自動停止，這一點我在源碼里面也沒有找到對應的設置和約束，原因還找不到，因為是日志沒有寫，所以原因也不好跟蹤還不知道具體原因。

6，Kettle之效率提升。
    Kettle作為一款ETL工具，肯定無法避免遇到效率問題，當很大的數據源輸入的時候，就會遇到效率的問題。對此有幾個解決辦法：
    1）數據庫端創建索引。對需要進行查詢的數據庫端字段，創建索引，可以在很大程度上提升查詢的效率，最多的時候，我不創建索引，一秒鐘平均查詢4條記錄，創建索引之后，一秒鐘查詢1300條記錄。
    2）數據庫查詢和流查詢注意使用環境。因為數據庫查詢為數據輸入端輸入一條記錄，就對目標表進行一次查詢，而流查詢則是將目標表讀取到內存中，數據輸入端輸入數據時，對內從進行查詢，所以，當輸入端為大數據量，而被查詢表數據量較小（幾百條記錄），則可以使用流查詢，畢竟將目標表讀到內存中，查詢的速度會有非常大的提升（內存的讀寫速度是硬盤的幾百倍，再加上數據庫自身條件的制約，速度影響會更大）。同理，對于目標表是大數據量，還是建議使用數據庫查詢，不然的話，一下子幾百M的內存被干進去了，還是很恐怖的。
    3）謹慎使用javascript腳本，因為javascript本身效率就不高，當你使用js的時候，就要考慮你每一條記錄，就要執行一次js所需要的時間了。
    4）數據庫commit次數，一條記錄和一百條記錄commit對效率的影響肯定是不一樣的。
    5）表輸入的sql語句的寫法。有些人喜歡在表輸入的時候，將所有關聯都寫進去，要么from N多個表，要么in來in去，這樣，就要面對我在2）里面說道的問題，需要注意。
    6）注意日志輸出，例如選擇數據庫更新方式，而且日志級別是debug，那么后臺就會拼命的輸出日志，會在很大程度上影響速度，此處一定要注意。

7，常見的調試BUG。
    Kettle提供了很多調試的解決辦法，但是對于常見的調試BUG還是能避免就避免。
    1）路徑問題。我最常遇到的問題就是在windows下調試成功，但是部署到UNIX下出問題，忘記將windows下路徑變成unix下，經常會出現問題。
    2）輸出端，數據庫插入更新選擇不對。輸出端，提供了三種數據庫輸出的辦法，數據庫輸出，插入/更新，更新，對于這三種，各有利弊，如果你知道數據庫輸出，完全是插入，如果有重復數據，則會報錯；插入更新和更新，因為更新數據時，后臺輸出很多日志，會導致效率很低。
   

    總體來說，Kettle還是一個很不錯的ETL工具，在開源軟件里面并不多見，以后有Kettle相關的問題，大家可疑相互探討。

    因為公司近期招了幾個實習生，需要我來做一個關于Kettle的培訓，準備了一些培訓的資料，一個ppt，用于培訓的，一個數據庫備份和一個詳細的操作文檔，是一個小作業，不敢獨享，發出來給大家share，順便作為這個技術博客的頭彩，希望大家能夠多來關注。

    如果大家不愿意看網頁版，可以下載相關文檔到本地研究：）

    培訓ppt在這里
    培訓手冊在這里
    練習數據庫備份在這里
    練習的腳本文件在這里

---

Kettle培訓手冊

 

Etl介紹

 

ETL（Extract-Transform-Load的縮寫，即數據抽取、轉換、裝載的過程），對于金融IT來說，經常會遇到大數據量的處理，轉換，遷移，所以了解并掌握一種etl工具的使用，必不可少。

Kettle是一款國外開源的etl工具，純java編寫，綠色無需安裝，數據抽取高效穩定。Kettle中有兩種腳本文件，transformation和job，transformation完成針對數據的基礎轉換，job則完成整個工作流的控制。

 

kettle部署運行

 

將kettle2.5.1文件夾拷貝到本地路徑，例如D盤根目錄。

雙擊運行kettle文件夾下的spoon.bat文件，出現kettle歡迎界面：

 

 

稍等幾秒

 

 

選擇沒有資源庫，打開kettle主界面

 

 

創建transformation，job

點擊頁面左上角的創建一個新的transformation，點擊保存到本地路徑，例如保存到D:/etltest下，保存文件名為EtltestTrans，kettle默認transformation文件保存后后綴名為ktr

 

點擊頁面左上角的創建一個新的job，點擊保存到本地路徑，例如保存到D:/etltest下，保存文件名為EtltestJob，kettle默認job文件保存后后綴名為kjb

 

創建數據庫連接

 

在transformation頁面下，點擊左邊的【Main Tree】，雙擊【DB連接】，進行數據庫連接配置。

 

 

Connection name自命名連接名稱

Connection type選擇需要連接的數據庫

Method of access選擇連接類型

Server host name寫入數據庫服務器的ip地址

Database name寫入數據庫名

Port number寫入端口號

Username寫入用戶名

Password寫入密碼

 

例如如下配置：

 

 

點擊【test】，如果出現如下提示則說明配置成功

 

 

點擊關閉，再點擊確定保存數據庫連接。

 

一個簡單的ktr例子

 

目標：

從交易表（trade），帳戶表（account），客戶表（cust）抽數交易相關的所有信息，并判斷對公對私分別進行處理，輸出到文本文件中。

 

操作步驟：

在EtltestTrans頁面下，點擊左側的【Core Objects】，點擊【Input】，選中【表輸入】，拖動到主窗口釋放鼠標。

 

雙擊【表輸入】圖標

 

數據庫連接選擇剛剛創建好的etltest數據庫連接，在主窗口寫入對應的查詢語句

 

 

 

點擊確定完成。

 

點擊左側的【Lookup】，選中【數據庫查詢】，拖動到主窗口釋放鼠標。

 

按住shift鍵，用鼠標點中剛才創建的【表輸入】，拖動到【數據庫查詢】上，則建立了兩個環節之間的連接。

 

 

雙擊【數據庫查詢】圖標

 

步驟名稱寫入account表查詢，數據庫連接選擇剛剛創建好的etltest數據庫連接，查詢的表寫入account，查詢所需的關鍵字中，表字段寫入acctno，比較操作符寫入”=”，字段1寫入acctno。

 

在查詢表返回的值里面寫入custno，確定完成。

 

 

同上，再創建一個數據庫查詢，命名為cust表查詢，查詢的表寫入cust，查詢所需的關鍵字寫入custno=custno，查詢表返回的值寫入custname，custid，custtype

 

 

點擊左側的【Transform】，選中【過濾記錄】，拖動到主窗口釋放鼠標。

 

點擊左側的【Scripting】，選中兩個【Modified Java Script Value】，拖動到主窗口釋放鼠標。分別雙擊打開，重命名為”對公類型修改”和”對私類型修改”。

 

同時，分別創建【過濾記錄】和【對公類型修改】，【對私類型修改】的連接。

 

雙擊【規律記錄】打開。

 

第一個<field>里面選擇custtype，點擊<value>，在Enter value里面寫入1，確定

 

在發送true數據給步驟里，選擇【對私類型修改】，在發送false數據給步驟里，選擇【對公類型修改】，確定保存。

 

 

雙擊【對公類型修改】，在里面寫入javascript腳本語句

 

 

在字段中寫入custtype_cn，類型選為string。確定。

 

 

同理，在【對私類型修改】中，在里面寫入javascript腳本語句

 

 

在字段中寫入custtype_cn，類型選為string。確定。

 

點擊左側的【Transform】，選中兩個【增加常量】，拖動到主窗口釋放鼠標。分別雙擊打開，重命名為”增加對公常量”和”增加對私常量”。

 

分別建立【對公類型修改】和【對私類型修改】與【增加對公常量】和【增加對私常量】的連接

 

 

雙擊【增加對公常量】，名稱寫入value，類型選擇string，值寫入”這是一筆對公客戶發生的交易”，確定保存。

 

 

同理，雙擊【增加對私常量】，名稱寫入value，類型選擇string，值寫入”這是一筆對私客戶發生的交易”，確定保存。

 

點擊左側的【Output】，選中【文本文件輸出】，拖動到主窗口釋放鼠標。

 

建立【增加對公常量】，【增加對私常量】和【文本文件輸出】的連接。

 

雙擊打開【文本文件輸出】，文件名稱寫入D:\etltest\etltest.txt

 

 

點擊內容標簽，根據情況進行修改，例如

 

 

點擊字段標簽

名稱依次寫入tradeid，acctno，amt，custno，custname，custid，custtype_cn，value，類型根據各個字段實際類型進行選擇

 

 

確定保存

 

點擊保存創建好的transformation。

 

點擊運行這個轉換。

 

 

點擊launch，開始運行

 

當所有狀態都變成已完成時，則轉換完成。

 

另一個簡單的ktr例子

 

目的：

將上一個ktr生成的文本導入到數據庫中。

 

操作步驟：

創建一個transformation，命名為EtltestTransfile2db.ktr，創建數據庫連接etltest，點擊【Input】，選中【文本文件輸入】，拖到主窗口，釋放鼠標，雙擊打開

 

文件名稱里面寫入D:\etltest\etltest.txt

 

 

點擊內容標簽，分隔符寫入@@，將頭部的鉤去掉

 

 

點擊字段標簽，名稱依次寫入tradeid，acctno，amt，custno，custname，custid，custtype_cn，value，類型根據各個字段實際類型進行選擇

 

 

點擊【Transform】，選中【字段選擇】，拖到主窗口，釋放鼠標

 

點擊【Output】，選中【表輸出】，拖到主窗口，釋放鼠標

 

建立【文本文件輸入】和【字段選擇】與【字段選擇】和【表輸出】的連接

 

 

雙擊【表輸出】，目標表中寫入trade_all，提交記錄數量寫成1，確定保存。

 

 

雙擊【字段選擇】，點擊 獲取選擇的字段，再點擊Edlt Mapping，點擊OK確定，編輯所有字段對應關系，點確定。

 

 

點擊運行這個轉換。，則將上一個ktr中生成的文本，導入到數據庫當中。

 

一個簡單的kjb例子

 

目的：

將上兩個transformation一起在同一個job里面調用執行。

 

操作步驟：

在EtlscriptJob頁面，點擊【Core Objects】，點擊【Job entries】，選中【START】拖動到主窗口釋放鼠標，再選中兩個【Transformation】，拖動到主窗口釋放鼠標，建立【START】和 【Transformation】與【Transformation】之間的連接。

 

雙擊第一個【Transformation】，在Transformation filename中寫入D:\etltest\EtltestTrans.ktr，確定保存。

 

 

    同時將另外一個【Transformation】，路徑指向D:\etltest\EtltestTransfile2db.ktr，保存。

 

點擊保存創建好的job。

 

點擊運行這個轉換。

 

待所有任務都顯示成功，則為job調用transformation運行成功。