kettle入门例子大全.docx
《kettle入门例子大全.docx》由会员分享，可在线阅读，更多相关《kettle入门例子大全.docx（59页珍藏版）》请在冰点文库上搜索。
kettle入门例子大全
Kettle培训技术文档0507
Etl介绍
ETL（Extract-Transform-Load的缩写，即数据抽取、转换、装载的过程），对于
金融IT来说,经常会遇到大数据量的处理，转换，迁移，所以了解并掌握一种
etl工具的使用，必不可少。
Kettle是一款国外开源的etl工具，纯java编写，绿色无需安装，数据抽取高
效稳定。
Kettle中有两种脚本文件，transformation和job,transformation完成针
对数据的基础转换，job则完成整个工作流的控制。
kettle部署运行
将kettle2.5。
1文件夹拷贝到本地路径,例如D盘根目录。
双击运行kettle文件夹下的spoon.bat文件，出现kettle欢迎界面:
稍等几秒
选择没有资源库，打开kettle主界面
创建transformation，job
点击页面左上角的创建一个新的transformation，点击保存到本地路
径,例如保存到D:
/etltest下,保存文件名为EtltestTrans，kettle默认transformation
文件保存后后缀名为ktr
点击页面左上角的创建一个新的job，点击保存到本地路径,例如保
存到D：
/etltest下，保存文件名为EtltestJob,kettle默认job文件保存后后缀名为kjb
创建数据库连接
在transformation页面下，点击左边的【MainTree】，双击【DB连接】，进行
数据库连接配置.
connectionname自命名连接名称
Connectiontype选择需要连接的数据库
Methodofaccess选择连接类型
Serverhostname写入数据库服务器的ip地址
Databasename写入数据库名
Portnumber写入端口号
Username写入用户名
Password写入密码
例如如下配置：
点击【test】，如果出现如下提示则说明配置成功
点击关闭，再点击确定保存数据库连接。
一个简单的ktr例子
目的:
将一个数据库导入到另一个数据库中。
操作步骤：
创建一个transformation，命名为etlTestTrans。
ktr，创建数据库连接
ods，点击【Input】，选中【表输入】，拖到主窗口,释放鼠标，双击打
开
如下图
点击【Transform】，选中【字段选择】，拖到主窗口,释放鼠标
点击【Output】，选中【表输出】，拖到主窗口，释放鼠标
建立【文本文件输入】和【字段选择】与【字段选择】和【表输出】的连接
双击【表输出】,目标表中写入ZT_TEST_KETTLE，,确定保存
双击【字段选择】，点击获取选择的字段，再点击EdltMapping，点击OK
确定,编辑所有字段对应关系，点确定。
点击
运行这个转换。
，则将上一个ktr中生成的文本,导入到数据库当中。
一个简单的kjb例子
目的：
将上一个transformation在一个job里面调用执行。
操作步骤：
在etlTestJob页面，点击【CoreObjects】,点击【Jobentries】,选中【START】
拖动到主窗口释放鼠标，再选中【Transformation】，拖动到主窗口释放鼠标，
建立【START】和【Transformation】之间的连接。
双击【Transformation】,在Transformationfilename中写入
E：
\kettleWorkspace\etlTestTrans。
ktr，确定保存。
点击
保存创建好的job。
点击
运行这个转换。
待所有任务都显示成功，则为job调用transformation运行成功。
一个增量的例子
增量更新按照数据种类的不同大概可以分成：
1. 只增加，不更新，
2. 只更新,不增加
3。
即增加也更新
4。
有删除,有增加,有更新
下面针对前三种做一个增量的ETL抽取.过程如下：
根据前面讲解的例子一样，首先建立源表（fina_test1）和目标表（fina_test2）,整个设计流程如下：
其中第一个步骤（输入-目标表）的sql大概如下模式：
selectifnull（max（date_seal），’1900-01-0100:
00:
00'）fromfina_test2
你会注意到第二个步骤和第一个步骤的连接是黄色的线，这是因为第二个tableinput（输入—源表）步骤把前面一个步骤的输出当作一个参数来用,所有Kettle用黄色的线来表示,第二个tableinput（输入—源表）的sql模式大概如下：
SELECT*FROMfina_test1wheredate_seal>?
后面的一个问号就是表示它需要接受一个参数，你在这个tableinput（输入-源表）下面需要指定replacevariableinscript选项和执行每一行为选中状态,这样，Kettle就会循环执行这个sql,执行的次数为前面参数步骤传入的数据集的大小.
关于第三个步骤执行插入/更新步骤需要特别解释一下，
Kettle执行这个步骤是需要两个数据流对比，其中一个是目标数据库，你在目标表里面指定的，它放在用来查询的关键字左边的表字段里面的，另外一个数据流就是你在前一个步骤传进来的,它放在用来查询的关键字的右边，Kettle首先用你传进来的key在数据库中查询这些记录，如果没有找到,它就插入一条记录，所有的值都跟你原来的值相同，如果根据这个key找到了这条记录，kettle会比较这两条记录，根据你指定updatefield来比较，如果数据完全一样,kettle就什么都不做，如果记录不完全一样，kettle就执行一个update步骤.
备注：
主键被修改得数据认为是新记录
删除的数据由在仓库中需要保留无需考虑
然后点击新建—job，然后job的核心对象
jobentries拉出组建，进行执行抽取。
创建kettle资料库
资源库是用来保存转换任务的，用户通过图形界面创建的的转换任务可以保存在资源库中。
资源库可以是各种常见的数据库，用户通过用户名/密码来访问资源库中的资源，默认的用户名/密码是admin/admin
资源库并不是必须的，如果没有资源库，用户还可以把转换任务保存在xml文件中。
如果用户需要创建一个资源库，在资源库的登录窗口（PDI启动时的第一个窗口）
中有【新建】按钮,点击该按钮弹出新建资源库窗口，
在该窗口中选择一个数据库连接，如果没有事先定义的数据库连接，则还要点击【新建】按钮，来创建一个数据库连接。
选择数据库连接后，要为该资源库命名,作为这个资源库的唯一标志,
最后选择【创建或更新】按钮来创建这个资源库。
资源库可以使多用户共享转换任务，转换任务在资源库中是以文件夹形式分组管理的，用户可以自定义文件夹名称。
如何使用kettle读取包含多行表的Excel文件
如果Excel工作表的表头只有一行，使用Kettle读取这样的文件是很容易的。
如果Excel工作表的表头是多行的,或者是分级的就需要在内容标签下正确设置列名所占行数才可以读取。
考虑这样的一个工作表
如果想把里面的12列数据都读出来，就要考虑如何处理多级表头。
步骤设置的详细描述:
步骤一选择文件名，现在文件或目录里
到所要添加的excel文档，然后点击，确定后，点击
步骤二选择要读取的工作表名称和要读取的内容在工作表里的起始位置，也就是表头开始的行号和列号（这里行号和列号是以0开始的）
步骤三设置要读取的内容的一些属性,这里要设置表头的所占行数是4行.
步骤四错误处理,选择如果有错误终止还是继续，错误信息保存的文件等.
（图略）
步骤五选择字段，如果前面的三个步骤（不包括错误处理步骤）都设置正确,在这个页面选择”获取字段"字段按钮，就会获得所有的列名称和数据类型.
这里我们可以看到：
多级表头中各级表头的名称被叠加起来，形成了唯一的列名。
点击［预览]按钮可以预览到数据
对于表头跨连续的多行,但不分级的情况也可以使用上述方式处理.
kettle注释：
1、kettle的控制流可以设置一些简单的时间，并且可以实现隔断天、周、月（三个只能选一个，不能选那个月的那周那日），但是kettle工具不能关，如果关了，必须重新启动.
2、kettle里面缺少一个编辑的字段的插件,导致字段编辑很麻烦，这只能先sql中进行手写，这个对写sql的要求很高。
一个kettle字段转换（截取）的例子
大致的流程是：
表输入还是正常的sql查询，没有添加参数。
字段转换（截取）是在
进行修改。
具体样式如下:
具体的用法：
transformFunctions里面包括了字符、数字的一些函数方法，这些函数方法可以解决一些字段需要转化的问题。
Inputfields和Outputfields里面包括了从表输入
进来的字段（数据）。
字段主要转化的操作界面：
注意下：
substr（xxx,1,2）中的1代表是第一位开始，2代表是取2位,在这里面还可以添加if等语句，进行编写。
在字段选择
那里面要配置从js过来的字段，点击列映射
（前提是已经和表输出连接上），这个字段对应要根据你实际从js倒过来的字段和目标表相对应的字段一一对应。
开源ETL工具kettle系列之常见问题
摘要：
本文主要介绍使用kettle设计一些ETL任务时一些常见问题,这些问题大部分都不在官方FAQ上，你可以在kettle的论坛上找到一些问题的答案
1。
Join
我得到A数据流（不管是基于文件或数据库），A包含field1，field2,field3字段，然后我还有一个B数据流，B包含field4,field5,field6，我现在想把它们‘加’起来,应该怎么样做.
这是新手最容易犯错的一个地方，A数据流跟B数据流能够Join,肯定是它们包含joinkey,joinkey可以是一个字段也可以是多个字段。
如果两个数据流没有joinkey,那么它们就是在做笛卡尔积，一般很少会这样。
比如你现在需要列出一个员工的姓名和他所在部门的姓名，如果这是在同一个数据库,大家都知道会在一个sql里面加上where限定条件，但是如果员工表和部门表在两个不同的数据流里面,尤其是数据源的来源是多个数据库的情况，我们一般是要使用DatabaseJoin操作，然后用两个databasetableinput来表示输入流，一个输入是部门表的姓名，另一个是员工表的姓名，然后我们认为这两个表就可以”Join”了，我们需要的输出的确是这两个字段，但是这两个字段的输出并不代表只需要这两个字段的输入,它们之间肯定是需要一个约束关系存在的。
另外,无论是在做Join，Merge,Update，Delete这些常规操作的时候,都是先需要做一个compare操作的,这个compare操作都是针对comparekey的，无论两个表结构是不是一样的,比如employee表和department表，它们比较的依据就是employee的外键department_id,没有这个comparekey这两个表是不可能连接的起来的.。
对于两个表可能还有人知道是直接sql来做连接，如果是多个输入数据源，然后是三个表,有人就开始迷茫了，A表一个字段,B表一个字段，C表一个字段，然后就连Join操作都没有，直接databasetableoutput,然后开始报错，报完错就到处找高手问,他们的数据库原理老师已经在吐血了。
如果是三个表连接，一个sql不能搞定,就需要先两个表两个表的连接,通过两次comparekey连接之后得到你的输出，记住，你的输出并不能代表你的输入。
下面总结一下：
1. 单数据源输入,直接用sql做连接
2. 多数据源输入，（可能是文本或是两个以上源数据库），用databasejoin操作。
3。
三个表以上的多字段输出。
2。
Kettle的数据库连接模式
Kettle的数据库连接是一个步骤里面控制一个单数据库连接，所以kettle的连接有数据库连接池，你可以在指定的数据库连接里面指定一开始连接池里面放多少个数据库连接，在创建数据库连接的时候就有Pooling选项卡，里面可以指定最大连接数和初始连接数，这可以一定程度上提高速度.
3。
transaction
我想在步骤A执行一个操作（更新或者插入），然后在经过若干个步骤之后，如果我发现某一个条件成立，我就提交所有的操作，如果失败，我就回滚，kettle提供这种事务性的操作吗？
Kettle里面是没有所谓事务的概念的，每个步骤都是自己管理自己的连接的,在这个步骤开始的时候打开数据库连接，在结束的时候关闭数据库连接,一个步骤是肯定不会跨session的（数据库里面的session），另外，由于kettle是并行执行的，所以不可能把一个数据库连接打开很长时间不放，这样可能会造成锁出现,虽然不一定是死锁,但是对性能还是影响太大了。
ETL中的事务对性能影响也很大,所以不应该设计一种依赖与事务方式的ETL执行顺序，毕竟这不是OLTP，因为你可能一次需要提交的数据量是几百GB都有可能，任何一种数据库维持一个几百GB的回滚段性能都是会不大幅下降的。
4. 我真的需要transaction但又不想要一个很复杂的设计,能不能提供一个简单一点的方式
Kettle在3.0。
2GA版中将推出一种新功能，在一个tableoutput步骤中有一个Miscellaneous选项卡，其中有一个Useuniqueconnections的选项，如果你选中的话就可以得到一个transaction的简单版，
由于是使用的单数据库连接，所以可以有错误的时候回滚事务，不过要提醒一点是这种方式是以牺牲非常大的性能为前提条件的，对于太大的数据量是不适合的（个人仍然不建议使用这种方式）
5。
temporary表如何使用
我要在ETL过程中创建一个中间表，当某个条件成立的时候，我要把中间表的数据进行转换，当另一条件成立的时候我要对中间表进行另一个操作,我想使用数据库的临时表来操作,应该用什么步骤。
首先从temp表的生命周期来分，temp分为事务临时表和会话临时表，前面已经解释过了，kettle是没有所谓事务的概念的,所以自然也没有所谓的事务临时表。
Kettle的每个步骤管理自己的数据库连接,连接一结束，kettle也就自然丢掉了这个连接的session的handler，没有办法可以在其他步骤拿回这个session的handler，所以也就不能使用所谓的会话临时表,当你尝试再开一个连接的时候,你可以连上这个临时表，但是你想要的临时表里面的数据都已经是空的（数据不一定被清除了，但是你连不上了）,所以不要设计一个需要使用临时表的转换
之所以会使用临时表，其实跟需要”事务”特性有一点类似，都是希望在ETL过程中提供一种缓冲。
临时表很多时候都不是某一个源表的全部数据的镜像，很多时候临时表都是很小一部分结果集，可能经过了某种计算过程，你需要临时表无非是基于下面三个特性:
1. 表结构固定，用一个固定的表来接受一部分数据。
2. 每次连接的时候里面没有数据。
你希望它接受数据,但是不保存，每次都好像执行了truncatetable操作一样
3. 不同的时候连接临时表用同一个名字，你不想使用多个连接的时候用类似与temp1,temp2,temp3，temp4这种名字，应为它们表结构一样。
既然临时表不能用，应该如何设计ETL过程呢?
（可以用某种诡异的操作搞出临时表，不过不建议这样做罢了）
如果你的ETL过程比较的单线程性，也就是你清楚的知道同一时间只有一个这样的表需要,你可以创建一个普通的表,每次连接的时候都执行truncate操作,不论是通过tableoutput的truncatetable选项,还是通过手工执行truncatetablesql语句（在executesqlscript步骤）都可以达到目的（基于上面的1，2特性）
如果你的ETL操作比较的多线程性，同一时间可能需要多个表结构一样并且里面都是为空的表（基于上面1,2,3特性）,你可以创建一个“字符串+序列” 的模式，每次需要的时候，就创建这样的表，用完之后就删除，因为你自己不一定知道你需要多少个这种类型的表,所以删除会比truncate好一些。
下面举个例子怎么创建这种表：
你可以使用某种约定的表名比如department_temp作为department的临时表。
或者
把argument传到表名，使用department_${argument}的语法，
如果你需要多个这种表,使用一个sequence操作+executesqlscript操作,executesqlscript就下面这种模式
Createtable_?
（…………..）
在表的名字上加参数，前面接受一个sequence或类似的输入操作。
需要注意的是这种参数表名包括databasetableinput或者executesqlscript,只要是参数作为表名的情况前面的输入不能是从数据库来的，应为没有办法执行这种preparedStatement 语句,从数据库来的值后面的操作是“值操作”,而不是字符串替换，只有argument或者sequence操作当作参数才是字符串替换。
（这一点官方FAQ也有提到）
6。
updatetable和executesqlscript里面执行update的区别
执行updatetable操作是比较慢的，它会一条一条基于comparekey对比数据，然后决定是不是要执行updatesql，如果你知道你要怎么更新数据尽可能的使用executesqlscript操作，在里面手写updatesql（注意源数据库和目标数据库在哪），这种多行执行方式（updatesql）肯定比单行执行方式（updatetable操作）快的多.
另一个区别是executesqlscript操作是可以接受参数的输入的。
它前面可以是一个跟它完全不关的表一个sql：
selectfield1,field2field3fromtableA
后面执行另一个表的更新操作:
updatetableBsetfield4=?
wherefield5=?
Andfield6=?
然后选中executesqlscript的executeforeachrow.注意参数是一一对应的。
（field4对应field1的值，
field5对应field2的值,field6对应field3的值）
7. kettle的性能
kettle本身的性能绝对是能够应对大型应用的,一般的基于平均行长150的一条记录，假设源数据库，目标数据库以及kettle都分别在几台机器上（最常见的桌面工作模式，双核,1G内存），速度大概都可以到5000行每秒左右，如果把硬件提高一些，性能还可以提升，但是ETL过程中难免遇到性能问题，下面一些通用的步骤也许能给你一些帮助。
尽量使用数据库连接池
尽量提高批处理的commitsize
尽量使用缓存，缓存尽量大一些（主要是文本文件和数据流）
Kettle是Java做的，尽量用大一点的内存参数启动Kettle。
可以使用sql来做的一些操作尽量用sql
Group，merge，streamlookup，splitfield这些操作都是比较慢的，想办法避免他们.,能用sql就用sql
插入大量数据的时候尽量把索引删掉
尽量避免使用update,delete操作,尤其是update，如果可以把update变成先delete， 后insert。
能使用truncatetable的时候,就不要使用deleteallrow这种类似sql
合理的分区
如果删除操作是基于某一个分区的，就不要使用deleterow这种方式（不管是deletesql还是delete步骤），直接把分区drop掉,再重新创建
尽量缩小输入的数据集的大小（增量更新也是为了这个目的）
尽量使用数据库原生的方式装载文本文件（Oracle的sqlloader，mysql的bulkloader步骤）
尽量不要用kettle的calculate计算步骤，能用数据库本身的sql就用sql，不能用sql就尽量想办法用procedure,实在不行才是calculate步骤。
要知道你的性能瓶颈在哪,可能有时候你使用了不恰当的方式,导致整个操作都变慢，观察kettlelog生成的方式来了解你的ETL操作最慢的地方。
远程数据库用文件+FTP的方式来传数据，文件要压缩.（只要不是局域网都可以认为是远程连接）
8。
描述物理环境
源数据库的操作系统，硬件环境，是单数据源还是多数据源，数据库怎么分布的,做ETL的那台机器放在哪，操作系统和硬件环境是什么，目标数据仓库的数据库是什么，操作系统，硬件环境,数据库的字符集怎么选，数据传输方式是什么，开发环境，测试环境和实际的生产环境有什么区别，是不是需要一个中间数据库（staging数据库）,源数据库的数据库版本号是多少,测试数据库的版本号是多少,真正的目标数据库的版本号是多少…….这些信息也许很零散，但是都需要一份专门的文档来描述这些信息,无论是你遇到问题需要别人帮助的时候描述问题本身，还是发现测试环境跟目标数据库的版本号不一致,这份专门的文档都能提供一些基本的信息
9。
procedure
为什么我不能触发procedure？
这个问题在官方FAQ里面也有提到，触发procedure和httpclient都需要一个类似与触发器的条件,你可以使用generaterow步骤产生一个空的row,然后把这条记录连上procedure步骤,这样就会使这条没有记录的空行触发这个procedure（如果你打算使用无条件的单次触发），当然procedure也可以象tableinput里面的步骤那样传参数并且多次执行。
另外一个建议是不要使用复杂的procedure来完成本该ETL任务完成的任务，比如创建表，填充数据，创建物化视图等等。
10。
字符集
Kettle使用Java通常使用的UTF8来传输字符集，所以无论你使用何种数据库,任何数据库种类的字符集，kettle都是支持的，如果你遇到了字符集问题，也许下面这些提示可以帮助你:
1. 单数据库到单数据库是绝对不会出现乱码问题的,不管原数据库和目标数据库是何种种类，何种字符集
2。
多种不同字符集的原数据库到一个目标数据库，你首先需要确定多种源数据库的字符集的最大兼容字符集是什么，如果你不清楚,最好的办法就是使用UTF8来创建数据库.
3。
不要以你工作的环境来判断字符集：
现在某一个测试人员手上有一个oracle的基于xxx字
升级会员 