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

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

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

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

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

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

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

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

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

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

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

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

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