1、kettle入门例子大全kettle入门例子大全(总50页)Kettle 培训技术文档 0507Etl 介绍ETL(Extract-Transform-Load的缩写,即数据抽取、转换、装载的过程),对于金融IT 来说,经常会遇到大数据量的处理,转换,迁移,所以了解并掌握一种etl工具的使用,必不可少。Kettle是一款国外开源的etl工具,纯java编写,绿色无需安装,数据抽取高效稳定。Kettle中有两种脚本文件,transformation和job,transformation完成针对数据的基础转换,job则完成整个工作流的控制。kettle 部署运行将文件夹拷贝到本地路径,例如D 盘根
2、目录。双击运行kettle文件夹下的文件,出现kettle欢迎界面:稍等几秒选择没有资源库,打开kettle主界面创建transformation,job点击页面左上角的创建一个新的transformation,点击保存到本地路径,例如保存到D:/etltest下,保存文件名为EtltestTrans,kettle默认transformation文件保存后后缀名为ktr点击页面左上角的创建一个新的job,点击保存到本地路径,例如保存到D:/etltest下,保存文件名为EtltestJob,kettle默认job文件保存后后缀名为kjb创建数据库连接在transformation页面下,点击左
3、边的【Main Tree】,双击【DB连接】,进行数据库连接配置。connection name自命名连接名称Connection type选择需要连接的数据库Method of access选择连接类型Server host name写入数据库服务器的ip地址Database name写入数据库名Port number写入端口号Username写入用户名Password写入密码例如如下配置:点击【test】,如果出现如下提示则说明配置成功点击关闭,再点击确定保存数据库连接。一个简单的ktr 例子目的:将一个数据库导入到另一个数据库中。操作步骤:创建一个transformation,命名为,创
4、建数据库连接ods,点击【Input】,选中【表输入】,拖到主窗口,释放鼠标,双击打开如下图点击【Transform】,选中【字段选择】,拖到主窗口,释放鼠标点击【Output】,选中【表输出】,拖到主窗口,释放鼠标建立【文本文件输入】和【字段选择】与【字段选择】和【表输出】的连接双击【表输出】,目标表中写入ZT_TEST_KETTLE,确定保存双击【字段选择】,点击 获取选择的字段,再点击Edlt Mapping,点击OK确定,编辑所有字段对应关系,点确定。点击运行这个转换。,则将上一个ktr中生成的文本,导入到数据库当中。一个简单的kjb例子目的:将上一个transformation在一个
5、job里面调用执行。操作步骤:在etlTestJob页面,点击【Core Objects】,点击【Job entries】,选中【START】拖动到主窗口释放鼠标,再选中【Transformation】,拖动到主窗口释放鼠标,建立【START】和【Transformation】之间的连接。双击【Transformation 】, 在Transformation filename 中写入E:kettleWorkspace,确定保存。点击保存创建好的job。点击运行这个转换。待所有任务都显示成功,则为job调用transformation运行成功。一个增量的例子增量更新按照数据种类的不同大概可以分
6、成:1.只增加,不更新,2.只更新,不增加3.即增加也更新4.有删除,有增加,有更新下面针对前三种做一个增量的ETL抽取。过程如下:根据前面讲解的例子一样,首先建立源表(fina_test1)和目标表(fina_test2),整个设计流程如下:其中第一个步骤(输入-目标表)的sql 大概如下模式:select ifnull(max(date_seal),1900-01-01 00:00:00) from fina_test2你会注意到第二个步骤和第一个步骤的连接是黄色的线,这是因为第二个table input(输入-源表)步骤把前面一个步骤的输出当作一个参数来用,所有Kettle用黄色的线来表
7、示,第二个table input(输入-源表) 的sql 模式大概如下:SELECT * FROM fina_test1 where date_seal后面的一个问号就是表示它需要接受一个参数,你在这个table input(输入-源表)下面需要指定replace variable in script 选项和 执行每一行 为选中状态,这样,Kettle就会循环执行这个sql , 执行的次数为前面参数步骤传入的数据集的大小。关于第三个步骤执行插入/更新步骤需要特别解释一下,Kettle执行这个步骤是需要两个数据流对比,其中一个是目标数据库,你在目标表 里面指定的,它放在用来查询的关键字左边的表字
8、段里面的,另外一个数据流就是你在前一个步骤传进来的,它放在用来查询的关键字 的右边,Kettle首先用你传进来的key 在数据库中查询这些记录,如果没有找到,它就插入一条记录,所有的值都跟你原来的值相同,如果根据这个key找到了这条记录,kettle会比较这两条记录,根据你指定update field 来比较,如果数据完全一样,kettle就什么都不做,如果记录不完全一样,kettle就执行一个update 步骤。备注:主键被修改得数据认为是新记录 删除的数据由在仓库中需要保留无需考虑然后点击新建-job,然后job的核心对象job entries拉出组建,进行执行抽取。创建kettle资料库
9、资源库是用来保存转换任务的,用户通过图形界面创建的的转换任务可以保存在资源库中。资源库可以是各种常见的数据库,用户通过用户名/密码来访问资源库中的资源,默认的用户名/密码是admin/admin资源库并不是必须的,如果没有资源库,用户还可以把转换任务保存在 xml 文件中。如果用户需要创建一个资源库,在资源库的登录窗口(PDI 启动时的第一个窗口)中有 【新建】 按钮,点击该按钮弹出新建资源库窗口,在该窗口中选择一个数据库连接,如果没有事先定义的数据库连接,则还要点击【新建】按钮,来创建一个数据库连接。选择数据库连接后,要为该资源库命名,作为这个资源库的唯一标志,最后选择【创建或更新】按钮来创
10、建这个资源库。资源库可以使多用户共享转换任务,转换任务在资源库中是以文件夹形式分组管理的,用户可以自定义文件夹名称。如何使用kettle读取包含多行表的Excel文件如果 Excel 工作表的表头只有一行,使用 Kettle 读取这样的文件是很容易的.如果 Excel 工作表的表头是多行的, 或者是分级的就需要在内容标签下正确设置列名所占行数才可以读取.考虑这样的一个工作表如果想把里面的 12列数据都读出来, 就要考虑如何处理多级表头.步骤设置的详细描述:步骤一 选择文件名,现在文件或目录里到所要添加的excel文档,然后点击,确定后,点击,步骤二 选择要读取的工作表名称和要读取的内容在工作表
11、里的起始位置, 也就是表头开始的行号和列号 (这里行号和列号是以 0 开始的)步骤三 设置要读取的内容的一些属性, 这里要设置表头的所占行数是 4行.步骤四 错误处理, 选择如果有错误终止还是继续, 错误信息保存的文件等.(图略)步骤五 选择字段, 如果前面的三个步骤(不包括错误处理步骤)都设置正确, 在这个页面选择 获取字段 字段按钮, 就会获得所有的列名称和数据类型.这里我们可以看到: 多级表头中各级表头的名称被叠加起来, 形成了唯一的列名.点击 预览 按钮可以预览到数据对于表头跨连续的多行, 但不分级的情况也可以使用上述方式处理.kettle注释:1、kettle的控制流可以设置一些简单
12、的时间,并且可以实现隔断天、周、月(三个只能选一个,不能选那个月的那周那日),但是kettle工具不能关,如果关了,必须重新启动。2、kettle里面缺少一个编辑的字段的插件,导致字段编辑很麻烦,这只能先sql中进行手写,这个对写sql的要求很高。一个kettle字段转换(截取)的例子大致的流程是:表输入还是正常的sql查询,没有添加参数。字段转换(截取)是在进行修改。具体样式如下:具体的用法:transform Functions 里面包括了字符、数字的一些函数方法,这些函数方法可以解决一些字段需要转化的问题。Input fields和 Output fields 里面包括了从表输入进来的字
13、段(数据)。字段主要转化的操作界面:注意下:substr(xxx,1,2) 中的1代表是第一位开始,2代表是取2位,在这里面还可以添加if等语句,进行编写。在字段选择那里面要配置从js过来的字段,点击列映射(前提是已经和表输出连接上),这个字段对应要根据你实际从js倒过来的字段和目标表相对应的字段一一对应。开源ETL工具kettle系列之常见问题 摘要:本文主要介绍使用kettle设计一些ETL任务时一些常见问题,这些问题大部分都不在官方FAQ上,你可以在kettle的论坛上找到一些问题的答案我得到A 数据流(不管是基于文件或数据库),A包含field1 , field2 , field3 字
14、段,然后我还有一个B数据流,B包含field4 , field5 , field6 , 我现在想把它们 加 起来, 应该怎么样做.这是新手最容易犯错的一个地方,A数据流跟B数据流能够Join,肯定是它们包含join key ,join key 可以是一个字段也可以是多个字段。如果两个数据流没有join key ,那么它们就是在做笛卡尔积,一般很少会这样。比如你现在需要列出一个员工的姓名和他所在部门的姓名,如果这是在同一个数据库,大家都知道会在一个sql 里面加上where 限定条件,但是如果员工表和部门表在两个不同的数据流里面,尤其是数据源的来源是多个数据库的情况,我们一般是要使用Databa
15、se Join 操作,然后用两个database table input 来表示输入流,一个输入是部门表的姓名,另一个是员工表的姓名,然后我们认为这两个表就可以 ”Join” 了,我们需要的输出的确是这两个字段,但是这两个字段的输出并不代表只需要这两个字段的输入,它们之间肯定是需要一个约束关系存在的。另外,无论是在做 Join , Merge , Update , Delete 这些常规操作的时候,都是先需要做一个compare 操作的,这个compare 操作都是针对compare key 的,无论两个表结构是不是一样的,比如employee 表和department 表,它们比较的依据就是
16、employee 的外键department_id , 没有这个compare key 这两个表是不可能连接的起来的. 对于两个表可能还有人知道是直接sql 来做连接,如果是多个输入数据源,然后是三个表,有人就开始迷茫了,A表一个字段,B表一个字段,C表一个字段,然后就连Join操作都没有,直接 database table output , 然后开始报错,报完错就到处找高手问,他们的数据库原理老师已经在吐血了。如果是三个表连接,一个sql 不能搞定,就需要先两个表两个表的连接,通过两次compare key 连接之后得到你的输出,记住,你的输出并不能代表你的输入. 下面总结一下:1.单数据源
17、输入,直接用sql 做连接2.多数据源输入,(可能是文本或是两个以上源数据库),用database join 操作.3.三个表以上的多字段输出.的数据库连接模式Kettle的数据库连接是一个步骤里面控制一个单数据库连接,所以kettle的连接有数据库连接池,你可以在指定的数据库连接里面指定一开始连接池里面放多少个数据库连接,在创建数据库连接的时候就有Pooling 选项卡,里面可以指定最大连接数和初始连接数,这可以一定程度上提高速度.我想在步骤A执行一个操作(更新或者插入),然后在经过若干个步骤之后,如果我发现某一个条件成立,我就提交所有的操作,如果失败,我就回滚,kettle提供这种事务性的
18、操作吗Kettle 里面是没有所谓事务的概念的,每个步骤都是自己管理自己的连接的,在这个步骤开始的时候打开数据库连接,在结束的时候关闭数据库连接,一个步骤是肯定不会跨session的(数据库里面的session), 另外,由于kettle是并行执行的,所以不可能把一个数据库连接打开很长时间不放,这样可能会造成锁出现,虽然不一定是死锁,但是对性能还是影响太大了。ETL中的事务对性能影响也很大,所以不应该设计一种依赖与事务方式的ETL执行顺序,毕竟这不是OLTP,因为你可能一次需要提交的数据量是几百 GB都有可能,任何一种数据库维持一个几百GB的回滚段性能都是会不大幅下降的.4.我真的需要tran
19、saction 但又不想要一个很复杂的设计,能不能提供一个简单一点的方式Kettle 在版中将推出一种新功能,在一个table output 步骤中有一个Miscellaneous 选项卡,其中有一个Use unique connections 的选项,如果你选中的话就可以得到一个transaction 的简单版,由于是使用的单数据库连接,所以可以有错误的时候回滚事务,不过要提醒一点是这种方式是以牺牲非常大的性能为前提条件的,对于太大的数据量是不适合的(个人仍然不建议使用这种方式) 表如何使用我要在ETL过程中创建一个中间表,当某个条件成立的时候,我要把中间表的数据进行转换,当另一条件成立的时
20、候我要对中间表进行另一个操作,我想使用数据库的临时表来操作,应该用什么步骤。首先从temp 表的生命周期来分,temp分为事务临时表和会话临时表,前面已经解释过了,kettle是没有所谓事务的概念的,所以自然也没有所谓的事务临时表。Kettle的每个步骤管理自己的数据库连接,连接一结束,kettle也就自然丢掉了这个连接的session 的handler , 没有办法可以在其他步骤拿回这个session 的handler , 所以也就不能使用所谓的会话临时表,当你尝试再开一个连接的时候,你可以连上这个临时表,但是你想要的临时表里面的数据都已经是空的(数据不一定被清除了,但是你连不上了),所以不
21、要设计一个需要使用临时表的转换之所以会使用临时表,其实跟需要 ”事务” 特性有一点类似,都是希望在ETL过程中提供一种缓冲。临时表很多时候都不是某一个源表的全部数据的镜像,很多时候临时表都是很小一部分结果集,可能经过了某种计算过程,你需要临时表无非是基于下面三个特性:1.表结构固定,用一个固定的表来接受一部分数据。2.每次连接的时候里面没有数据。你希望它接受数据,但是不保存,每次都好像执行了truncate table 操作一样3.不同的时候连接临时表用同一个名字,你不想使用多个连接的时候用类似与temp1 , temp2 , temp3 , temp4 这种名字,应为它们表结构一样。既然临时
22、表不能用,应该如何设计ETL过程呢(可以用某种诡异的操作搞出临时表,不过不建议这样做罢了)如果你的ETL过程比较的单线程性,也就是你清楚的知道同一时间只有一个这样的表需要,你可以创建一个普通的表,每次连接的时候都执行truncate 操作,不论是通过table output 的truncate table 选项,还是通过手工执行truncate table sql 语句(在execute sql script 步骤)都可以达到目的(基于上面的1,2 特性)如果你的ETL操作比较的多线程性,同一时间可能需要多个表结构一样并且里面都是为空的表(基于上面1,2,3特性),你可以创建一个 “字符串+序
23、列” 的模式,每次需要的时候,就创建这样的表,用完之后就删除,因为你自己不一定知道你需要多少个这种类型的表,所以删除会比truncate 好一些。下面举个例子怎么创建这种表:你可以使用某种约定的表名比如department_temp 作为department 的临时表。或者把argument 传到表名,使用 department_$argument 的语法, 如果你需要多个这种表,使用一个sequence 操作+execute sql script 操作,execute sql script 就下面这种模式 Create table_ (.)在表的名字上加参数,前面接受一个sequence 或
24、类似的输入操作.需要注意的是这种参数表名包括database table input 或者execute sql script ,只要是参数作为表名的情况前面的输入不能是从数据库来的,应为没有办法执行这种preparedStatement 语句,从数据库来的值后面的操作是 “值操作” ,而不是字符串替换,只有argument 或者sequence 操作当作参数才是字符串替换. (这一点官方FAQ也有提到) table 和execute sql script 里面执行update 的区别执行update table 操作是比较慢的,它会一条一条基于compare key 对比数据,然后决定是不是
25、要执行update sql , 如果你知道你要怎么更新数据尽可能的使用execute sql script 操作,在里面手写update sql (注意源数据库和目标数据库在哪),这种多行执行方式(update sql)肯定比单行执行方式(update table 操作)快的多。另一个区别是execute sql script 操作是可以接受参数的输入的。它前面可以是一个跟它完全不关的表一个sql :select field1, field2 field3 from tableA 后面执行另一个表的更新操作:update tableB set field4 = where field5= An
26、d field6= 然后选中execute sql script 的execute for each row .注意参数是一一对应的.(field4 对应field1 的值,field5 对应field2 的值, field6 对应field3 的值)的性能kettle本身的性能绝对是能够应对大型应用的,一般的基于平均行长150的一条记录,假设源数据库,目标数据库以及kettle都分别在几台机器上(最常见的桌面工作模式,双核,1G内存),速度大概都可以到5000 行每秒左右,如果把硬件提高一些,性能还可以提升 , 但是ETL 过程中难免遇到性能问题,下面一些通用的步骤也许能给你一些帮助.尽量使
27、用数据库连接池尽量提高批处理的commit size尽量使用缓存,缓存尽量大一些(主要是文本文件和数据流)Kettle 是Java 做的,尽量用大一点的内存参数启动Kettle.可以使用sql 来做的一些操作尽量用sql Group , merge , stream lookup ,split field 这些操作都是比较慢的,想办法避免他们.,能用sql 就用sql插入大量数据的时候尽量把索引删掉尽量避免使用update , delete 操作,尤其是update , 如果可以把update 变成先delete , 后insert .能使用truncate table 的时候,就不要使用de
28、lete all row 这种类似sql合理的分区如果删除操作是基于某一个分区的,就不要使用delete row 这种方式(不管是delete sql 还是delete 步骤),直接把分区drop 掉,再重新创建尽量缩小输入的数据集的大小(增量更新也是为了这个目的)尽量使用数据库原生的方式装载文本文件(Oracle 的sqlloader , mysql 的bulk loader 步骤)尽量不要用kettle 的calculate 计算步骤,能用数据库本身的sql 就用sql ,不能用sql 就尽量想办法用procedure , 实在不行才是calculate 步骤.要知道你的性能瓶颈在哪,可能
29、有时候你使用了不恰当的方式,导致整个操作都变慢,观察kettle log 生成的方式来了解你的ETL操作最慢的地方。远程数据库用文件+FTP 的方式来传数据 ,文件要压缩。(只要不是局域网都可以认为是远程连接)8.描述物理环境源数据库的操作系统,硬件环境,是单数据源还是多数据源,数据库怎么分布的,做ETL的那台机器放在哪,操作系统和硬件环境是什么,目标数据仓库的数据库是什么,操作系统,硬件环境,数据库的字符集怎么选,数据传输方式是什么,开发环境,测试环境和实际的生产环境有什么区别,是不是需要一个中间数据库(staging 数据库) ,源数据库的数据库版本号是多少,测试数据库的版本号是多少,真正
30、的目标数据库的版本号是多少. 这些信息也许很零散,但是都需要一份专门的文档来描述这些信息,无论是你遇到问题需要别人帮助的时候描述问题本身,还是发现测试环境跟目标数据库的版本号不一致,这份专门的文档都能提供一些基本的信息为什么我不能触发procedure这个问题在官方FAQ里面也有提到,触发procedure 和 http client 都需要一个类似与触发器的条件,你可以使用generate row 步骤产生一个空的row ,然后把这条记录连上procedure 步骤,这样就会使这条没有记录的空行触发这个procedure (如果你打算使用无条件的单次触发) ,当然procedure 也可以象
31、table input 里面的步骤那样传参数并且多次执行.另外一个建议是不要使用复杂的procedure 来完成本该ETL任务完成的任务,比如创建表,填充数据,创建物化视图等等.10.字符集Kettle使用Java 通常使用的UTF8 来传输字符集,所以无论你使用何种数据库,任何数据库种类的字符集,kettle 都是支持的,如果你遇到了字符集问题,也许下面这些提示可以帮助你:1.单数据库到单数据库是绝对不会出现乱码问题的,不管原数据库和目标数据库是何种种类,何种字符集2.多种不同字符集的原数据库到一个目标数据库,你首先需要确定多种源数据库的字符集的最大兼容字符集是什么,如果你不清楚,最好的办法
32、就是使用UTF8来创建数据库.3.不要以你工作的环境来判断字符集:现在某一个测试人员手上有一个oracle 的基于xxx 字符集的已经存在的数据库,并且非常不幸的是xxx 字符集不是utf8 类型的,于是他把另一个基于yyy字符集的oracle 数据库要经过某一个ETL过程转换到oracle , 后来他发现无论怎么样设置都会出现乱码,这是因为你的数据库本身的字符集不支持,无论你怎么设置都是没用的. 测试的数据库不代表最后产品运行的数据库,尤其是有时候为了省事把多个不同的项目的不相关的数据库装在同一台机器上,测试的时候又没有分析清楚这种环境,所以也再次强调描述物理环境的重要性.4.你所看到的不一定代表实际储存的:mysql 处理字符集的时候是要在jdbc 连接的参数里面加上字符集参数的,
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1