KETTLE详细设计

1、KETTLE羊细设计说明书 变更记录 版本 文件内容描述 日期 编写 审核 批准 创建 2014-7-3 王小龙 1 Kettle 界面介绍 双击或者打开 Kettle 图形界面化如下： （图 Kettle 中有两类设计分别是：Transformation （转换）与Job （作业），Transformation 完成针对数 据的基础转换， Job 则完成整个工作流的控制。 Kettle 常用三大家族： Spoon、 Pan、Kitchen 。 Spoon: 通过图形界面方式设计、运行、调试Job 与 Transformation 。 Pan: 通过脚本命令方式来运行 Transformati

2、on 。 Kitchen :通过脚本命令方式来运行 Job，般就是通过调用 Kitchen脚本来完成定时任务。 说明：当打开Kettle时显示的是Kettle的相关版本信息与GNU相关协议说明。 （图 说明： 打开 Kettle 时弹出的窗口提示以资源库方式登录相关信息，关于乌江水电项目工程 存储方式是以XML文件方式来进行存储，所以我就直接点击“没有资源库”。 图 说明： 进入 Kettle 设计界面。提示关于 Spoon 相关信息，这里直接点击“关闭”按钮。 （图 说明： 图片中所使用到的字母位置标识说明。 （A）： Kettle 所使用到的菜单栏。 （B）：在使用 Kettle 时所涉及

3、使用到的对象。 （C）： Kettle 中所有的组件。 （D）:根据选择（B）或者（C）显示相应的结果。 （E）： Kettle 设计界面。 （图 说明： 文件：是对 Kettle 所生成的 Job 与 Trans 进行相关的操作，如：新建、打开、保存、导入、 导出等相关操作。 （图 说明： 编辑：是对 Kettle 当前打开的 Job 与 Trans 进行相关的操作，如：复制、撤销、环境变量 显示、资源库查看、图形界面自定义调整 （颜色、字体、样式 ）等相关操作。 （图 说明： 视图：是对 Kettle 当前打开的 Job 与 Trans 进行放大、缩小相关操作。 （图 说明： 资源库：是对

4、 Kettle 中所使用到的资源库进行操作，如：资源库连接、断开、当前用户编 辑等操作。 （图 说明： 转换：是对 Kettle 当前所打开的 Trans 进行相关测试功能。 （图 说明： 作业：是对 Kettle 当前所打开的 Job 进行操作，如：运行、复制、参数设置等相关操作。 （图 说明： 向导：是对 Kettle 当前所打开的 Job 或者 Trans 对其进行一步一步的指导性操作。 （图 说明： 帮助：查看当前 Kettle 版本相关信息。 2 Kettle 数据源连接配置 Kettle 中对于数据源有作用域的定义，也就是说有全局数据源与局部数据源两种。 首先打开 Kettle U

5、I 界面，使用快键方式 （Ctrl+N） 创建一个转换，新建数据 源如下图： （图 通过点击“新建”，则出现 （ 图 （图 说明： 通过 （图我们可以看到创建数据源时需要配置相应的参数： Connection Name（ 必填 ） ：配置数据源使用名称，如： wjsd_src Host Name（必填）：数据库主机IP地址，如：Name（必填）：数据库实例名称，如：WJSD Tablespace for Data（ 可选 ） ：数据表空间名称 Tablespace for indices（ 可选）：数据索引名称 Port Number（ 必填 ）：端口号 User Name（ 必填 ） ：用户

6、名 Password（ 必填 ） ：密码 Access :选择数据库连接方式，ODBC方式则需要配置系统 ODBC JNDI则使用配置文件，默 认为Native（JDBC）连接方式，。 注：在Kettle中如果在填写框末尾岀现$符号标识说明此处可以使用变量参数来定义。 填写好数据库连接信息后，点击“ Test”按钮，出现效果如（图 （图 说明： 配置成功数据源后默认情况下数据源作用域为局部数据源，如果需要把数据源修改成为全局 数据源，则选择数据源wjsd_src右击Share保存即可。 3 Kettle全量抽取 对于纬表、字典表或者是没有时间戳的表，一般我们都采用全量抽取的方式将业务系 统库中

7、的数据抽取到数据仓库中。 乌江水电项目需要用到全量抽取的源表有： 1、创建转换（Ctrl+N），转换名称为：d_Reservoir 2、创建数据源连接 wjsd_src、wjsd_tag，可参考【Kettle数据源连接配置】 3、在Kettle设计盘中拖入“表输入”、“表输出”两个组件即可 4、执行转换对数据进行抽取 5、查看结果是否与数据源中的数据是否一致 步骤1、创建转换 （图 说明： 当创建转换时点击“核心对象”下方则显示可以在转换中能使用的所有组件。点击“输 入”-单击“表输入”把“表输入”组件往空白处拖入即可。 （图 说明： 双击“表输入”组件弹出（图，输入或选择相应的参数信息。 步

