kettle公司内部培训手册.doc

1、entakerKettle培训手册一、Etl介绍ETL （Extract-Transform-Load的缩写，即数据抽取、转换、装载的过程），对于 金融IT来说，经常会遇到大数据量的处理，转换，迁移，所以了解并掌握一种 etl工具的使用，必不可少。Kettle是一款国外开源的etl工具，纯java编写，绿色无需安装，数据抽取高 效稳定。Kettle中有两种脚本文件，transformation和job, transformation成针 对数据的基础转换，job则完成整个工作流的控制。二、kettle部署运行将kettle2.5.1文件夹拷贝到本地路径，例如 D盘根目录。双击运行kettle文

2、件夹下的spoon.ba文件，出现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 对 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啊团倒 Uc«fisa 电 dlrlbuEBdm anISh btil% VWTHOLn1 憾RRAMTM OF ANVttthar &#

4、171;qmw orinriplltd Pi4>M f窗 Ed th* hentt for 1h«由 goy4rhiyou门nphEt的 d llmitflilona.稍等几秒选择没有资源库，打开kettle主界面仓UIt transformation, job点击页面左上角的创建一个新的transformation,点击保存到本地路径，例如保存到D:/etltest下，保存文件名为EtltestTrans, kettle默认transformation 文件保存后后缀名为ktr点击页面左上角的创建一个新的job,点击保存到本地路径，例如保存到D:/etltest下，保存文件名

5、为EtltestJob, kettle默认job文件保存后后缀名为kjb创建数据库连接在transformations面下，点击左边的【Main Tree,双击【DB连接】,进行 数据库连接配置。connection nameg命名连接名称Connection type择需要连接的数据库Method of acces选择连接类型Server host namei1入数据库服务器的ip地址Database name?入数据库名Port numbeiW入端口号Usernames入用户名Password入密码例如如下配置：点击【test,如果出现如下提示则说明配置成功文件漏辑赞询即秘了心向守都聃一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-节点连与；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历史点击关闭，再点击确定保存数据库连接。一个简单的ktr例子目的：将一个数据库导入到另一个数据库中。操作步骤：创建一个transformation,命名为etlTestTrans.ktr,创建数据库连接ods,点击【Input,选中【表输入】，拖到主窗口，释放鼠标，双击打

8、开如下图点击【Transform,选中【字段选择】，拖到主窗口，释放鼠标点击【Output】，选中【表输出】，拖到主窗口，释放鼠标强输入1船建立【文本文件输入】和【字段选择】与【字段选择】和【表输出】的连接表输油双击【表输出】，目标表中写入 ZT_TEST_KETTLE ,确定保存双击【字段选择】，点击 获取选择的字段，再点击Edit Mapping,点击OK 确定，编辑所有字段对应关系，点确定。点击成运行这个转换。，则将上一个ktr中生成的文本，导入到数据库当中。一个简单的kjb例子目的：将上一个transformation4一个job里面调用执行。操作步骤：在 etlTestJotM 面，

9、点击【Core Objects!，点击【Job entries1,选中【START 拖动到主窗口释放鼠标，再选中【Transformation,拖动到主窗口释放鼠标， 建立【START和Transformation之间的连接。双击 Transformation , 在Transformation filename 中写入E:kettleWorkspaceetlTestTrans.ktr;确定保存。点击回保存创建好的jobEtsyt点击IM运行这个转换。待所有任务都显示成功，则为job调用transformational行成功任冬/任务泉目注释结果原因-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 -任筠巴鲤结束一S的rhd S 如宅卬3 (cQnwnit=lQQ)正在开始运行.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一个增量的例子增量更新按照数据种类的不同大概可以分成：1 .只增加，不更新，2 .只更新，不增加3 .即增加也更新4 .有删除，

13、有增加，有更新下面针对前三种做一个增量的 ETL抽取。过程如下：根据前面讲解的例子一样，首先建立源表（fina_test1）和目标表（巾na_test2）,整个 设计流程如下：输入-目标表输入-海裴其中第一个步骤（输入-目标表）的sql大概如下模式：select ifnull（max（date_seal）,'1900-01-01 00:00:00'） from fina_test2你会注意到第二个步旗和第一个步骤的连接是黄色的线，这是因为第二个table input（输入-源表）步骤把前面一个步骤的输出当作一个参数来用，所有Kettle用黄色的线来表示，第二个table inp

14、ut （输入-源表）的sql模式大概如下：SELECT * FROM fina_test1 where date_seal>?后面的一个问号就是表示它需要接受一个参数，你在这个table input （输入-源表）下面需要指定replace variable in script选项和 执行每一行 为选中状态，这样，Kettle 就会循环执行这个sql ,执行的次数为前面参数步骤传入的数据集的大小。Enable isnvers ion LI Replace variables in script? H从步骤插入数据输人-目标表执行每一行？ 0记录数量限制口关于第三个步骤执行插入/更新步骤需

15、要特别解释一下,冬播A/更猫二叁杳宇觥,克手就5 吊LirmjTIWUmT'OTFT22T CMEZ1 CODEI劣rrjr回rr vmr1U阳hIE UNGIkSI® USE LEVIL Dj1<115WT.MnTTLJJiT 艮T6LiA7£j；liALi|railBT®ZT三才宇各前印招用打字印Kettle执行这个步骤是需要两个数据流对比，其中一个是目标数据库，你在 目标表 里面指定的，它放在用来查询的关键字左边的表字段里面的，另外一个数据流就是 你在前一个步骤传进来的，它放在用来查询的关键字的右边，Kettle首先用你传进 来的key在数据库

