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

1、entakerKettle培訓(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盤(pán)根目錄。雙擊運(yùn)行kettle文

2、件夾下的spoon.ba文件，出現(xiàn)kettle歡迎界面:source business intelligence?L,Penta ho Data IntegrationPreviously KettleCop/right 200 P日ntah。CorpaMlon All rights TerMN日dOriglnBlIy developEd by Pantaho CorporatiDn proruldad under itie termi 對(duì) Ihe GNU Lesser Gen erai Pu&hc LK：enmg ILQPL), Vera： 2.1, or eny later ver

3、aim. You may rwtu&a ttira file except In compliancs with the hrjanee. IT you nesd m copy of trie 肥日旭日1pk)日和 to hHp:fww©nu.ur#liQerGgpl.tKL The Initial DsvEjoper Pentahn Carporabon.diatrlbulBd undar ihe <jNU L&s啊團(tuán)倒 Uc«fisa 電 dlrlbuEBdm anISh btil% VWTHOLn1 憾RRAMTM OF ANVttthar &#

4、171;qmw orinriplltd Pi4>M f窗 Ed th* hentt for 1h«由 goy4rhiyou門(mén)nphEt的 d llmitflilona.稍等幾秒選擇沒(méi)有資源庫(kù)，打開(kāi)kettle主界面?zhèn)}UIt 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下，保存文件名

5、為EtltestJob, kettle默認(rèn)job文件保存后后綴名為kjb創(chuàng)建數(shù)據(jù)庫(kù)連接在transformations面下，點(diǎn)擊左邊的【Main Tree,雙擊【DB連接】,進(jìn)行 數(shù)據(jù)庫(kù)連接配置。connection nameg命名連接名稱(chēng)Connection type擇需要連接的數(shù)據(jù)庫(kù)Method of acces選擇連接類(lèi)型Server host namei1入數(shù)據(jù)庫(kù)服務(wù)器的ip地址Database name?入數(shù)據(jù)庫(kù)名Port numbeiW入端口號(hào)Usernames入用戶(hù)名Password入密碼例如如下配置：點(diǎn)擊【test,如果出現(xiàn)如下提示則說(shuō)明配置成功文件漏輯贊詢(xún)即秘了心向守都聃一3

6、 fl 2降q即lain TietU Spoon et 11 e st I rails3= VTansf < rn ati «i.3Conncctioii in£axn.atioili- ftl"estTr4Ji.5-0，ops» £t/"步驟f-節(jié)點(diǎn)連與；fil iataHse jar i.i Lio £L»ad t arcr jtatlLL 4t cL,tcrC<tfiLj il P&oliB. ySQL Ujf&tl. Inloi'irsx £AE Jt»

7、;!3 仇氨上亡 JpHgt EQL tl電：LerConnietieat taHostnameBort gta±l 三尋 H9AEd3b“整0D6 is DK172.L7 192 191521orcl 25 on 硼。Explut cFertile *e Lin取消Cvr e Olii«GlsW豐龔wa歷史點(diǎn)擊關(guān)閉，再點(diǎn)擊確定保存數(shù)據(jù)庫(kù)連接。一個(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)，雙擊打

8、開(kāi)如下圖點(diǎn)擊【Transform,選中【字段選擇】，拖到主窗口，釋放鼠標(biāo)點(diǎn)擊【Output】，選中【表輸出】，拖到主窗口，釋放鼠標(biāo)強(qiáng)輸入1船建立【文本文件輸入】和【字段選擇】與【字段選擇】和【表輸出】的連接表輸油雙擊【表輸出】，目標(biāo)表中寫(xiě)入 ZT_TEST_KETTLE ,確定保存雙擊【字段選擇】，點(diǎn)擊 獲取選擇的字段，再點(diǎn)擊Edit Mapping,點(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è)transformation4一個(gè)job里面調(diào)用執(zhí)行。操作步驟：在 etlTestJotM 面，

9、點(diǎn)擊【Core Objects!，點(diǎn)擊【Job entries1,選中【START 拖動(dòng)到主窗口釋放鼠標(biāo)，再選中【Transformation,拖動(dòng)到主窗口釋放鼠標(biāo)， 建立【START和Transformation之間的連接。雙擊 Transformation , 在Transformation filename 中寫(xiě)入E:kettleWorkspaceetlTestTrans.ktr;確定保存。點(diǎn)擊回保存創(chuàng)建好的jobEtsyt點(diǎn)擊IM運(yùn)行這個(gè)轉(zhuǎn)換。待所有任務(wù)都顯示成功，則為job調(diào)用transformational行成功任冬/任務(wù)泉目注釋結(jié)果原因-etlTestJob任善:HlTrtJob