8、骤名称：默认为“表输入”，如果想规范化建议填写源表名称信息，以便清楚查询是某张 表结构信息 数据库连接：选择数据来源数据源名称 SQL编写查询源数据 SQL脚本，可以通过“获取 SQL查询语句”获取。 允许延迟转换：强烈建议默认 替换SQL语句里的变量：如果 SQL语句中有使用到变量则需要勾选 从步骤插入数据：默认 执行每一行：默认 记录数量限制： 0 表示默认，如果有特殊需求可以自定义只查询出多少条记录数 注： 1、在SQL语句中一定不能加入分号（;），这是初学者很容易犯错的一个地方。 2、如果无法确定 SQL语句是否正常时可以通过“预览”来查看SQL语句是否正确。 （图 说明： 有了源之后

9、则需要有目标，也就是说有我水我们则需要把水通过渠道通向目标如（ 图 拖入“表输出”后，我们通过按住“ Shift ”键单击源划向目标“表输出”，则会出现源指 向目标的箭头线。双击“表输出”如（图，另外可以选择两个或者两个以上的组件进行位置 对齐，可以通过 Ctrl+ 向上箭头、 Ctrl+ 向下箭头、 Ctrl+ 向左箭头、 Ctrl+ 向右箭头。 步骤名称： 默认为“表输入”， 如果想规范化建议填写目标表名称信息， 以便清楚查询是某 张表结构信息 数据库连接：选择数据来目标数据源名称 目标模式： schema， Oracle 数据库则是用户名。 目标表：从源数据输出到目标表名 提交记录数：设

10、置数据库提交大小 裁剪表：清空目标表数据，相当于 Oracle 数据库 Truncate 语法 忽略插入错误：当插入数据库出错时则可以忽略，默认忽略18 条记录数，记不太清楚 Specify database fields：手动指定源与目标字段映射关系，如 （ 图 Main options ：如果是表分区则可以指定表分区，一般为默认 Database fields ：如 （ 图 （图 说明： 指定源与目标字段映射关系，点击 Get fields 匹配字段映射关系。 注：在没有勾选“ Specify databasefields ”情况下，源表字段不能多于目标表的字段数量，否则会 出错。 （图

11、说明： 点击运行按钮，或按 F9，运行这个转换，根据（图可以查看出转换运行的日志情况。 （图 说明： 根据（图可以查看出每个步骤执行的记录数、读、写、输入、输出、更新、拒绝、错误、激 活、时间、速度。 可以通过查看此处分析当前转换运行效率而断定转换是否需要进行优化。 总结： 1、在运行转换时，当前转换是处于并发状态，如果有多个表输入则会同时运行。 2、在设计转换时最好是一个数据流，不要有多条数据流。如果有多条数据流则有可能发 生锁表问题。 4 Kettle增量抽取 对于 SOA WJ AGC RECORDS T SOA WJ DISPATCH MON PLAN T SOA_WJ_DISPATC

12、H_YEAR_PLAN_T SOA WJ REPAIR T SOA WJ RUNDAY REPORT T daydb pla nl oad factory 下面以traget_day 为例，详细描述增量抽取数据。因为是增量的抽取，所以参数的传递是 必要的。增量抽取一般都是抽取昨天的数据装载到目标表中。Kettle 中参数使用方法有两 种：一种是（变量名%种是$变量名。这两种方法变量数据类型都是数字类型。 1、创建转换（Ctrl+N），转换名称为：traget_day 2、创建数据源连接 wjsd_src、wjsd_tag，可参考【Kettle数据源连接配置】 3、在Kettle设计盘中拖入执行

13、 SQL脚本”、“表输入”、“表输出”三个组件 4、执行转换对数据进行抽取 5、查看结果是否与数据源中的数据是否一致 （图） 注：在SQL中使用变量时需要把“是否替换变量”勾选上，否则无法使变量生效。 说明：这里需要注意的是后面的限定 where条件，因为源表的时间字段是时间类型，我们的 参数是数字类型，所以需要根据源表时间字段的不同注意转换。 （图） 说明：这里与全量抽取的表输出差不多。 （图） 说明：这里要考虑到数据出错重跑的问题，如果需要重新插入输入参数日期的数据，就必须 要先删除输入参数日期的数据，不然会报错。因为我参数在条件里面，变量替换要打上沟， 不然设置的变量会失效。 5 Ket

