KETTLE详细设计说明.docx

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

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

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

4、当前用户编辑等操作。(图1.9)说明：转换：是对Kettle当前所打开的Trans进行相关测试功能。(图1.10)说明：作业：是对Kettle当前所打开的Job进行操作，如：运行、复制、参数设置等相关操作。(图1.11)说明：向导：是对Kettle当前所打开的Job或者Trans对其进行一步一步的指导性操作。(图1.12)说明：帮助：查看当前Kettle版本相关信息。2 Kettle数据源连接配置Kettle中对于数据源有作用域的定义，也就是说有全局数据源与局部数据源两种。 首先打开Kettle UI界面，使用快键方式(Ctrl+N)创建一个转换，新建数据源如下图：(图2.0)通过点击“新建”

5、，则出现(图2.1)(图2.1)说明：通过(图2.1)我们可以看到创建数据源时需要配置相应的参数：Connection Name(必填)：配置数据源使用名称，如：wjsd_srcHost Name(必填)：数据库主机IP地址，如：192.168.1.254Database Name(必填)：数据库实例名称，如：WJSDTablespace for Data(可选)：数据表空间名称Tablespace for indices(可选)：数据索引名称Port Number(必填)：端口号User Name(必填)：用户名Password(必填)：密码Access：选择数据库连接方式，ODBC方式则需

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

7、MeasurementCIM.Measurement_TypeCIM.d_ReservoirCIM.Center_Sys_CodeCIM.CURVE_SCHED_DATACIM.d_dateCIM.CURVE_SCHEDULECIM.WJ_CURVE_TYPECIM.power_System_resourcePSR_TYPE下面以CIM下面的d_Reservoir为例详细描述Kettle全量抽取的过程1、 创建转换(Ctrl+N)，转换名称为：d_Reservoir2、 创建数据源连接wjsd_src、wjsd_tag，可参考【Kettle数据源连接配置】3、 在Kettle设计盘中拖入“表输

8、入”、“表输出”两个组件即可4、 执行转换对数据进行抽取5、 查看结果是否与数据源中的数据是否一致步骤1、创建转换(图3.1)说明：当创建转换时点击“核心对象”下方则显示可以在转换中能使用的所有组件。点击“输入”-单击“表输入”把“表输入”组件往空白处拖入即可。(图3.2)说明：双击“表输入”组件弹出(图4.2)，输入或选择相应的参数信息。步骤名称：默认为“表输入”，如果想规范化建议填写源表名称信息，以便清楚查询是某张表结构信息数据库连接：选择数据来源数据源名称SQL：编写查询源数据SQL脚本，可以通过“获取SQL查询语句”获取。允许延迟转换：强烈建议默认替换SQL语句里的变量：如果SQL语句

9、中有使用到变量则需要勾选从步骤插入数据：默认执行每一行：默认记录数量限制：0表示默认，如果有特殊需求可以自定义只查询出多少条记录数注：1、 在SQL语句中一定不能加入分号(;)，这是初学者很容易犯错的一个地方。2、 如果无法确定SQL语句是否正常时可以通过“预览”来查看SQL语句是否正确。(图3.3)说明：有了源之后则需要有目标，也就是说有我水我们则需要把水通过渠道通向目标如(图4.3)拖入“表输出”后，我们通过按住“Shift”键单击源划向目标“表输出”，则会出现源指向目标的箭头线。双击“表输出”如(图4.3)，另外可以选择两个或者两个以上的组件进行位置对齐，可以通过Ctrl+向上箭头、Ct

10、rl+向下箭头、Ctrl+向左箭头、Ctrl+向右箭头。步骤名称：默认为“表输入”，如果想规范化建议填写目标表名称信息，以便清楚查询是某张表结构信息数据库连接：选择数据来目标数据源名称目标模式：schema，Oracle数据库则是用户名。目标表：从源数据输出到目标表名提交记录数：设置数据库提交大小裁剪表：清空目标表数据，相当于Oracle数据库Truncate语法忽略插入错误：当插入数据库出错时则可以忽略，默认忽略18条记录数，记不太清楚Specify database fields：手动指定源与目标字段映射关系，如(图4.4)Main options：如果是表分区则可以指定表分区，一般为默认