10、St ar t q£ job exwmtLomSt 4rtJob «ntry starttdstartSt 4rtJdh intry 也nd&d成功etlTestTr &Ik5Job entry startedFollowedtlTestTr axisJob entry ended成功隹寥: etlTastJcilJolb execiLti on eitdled,成功uilcoai _ £008/12/30 2003/120 EOOa/lEfSO 2000/12/30 Z008/12/30 2000/12/30 2008/12/30 2003/12

11、/30 2008/12/30 2003/12/30 2008/120008:43:03 - ttlTtslTruit -正在布始化 3 步驟.00；43；。3 -表輸出,。- 08:43:03 -手段選擇0 08:43:03 -表摘入.0 - 08:43:03 -聲拓 t- 08:43:03 -塞輸又口 - 08:43:03 -至覦翳 0 00:43:03 -表輸入一0 - 08:43:03 -神(出< - 00:43:03 - ttlTestlob08:43:03 - Chef -任筠巴鯉結(jié)束一S的rhd S 如宅卬3 (cQnwnit=lQQ)正在開(kāi)始運(yùn)行.Starting to r

12、un. s .Starting1 to fuiib -,Fi ill sked. r e aidi ng ixeiryj, cl o s i zxg <c orute c -l i on.-完成處理1CT=0.。二Q 氏。留二d IT二6E=0完成如通 a=o, o=c; r=ot i=o; u=o/ e=d完成處理 0=0, 0W,R-0, n=d UR, E=D-Finished j obtnlry EetllestTr an (result- true J一個(gè)增量的例子增量更新按照數(shù)據(jù)種類(lèi)的不同大概可以分成：1 .只增加，不更新，2 .只更新，不增加3 .即增加也更新4 .有刪除，

13、有增加，有更新下面針對(duì)前三種做一個(gè)增量的 ETL抽取。過(guò)程如下：根據(jù)前面講解的例子一樣，首先建立源表（fina_test1）和目標(biāo)表（巾na_test2）,整個(gè) 設(shè)計(jì)流程如下：輸入-目標(biāo)表輸入-海裴其中第一個(gè)步驟（輸入-目標(biāo)表）的sql大概如下模式：select ifnull（max（date_seal）,'1900-01-01 00:00:00'） from fina_test2你會(huì)注意到第二個(gè)步旗和第一個(gè)步驟的連接是黃色的線，這是因?yàn)榈诙€(gè)table input（輸入-源表）步驟把前面一個(gè)步驟的輸出當(dāng)作一個(gè)參數(shù)來(lái)用，所有Kettle用黃色的線來(lái)表示，第二個(gè)table inp

14、ut （輸入-源表）的sql模式大概如下：SELECT * FROM fina_test1 where date_seal>?后面的一個(gè)問(wèn)號(hào)就是表示它需要接受一個(gè)參數(shù)，你在這個(gè)table input （輸入-源表）下面需要指定replace variable in script選項(xiàng)和 執(zhí)行每一行 為選中狀態(tài)，這樣，Kettle 就會(huì)循環(huán)執(zhí)行這個(gè)sql ,執(zhí)行的次數(shù)為前面參數(shù)步驟傳入的數(shù)據(jù)集的大小。Enable isnvers ion LI Replace variables in script? H從步驟插入數(shù)據(jù)輸人-目標(biāo)表執(zhí)行每一行？ 0記錄數(shù)量限制口關(guān)于第三個(gè)步驟執(zhí)行插入/更新步驟需

15、要特別解釋一下,冬播A/更貓二叁杳宇觥,克手就5 吊LirmjTIWUmT'OTFT22T CMEZ1 CODEI劣rrjr回rr vmr1U陽(yáng)hIE UNGIkSI® USE LEVIL Dj1<115WT.MnTTLJJiT 艮T6LiA7£j；liALi|railBT®ZT三才宇各前印招用打字印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ù)

16、中查詢(xún)這些記錄，如果沒(méi)有找到，它就插入一條記錄，所有的值 都跟你原來(lái)的值相同，如果根據(jù)這個(gè)key找到了這條記錄，kettle會(huì)比較這兩條記錄， 根據(jù)你指定update field來(lái)比較，如果數(shù)據(jù)完全一樣，kettle就什么都不做，如果記 錄不完全一樣，kettle就執(zhí)行一個(gè)update步驟。備注：主鍵被修改得數(shù)據(jù)認(rèn)為是新記錄刪除的數(shù)據(jù)由在倉(cāng)庫(kù)中需要保留無(wú)需考慮:=橫心對(duì)裝J-qT> entriesjob entries 拉出組然后點(diǎn)擊新建-job,然后job的核心對(duì)象 建，進(jìn)行執(zhí)行抽取。etlTestZL創(chuàng)建kettle資料庫(kù)資源庫(kù)是用來(lái)保存轉(zhuǎn)換任務(wù)的，用戶(hù)通過(guò)圖形界面創(chuàng)建的的轉(zhuǎn)換任務(wù)可

17、以保存在資源庫(kù)中。資源庫(kù)可以是各種常見(jiàn)的數(shù)據(jù)庫(kù)，用戶(hù)通過(guò)用戶(hù)名/密碼來(lái)訪問(wèn)資源庫(kù)中的資源，默認(rèn)的用戶(hù)名/密碼是admin/admin資源庫(kù)并不是必須的，如果沒(méi)有資源庫(kù)，用戶(hù)還可以把轉(zhuǎn)換任務(wù)保存在xml文件中。如果用戶(hù)需要?jiǎng)?chuàng)建一個(gè)資源庫(kù)，在資源庫(kù)的登錄窗口（ PDI啟動(dòng)時(shí)的第一個(gè)窗歡迎使用Kettle-赍源庫(kù) 登十 密碼中有 【新建】 按鈕，點(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)志，囹法源庫(kù)信息-n x逸擇數(shù)據(jù)庫(kù)連接 拉*畫(huà)圖 畫(huà)囿 照名