16、中查询这些记录，如果没有找到，它就插入一条记录，所有的值 都跟你原来的值相同，如果根据这个key找到了这条记录，kettle会比较这两条记录， 根据你指定update field来比较，如果数据完全一样，kettle就什么都不做，如果记 录不完全一样，kettle就执行一个update步骤。备注：主键被修改得数据认为是新记录删除的数据由在仓库中需要保留无需考虑:=横心对装J-qT> entriesjob entries 拉出组然后点击新建-job,然后job的核心对象 建，进行执行抽取。etlTestZL创建kettle资料库资源库是用来保存转换任务的，用户通过图形界面创建的的转换任务可

17、以保存在资源库中。资源库可以是各种常见的数据库，用户通过用户名/密码来访问资源库中的资源，默认的用户名/密码是admin/admin资源库并不是必须的，如果没有资源库，用户还可以把转换任务保存在xml文件中。如果用户需要创建一个资源库，在资源库的登录窗口（ PDI启动时的第一个窗欢迎使用Kettle-赍源库 登十 密码中有 【新建】 按钮，点击该按钮弹出新建资源库窗口,在该窗口中选择一个数据库连接，如果没有事先定义的数据库连接，则还要点 击【新建】按钮，来创建一个数据库连接。选择数据库连接后，要为该资源库命名, 作为这个资源库的唯一标志，囹法源库信息-n x逸择数据库连接 拉*画图 画囿 照名

18、称贯源库 描述簸创建或更新Sb I 股消最后选择【创建或更新】按钮来创建这个资源库。资源库可以使多用户共享转换任务，转换任务在资源库中是以文件夹形式分组 管理的，用户可以自定义文件夹名称。如何使用kettle读取包含多行表的 Excel文件如果Excel工作表的表头只有一行，使用Kettle读取这样的文件是很容易的.如果Excel工作表的表头是多行的，或者是分级的就需要在内容标签下正确设置列名所占 行数才可以读取.考虑这样的一个工作表OOO 2O CO2OOO2O002OOO 20 O02OO 02OOU2200 OU2OOO2OOU220000120000220000320000420000

19、5200006200007200003200009200010200011200012IXkECDEYFBHNFBMYFJICSXH如果想把里面的12列数据都读出来，就要考虑如何处理多级表头步骤设置的详细描述：步骤一 选择文件名，现在文件或目录里浏览鼠一到所要添加的excel文档，然后点击,增加确定后，点击士舞名荷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源码 步骤二选择要读取的工作表名称和要读取的内容在工作表里的起始位置，也就是表头开始的行号和列号（这里行号和列号是以0开始的）步骤三设置要读取的内容的一些属性，这里要设置表头的所占行数是4行.步骤告存Irel tt?i.空件工忙衰为看楮汉显直字盘回F1匚头部非亚承住在空记示T件名新停转用制茄丹加0禾E件总回律定现11里泊峪JL现宛记录一步骤四错误处理，选择如果有错误终止还是继续，错误信息保存的文件等 （图略）步骤五选择字段，如果前面的三个步骤（不包括错误处理步骤）都设置正确，在这个页面选

21、择"获取字段"字段按钮，就会获得所有的列名称和数据类型.这里我们可以看到：多级表头中各级表头的名称被叠加起来，形成了唯一的列名.对于表头跨连续的多行，但不分级的情况也可以使用上述方式处理kettle 注释：1、 kettle的控制流可以设置一些简单的时间，并且可以实现隔断天、周、月（三个只能选一个，不能选那个月的那周那日），但是 kettle工具不能关，如果关了，必须 重新启动。sql中进2、 kettle里面缺少一个编辑的字段的插件，导致字段编辑很麻烦，这只能先 行手写，这个对写sql的要求很高。一个kettle字段转换（截取）的例子大致的流程是：i=i *表输入Jkva

22、 Script Value 字段茂寿表输出表输入还是正常的sql查询，没有添加参数MccIl £i ed J ava Scrip! Value字段转换（截取）是在进行修改。具体样式如下:Ji1fFl尸t rrdB" Ffim § ti 1件 sQ Tit的二Ear :：皿匹15位 i Tr4D.TtWH ，9归 It CM3 M环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户方工近飞”。if巾：成1Tgi “IB 口3 =3/M «rl(" = up Kt 4 !_3QT_niJEj j/Alcri T如侬 J * IT_向她);/£Jwi iT-uptul '+ ZT 皿1卫门：* * si机1皿!皿为肘lewm + 酝 IHJIU)匚支IE； J;i C * + - A'JE._SUdJ .行号aCmf sub i 1 rljf rir*i 画T&.i酝r ccni2Zr_&#

25、163;DDElsf iwyete5 电加 jJWELj计SBEL Bin UI DA.T1A- - V-号向©|_户不累呈，电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写-+ Input fields4 Output fi tl dst

26、ransform Functions里面包括了字符、数字的一些函数方法,这些函数方法可以解决一些字段需要转化的问题。-+ 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,来的字段（数据）。字段主要转化的操作界面: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.莽强日。；vaj iDEZl = EDB3, gatlntQgar Q ;i£ 3DBZl0)BDBZ1=1 el5eBDBZl=O)/Alert C input： fr + UBIT_0ODE1);/