11、Database fields：如(图3.4)(图3.4)说明：指定源与目标字段映射关系，点击Get fields匹配字段映射关系。注：在没有勾选“Specify databasefields”情况下，源表字段不能多于目标表的字段数量，否则会出错。(图3.5)说明：点击运行按钮，或按F9，运行这个转换，根据(图3.5)可以查看出转换运行的日志情况。(图3.6)说明：根据(图3.6)可以查看出每个步骤执行的记录数、读、写、输入、输出、更新、拒绝、错误、激活、时间、速度。可以通过查看此处分析当前转换运行效率而断定转换是否需要进行优化。总结：1、 在运行转换时，当前转换是处于并发状态，如果有多个表输

12、入则会同时运行。2、 在设计转换时最好是一个数据流，不要有多条数据流。如果有多条数据流则有可能发生锁表问题。4 Kettle增量抽取对于CIM.WJ_Day_DataCIM.WJ_Run_Crenet_Powerhd.target_dayAPP.DNN_POINT_DAY_VALUEAPP.DNN_OBJECT_DAY_VALUESOA_WJ_AGC_RECORDS_TSOA_WJ_DISPATCH_MON_PLAN_TSOA_WJ_DISPATCH_YEAR_PLAN_TSOA_WJ_REPAIR_TSOA_WJ_RUNDAY_REPORT_Tdaydbplanload_factory下面以

13、traget_day为例，详细描述增量抽取数据。因为是增量的抽取，所以参数的传递是必要的。增量抽取一般都是抽取昨天的数据装载到目标表中。Kettle中参数使用方法有两种：一种是%变量名%，一种是$变量名。这两种方法变量数据类型都是数字类型。1、 创建转换(Ctrl+N)，转换名称为：traget_day2、 创建数据源连接wjsd_src、wjsd_tag，可参考【Kettle数据源连接配置】3、 在Kettle设计盘中拖入执行SQL脚本”、“表输入”、“表输出”三个组件4、 执行转换对数据进行抽取5、 查看结果是否与数据源中的数据是否一致 （图 4.1）注：在SQL中使用变量时需要把“是否替

14、换变量”勾选上，否则无法使变量生效。说明：这里需要注意的是后面的限定where条件，因为源表的时间字段是时间类型，我们的参数是数字类型，所以需要根据源表时间字段的不同注意转换。 （图 4.2）说明：这里与全量抽取的表输出差不多。 （图 4.3）说明：这里要考虑到数据出错重跑的问题，如果需要重新插入输入参数日期的数据，就必须要先删除输入参数日期的数据，不然会报错。因为我参数在条件里面，变量替换要打上沟，不然设置的变量会失效。5 Kettle实时数据抽取乌江水电项目实时数据的表有hd.target_realhd.target_dayrtdbRTEMSrtcalcRTSQREALhourdb实时抽取

15、与增量抽取基本上相差不大，只是对时间戳的处理方式不同，以REAL这张表为例，详细描述实时抽取过程的步骤。1、 创建转换(Ctrl+N)，转换名称为：traget_day2、 创建数据源连接wjsd_src、wjsd_tag，可参考【Kettle数据源连接配置】3、 在Kettle设计盘中拖入执行SQL脚本”、“表输入”、“表输出”三个组件4、 执行转换对数据进行抽取5、 查看结果是否与数据源中的数据是否一致 （图 5-1）说明：因为实时抽取的表时间戳字段一般都为TIMESTAMP类型，这里where条件对参数的处理必须这样，不然可能会在抽取数据的时候导致数据丢失。 （图 5-2）说明：表输出与

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

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

