ETL工具Kettle

转载自 https://www.cnblogs.com/SunHuaJ/p/7593239.html

ETL是EXTRACT&＃xff08;抽取&＃xff09;、TRANSFORM&＃xff08;转换&＃xff09;、LOAD&＃xff08;加载&＃xff09;的简称&＃xff0c;实现数据从多个异构数据源加载到数据库或其他目标地址&＃xff0c;是数据仓库建设和维护中的重要一环也是工作量较大的一块。当前知道的ETL工具有informatica, datastage,kettle,ETL Automation,sqoop,SSIS等等。这里我们聊聊kettle的学习吧&＃xff08;如果你有一定的kettle使用&＃xff0c;推荐看看Pentaho Kettle解决方案&＃xff0c;这里用kettle实践kimball的数据仓库理论。&＃xff09;。

　　内容有&＃xff1a;认识kettle、安装kettle、简单入门实例、进阶实例、Linux中kettle部署、kettle发邮件、常见错误

　　认识kettle

　　kettle是纯java开发&＃xff0c;开源的etl工具。可以在Linux、windows、unix中运行。有图形界面&＃xff0c;也有命令脚本还可以二次开发。&＃xff08;官方社区&＃xff1a;http://forums.pentaho.com/&＃xff1b;官网wiki&＃xff1a;http://wiki.pentaho.com/display/COM/Community&＃43;Wiki&＃43;Home&＃xff1b;源码地址&＃xff1a;https://github.com/pentaho/pentaho-kettle&＃xff09;

　　安装kettle

　　1、kettle是基于java开发的&＃xff0c;所以需要java环境&＃xff08;下载jdk&＃xff1a;http://www.oracle.com/technetwork/java/javase/downloads/jdk9-downloads-3848520.html&＃xff09;

　　2、kettle使用时&＃xff0c;需要访问相关的关系型数据库&＃xff0c;则需要下载对应的链接驱动。比如我们访问MySQL&＃xff0c;则下载相应的驱动解压后放入kettle文件的lib目录下

　　3、下载kettle并解压到自定义位置。kettle其实是以前的叫法&＃xff0c;现在官方称为&＃xff1a;PDI(Pentaho Data Integeration)。在windows中&＃xff0c;双击目录中的Spoon.bat启动kettle.

　　简单的kettle实例

 　　1、新建作业/转换&＃xff08;功能区&＃xff1a;文件 --> 新建 --> 作业&＃xff1b;新建-->转换&＃xff09;

　　　　一个作业&＃xff08;job&＃xff0c;文件以kjb结尾&＃xff09;的主体是转换&＃xff08;transform&＃xff0c;以ktr结尾&＃xff09;&＃xff0c;job主要来设置调度&＃xff0c;可以有影子拷贝&＃xff0c;任一拷贝信息修改所有拷贝的都被修改&＃xff1b;transform做主体的内容&＃xff0c;控件名称唯一。

　　2、三个控件&＃xff08;start、转换、成功&＃xff09;和流程线&＃xff08;hop&＃xff09;&＃xff1b;

　　　　start&＃xff1a;job开始的地方&＃xff0c;可以设置开始的时间、频率、周期等&＃xff08;但要求kettle不能关闭&＃xff0c;有点挫&＃xff09;

　　　　转换&＃xff1a;后续详解

　　　　成功&＃xff1a;job结束

　　　　流程线&＃xff1a;关联两个控件&＃xff08;实体&＃xff09;&＃xff0c;指定数据流。同时还可以设置是否可用、分发模式、错误输出等&＃xff1b;添加方式&＃xff1a;按住shift进行鼠标拖动

　　3、转换的工作

　　　　新建的转换&＃xff1a;job中需引用该转换文件

　　　　加入我们现在要同步MySQL中的一张表。在转换中要有输入和输出。

　　　　> 表输入&＃xff1a;先配置链接&＃xff08;完成后测试一下是否OK&＃xff09;&＃xff0c;再输入查询sql&＃xff08;比如&＃xff1a;select id from tab2 limit 10;&＃xff09;

　　　　>excel输出。&＃xff0c;指定输出路径

　　　　> 完成转换的配置后保存&＃xff0c;在job中引用保存的文件。我们来跑一下吧~

　　　　> 完成&＃xff0c;结束&＃xff01;

 　　　　进阶实例&＃xff1a;

　　　　百度上看到了一篇关于kettle的作业&＃xff0c;但是没有详细的过程。这里以此说明&＃xff0c;全图过程如下。

