kettle操作文檔

1、Kettle操作文檔1.Kettle介紹。22. Kettle下載。23.使用規(guī)范和注意事項。23.1.配置數(shù)據(jù)庫的全局變量。23.2.文件命名規(guī)范（不要用漢字）。23.3.文件存放位置。33.4.配置啟動任務(wù)文件。33.5.注意事項。34. Kettle部署使用。44.1.啟動kettle。44.2.創(chuàng)建轉(zhuǎn)換文件。54.2.1.使用標(biāo)識字段實現(xiàn)新增數(shù)據(jù)。54.2.2.使用時間戳實現(xiàn)新增或修改數(shù)據(jù)。104.2.3.使用時間比較實現(xiàn)新增或修改數(shù)據(jù)。134.2.4.使用對某一數(shù)據(jù)值比較實現(xiàn)新增或修改數(shù)據(jù)。164.3.創(chuàng)建任務(wù)文件。184.4.命令行啟動任務(wù)。191.Kettle介紹。 ETL（Ex

2、tract-Transform-Load的縮寫，即數(shù)據(jù)抽取、轉(zhuǎn)換、裝載的過程），對于政府部門來說，經(jīng)常會遇到大數(shù)據(jù)量的處理，轉(zhuǎn)換，遷移，所以了解并掌握一種etl工具的使用，必不可少。Kettle是一款國外開源的etl工具，純java編寫，綠色無需安裝，數(shù)據(jù)抽取高效穩(wěn)定,提供了圖形化界面，使用很方便。Kettle中有兩種腳本文件，transformation和job，transformation完成針對數(shù)據(jù)的基礎(chǔ)轉(zhuǎn)換，job則完成整個工作流的控制。2. Kettle下載。Kettle可以在40/bbs/網(wǎng)站下載kettle壓縮包，因kettle為綠色軟件，解壓即可使用。

3、這個壓縮包，公司加了一些功能，跟官網(wǎng)下載的不太一樣，建議使用公司論壇上的壓縮包。 資料網(wǎng)站：注：解壓路徑盡量不要有中文。.kettle:存放kettle的一些環(huán)境變量信息，資源庫的信息。jre6:存放java運行庫文件（1.6版本）。launcher：存放啟動的文件以及啟動的配置信息。lib：存放kettle的庫文件。libext:存放用到的外部庫文件。libswt:存放用到的界面庫文件。plugins:存放插件文件。resources:存放編寫的轉(zhuǎn)換和任務(wù)文件，以及日志和任務(wù) 配置信息。ui：存放用到的的圖片和配置信息。Kitchen.bat:任務(wù)文件執(zhí)行器（命令行模式）。Pan.bat:轉(zhuǎn)

4、換文件執(zhí)行器（命令行模式）。set-pentaho-env.bat:設(shè)置kettle運行的環(huán)境變量。Spoon.bat:打開設(shè)計工具（界面方式）。Spoon.ico,spoon.png:工具的圖標(biāo)。start.bat:啟動存放在resources目錄下的任務(wù)配置文件。說明.txt:使用的規(guī)范和注意事項。3.使用規(guī)范和注意事項。3.1.配置數(shù)據(jù)庫的全局變量。 在.kettle目錄下的perties文件配置數(shù)據(jù)庫全局變量。 屬性：HostName,DatabaseName,PortNumber,UserName,Password。 規(guī)則：地市名+數(shù)據(jù)庫名+屬性名=值,且命名時第

5、一個單詞以小寫字母作為開頭，后面的單詞則用大寫字母開頭。 如：昆山的ebcmks數(shù)據(jù)庫，配置如下： ksEbcmksHostName= ksEbcmksDatabaseName=ebcmks ksEbcmksPortNumber=1433 ksEbcmksUserName=sa ksEbcmksPassword=powerdata 在工具中使用方法為：$ksEbcmksHostName。3.2.文件命名規(guī)范（不要用漢字）。 轉(zhuǎn)換文件用操作數(shù)據(jù)庫的表名(大寫中文首字母)。 任務(wù)文件用項目名。 配置啟動任務(wù)文件用項目名。 日志文件用“kettlelog_項目名.log”。3.3.文件

