KETTLE详细设计.docx
《KETTLE详细设计.docx》由会员分享，可在线阅读，更多相关《KETTLE详细设计.docx（38页珍藏版）》请在冰豆网上搜索。
KETTLE详细设计
KETTLE详细设计说明书
V0.1
变更记录
版本
文件内容描述
日期
编写
审核
批准
V0.1
创建
2014-7-3
王小龙
1Kettle界面介绍
双击Kettle.exe或者Spoon.bat打开Kettle图形界面化如下：
（图1.1）
Kettle中有两类设计分别是：
Transformation（转换）与Job（作业），Transformation完成针对数据的基础转换，Job则完成整个工作流的控制。
Kettle常用三大家族：
Spoon、Pan、Kitchen。
Spoon:
通过图形界面方式设计、运行、调试Job与Transformation。
Pan:
通过脚本命令方式来运行Transformation。
Kitchen:
通过脚本命令方式来运行Job，一般就是通过调用Kitchen脚本来完成定时任务。
说明：
当打开Kettle时显示的是Kettle的相关版本信息与GNU相关协议说明。
（图1.2）
说明：
打开Kettle时弹出的窗口提示以资源库方式登录相关信息，关于乌江水电项目工程存储方式是以XML文件方式来进行存储，所以我就直接点击“没有资源库”。
图（1.3）
说明：
进入Kettle设计界面。
提示关于Spoon相关信息，这里直接点击“关闭”按钮。
（图1.4）
说明：
图片中所使用到的字母位置标识说明。
（A）：
Kettle所使用到的菜单栏。
（B）：
在使用Kettle时所涉及使用到的对象。
（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中所使用到的资源库进行操作，如：
资源库连接、断开、当前用户编辑等操作。
（图1.9）
说明：
转换：
是对Kettle当前所打开的Trans进行相关测试功能。
（图1.10）
说明：
作业：
是对Kettle当前所打开的Job进行操作，如：
运行、复制、参数设置等相关操作。
（图1.11）
说明：
向导：
是对Kettle当前所打开的Job或者Trans对其进行一步一步的指导性操作。
（图1.12）
说明：
帮助：
查看当前Kettle版本相关信息。
2Kettle数据源连接配置
Kettle中对于数据源有作用域的定义，也就是说有全局数据源与局部数据源两种。
首先打开KettleUI界面，使用快键方式（Ctrl+N）创建一个转换，新建数据源如下图：
（图2.0）
通过点击“新建”，则出现（图2.1）
（图2.1）
说明：
通过（图2.1）我们可以看到创建数据源时需要配置相应的参数：
ConnectionName（必填）：
配置数据源使用名称，如：
wjsd_src
HostName（必填）：
数据库主机IP地址，如：
192.168.1.254
DatabaseName（必填）：
数据库实例名称，如：
WJSD
TablespaceforData（可选）：
数据表空间名称
Tablespaceforindices（可选）：
数据索引名称
PortNumber（必填）：
端口号
UserName（必填）：
用户名
Password（必填）：
密码
Access：
选择数据库连接方式，ODBC方式则需要配置系统ODBC，JNDI则使用配置jdbc.properties文件，默认为Native（JDBC）连接方式，。
注：
在Kettle中如果在填写框末尾出现$符号标识说明此处可以使用变量参数来定义。
填写好数据库连接信息后，点击“Test”按钮，出现效果如（图2.2）
（图2.2）
说明：
配置成功数据源后默认情况下数据源作用域为局部数据源，如果需要把数据源修改成为全局数据源，则选择数据源wjsd_src右击Share保存即可。
3Kettle全量抽取
对于纬表、字典表或者是没有时间戳的表，一般我们都采用全量抽取的方式将业务系统库中的数据抽取到数据仓库中。
乌江水电项目需要用到全量抽取的源表有：
CIM.Measurement
CIM.Measurement_Type
CIM.d_Reservoir
CIM.Center_Sys_Code
CIM.CURVE_SCHED_DATA
CIM.d_date
CIM.CURVE_SCHEDULE
CIM.WJ_CURVE_TYPE
CIM.power_System_resource
PSR_TYPE
下面以CIM下面的d_Reservoir为例详细描述Kettle全量抽取的过程
1、 创建转换（Ctrl+N），转换名称为：
d_Reservoir
2、 创建数据源连接wjsd_src、wjsd_tag，可参考【Kettle数据源连接配置】
3、 在Kettle设计盘中拖入“表输入”、“表输出”两个组件即可
4、 执行转换对数据进行抽取
5、 查看结果是否与数据源中的数据是否一致
步骤1、创建转换
（图3.1）
说明：
当创建转换时点击“核心对象”下方则显示可以在转换中能使用的所有组件。
点击“输入”--单击“表输入”把“表输入”组件往空白处拖入即可。
（图3.2）
说明：
双击“表输入”组件弹出（图4.2），输入或选择相应的参数信息。
步骤名称：
默认为“表输入”，如果想规范化建议填写源表名称信息，以便清楚查询是某张表结构信息
数据库连接：
选择数据来源数据源名称
SQL：
编写查询源数据SQL脚本，可以通过“获取SQL查询语句…”获取。
允许延迟转换：
强烈建议默认
替换SQL语句里的变量：
如果SQL语句中有使用到变量则需要勾选
从步骤插入数据：
默认
执行每一行：
默认
记录数量限制：
0表示默认，如果有特殊需求可以自定义只查询出多少条记录数
注：
1、 在SQL语句中一定不能加入分号（;），这是初学者很容易犯错的一个地方。
2、 如果无法确定SQL语句是否正常时可以通过“预览”来查看SQL语句是否正确。
（图3.3）
说明：
有了源之后则需要有目标，也就是说有我水我们则需要把水通过渠道通向目标如（图4.3）
拖入“表输出”后，我们通过按住“Shift”键单击源划向目标“表输出”，则会出现源指向目标的箭头线。
双击“表输出”如（图4.3），另外可以选择两个或者两个以上的组件进行位置对齐，可以通过Ctrl+向上箭头、Ctrl+向下箭头、Ctrl+向左箭头、Ctrl+向右箭头。
步骤名称：
默认为“表输入”，如果想规范化建议填写目标表名称信息，以便清楚查询是某张表结构信息
数据库连接：
选择数据来目标数据源名称
目标模式：
schema，Oracle数据库则是用户名。
目标表：
从源数据输出到目标表名
提交记录数：
设置数据库提交大小
裁剪表：
清空目标表数据，相当于Oracle数据库Truncate语法
忽略插入错误：
当插入数据库出错时则可以忽略，默认忽略18条记录数，记不太清楚
Specifydatabasefields：
手动指定源与目标字段映射关系，如（图4.4）
Mainoptions：
如果是表分区则可以指定表分区，一般为默认
Databasefields：
如（图3.4）
（图3.4）
说明：
指定源与目标字段映射关系，点击Getfields匹配字段映射关系。
注：
在没有勾选“Specifydatabasefields”情况下，源表字段不能多于目标表的字段数量，否则会出错。
（图3.5）
说明：
点击运行按钮，或按F9，运行这个转换，根据（图3.5）可以查看出转换运行的日志情况。
（图3.6）
说明：
根据（图3.6）可以查看出每个步骤执行的记录数、读、写、输入、输出、更新、拒绝、错误、激活、时间、速度。
可以通过查看此处分析当前转换运行效率而断定转换是否需要进行优化。
总结：
1、 在运行转换时，当前转换是处于并发状态，如果有多个表输入则会同时运行。
2、 在设计转换时最好是一个数据流，不要有多条数据流。
如果有多条数据流则有可能发生锁表问题。
4Kettle增量抽取
对于
CIM.WJ_Day_Data
CIM.WJ_Run_Crenet_Power
hd.target_day
APP.DNN_POINT_DAY_VALUE
APP.DNN_OBJECT_DAY_VALUE
SOA_WJ_AGC_RECORDS_T
SOA_WJ_DISPATCH_MON_PLAN_T
SOA_WJ_DISPATCH_YEAR_PLAN_T
SOA_WJ_REPAIR_T
SOA_WJ_RUNDAY_REPORT_T
daydb
planload_factory
下面以traget_day为例，详细描述增量抽取数据。
因为是增量的抽取，所以参数的传递是必要的。
增量抽取一般都是抽取昨天的数据装载到目标表中。
Kettle中参数使用方法有两种：
一种是%%变量名%%，一种是${变量名}。
这两种方法变量数据类型都是数字类型。
1、 创建转换（Ctrl+N），转换名称为：
traget_day
2、 创建数据源连接wjsd_src、wjsd_tag，可参考【Kettle数据源连接配置】
3、 在Kettle设计盘中拖入’’执行SQL脚本”、“表输入”、“表输出”三个组件
4、 执行转换对数据进行抽取
5、 查看结果是否与数据源中的数据是否一致
（图4.1）
注：
在SQL中使用变量时需要把“是否替换变量”勾选上，否则无法使变量生效。
说明：
这里需要注意的是后面的限定where条件，因为源表的时间字段是时间类型，我们的参数是数字类型，所以需要根据源表时间字段的不同注意转换。
（图4.2）
说明：
这里与全量抽取的表输出差不多。
（图4.3）
说明：
这里要考虑到数据出错重跑的问题，如果需要重新插入输入参数日期的数据，就必须要先删除输入参数日期的数据，不然会报错。
因为我参数在条件里面，变量替换要打上沟，不然设置的变量会失效。
5Kettle实时数据抽取
乌江水电项目实时数据的表有
hd.target_real
hd.target_day
rtdb
RTEMS
rtcalc
RTSQ
REAL
hourdb
实时抽取与增量抽取基本上相差不大，只是对时间戳的处理方式不同，以REAL这张表为例，详细描述实时抽取过程的步骤。
1、 创建转换（Ctrl+N），转换名称为：
traget_day
2、 创建数据源连接wjsd_src、wjsd_tag，可参考【Kettle数据源连接配置】
3、 在Kettle设计盘中拖入’’执行SQL脚本”、“表输入”、“表输出”三个组件
4、 执行转换对数据进行抽取
5、 查看结果是否与数据源中的数据是否一致
（图5-1）
说明：
因为实时抽取的表时间戳字段一般都为TIMESTAMP类型，这里where条件对参数的处理必须这样，不然可能会在抽取数据的时候导致数据丢失。
（图5-2）
说明：
表输出与增量抽取相同，只是目标表不同。
图（5-3）
说明：
这里与增量抽取相类似，回跑的时候参数日期的数据必须要删除。
同时注意变量替换打上勾。
图（5-4）
说明：
依次把每个步骤连接起来，顺序如图5-4所示。
6Kettle定时任务
在增量抽取过程中，ETL定时任务是一个必不可少的环节，因为定时任务取决与你的ETL程序抽取业务数据的频率程度（日、周、季、月、年），一般情况下都采用T+1方式来抽取数据到目标表中，也可以通过指定日期参数抽取到目标表。
6.1参数变量设置转换
首先创建相应的参数变量设置转换（Set_Param.ktr）。
对于怎样获取昨天日期，一般通过“获取系统信息”组件获取昨天日期，再通过字段选择转换成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）
将事先完成的参数转换Set_Param插入到作业中，添加作业变量：
图（6.2-2）
作业设置，快捷键Ctrl+J，设置变量名为：
YESTERDAY，默认值为：
NULL。
添加Set_Param转换变量如下图：
图（6.2-3）
然后我们把作业中的YESTERDAY变量值传递给Set_Param转换
图（6.2-4）
转换设置，快捷键Ctrl+T，设置变量名为：
YESTERDAY，默认值为：
NULL。
设置完成转换变量后，我们通过“GetVariables”组件来获取YESTERDAY变量的值，如下图：
图（6.2-5）
再通过“过滤记录”组件来判断YESTERDAY变量是否有等于NULL。
如下图：
（图6.2-5）
如果等于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）
Transformation2的名称可以自己更改，这里为ETL_DAYDB，点击雪花形状的绿色按钮，找到ETL_DAYDB这个转换，点击确定即可完成对作业的设置。
6.3Kitchen脚本
完成作业与转换的开发，下一步将编写Kitchen脚本，Kitchen脚本程序分为增量脚本、全量脚本与实时脚本，脚本程序如下：
名称：
Etl_Sch_day
@echooff
setkettle_path=C:
\Kettle4.2
setJOB_PATH=C:
\kettle_rep
setYE=%date:
~0,4%
setMO=%date:
~5,2%
setDA=%date:
~8,2%
setDG=1
set/avY1=%YE%%%400
set/avY2=%YE%%%4
set/avY3=%YE%%%100
if%vY1%==0（setvar=true）else（if%vY2%==0（if%vY3%==0（setvar=false）else（setvar=true））else（setvar=false））
setLY=%YE%
setLM=%MO%
if%MO:
~0,1%==0（setMO=%MO:
~1,1%）
if%DA:
~0,1%==0（setDA=%DA:
~1,1%）
if%DA%GTR%DG%（set/aLD=%DA%-%DG%）else（
if%MO%==1（set/aLY=%YE%-1）&（set/aLM=12）&（set/aLD=31+%DA%-%DG%）else（
set/aLM=%MO%-1
if%MO%==3（if%var%==false（set/aLD=28+%DA%-%DG%）else（set/aLD=29+%DA%-%DG%））
for%%ain（2468911）do（if"%MO%"=="%%a"（set/aLD=31+%DA%-%DG%））
for%%bin（571012）do（if"%MO%"=="%%b"（set/aLD=30+%DA%-%DG%））））
if%LM%LSS10setLM=0%LM:
~-1%
if%LD%LSS10setLD=0%LD:
~-1%
setstrLstDt=%LY%%LM%%LD%
CALL%KETTLE_PATH%\Kitchen.bat
-file=%JOB_PATH%\etl_day_schedule.kjb-param:
YESTERDAY=%strLstDt%
脚本名称：
Etl_Sch_rt
@echooff
setkettle_path=C:
\Kettle4.2
setJOB_PATH=C:
\kettle_rep
CALL%KETTLE_PATH%\Kitchen.bat-file=%JOB_PATH%\etl_real_schedule.kjb-param:
YESTERDAY=%date:
~0,4%%date:
~5,2%%date:
~8,2%
脚本名称：
Etl_Sch_all
@echooff
setkettle_path=C:
\Kettle4.2
setJOB_PATH=C:
\kettle_rep
CALL%KETTLE_PATH%\Kitchen.bat-file=%JOB_PATH%\etl_all_schedule.kjb
6.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方式抽取数据。
实时数据的抽取的定时任务也是类似这样的步骤，不同点在于实时数据的抽取是间歇性的，一般是每隔多少分钟跑一次数据，而不是定时在每天一个时间点跑数。
升级会员 