kettle公司內(nèi)部培訓(xùn)手冊

1、Kettle 培訓(xùn)手冊一、 Etl 介紹ETL（ Extract-Transform-Load 的縮寫，即數(shù)據(jù)抽取、轉(zhuǎn)換、裝載的過程），對于 金融 IT 來說，經(jīng)常會遇到大數(shù)據(jù)量的處理，轉(zhuǎn)換，遷移，所以了解并掌握一種 etl 工具的使用，必不可少。Kettle 是一款國外開源的 etl 工具，純 java 編寫，綠色無需安裝，數(shù)據(jù)抽取高 效穩(wěn)定。Kettle 中有兩種腳本文件， transformation 和job ，transformation 完成針 對數(shù)據(jù)的基礎(chǔ)轉(zhuǎn)換， job 則完成整個工作流的控制。二、 kettle 部署運行將 kettle2.5.1 文件夾拷貝到本地路徑，例如 D

2、 盤根目錄。 雙擊運行 kettle 文件夾下的 spoon.bat 文件，出現(xiàn) kettle 歡迎界面：稍等幾秒選擇沒有資源庫，打開 kettle 主界面創(chuàng)建 transformation ，job 點擊頁面左上角的創(chuàng)建一個新的 transformation ，點擊保存到本地路 徑，例如保存到 D:/etltest 下，保存文件名為 EtltestTrans ， kettle 默認(rèn) transformation 文件保存后后綴名為 ktr點擊頁面左上角的創(chuàng)建一個新的 job ，點擊保存到本地路徑，例如保 存到D:/etltest 下，保存文件名為 EtltestJob ，kettle 默認(rèn)j

3、ob 文件保存后后綴名為 kjb創(chuàng)建數(shù)據(jù)庫連接在 transformation 頁面下，點擊左邊的【 Main Tree 】，雙擊【 DB連接】，進(jìn)行 數(shù)據(jù)庫連接配置。connection name 自命名連接名稱Connection type 選擇需要連接的數(shù)據(jù)庫 Method of access 選擇連接類型Server host name 寫入數(shù)據(jù)庫服務(wù)器的 ip 地址 Database name寫入數(shù)據(jù)庫名Port number 寫入端口號Username寫入用戶名Password寫入密碼 例如如下配置：點擊【 test 】，如果出現(xiàn)如下提示則說明配置成功點擊關(guān)閉，再點擊確定保存數(shù)據(jù)庫

4、連接。一個簡單的 ktr 例子目的： 將一個數(shù)據(jù)庫導(dǎo)入到另一個數(shù)據(jù)庫中。操作步驟：創(chuàng)建一個 transformation ，命名為 etlTestTrans.ktr ，創(chuàng)建數(shù)據(jù)庫連接 ods，點擊【 Input 】，選中【表輸入】，拖到主窗口，釋放鼠標(biāo)，雙擊打 開如下圖點擊【 Transform 】，選中【字段選擇】，拖到主窗口，釋放鼠標(biāo)點擊【 Output 】，選中【表輸出】，拖到主窗口，釋放鼠標(biāo) 建立【文本文件輸入】和【字段選擇】與【字段選擇】和【表輸出】的連接雙擊【表輸出】，目標(biāo)表中寫入 ZT_TEST_KETTL，E，確定保存雙擊【字段選擇】，點擊 獲取選擇的字段，再點擊 Edlt M

5、apping ，點擊 OK 確定，編輯所有字段對應(yīng)關(guān)系，點確定。點擊 運行這個轉(zhuǎn)換。，則將上一個 ktr 中生成的文本，導(dǎo)入到數(shù)據(jù)庫當(dāng)中一個簡單的 kjb 例子目的： 將上一個 transformation 在一個 job 里面調(diào)用執(zhí)行。操作步驟：在etlTestJob 頁面，點擊【 Core Objects 】，點擊【 Job entries 】，選中【 START】 拖動到主窗口釋放鼠標(biāo)，再選中【 Transformation 】，拖動到主窗口釋放鼠標(biāo)， 建立【 START】和【 Transformation 】之間的連接。雙擊【 Transformation 】， 在 Transform

6、ation filename 中寫入 E:kettleWorkspaceetlTestTrans.ktr ，確定保存。點擊 保存創(chuàng)建好的 job點擊 運行這個轉(zhuǎn)換。待所有任務(wù)都顯示成功，則為 job 調(diào)用 transformation 運行成功。一個增量的例子增量更新 按照數(shù)據(jù)種類的不同大概可以分成：1. 只增加，不更新，2. 只更新，不增加3. 即增加也更新4. 有刪除，有增加，有更新 下面針對前三種做一個增量的 ETL抽取。過程如下： 根據(jù)前面講解的例子一樣，首先建立源表( fina_test1 )和目標(biāo)表( fina_test2 )， 整個設(shè)計流程如下 :其中第一個步驟(輸入 - 目標(biāo)表

7、)的 sql 大概如下模式：select ifnull(max(date_seal),1900-01-01 00:00:00) from fina_test2 你會注意到第二個步驟和第一個步驟的連接是黃色的線，這是因為第二個 table input (輸入 -源表)步驟把前面一個步驟的輸出當(dāng)作一個參數(shù)來用，所有 Kettle 用 黃色的線來表示，第二個 table input (輸入 - 源表) 的sql 模式大概如下： SELECT * FROM fina_test1 where date_seal?后面的一個問號就是表示它需要接受一個參數(shù)， 你在這個 table input （輸入 -源表

