ETL工具kettle学习总结.docx
《ETL工具kettle学习总结.docx》由会员分享，可在线阅读，更多相关《ETL工具kettle学习总结.docx（28页珍藏版）》请在冰豆网上搜索。
ETL工具kettle学习总结
概览
Kettle也叫PDI（全称是PentahoDataIntegeration），是一款开源的ETL工具，项目开始于2003年，2006年加入了开源的BI组织Pentaho,正式命名为PDI。
官方网站：
http:
//kettle.pentaho.org/
术语
1.Transformation
转换步骤，可以理解为将一个或者多个不同的数据源组装成一条数据流水线。
然后最终输出到某一个地方，文件或者数据库等。
2.Job
作业，可以调度设计好的转换，也可以执行一些文件处理（比较，删除等），还可以ftp上传，下载文件，发送邮件，执行shell命令等,
3.Hop连接转换步骤或者连接Job（实际上就是执行顺序）的连线
Transformationhop：
主要表示数据的流向。
从输入，过滤等转换操作，到输出。
Jobhop：
可设置执行条件：
1，无条件执行
2，当上一个Job执行结果为true时执行
3，当上一个Job执行结果为false时执行
Kettle,etl设计及运行
1.Kettle整体结构图
Kettle整体结构图
2.转换设计样例图
绿色线条为hop,流水线
转换设计样例
3.运行方式
使用javawebstart方式运行的配置方法
命令行方式
1）Windows下执行kitchen.bat，多个参数之间以“/”分隔，Key和value以”:
”分隔
例如：
kitchen.bat/file:
F:
\samples\demo-table2table.ktr/level:
Basic/log:
test123.log
/file：
指定转换文件的路径
/level：
执行日志执行级别
/log:
执行日志文件路径
2）Linux下执行kitchen.sh，多个参数之间以“-”分隔，Key和value以”=”分隔
kitchen.sh-file=/home/updateWarehouse.kjb-level=Minimal
如果设计的转换，Job是保存在数据库中，则命令如下：
Kitchen.bat/rep:
资源库名称/user:
admin/pass:
admin/job:
job名
4.Xml保存转换，job流程
设计用户定义的作业可以保存在（xml格式）中或某一个特定的数据库中
转换的设计文件以.ktr结尾（xml文格式），保存所有配置好的数据库连接，文件相对路径，字段映射关系等信息。
Job的设计文件以.kjb结尾，下面是一个调用已经设计好的转换的job文件的一部分：
demotest
Transformation
TRANS
${Internal.Job.Filename.Directory}/demo-table2table.ktr
loadcustomerdatajob
${Internal.Job.Filename.Directory}
N
N
N
N
N
………
5.数据库保存转换，job流程
列出几个重要的表
1）r_job:
保存job的id,name,status,执行时间，创建时间，修改时间等信息
建表语句：
DROPTABLEIFEXISTS`r_job`;
CREATETABLE`r_job`（
`ID_JOB`bigint（20）NOTNULL,
`ID_DIRECTORY`int（11）DEFAULTNULL,
`NAME`varchar（255）DEFAULTNULL,
`DESCRIPTION`mediumtext,
`EXTENDED_DESCRIPTION`mediumtext,
`JOB_VERSION`varchar（255）DEFAULTNULL,
`JOB_STATUS`int（11）DEFAULTNULL,
`ID_DATABASE_LOG`int（11）DEFAULTNULL,
`TABLE_NAME_LOG`varchar（255）DEFAULTNULL,
`CREATED_USER`varchar（255）DEFAULTNULL,
`CREATED_DATE`datetimeDEFAULTNULL,
`MODIFIED_USER`varchar（255）DEFAULTNULL,
`MODIFIED_DATE`datetimeDEFAULTNULL,
`USE_BATCH_ID`char
（1）DEFAULTNULL,
`PASS_BATCH_ID`char
（1）DEFAULTNULL,
`USE_LOGFIELD`char
（1）DEFAULTNULL,
`SHARED_FILE`varchar（255）DEFAULTNULL,
PRIMARYKEY（`ID_JOB`）
）
2）r_jobentry:
设计界面上的一个实体对应一个entry,通过job的id关联
DROPTABLEIFEXISTS`r_jobentry`;
CREATETABLE`r_jobentry`（
`ID_JOBENTRY`bigint（20）NOTNULL,
`ID_JOB`int（11）DEFAULTNULL,
`ID_JOBENTRY_TYPE`int（11）DEFAULTNULL,
`NAME`varchar（255）DEFAULTNULL,
`DESCRIPTION`mediumtext,
PRIMARYKEY（`ID_JOBENTRY`）
）
3）r_jobentry_attribute：
job的详细信息表，包括job执行规则，执行过程中的参数来源，日志记录等
DROPTABLEIFEXISTS`r_jobentry_attribute`;
CREATETABLE`r_jobentry_attribute`（
`ID_JOBENTRY_ATTRIBUTE`bigint（20）NOTNULL,
`ID_JOB`int（11）DEFAULTNULL,
`ID_JOBENTRY`int（11）DEFAULTNULL,
`NR`int（11）DEFAULTNULL,
`CODE`varchar（255）DEFAULTNULL,
`VALUE_NUM`doubleDEFAULTNULL,
`VALUE_STR`mediumtext,
PRIMARYKEY（`ID_JOBENTRY_ATTRIBUTE`）
）
4）r_step：
保存转换的步骤id,名字等
r_step建表语句：
DROPTABLEIFEXISTS`r_step`;
CREATETABLE`r_step`（
`ID_STEP`bigint（20）NOTNULL,
`ID_TRANSFORMATION`int（11）DEFAULTNULL,
`NAME`varchar（255）DEFAULTNULL,
`DESCRIPTION`mediumtext,
`ID_STEP_TYPE`int（11）DEFAULTNULL,
`DISTRIBUTE`char
（1）DEFAULTNULL,
`COPIES`int（11）DEFAULTNULL,
`GUI_LOCATION_X`int（11）DEFAULTNULL,
`GUI_LOCATION_Y`int（11）DEFAULTNULL,
`GUI_DRAW`char
（1）DEFAULTNULL,
PRIMARYKEY（`ID_STEP`）
）
5）r_step_attribute：
转换步骤的详细信息，字段重命名，字段映射等。
通过外键id_transformation与r_step的id_transformation关联
DROPTABLEIFEXISTS`r_step_attribute`;
CREATETABLE`r_step_attribute`（
`ID_STEP_ATTRIBUTE`bigint（20）NOTNULL,
`ID_TRANSFORMATION`int（11）DEFAULTNULL,
`ID_STEP`int（11）DEFAULTNULL,
`NR`int（11）DEFAULTNULL,
`CODE`varchar（255）DEFAULTNULL,--操作编码譬如：
truncate，也可以是字段映射等信息
`VALUE_NUM`bigint（20）DEFAULTNULL,--操作值
`VALUE_STR`mediumtext,--操作值，譬如truncate对应的是Y或者N
PRIMARYKEY（`ID_STEP_ATTRIBUTE`）,
UNIQUEKEY`IDX_STEP_ATTRIBUTE_LOOKUP`（`ID_STEP`,`CODE`,`NR`）
）
说明：
如果有一个字段firtstname映射到name则在r_step_attribute中增加两条记录。
6.Kettle组成部分
Chef：
是一个图形用户界面，使用SWT开发，用来设计一个作业，转换，SQL，FTP，邮件，检查表存在，检查文件存在，执行SHELL脚本
Kitchen：
作业执行引擎，用来进行转换，校验，FTP上传。
可以执行xml格式定义的任务以及保存在数据库上的。
kitchen.bat/file:
D:
\Jobs\updateWarehouse.kjb/level:
Basic
kitchen.sh-file=/PRD/updateWarehouse.kjb-level=Minimal
kitchen.bat/rep:
"ProductionRepository"
/job:
"Updatedimensions"
/dir:
/Dimensions
/user:
matt
/pass:
somepassword123
/level:
Basic
kitchen.bat/file:
F:
\java\pdi-ce-3.2.0-stable\data-integration\samples\transformations\files\demo-table2table.ktr/level:
Basic/log:
test123.log
Spoon：
Spoon是Kettle的另一个图形用户界面，用来设计数据转换过程
Pan：
Pan是一个数据转换引擎，负责从不同的数据源读写和转换数据。
pan.sh-file="/PRD/CustomerDimension.ktr"-level=Minimal
Transformation步骤
1.输入类型：
1.1Csvfileinput
读取csv文件，设置csv文件路径，可以设置csv文件的相对路径或者绝对路径，字段分隔符，文件读取的缓存大小等
1.2ExcelInput
读取excel文件，和csv文件读取类似，增加了表单，表头，出错（是否忽略错误，严格的类型判断等）的处理
1.3PropertyInput
读取属性.properties文件
1.4Tableinput
从数据库读数据，动态绑定参数的SQL语句，参数替换可以从上一个步骤从获取。
例如SELECT*FROMcustomerWHEREbirthdate<’${current_date}’
这里的${current_date}在执行过程中会作为动态参数被替换掉。
这个值是前一个转换步骤设置的。
注：
但是测试过程中发现如果上一个步骤设置的变量，在tableinput里面获取不到，变量设置必须作为一个单独的转换先执行一次，然后才能获取到这个变量。
1.5Textfileinput
主要是txt文件内容等，和csv差不多。
1.6GenerateRows
生成一些固定字段的记录，主要用来模拟一些数据进行测试。
1.7GetFileNames
读取给定目录或者文件全路径的文件名
1.8GetSystemInfo
包括命令行输入的参数，操作系统时间，ip地址，一些特殊属性，kettle版本等
1.9De-serializefromfile
从二进制kettlecube文件中读取记录
1.10Accessinput
读取access数据库
1.11ESRIShapefileReader
1.12Fixedfileinput
读取固定大小文件
1.13Generaterandomvalue
1.14GetFilesRowsCount
获取文件内容的行数
1.15GetSubFoldernames
1.16GetdatafromXML
从xml文件解析出数据
1.17LDAPInput
从ldap库读取数据。
1.18LDIFInput
读取ldap的ldif文件
1.19MondrianInput
MDX语言从Mondrian服务器上读取数据
1.20RSSInput
1.21S3CSVInput
1.22SalesForceInput
1.23XBaseinput
读取XBase系列文件，如Foxpro文件，主要是数据库语言
2.输出类型
2.1Tableoutput
将数据写入到数据库，可以指定是否truncate表，编辑前一步转换字段与现在表结构的字段映射关系。
以及每次commit的记录数大小等。
2.2Textfileoutput
将数据写入到文本文件，通常是csv文件
2.3Insert/Update
根据关键字找对应的记录，如果找不到则执行insert，否则执行update
2.4Update
跟insert/update类似，只是没有insert操作
2.5Delete
跟update类似，只是执行的是delete操作
2.6ExcelOutput
输出到excel，格式可以采用excel模板
2.7Serializetofile
将记录写到二进制文件中（cube文件）
2.8AccessOutput
2.9PropertiesOutput
输出到properties文件
2.10RSSOutput
2.11SQLFileOutput
将输出的sqlinsert语句保存到文件
2.12Synchronizeaftermerge
2.13XMLOutput
输出到xml文件
3.Transform类型
3.1Selectvalues
用于选择列，重命名列，指定列长度或精度
3.2Filterrows
通过使用一个表达式从输入行中过滤数据，将结果是TURE或FALSE的行输出到不同的节点。
表达式是“”“OPERATOR”“”的形式，其中OPERATOR可以是=，<>,<,>,<=,>=,REGEXP,ISNULL,ISNOTNULL,INLIST,CONTAINS,STARTSWITH,ENDSWITH。
用户可以增加多个表达式，并用AND或OR连接。
3.3Sortrows
对指定的列以升序或降序排序，当排序的行数超过5000时需要临时表。
3.4Addsequence
为数据流增加一个序列，可以使用ORACLE中某一序列的值或由用户指定值
3.5Dummy
不做任何处理，一般作为流程的终点。
3.6JoinRows（catesianproduct）
对所有输入流做笛卡儿乘积。
3.7AggregateRows
聚集行数据，提供SUM，AVERAGE，COUNT，MIN，MAX，FIRST，LAST聚集函数，该类型不提倡使用，将来会被GROUPBY类型替代。
3.8JavaScriptvalue
使用mozilla的rhino作为脚本语言，并提供了很多函数，用户可以在脚本中使用这些函数。
例如varprev_row;if（prev_row==null）prev_row=row;...StringpreviousName=prev_row.getString（“Name”,“-”）;...prev_row=row;可以获得字段Name的前一条记录的值。
3.9RowNormaliser
该步骤可以从透视表（PIVOTTABLE）中还原数据到事实表，如从表一转换成表二，需要使用该步骤。
3.10Uniquerows
去掉输入流中的重复行，在使用该节点前要先排序，否则只能删除连续的重复行。
3.11Groupby
分组
3.12Calculator
提供了一组函数对列值进行运算，使用该方式比用户自定义JAVASCRIPT脚本速度更快。
3.13Addconstants
增加常量值。
3.14Rowdenormaliser
同正规化过程相反。
3.15Rowflattener
表扁平化处理除了上述基本节点类型外还定义了扩展节点类型，如SPLITFIELDS，EXECUTESQLSCRIPT，CUBEINPUT，CUBEOUTPUT等。
图一中创建了一个简单的数据流程示例，共包括5个节点,其中Tableinput节点使用了SQLSERVER数据库中的一张表（三条记录）,Filterrows中定义了过滤条件,将符合条件的发送到file2节点,不符和条件的记录发送到Selectvalues节点。
Selectvalues节点中选择列，并对选择的列进行了设置，将结果发送到file1节点。
file1,file2节点分别是两个文本文件节点,最终用来保存数据。
该流程运行后，可以在LogView面板中查看运行结果，如图四所示从tableinput结点输入3条记录，经过滤后输出到file2节点2条记录（OUTPUT列中的3是指2条记录加1行列名），输出到file1节点1条记录（OUTPUT列中的2是指1条记录加1行列名）。
4.Flow
4.1Abort
忽略上一步的输入流，通常用在错误处理中，譬如不处理X条记录后的所有记录
4.2Appendstreams
主要用来处理步骤之间有优先级的问题。
从两个步骤从读取数据流，指定步骤的读取顺序。
4.3BlockingStep
阻塞所有的输出直到最后一条记录到达
4.4Detectemptystream
当输入流为空的时候，输出一条空的记录
4.5Dummy（donothing）
空操作。
是一个空操作的插件
4.6Filterrows
通过设定过滤条件来过滤记录
4.7Identifylastrowinastream
4.8Switch/Case
类似Java的switch语法，通过比较某一确定的字段值来将数据转发到不同的转化步骤
5.Joins
5.1Mergejoin
合并两种不同输入流，连接方式有内连，左外连接等。
需要注意的是记录需要先按关键字进行排序
5.2MergeRows（diff）
用于比较两组输入数据，一般用于更新后的数据重新导入到数据仓库中。
两组数据中一组是引用流，一组是比较流，每次比较后只有最新版本的行数据被输出到下一步。
比较结果包括：
idectical一致：
两组流的主键一致，值一致
changed有变化：
两组流的主键一致，值有一个或多个不同
new新行：
引用流中有而比较流中没有某一主键
deleted被删除的行：
比较流中有而引用流中没有某一主键
比较流里面的数据除了被标记为deleted都会进入下一个步骤里面
5.3SortedMerge
对记录按某个关键字进行排序
5.4XMLJoin
将一个XML文作为节点添加到另一个XML里面
合并前的XML文
需要合并的XML
合并后的XML文
6.Scripting
6.1ExecuteSQLscript
执行SQL脚本，应该避免使用这一步骤，尽可能的使用“tableinput（select）”,”tableoutput（insert）”,”update”,”delete”等步骤来替代。
譬如动态创建表（表名是可变的，table1,table2,table3）：
SQL脚本是：
CREATETABLE?
（
IDINTEGER
）;
6.2ExecuterowSQLscript
对Executesqlscript的补充，增加了可以自定义sql语句的字段名
6.3Formula
在数据流中执行公式
6.4ModifiedJavaScriptValue
ModifiedJavaScriptValue应该说是转换步骤里最强大的一步，可以获取前一步的输入流的所有字段，调用Javaapi对数据做转换等操作，改变所有输出的值。
还能通过设置转换状态常量对现有转换流程做改变，（忽略转换，设置为错误，继续转换）。
脚本是Mozilla的Rhino，Rhino是一个Java实现的Javascript解释器。
现在已经加入到JDK1.6的javax.script包中了。
对数据流进行修改等操作
提供了常量,函数,输入字段,输出字段的列表显示
1）Transformationscripts已经创建的脚本
2）Transformationconstants已经预先定义好的静态常量,不可更改,例如SKIP_TRANSFORMATION,ERROR_TRANSFORMATION,CONTINUE_TRANSFORMATION
3）Transformationfunctions
类型转换,操作函数:
字符串,数字,日期之间的转换,字符串截取等
逻辑判断函数：
isDate（var），isNumb
升级会员 