14、tle实时数据抽取 乌江水电项目实时数据的表有 REAL这张表 为例，详细描述实时抽取过程的步骤。 1、 创建转换（Ctrl+N），转换名称为：traget_day 2、 创建数据源连接 wjsd_src、wjsd_tag，可参考【Kettle 数据源连接配置】 3、 在Kettle设计盘中拖入执行 SQL脚本”、“表输入” 、“表输出”三个组件 4、 执行转换对数据进行抽取 5、 查看结果是否与数据源中的数据是否一致 （图 5-1） 说明：因为实时抽取的表时间戳字段一般都为TIMESTAMPS型，这里where条件对参数的处 理必须这样，不然可能会在抽取数据的时候导致数据丢失。 （图 5-2

15、 ） 说明：表输出与增量抽取相同，只是目标表不同。 图（5-3） 说明：这里与增量抽取相类似，回跑的时候参数日期的数据必须要删除。同时注意变量替换 打上勾。 图（5-4） 说明：依次把每个步骤连接起来，顺序如图5-4所示。 6 Kettle 定时任务 在增量抽取过程中， ETL 定时任务是一个必不可少的环节， 因为定时任务取决与你的 ETL 程序抽取业务数据的频率程度 （日、周、季、月、年），一般情况下都采用 T+1方式来抽取数 据到目标表中，也可以通过指定日期参数抽取到目标表。 参数变量设置转换 首先创建相应的参数变量设置转换。 对于怎样获取昨天日期， 一般通过“获取系统信息”组件获取昨天日

16、期， 再通过字段选择转 换成 yyyy-mm-dd 格式，最后设置成变量，设置参数变量为 $YESTERDAY。 如下图： （图） 获取相关的日期参数，昨天日期。 （图 ） 由于获取日期是到时分秒，通过字段选择转换成年月日格式。 图（） 设置参数，点确定时会提示大致意思是“设置的参数不能在当前转换中使用”。 图（） 依次连接每个步骤，简单的参数设置转换就算完成了。 创建作业 （Job） 关于Kettle的Job是数据流抽取的重点环节，首先创建一个作业（Job），点击文件 新建作业。在作业里面放入START、 Transform 、 Success, 如图（）所示 图（） 将事先完成的参数转换

17、Set_Param 插入到作业中，添加作业变量： 图（） 作业设置，快捷键 Ctrl+J，设置变量名为： YESTERDA丫默认值为：NULL 添加 Set_Param 转换变量如下图： 图（） 然后我们把作业中的YESTERDA变量值传递给Set_Param转换 图（） 转换设置，快捷键 Ctrl+T，设置变量名为：YESTERDAY默认值为：NULL。 设置完成转换变量后，我们通过“ Get Variables ”组件来获取 YESTERDA变量的值，如下图： 图（） 再通过“过滤记录”组件来判断YESTERDA变量是否有等于 NULL。如下图： （图 如果等于NULL数据流则走“获取系统

18、信息组件”，反之则直接设置变量。如下图： （图 注：由于自定义的变量与获取系统信息中的变量重名，产生冲突，无法获取到相应的数值， 需要通过“字段选择”排除掉YESTERDA变量，而Yesterday则自动改成 Yesterday然后 再改成 Yesterday 名，如下图： （图 最终 Set_Param 转换数据流程如下图： （图 设置好 Set_Param后，在job中还需要对参数进行设置，参数名称YESTERDAY参数值 $YESTERDAY 图（） 将事先完成好的抽取数据流转换添加到JOB中，如下图 图（） Transformation2 的名称可以自己更改，这里为ETL_DAYD，B

19、 点击雪花形状的绿色按钮，找 到ETL_DAYD这个转换，点击确定即可完成对作业的设置。 Kitchen 脚本 完成作业与转换的开发，下一步将编写 Kitchen 脚本， Kitchen 脚本程序分为增量脚本、全 量脚本与实时脚本，脚本程序如下： 名称： Etl_Sch_day echo off set JOB_PATH=C:kettle_rep set YE=%date:0,4% set MO=%date:5,2% set DA=%date:8,2% set DG=1 set/a vY1=%YE% % 400 set/a vY2=%YE% % 4 set/a vY3=%YE% % 100 i

20、f %vY1%=0（set var=true） else （if %vY2%=0（if %vY3%=0（set var=false） else （set var=true) else (set var=false) set LY=%YE% set LM=%MO% if %MO:0,1%=0 (set MO=%MO:1,1%) if %DA:0,1%=0 (set DA=%DA:1,1%) if %DA% GTR %DG% (set/a LD=%DA%-%DG%) else ( if %MO%=1 (set/a LY=%YE%-1) & (set/a LM=12) & (set/a LD=31+%DA%-%DG%) else ( set/a LM=%MO%-1 if %MO%=3(if %var%=false (set/a LD=28+%DA%-%DG%) else (set/a LD=29+%DA%-%DG%) for %a in (2 4 6 8 9 11) do (if %MO%=%a (set/a LD=31+%DA%-%DG%