　　　　作业说明&＃xff1a;生成 100 个随机数&＃xff0c;随机数取值于[0&＃xff0c;100&＃xff09;之间&＃xff0c; 计算小于等于 50 的随机数个数和 大于50 的随机数个 数。 并把这两个统计数字放在数据库表的一行的两列中&＃xff0c; 即输出的结果有一行&＃xff0c;一行包括两列&＃xff0c;每列是一个统 计值。

　　　　第一步&＃xff1a;生成随机数&＃xff08;输入-->生成随机数&＃xff1b;需要生成100个随机数&＃xff0c;右击控件&＃xff0c;选择"改变开始开始...数量"为100&＃xff09;

　　　　第二步&＃xff1a;增加常量&＃xff08;转换-->增加常量&＃xff1b;给变量取个名称&＃xff0c;类型和值。&＃xff09;

　　　　第三步&＃xff1a;计算器&＃xff08;转换-->计算器&＃xff1b;给出你的计算逻辑和计算出的字段&＃xff1b;&＃xff09;

　　　　第四步&＃xff1a;两个分支&＃xff0c;一个输出&＃xff1b;一个过滤&＃xff1b;输出指定Excel&＃xff0c;并执行数据发送模式&＃xff08;√&＃xff1a;复制发送模式&＃xff09;

　　　　第五步&＃xff1a;设置过滤&＃xff08;流程-->过滤记录&＃xff09;&＃xff1b;并双击控件填写对应的条件&＃xff1b;

　　　　第六步&＃xff1a;分组&＃xff08;统计-->分组&＃xff09;&＃xff0c;双击控件后有两个需要关注&＃xff0c;一个是分组&＃xff08;相当于group by&＃xff09;&＃xff1b;一个是聚合&＃xff08;相当于count、sum等函数&＃xff09;

 　　　　第七步&＃xff1a;记录关联&＃xff08;连接-->记录关联&＃xff08;笛卡尔输出&＃xff09;&＃xff09;&＃xff1b;这是一个join操作&＃xff0c;但是没有on条件&＃xff1b;但是控件中提供了sql中where条件的刷选

　　　　第八步&＃xff1a;输出

　　　　Linux上部署kettle任务

　　　　kettle的"开始"控件虽然可以进行调度&＃xff0c;但要求程序一直运行。在实际工作中通常在windos中测试&＃xff0c;放到Linux中以crontab的方式进行调度。在Linux中以kitchen.sh执行job任务&＃xff0c;pan.sh执行transform任务&＃xff1b;这里我们以上面为实例&＃xff0c;如何在Linux中进行部署。

　　　　第一步&＃xff1a;通过WinSCP将kettle拷贝到Linux中&＃xff0c;在拷贝路径中执行. kitchen.sh &＃xff0c;如果有参考消息输出则没有问题

　　　　第二步&＃xff1a;对于已在windos中执行成功的地址、文件名、用户等参数进行变量替换。执行export KETTLE_HOME&＃61;/home/shj&＃xff0c;会生成/home/shj/.kettle目录&＃xff0c;通过编辑目录下的kettle.properties文件来设置变量。实例中&＃xff0c;我们仅仅需要替换两个输出文件的地址为变量即可。

　　　　第三步&＃xff1a;修改kettle目录下的.sh文件权限为可执行&＃xff08;chmod a&＃43;x  *.sh&＃xff09;&＃xff1b;并执行文件。

　　　　这里说明&＃xff1a;/norep&＃xff1a;表示不往资源库中写日志&＃xff0c;Do not log into the repository

　　　　　　　　   /file&＃xff1a;使用文件&＃xff0c;The filename (Job XML) to launch

　　　　第四步&＃xff1a;验证结果。

 　　　　kettle中发送邮件

 　　　　kettle发送邮件还是比较简单的&＃xff0c;我们需要一个邮件发送的控件和对应的账号密码等自有信息

　　　　简单的流程&＃xff1a;

　　　　需要配置发送邮件控件&＃xff1a;

　　　　这样执行后&＃xff0c;邮件就发送出去了。那么如何在kettle生产中利用邮件功能呢&＃xff1f;我们可以将kettle的转换信息、统计信息、错误信息以文件的形式放入到指定的位置&＃xff08;或形成指定的参数&＃xff09;&＃xff0c;使用邮件以附件形式发送这些信息。

　　　　流程&＃xff1a;

　　　　1、这里我们新增控件&＃xff1a;添加文件到结果文件中&＃xff1b;配置转换中的输出的文件

　　　　2、发送邮件中我们增加附件的配置&＃xff0c;如下图

　　　　补充&＃xff1a;如果觉得kettle发送的正文信息太多&＃xff0c;可以配置邮件信息中&＃xff0c;只发送邮件注释&＃xff08;注释信息需要自己写&＃xff0c;如果是动态的话需要开发&＃xff09;

　　　　常见错误

　　　　第一种&＃xff1a;Timestamp&＃xff1a;Unable to get timestamp from resultset at index 3**&＃xff0c;如图。这个错误需要在db链接的选线中设置命令参数zeroDateTimeBehavior&＃xff08;值&＃xff1a;convertToNull  &＃xff09;

　　　　第二种&＃xff1a;字段的空被替换成了null值。这是kettle默认的设置&＃xff0c;需要我们在kettle.properties中增加设置&＃xff08;KETTLE_EMPTY_STRING_DIFFERS_FROM_NULL&＃61;Y&＃xff09;。

　　　　补充&＃xff0c;在设计流程时我们并不希望出错了作业就停止了&＃xff0c;而是继续执行并将错误信息以某种方式反馈出来。这时&＃xff0c;我们可以通过“定义错误处理”来实现。

　　　　并将错误信息输出&＃xff0c;供后续引用。