6、存放位置。 文件統(tǒng)一放到resources目錄下。 存放規(guī)則：項目名目錄->（transformations目錄，jobs目錄）,config目錄,logs目錄； 其中transformations目錄主要存放轉(zhuǎn)換文件，jobs目錄存放任務(wù)文件，config目錄存放配置啟動任務(wù)文件（需要執(zhí)行的任務(wù)文件配置信息）。 如昆山：項目名目錄為：ks,轉(zhuǎn)換文件目錄:transformations,任務(wù)文件目錄：jobs，配置文件目錄:config，日志文件目錄:logs。3.4.配置啟動任務(wù)文件。 配置文件統(tǒng)一放到：項目名目錄->config目錄下。 文件后綴名為：bat（批處理文件）。 編

7、寫規(guī)則：Kitchen.bat -file=%cd%resources任務(wù)文件（從項目名目錄開始） -level=日志等級 -log=resourceslogs日志文件（以工具主目錄為起始目錄），之間用空格分割; 如：Kitchen.bat -file=%cd%resourcesksjobsebcmks.kjb -level=Basic -log=resourceslogskettlelog_ebcmks.log，其中l(wèi)evel等級包括（Basic, Detailed, Debug, Rowlevel, Error, Nothing）。如下：3.5.注意事項。 1）：路徑和文件名稱不要使用中文

8、。 2）:在只進行新增操作時，如果兩邊的表字段數(shù)量不一樣，應(yīng)把“表輸出”的specify database選中。 3）：啟動任務(wù)只需執(zhí)行主目錄下的start.bat文件。 4）：在任務(wù)中選擇一個交換時的目錄應(yīng)使用相對路徑，如：應(yīng)把“D:kettlekettle4.2.0resourceskstransformationsT_WORKFLOW_GZLCSL.ktr”修改為：“$KETTLE_HOMEresourceskstransformationsT_WORKFLOW_GZLCSL.ktr”,使用$KETTLE_HOME全局變量。 5）：在表里面新增字段后，如果在別的控件（如“表輸入”）中沒有

9、找到新增的字段， 此時應(yīng)操作菜單Tool->Database->Clear Cache，把緩存清除掉。 6）：在預(yù)覽數(shù)據(jù)時，如果數(shù)據(jù)量比較大的情況下可能會報錯， 此時應(yīng)把“表輸入”的“記錄數(shù)量限制”修改為100,正式轉(zhuǎn)換時應(yīng)把值修改為0。4. Kettle部署使用。4.1.啟動kettle。把下載的壓縮文件壓縮到D盤根目錄。雙擊kettle4.2.0目錄下的Spoon.bat文件，出現(xiàn)kettle歡迎界面如圖1。圖1進入主界面如圖2。題24.2.創(chuàng)建轉(zhuǎn)換文件。4.2.1.使用標(biāo)識字段實現(xiàn)新增數(shù)據(jù)。如：傳輸人員的軌跡信息。單擊菜單項的文件->新建->轉(zhuǎn)換，創(chuàng)建一個轉(zhuǎn)換文件

10、；保存文件名為：WZXX路徑為主目錄下的resourceskstransformationsWZXX.ktr；右鍵選擇“轉(zhuǎn)換設(shè)置”，把轉(zhuǎn)換名字修改為：軌跡信息；如圖3. 圖3把左邊菜單“輸入”下的“表輸入”，通過鼠標(biāo)左鍵拖到主界面中，并雙擊“表輸入”，如圖4。圖4單擊圖4界面中的“新建”按鈕,創(chuàng)建源數(shù)據(jù)連接信息，彈出框如圖5。Connection Name:ebcmks。(連接庫的名稱)Connection Type:MS Sql Server。(數(shù)據(jù)庫類型)Host Name:通過Ctrl+Alt+Space(空格)，選擇ksEbcmksHostName（也可直接輸入）。（數(shù)據(jù)庫的服務(wù)器IP

11、地址）Database Name:$ksEbcmksDatabaseName。（數(shù)據(jù)庫名稱）Port Number:$ksEbcmksPortNumber。（數(shù)據(jù)庫端口號）User Name:$ksEbcmksUserName。（數(shù)據(jù)庫用戶名）Password:$ksEbcmksPassword。（數(shù)據(jù)庫密碼）說明：使用$value表示的value值，都是在perties文件中配置的變量名。配置格式為：ksEbcmksDatabaseName=ebcmks。配置好上面的信息之后，可以用“Test”按鈕進行測試，如果連接成功，單擊“OK”按鈕。圖5在圖4中的Sql面板中輸入“

