kettle使用规范(修改版)

Kettle使用规范1、Kettle概念Kettle是一款国外开源的ETL工具，纯java编写，可以在Window、Linux、Unix上运行，绿色无需安装，数据抽取高效稳定。Kettle中文名称叫水壶，该项目的主程序员MATT希望把各种数据放到一个壶里，然后以一种指定的格式流出。Kettle这个ETL工具集，它允许你管理来自不同数据库的数据，通过提供一个图形化的用户环境来描述你想做什么，而不是你想怎么做。Kettle中有两种脚本文件，transformation和job，transformation完成针对数据的基础转换，job则完成整个工作流的控制。2、下载和部署下载kettle压缩包，因kettle为绿色软件，解压缩到任意本地路径即可3、Kettle环境配置3.1安装JDK（略）3.2测试JDK安装成功（略）3.3kettle的java环境变量以win7为例：【开始】-【计算机】-【属性】-【高级系统属性】-【高级】-【环境变量】-【新建】，创建用户变量。变量名输入：PENTAHO_JAVA_HOME；变量值输入刚才jdk的安装路径。3.4运行KettleWindows下找到$KETTLE_HOME/spoon.dat，双击运行欢迎界面如下图所示：4、KETTLE组件介绍与使用4.1Kettle使用Kettle提供了资源库的方式来整合所有的工作,即所建立的transformation和job及其运行日志将被保存在数据库中，否则将以文件的形式存储在本地。1）创建一个新的transformation，点击保存到本地路径，例如保存到D:/etltest下，保存文件名为Trans，kettle默认transformation文件保存后后缀名为ktr；2）创建一个新的job，点击保存到本地路径，例如保存到D:/etltest下，保存文件名为Job，kettle默认job文件保存后后缀名为kjb；4.2组件树介绍4.2.1Transformation的主对象树和核心对象分别如下图：Transformation中的节点介绍如下：MainTree：菜单列出的是一个transformation中基本的属性，可以通过各个节点来查看。DB连接：显示当前transformation中的数据库连接，每一个transformation的数据库连接都需要单独配置。Steps：一个transformation中应用到的环节列表Hops：一个transformation中应用到的节点连接列表核心对象菜单列出的是transformation中可以调用的环节列表，可以通过鼠标拖动的方式对环节进行添加：Input：输入环节Output：输出环节Lookup：查询环节Transform：转化环节Joins：连接环节Scripting：脚本环节4.2.2Job的主对象树和核心对象分别如下图：MainTree菜单列出的是一个Job中基本的属性，可以通过各个节点来查看。DB连接：显示当前Job中的数据库连接，每一个Job的数据库连接都需要单独配置。Jobentries/作业项目：一个Job中引用的环节列表核心对象菜单列出的是Job中可以调用的环节列表，可以通过鼠标拖动的方式对环节进行添加。每一个环节可以通过鼠标拖动来将环节添加到主窗口中。并可通过shift+鼠标拖动，实现环节之间的连接。4.3使用举例14.3.1建立Transformation选中列表中的的“表输入”、“表输出”、“插入/更新”，拖拽到右侧工作区。鼠标选中节点键盘按shift键，拖拽即可连线。下图是一个转换，有两个动作，一是直接从数据源表同步数据到目标表，一是检查源表的插入和更新，同步到目标表。黑色线为生效，灰色线为失效，节点和连接可双击进行编辑。可预览数据是否正确。双击编辑表输出动作。要获取的字段设置如下所示：操作的数据库名点击“Enterfieldmapping/输入字段映射”进行字段选择。选择要用的字段后点确定，注意字段的约束条件。编辑好后点击上面的执行即可。左侧参数暂不说明，执行完成后可以观看下方的日志。双击编辑“插入/更新”动作。然后点击运行。执行前，在源库里加条新纪录。确定作业执行正常后可以保存作业，如保存路径到本地磁盘地址C:\etl2.ktr。4.3.2建立Job建立JOB的前提是有可以完整执行的作业，上面步骤定制的作业进行保存。从左侧列表拖拽“START”，“Success”，“转换”，“发送邮件”到工作区并连接，如下图。然后双击“START”动作进行编辑。然后双击编辑“Transformation”活动。转换文件名选择上一步编辑好的.ktr文件双击发送邮件，参数设置如下：编辑完成后直接点击运行即可。运行后查看日志。当任务执行以后可以查看相对应的日志，也可自行配置日志。4.4使用举例2本例是完成读取目录下的.log文件，找出其中包含错误代码的行，并统计个数4.4.1新建Transformation本例所用到的步骤和组件如下图所示：连接图如下所示：a)获取文件名设置如下：其中已选择的文件名称的变量都需要手写输入，可以点击显示文件名和预览记录检查是否配置正确。b)文本文件输入设置如下：文本文件输入选项中选择从上一步骤获取文件名，其中在输入里的字段被当做文件名一栏输入uri，步骤读取的文件名来自填写获取文件名2内容选项卡的设置如下：文件类型选择CSV，分隔符自己定义，格式为mixed，编码方式选择UTF-8字段选项可设置如下：需要手写输入str名称，设置类型为Stringc)获取字符串设置如下：d)测试脚本设置如下其中可以点击获取变量和测试脚本来测试脚本的正确性点击测试脚本界面如下：脚本代码由JavaScript编写e)聚合记录聚合记录就是将上一步的结果进行聚合，这里的名称字段是点击获取字段获取的，而不是自己输入的，newname一列是制定新名字，可以自己输入填写，f)文本文件输出文本文件的输出设置如下，其中字段属性的设置名称字段是点击获取字段自动获取的，不是输入的，设置如下g)运行日志显示如下：4.4.2建立JobJob的建立于4.3中方法一样，用到的组件如下所示转换组件设置如下：将上一步保存的*.ktr添加到转换名文件路径中点击运行按钮:生成日志如下所示：5、华科校医院案例对HIS库数据做全量和增量抽取的简单例子。5.1全量抽取的transformation表输入的命名是：源库名_表名，表输出的命名是：目标库_表名，例如以上例子，his_dbo.VI_TB_HIS_JZ_Charge代表着源库为his库，原表为dbo.VI_TB_HIS_JZ_Charge;ggpt_tb_his_jz_charge代表目标库为ggpt库，目标表名为tb_his_jz_charge.整个转换的命名为ggpt_all_tb_his_jz_charge，表示对目标库ggpt做全量的抽取，针对的目标表为tb_his_jz_charge.错误处理表示，若表输出中出现错误数据无法插入，整个转换会继续进行，错误行的信息会写入错误日志表中。空操作表示如数据无误则不做处理，分支便于理解。表输入中即简单的查询语句，对日期字段做了处理。5.2全量抽取job新建一个job，job中执行转换，job的命名为机构名_增量/全量_目标库名，例如以上job名称即为HUST_ALL_GGPT,代表着华科项目对GGPT库的全量抽取的job。5.3增量抽取transformation和全量抽取脚本类似，转化命名为ggpt_add_tb_his_jz_charge,表示对ggpt库的tb_his_jz_charge表做增量抽取的转换。表输入中加了时间条件的限制，只抽当天的数据。也可改成抽前一天数据，把convert(varchar.getdate(),112)改为convert(varchar.getdate()-1,112)即可,根据具体抽取时间做调整。5.4增量抽取的job和全量抽取的job类似，命名为HUST_ADD_GGPT表示华科项目对目标库GGPT做增量抽取.5.5JOB和transformation日志的设置基于项目所有的transformation和job是存在资源库中，即以表的形式存储,job和transformation的日志也写入了数据库表中。选中某一job或者transformation,点击右键，即可配置日志信息，如下图所示，选择数据库连接和表名，勾选日志表对应字段，即完成配置。日志配置流程：执行完毕后会自动在数据库里新建该表；5.6错误处理说明对于一个转换中抽取出现错误数据，有想要脚本继续运行，则需要对脚本增加错误处理，如下图：先在数据库中建相应的错误表例：createtableODR_ERR(idNUMBERnotnull,errornumVARCHAR2(4000),errordescVARCHAR2(4000),errornameVARCHAR2(4000),errorcodeVARCHAR2(4000),tablenameVARCHAR2(100));createsequenceSEQ_ODR_ERRminvalue1maxvalue9999999999999999999999999999startwith185180incrementby1nocache;createorreplacetriggerODR_ERR_idbeforeinsertonODR_ERR--ODR_ERR是表名foreachrowdeclarenextidnumber;beginIF:new.idISNULLor:new.id=0THEN--id是列名selectSEQ_ODR_ERR.nextval--SEQ_ODR_ERR正是刚才创建的intonextidfromsys.dual;:new.id:=nextid;endif;endODR_ERR_id;在数据库中会事先建立好一张记录错误信息的表，命名为库名_ERR,代表针对这一个库的数据抽取的所有错误数据信息将被记录在此表中，表中五个字段ERRORNUM错误数，ERRORDESC错误描述，ERRORNAME错误列，ERRORCODE错误编码，TABLENAME发生错误的表名，来方便查看发生错误具体是哪一张表（对于每一个转化增加一个常量，常量值为表名，做错误处理时映射到tablename字段即可）。在脚本中则对表输出做分支，有错误的数据则流入ERR表中做记录，没有错误的直接插入目标表中，空操作代表不作任何处理，方便分支流程的查看。6、脚本命名规范总结为了方便管理脚本，对脚本使用一套规范的命名必不可少，合理的命名对编写脚本，修改脚本的效率有明显的提高。6.1transformation命名对于transformation命名一方面是整体命名，一方面是对tranformation中每个控件的命名。（1）整体命名：目标库名_增量/全量(add/all)_目标表名代表针对某一个库中某一张表做全量或者增量抽取数据（2）控件命名：a.表输入源库名_源表名代表从某一个库中的某一张表开始抽取数据b.表输出/插入更新目标库名_目标表名代表数据抽到某一个库中的某一张表c.其他控件简单说明控件做的操作，例如执行sql控件，简单描述sql执行的业务情况即可。6.2job命名对于job的命名主要是整体命名：机构名称_库名称_增量/全量（ADD/ALL）代表着针对此项目的某一个库做的增量或者全量的数据抽取操作的job脚本。7、脚本设置规范为了方便统一管理，对脚本信息进行统一设置添加脚本信息和脚本日志1.对kettle脚本中的tranformation添加作业描述，为job添加作业描述和扩展描述，方便展示当前的作业信息。2.对kettle脚本添加唯一连接设置3.对kettle转换和作业脚本添加错误日志信息