搜索
Kettle培训技术文档0507Etl介绍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提供这种事务性的操作吗