12、select * from t_emop_wzxx where isNUll(flag,'0')<>'1'”可以通過“預(yù)覽”按鈕，查看數(shù)據(jù)，如果數(shù)據(jù)量比較大的情況下可能會報錯，此時應(yīng)把“表輸入”的“記錄數(shù)量限制”修改為100,正式轉(zhuǎn)換時應(yīng)把值修改為0，單擊“OK”。與“表輸入”同理，把左邊菜單中的“表輸出”用鼠標(biāo)拖到主界面中，按住Shift鍵，用鼠標(biāo)把“表輸入”與“表輸出”連接起來，如圖5。圖5“表輸出”的功能就是往表里面新增數(shù)據(jù)，在圖5中雙擊“表輸出”，在彈出的窗口中單擊“新建”按鈕，創(chuàng)建目標(biāo)數(shù)據(jù)連接信息（與源目標(biāo)連接信息同理）,配置信息如下，如圖

13、6：Connection Name:datacenter_ks。Connection Type:MS Sql Server。Host Name:通過Ctrl+Alt+Space(空格)，選擇ksDatacenter_ksHostName（也可直接輸入）。Database Name:$ksDatacenter_ksDatabaseName。Port Number:$ksDatacenter_ksPortNumber。User Name:$ksDatacenter_ksUserName。Password:$ksDatacenter_ksPassword。圖6表輸出配置信息如下（圖7）：數(shù)據(jù)庫連接選

14、為：datacenter_ks。目標(biāo)表選擇：t_emop_wzxx。Specify database fileds前面的勾打上。圖7單擊圖7中的Database fileds選項卡，如圖8，單擊Enter filed mapping,在彈出框中單擊“猜一猜”，會把相同的字段匹配上，單擊“確定”。圖8把左邊菜單中的“腳本”下的“執(zhí)行SQL腳本”拖到主界面中，并通過鼠標(biāo)把“表輸出”與“執(zhí)行SQL腳本”連接起來，如圖9。圖9“執(zhí)行SQL腳本”的功能就是執(zhí)行里面寫sql語句，雙擊“執(zhí)行SQL腳本”，配置如下信息（圖10）：數(shù)據(jù)庫連接：ebcmks。Sql面板中輸入：update t_emop_wzxx

15、 set flag='1' where uuid='?'?！皥?zhí)行每一行”前面的勾打上?！白兞刻鎿Q”前面的勾打上。 參數(shù)中：選中UUID。圖10運行該轉(zhuǎn)換文件，單擊左上角的按鈕啟動，此時下面會顯示執(zhí)行結(jié)果信息，如圖11。圖114.2.2.使用時間戳實現(xiàn)新增或修改數(shù)據(jù)。如：傳輸前一天的數(shù)據(jù)。單擊菜單項的文件->新建->轉(zhuǎn)換，創(chuàng)建一個轉(zhuǎn)換文件；保存文件名為：XCJC路徑為主目錄下的resourceskstransformationsXCJC.ktr；右鍵選擇“轉(zhuǎn)換設(shè)置”，把轉(zhuǎn)換名字修改為：現(xiàn)場檢查；如圖12，“字段選擇”在左邊“轉(zhuǎn)換”菜單下，“插入或更新”

16、在左邊“輸出”菜單下。 圖12“表輸入”配置信息，只查詢前昨天的數(shù)據(jù)，如圖13。圖13“字段選擇”的功能可以把前面的字段重命名，把與目標(biāo)庫不同的字段名，重命名為目標(biāo)庫的字段名；其配置信息，如圖14，圖14“插入或更新”的功能是根據(jù)關(guān)鍵字找對應(yīng)的記錄，如果找不到則執(zhí)行新增，否則執(zhí)行更新，其配置信息，如圖15，用來查詢的關(guān)鍵字就是用來比較的字段，通過“獲取和更新字段”按鈕，來獲取更新字段；更新字段就是更新目標(biāo)表的字段，表字段：目標(biāo)表的字段；流字段：前一步驟傳過來的字段；Update:是否更新。 圖154.2.3.使用時間比較實現(xiàn)新增或修改數(shù)據(jù)。如：根據(jù)修改時間來判斷數(shù)據(jù)是否需要傳輸，只有當(dāng)源數(shù)據(jù)的