8、） 下面需要指定 replace variable in script 選項和 執(zhí)行每一行 為選中狀態(tài)， 這樣， Kettle 就會循環(huán)執(zhí)行這個 sql , 執(zhí)行的次數(shù)為前面參數(shù)步驟傳入的數(shù)據(jù)集的大小。關(guān)于第三個步驟執(zhí)行插入Kettle 執(zhí)行這個步驟是需要兩個數(shù)據(jù)流對比， 其中一個是目標(biāo)數(shù)據(jù)庫， 你在 目標(biāo)表 里面指定的，它放在 用來查詢的關(guān)鍵字左邊 的表字段里面的，另外一個數(shù)據(jù)流就是 你在前一個步驟傳進(jìn)來的，它放在 用來查詢的關(guān)鍵字 的右邊， Kettle 首先用你傳 進(jìn)來的 key 在數(shù)據(jù)庫中查詢這些記錄，如果沒有找到，它就插入一條記錄，所有的 值都跟你原來的值相同， 如果根據(jù)這個 key

9、找到了這條記錄， kettle 會比較這兩條 記錄，根據(jù)你指定 update field 來比較，如果數(shù)據(jù)完全一樣， kettle 就什么都不 做，如果記錄不完全一樣， kettle 就執(zhí)行一個 update 步驟。 備注：主鍵被修改得數(shù)據(jù)認(rèn)為是新記錄刪除的數(shù)據(jù)由在倉庫中需要保留無需考慮job entries 拉然后點擊新建 -job ，然后 job 的核心對象 出組建，進(jìn)行執(zhí)行抽取創(chuàng)建 kettle 資料庫 資源庫是用來保存轉(zhuǎn)換任務(wù)的，用戶通過圖形界面創(chuàng)建的的轉(zhuǎn)換任務(wù)可以保存在資 源庫中。資源庫可以是各種常見的數(shù)據(jù)庫， 用戶通過用戶名 / 密碼來訪問資源庫中的 資源，默認(rèn)的用戶名 / 密碼是

10、 admin/admin資源庫并不是必須的，如果沒有資源庫，用戶還可以把轉(zhuǎn)換任務(wù)保存在 xml 文件 中。如果用戶需要創(chuàng)建一個資源庫，在資源庫的登錄窗口（ PDI 啟動時的第一個窗 口）中有 【新建】 按鈕，點擊該按鈕彈出新建資源庫窗口，在該窗口中選擇一個數(shù)據(jù)庫連接，如果沒有事先定義的數(shù)據(jù)庫連接，則還要點 擊【新建】按鈕，來創(chuàng)建一個數(shù)據(jù)庫連接。選擇數(shù)據(jù)庫連接后，要為該資源庫命名， 作為這個資源庫的唯一標(biāo)志，最后選擇【創(chuàng)建或更新】按鈕來創(chuàng)建這個資源庫資源庫可以使多用戶共享轉(zhuǎn)換任務(wù)，轉(zhuǎn)換任務(wù)在資源庫中是以文件夾形式分組 管理的，用戶可以自定義文件夾名稱。如何使用 kettle 讀取包含多行表的 E

11、xcel 文件如果 Excel 工作表的表頭只有一行 ,使用 Kettle 讀取這樣的文件是很容易的如果 Excel 工作表的表頭是多行的 , 或者是分級的就需要在內(nèi)容標(biāo)簽下正確設(shè)置列名所占 行數(shù)才可以讀取 .考慮這樣的一個工作表如果想把里面的 12 列數(shù)據(jù)都讀出來 , 就要考慮如何處理多級表頭 步驟設(shè)置的詳細(xì)描述 步驟一 選擇文件名，現(xiàn)在文件或目錄里 到所要添加的 excel 文檔，然后點擊， 確定后，點擊步驟三 設(shè)置要讀取的內(nèi)容的一些屬性 , 這里要設(shè)置表頭的所占行數(shù)是 4 行.步驟四 錯誤處理 , 選擇如果有錯誤終止還是繼續(xù) , 錯誤信息保存的文件等（圖略）步驟五 選擇字段 , 如果前面

12、的三個步驟 （ 不包括錯誤處理步驟 ） 都設(shè)置正確 , 在這個頁面選 擇 獲取字段 字段按鈕 , 就會獲得所有的列名稱和數(shù)據(jù)類型 .這里我們可以看到 : 多級表頭中各級表頭的名稱被疊加起來 , 形成了唯一的列名 .點擊 預(yù)覽 按鈕可以預(yù)覽到數(shù)據(jù)對于表頭跨連續(xù)的多行 , 但不分級的情況也可以使用上述方式處理kettle 注釋：1 、 kettle 的控制流可以設(shè)置一些簡單的時間，并且可以實現(xiàn)隔斷天、周、月（三個只 能選一個，不能選那個月的那周那日），但是 kettle 工具不能關(guān)，如果關(guān)了，必須 重新啟動。2 、 kettle 里面缺少一個編輯的字段的插件，導(dǎo)致字段編輯很麻煩，這只能先 sql 中進(jìn) 行手寫，這個對寫 sql 的要求很高。一個 kettle 字段轉(zhuǎn)換（截?。┑睦哟笾碌牧鞒淌牵罕磔斎脒€是正常的 sql 查詢，沒有添加參數(shù)字段轉(zhuǎn)換（截?。┦窃?進(jìn)行修改。具體樣式如下：具體的用法：transform Functions里面包括了字符、數(shù)字的一些函數(shù)方法，這些函數(shù)方法可以解決一些字段需要轉(zhuǎn)化的問題。Input fields