18、的参数转换Set_Param插入到作业中，添加作业变量： 图（6.2-2）作业设置，快捷键Ctrl+J，设置变量名为：YESTERDAY，默认值为：NULL。添加Set_Param转换变量如下图： 图（6.2-3）然后我们把作业中的YESTERDAY变量值传递给Set_Param转换 图（6.2-4）转换设置，快捷键Ctrl+T，设置变量名为：YESTERDAY，默认值为：NULL。设置完成转换变量后，我们通过“Get Variables”组件来获取YESTERDAY变量的值，如下图： 图（6.2-5）再通过“过滤记录”组件来判断YESTERDAY变量是否有等于NULL。如下图：(图6.2-5

19、)如果等于NULL数据流则走“获取系统信息组件”，反之则直接设置变量。如下图：(图6.2-6)注：由于自定义的变量与获取系统信息中的变量重名，产生冲突，无法获取到相应的数值，需要通过“字段选择”排除掉YESTERDAY变量，而Yesterday则自动改成Yesterday_1然后再改成Yesterday名，如下图：(图6.2-7)最终Set_Param转换数据流程如下图：(图6.2-8)设置好Set_Param后，在job中还需要对参数进行设置，参数名称YESTERDAY，参数值$YESTERDAY 图（6.2-9）将事先完成好的抽取数据流转换添加到JOB中，如下图 图（6.2-10）Tran

20、sformation2的名称可以自己更改，这里为ETL_DAYDB，点击雪花形状的绿色按钮，找到ETL_DAYDB这个转换，点击确定即可完成对作业的设置。6.3 Kitchen脚本完成作业与转换的开发，下一步将编写Kitchen脚本，Kitchen脚本程序分为增量脚本、全量脚本与实时脚本，脚本程序如下：名称：Etl_Sch_day echo offset kettle_path=C:Kettle4.2set JOB_PATH=C:kettle_repset YE=%date:0,4%set MO=%date:5,2%set DA=%date:8,2%set DG=1set/a vY1=%YE%

21、 % 400set/a vY2=%YE% % 4set/a vY3=%YE% % 100if %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

22、LY=%YE%-1) & (set/a LM=12) & (set/a LD=31+%DA%-%DG%) else (set/a LM=%MO%-1if %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%)for %b in (5 7 10 12) do (if %MO%=%b (set/a LD=30+%DA%-%DG%)if %LM% LSS 10 set LM=0%L

23、M:-1%if %LD% LSS 10 set LD=0%LD:-1%set strLstDt=%LY%LM%LD%CALL %KETTLE_PATH%Kitchen.bat -file=%JOB_PATH%etl_day_schedule.kjb -param:YESTERDAY=%strLstDt%脚本名称：Etl_Sch_rtecho offset kettle_path=C:Kettle4.2set JOB_PATH=C:kettle_repCALL %KETTLE_PATH%Kitchen.bat -file=%JOB_PATH%etl_real_schedule.kjb -para

24、m:YESTERDAY=%date:0,4%date:5,2%date:8,2%脚本名称：Etl_Sch_allecho offset kettle_path=C:Kettle4.2set JOB_PATH=C:kettle_repCALL %KETTLE_PATH%Kitchen.bat -file=%JOB_PATH%etl_all_schedule.kjb6.4 定时任务定时任务步骤：控制面板管理工具任务计划程序创建基本任务，如下图： 图 (6.4-1)名称：etl_sche_day描述：可写可不写点击下一步，如下图： 图（6.4-2）选择每天定时，点击下一步如下图： 图（6.4-3）定时成每天早上8点开始执行，点击下一步如图： 图（6.4-4）选择启动程序，点击下一步如下图： 图（6.4-5）输入Etl_Sch_day.bat文件路径，点击下一步如下图： 图（6.4-6）最后完成，这样定时任务计划则每天定时T+1方式抽取数据。实时数据的抽取的定时任务也是类似这样的步骤，不同点在于实时数据的抽取是间歇性的，一般是每隔多少分钟跑一次数据，而不是定时在每天一个时间点跑数。