17、修改時間大于目標(biāo)數(shù)據(jù)的修改時間時就修改這些數(shù)據(jù)或源數(shù)據(jù)是新增數(shù)據(jù)時就新增這些數(shù)據(jù)到目標(biāo)數(shù)據(jù)庫。單擊菜單項的文件->新建->轉(zhuǎn)換，創(chuàng)建一個轉(zhuǎn)換文件；保存文件名為：WRYJBXX路徑為主目錄下的resourceskstransformationsWRYJBXX.ktr；右鍵選擇“轉(zhuǎn)換設(shè)置”，把轉(zhuǎn)換名字修改為：污染源基本信息；如圖16。“插入”：在左邊“輸入”菜單下。“數(shù)據(jù)庫查詢”：在左邊“查詢”菜單下。“過濾記錄”：在左邊“Flow”菜單下。“空操作”：在左邊“Flow”菜單下。“插入或更新”：在左邊“輸出”菜單下。 圖16表輸入配置信息，如圖17。圖17數(shù)據(jù)庫查詢就是把前一步驟的數(shù)據(jù)

18、通過關(guān)鍵字與別的庫中的記錄進行比較，如圖18，等價于下面查詢語句括號里面的內(nèi)容：Select wrybh,wrymc,(select wrybh from datacenter_ks.t_wry_jbxx where wrybh=wry.wrybh and xgsj>=wry.xgsj) as newwrybh from t_wry_jbxx wry。查詢所需的關(guān)鍵字：與上邊括號中where后邊的查詢條件相同。查詢表返回的值：與上邊括號的值的別名相同。圖18“過濾記錄”就是對前面?zhèn)鬟^來的值進行判斷，如圖19。 當(dāng)NEWWRYBH為空時執(zhí)行插入/更新操作，否則執(zhí)行空操作。 圖19“空操作（

19、什么也不做）”表示一個提示信息，不執(zhí)行任何操作。“插入或更新”如圖20。圖204.2.4.使用對某一數(shù)據(jù)值比較實現(xiàn)新增或修改數(shù)據(jù)。如：通過目標(biāo)數(shù)據(jù)表的BH的最大值，作為源數(shù)據(jù)的比較值，來更新或修改數(shù)據(jù)。單擊菜單項的文件->新建->轉(zhuǎn)換，創(chuàng)建一個轉(zhuǎn)換文件；保存文件名為：GZLCSL路徑為主目錄下的resourceskstransformationsGZLCSL.ktr；右鍵選擇“轉(zhuǎn)換設(shè)置”，把轉(zhuǎn)換名字修改為：工作流程實例；如圖21。圖21獲取編號最大值，配置信息如圖22,查詢的是目標(biāo)數(shù)據(jù)庫的信息。圖22表輸入，配置信息如圖23。Sql面板的語句為：“SELECT * FROM T_W

20、ORKFLOW_GZLCSL WHERE BH>?”。替換Sql語句里的變量：前面的勾打上。從步驟插入數(shù)據(jù)：選擇獲取編號最大值。執(zhí)行每一行：前面的勾打上。圖23插入或更新，配置信息如圖24。圖244.3.創(chuàng)建任務(wù)文件。單擊菜單項的文件->新建->作業(yè)，創(chuàng)建一個任務(wù)文件；保存文件名為：ebcmks路徑為主目錄下的resourcesksjobsebcmks.kjb；右鍵選擇“作業(yè)設(shè)置”，把Job名字修改為：昆山轉(zhuǎn)換任務(wù)；配置如圖25的信息。Start:在左邊“通用”菜單下。Transformation,Transformation2,Transformation3,Transformation4:在左邊“通用”菜單下。Success：在左邊“通用”菜單下。圖25Start配置信息如圖26。重復(fù)：前面的勾如果打上，表示任務(wù)會循環(huán)執(zhí)行。類型：任務(wù)執(zhí)行方式。Time of day:1.表示每天凌晨1點執(zhí)行。圖26Transformation配置信息如圖27。Job名稱修改為：軌跡信息，其中轉(zhuǎn)換文件名的格式為：$KETTLE_HOMEresources項目名transformations轉(zhuǎn)換文件；如：$KETTLE_HOMEresourceskstransformationsWZXX.ktr。Transf