18、稱(chēng)貫源庫(kù) 描述簸創(chuàng)建或更新Sb I 股消最后選擇【創(chuàng)建或更新】按鈕來(lái)創(chuàng)建這個(gè)資源庫(kù)。資源庫(kù)可以使多用戶(hù)共享轉(zhuǎn)換任務(wù)，轉(zhuǎn)換任務(wù)在資源庫(kù)中是以文件夾形式分組 管理的，用戶(hù)可以自定義文件夾名稱(chēng)。如何使用kettle讀取包含多行表的 Excel文件如果Excel工作表的表頭只有一行，使用Kettle讀取這樣的文件是很容易的.如果Excel工作表的表頭是多行的，或者是分級(jí)的就需要在內(nèi)容標(biāo)簽下正確設(shè)置列名所占 行數(shù)才可以讀取.考慮這樣的一個(gè)工作表OOO 2O CO2OOO2O002OOO 20 O02OO 02OOU2200 OU2OOO2OOU220000120000220000320000420000

19、5200006200007200003200009200010200011200012IXkECDEYFBHNFBMYFJICSXH如果想把里面的12列數(shù)據(jù)都讀出來(lái)，就要考慮如何處理多級(jí)表頭步驟設(shè)置的詳細(xì)描述：步驟一 選擇文件名，現(xiàn)在文件或目錄里瀏覽鼠一到所要添加的excel文檔，然后點(diǎn)擊,增加確定后，點(diǎn)擊士舞名荷Exceil描.丁七和肉客隹理工苴宇殷文性或目錄|-第境力匚|訶流&視口岸武五色中血徉tWS7目錄通1 C We-CMfleiits = d. S e Ltirss Adni hi s-ti <1LLeBIJ JIT A ids訪,*1* 至,“前斑哥嘴后盟¥

20、許行n前面的本嘿在貶件名nhwit方牌律旬+老I7俁存至"名更用投名|理定'而一1|篇0源碼 步驟二選擇要讀取的工作表名稱(chēng)和要讀取的內(nèi)容在工作表里的起始位置，也就是表頭開(kāi)始的行號(hào)和列號(hào)（這里行號(hào)和列號(hào)是以0開(kāi)始的）步驟三設(shè)置要讀取的內(nèi)容的一些屬性，這里要設(shè)置表頭的所占行數(shù)是4行.步驟告存Irel tt?i.空件工忙衰為看楮漢顯直字盤(pán)回F1匚頭部非亞承住在空記示T件名新停轉(zhuǎn)用制茄丹加0禾E件總回律定現(xiàn)11里泊峪JL現(xiàn)宛記錄一步驟四錯(cuò)誤處理，選擇如果有錯(cuò)誤終止還是繼續(xù)，錯(cuò)誤信息保存的文件等 （圖略）步驟五選擇字段，如果前面的三個(gè)步驟（不包括錯(cuò)誤處理步驟）都設(shè)置正確，在這個(gè)頁(yè)面選

21、擇"獲取字段"字段按鈕，就會(huì)獲得所有的列名稱(chēng)和數(shù)據(jù)類(lèi)型.這里我們可以看到：多級(jí)表頭中各級(jí)表頭的名稱(chēng)被疊加起來(lái)，形成了唯一的列名.對(duì)于表頭跨連續(xù)的多行，但不分級(jí)的情況也可以使用上述方式處理kettle 注釋?zhuān)?、 kettle的控制流可以設(shè)置一些簡(jiǎn)單的時(shí)間，并且可以實(shí)現(xiàn)隔斷天、周、月（三個(gè)只能選一個(gè)，不能選那個(gè)月的那周那日），但是 kettle工具不能關(guān)，如果關(guān)了，必須 重新啟動(dòng)。sql中進(jìn)2、 kettle里面缺少一個(gè)編輯的字段的插件，導(dǎo)致字段編輯很麻煩，這只能先 行手寫(xiě)，這個(gè)對(duì)寫(xiě)sql的要求很高。一個(gè)kettle字段轉(zhuǎn)換（截?。┑睦哟笾碌牧鞒淌牵篿=i *表輸入Jkva

22、 Script Value 字段茂壽表輸出表輸入還是正常的sql查詢(xún)，沒(méi)有添加參數(shù)MccIl £i ed J ava Scrip! Value字段轉(zhuǎn)換（截?。┦窃谶M(jìn)行修改。具體樣式如下:Ji1fFl尸t rrdB" Ffim § ti 1件 sQ Tit的二Ear :：皿匹15位 i Tr4D.TtWH ，9歸 It CM3 M環(huán)41二lirjT.COU三 C1.s txin ZT_Ctn)£ 后6：亡1± 匚址1 ZTJ哂E £?tS(ri»I I SUB «ME JVtLJW REC_WILEi_IJi(.TE

23、mm口-thilpcrtUWltjUni. xvlValua ZT. COSE 3sHYUm、 事則E 小G5TEJME 二TYEJEDE HEC_MflLD_Ila.jy “I DAII JEAL 3«V/c BI BE tell，口.金！> m J| JF 箏 cn 1t i2 l出一扇其1 ; imr_caD- gisuiw lrw ZTcnniE 蕾NT.B陰Tffl-匯1增EL 二 .” ffiTjfi=rE. cctlriacOr ：，J >.tfl LU阻L GDI ££方邦帕士川.近3，工一：曬_口兇電 亂(>, rar7iirj

24、pii: = secuilo Iiate 5二mm工ru CJ.TE SLJJ UUH 口心 H 哺- ElBIi = HDEI2戶(hù)方工近飛”。if巾：成1Tgi “IB 口3 =3/M «rl(" = up Kt 4 !_3QT_niJEj j/Alcri T如儂 J * IT_向她);/£Jwi iT-uptul '+ ZT 皿1衛(wèi)門(mén)：* * si機(jī)1皿!皿為肘lewm + 醞 IHJIU)匚支IE； J;i C * + - A'JE._SUdJ .行號(hào)aCmf sub i 1 rljf rir*i 畫(huà)T&.i醞r ccni2Zr_&#

25、163;DDElsf iwyete5 電加 jJWELj計(jì)SBEL Bin UI DA.T1A- - V-號(hào)向©|_戶(hù)不累呈，電I隹同*市 仁二具體的用法:+ Trsnsform Sciripts-+ Tf孫!e£m,面 Const antsS Tr 皿s for閽 F皿utiDnwr由+String Functions Bmneric Functioiis Date functions L«gA c Functi oiks Spec i al Fnnet i oiki File Func七.*n寫(xiě)-+ Input fields4 Output fi tl dst

26、ransform Functions里面包括了字符、數(shù)字的一些函數(shù)方法,這些函數(shù)方法可以解決一些字段需要轉(zhuǎn)化的問(wèn)題。-+ Tirajisform Scxipts+3日T廣5 £O rim C QK515t 5 TransEorim Functi ojis Input Ei llsUBIT_CODE, Z7_CDEE. gfttStrifkgC Z7_llAWE. fttStrinC SUBJ»E_LEYEL_CODE HC-BUILDJATE.estDATE_5EM. getD ile CBJBZ ge(.Integer 0-Output fields111inJTT.CO

27、DE. set/alue S7_C01E. sfitValu« (r IT_HAIIE, “tValiwN nJB_Om_LEVEL_CODE KEC_BinU)_IArE.s.t DATE_SEAL. setValue BTRZ. StVali-ie l.var)11,來(lái)的字段（數(shù)據(jù)）。字段主要轉(zhuǎn)化的操作界面:Input fields 和 Output fields里面包括了從表輸入表輸入iT Script 1 送"Script here3 UUT COTE! = UHT CODE. £etSUinC();，皿 2T_CaDEl =3T_C0DE.0；var Z

28、T_1IAME1 -substr UT_HANIE. gfetString (), 1 . 2);var SUL_nNE_LZVEL_COBE_LENGTM 1 =SUB_ONE_EEVIL_CODE_LENGTH. getlnteger 0 ；Taj MC_BULLD_DATE1 = REC_BIHLD_DATE. cetDateO.YU DATE_SEALT = DATE_SEAL.莽強(qiáng)日。；vaj iDEZl = EDB3, gatlntQgar Q ;i£ 3DBZl0)BDBZ1=1 el5eBDBZl=O)/Alert C input： fr + UBIT_0ODE1);/