2008年1月28日

ETL高级教程

    在昨天的帖子里面,我介绍了一些ETL初级的东西,不知道大家是否已经掌握,我现在介绍一些Kettle应用中,比较有帮助的一些地方。

1,Kettle跨平台使用。
    例如:在AIX下(AIX是IBM商用UNIX操作系统,此处在LINUX/UNIX同样适用),运行Kettle的相关步骤如下:
    1)进入到Kettle部署的路径
    2)执行 chmod *.sh,将所有shell文件添加可执行权限
    3)在Kettle路径下,如果要执行transformation,就运行./pan.sh -file=?.ktr -debug=debug -log=log.log
    其中。-file说明你要运行的transformation文件所在的路径;-debug说明日志输出的级别;-log说明日志输出的路径
    4)同理,对于job的执行,请将./pan.sh更换成./kitchen.sh,其他部分说明不变。

2,Kettle环境变量使用。
    在transformation中,Core Objects-->Job-->Set Variables,可疑设置环境变量,对于绝对路径和相对路径的转换很有帮助,Kettle的跨平台很大程度依靠他的

3,其它功能的使用。
    其它功能包括DB存储过程调用,流查询,值映射,聚合记录等,各位自行摸索,有问题可以和我联系:)

4,Kettle定时功能。
    在Job下的start模块,有一个定时功能,可以每日,每周等方式进行定时,对于周期性的ETL,很有帮助。

5,Kettle经验之日志。
    Kettle对于日志的处理,存在一个BUG,看过上一篇的人或许已经看到了我的留言,Kettle对于日志处理有一个BUG,当日志多于49M(不是50M,也不是49M),Kettle就会自动停止,这一点我在源码里面也没有找到对应的设置和约束,原因还找不到,因为是日志没有写,所以原因也不好跟踪还不知道具体原因。

6,Kettle之效率提升。
    Kettle作为一款ETL工具,肯定无法避免遇到效率问题,当很大的数据源输入的时候,就会遇到效率的问题。对此有几个解决办法:
    1)数据库端创建索引。对需要进行查询的数据库端字段,创建索引,可以在很大程度上提升查询的效率,最多的时候,我不创建索引,一秒钟平均查询4条记录,创建索引之后,一秒钟查询1300条记录。
    2)数据库查询和流查询注意使用环境。因为数据库查询为数据输入端输入一条记录,就对目标表进行一次查询,而流查询则是将目标表读取到内存中,数据输入端输入数据时,对内从进行查询,所以,当输入端为大数据量,而被查询表数据量较小(几百条记录),则可以使用流查询,毕竟将目标表读到内存中,查询的速度会有非常大的提升(内存的读写速度是硬盘的几百倍,再加上数据库自身条件的制约,速度影响会更大)。同理,对于目标表是大数据量,还是建议使用数据库查询,不然的话,一下子几百M的内存被干进去了,还是很恐怖的。
    3)谨慎使用javascript脚本,因为javascript本身效率就不高,当你使用js的时候,就要考虑你每一条记录,就要执行一次js所需要的时间了。
    4)数据库commit次数,一条记录和一百条记录commit对效率的影响肯定是不一样的。
    5)表输入的sql语句的写法。有些人喜欢在表输入的时候,将所有关联都写进去,要么from N多个表,要么in来in去,这样,就要面对我在2)里面说道的问题,需要注意。
    6)注意日志输出,例如选择数据库更新方式,而且日志级别是debug,那么后台就会拼命的输出日志,会在很大程度上影响速度,此处一定要注意。

7,常见的调试BUG。
    Kettle提供了很多调试的解决办法,但是对于常见的调试BUG还是能避免就避免。
    1)路径问题。我最常遇到的问题就是在windows下调试成功,但是部署到UNIX下出问题,忘记将windows下路径变成unix下,经常会出现问题。
    2)输出端,数据库插入更新选择不对。输出端,提供了三种数据库输出的办法,数据库输出,插入/更新,更新,对于这三种,各有利弊,如果你知道数据库输出,完全是插入,如果有重复数据,则会报错;插入更新和更新,因为更新数据时,后台输出很多日志,会导致效率很低。
   

    总体来说,Kettle还是一个很不错的ETL工具,在开源软件里面并不多见,以后有Kettle相关的问题,大家可疑相互探讨。

posted @ 2008-01-28 19:20 shenxiangl 阅读(4178) | 评论 (6)编辑 收藏

2008年1月27日

    因为公司近期招了几个实习生,需要我来做一个关于Kettle的培训,准备了一些培训的资料,一个ppt,用于培训的,一个数据库备份和一个详细的操作文档,是一个小作业,不敢独享,发出来给大家share,顺便作为这个技术博客的头彩,希望大家能够多来关注。

    如果大家不愿意看网页版,可以下载相关文档到本地研究:)

    培训ppt在这里
    培训手册在这里
    练习数据库备份在这里
    练习的脚本文件在这里






Kettle培训手册

 

Etl介绍

 

ETL(Extract-Transform-Load的缩写,即数据抽取、转换、装载的过程),对于金融IT来说,经常会遇到大数据量的处理,转换,迁移,所以了解并掌握一种etl工具的使用,必不可少。

Kettle是一款国外开源的etl工具,纯java编写,绿色无需安装,数据抽取高效稳定。Kettle中有两种脚本文件,transformation和job,transformation完成针对数据的基础转换,job则完成整个工作流的控制。

 

kettle部署运行

 

将kettle2.5.1文件夹拷贝到本地路径,例如D盘根目录。

双击运行kettle文件夹下的spoon.bat文件,出现kettle欢迎界面:

 


 

稍等几秒

 


 

选择没有资源库,打开kettle主界面

 


 

创建transformation,job

点击页面左上角的创建一个新的transformation,点击保存到本地路径,例如保存到D:/etltest下,保存文件名为EtltestTrans,kettle默认transformation文件保存后后缀名为ktr

 

点击页面左上角的创建一个新的job,点击保存到本地路径,例如保存到D:/etltest下,保存文件名为EtltestJob,kettle默认job文件保存后后缀名为kjb

 

创建数据库连接

 

在transformation页面下,点击左边的【Main Tree】,双击【DB连接】,进行数据库连接配置。

 


 

Connection name自命名连接名称

Connection type选择需要连接的数据库

Method of access选择连接类型

Server host name写入数据库服务器的ip地址

Database name写入数据库名

Port number写入端口号

Username写入用户名

Password写入密码

 

例如如下配置:

 


 

点击【test】,如果出现如下提示则说明配置成功

 


 

点击关闭,再点击确定保存数据库连接。

 

一个简单的ktr例子

 

目标

从交易表(trade),帐户表(account),客户表(cust)抽数交易相关的所有信息,并判断对公对私分别进行处理,输出到文本文件中。

 

操作步骤:

在EtltestTrans页面下,点击左侧的【Core Objects】,点击【Input】,选中【表输入】,拖动到主窗口释放鼠标。


 

双击【表输入】图标

 

数据库连接选择刚刚创建好的etltest数据库连接,在主窗口写入对应的查询语句

 

Select * from trade

 


 

点击确定完成。

 

点击左侧的【Lookup】,选中【数据库查询】,拖动到主窗口释放鼠标。


 

按住shift键,用鼠标点中刚才创建的【表输入】,拖动到【数据库查询】上,则建立了两个环节之间的连接。

 


 

双击【数据库查询】图标

 

步骤名称写入account表查询,数据库连接选择刚刚创建好的etltest数据库连接,查询的表写入account,查询所需的关键字中,表字段写入acctno,比较操作符写入”=”,字段1写入acctno。

 

在查询表返回的值里面写入custno,确定完成。

 


 

同上,再创建一个数据库查询,命名为cust表查询,查询的表写入cust,查询所需的关键字写入custno=custno,查询表返回的值写入custname,custid,custtype

 


 

点击左侧的【Transform】,选中【过滤记录】,拖动到主窗口释放鼠标。


 

点击左侧的【Scripting】,选中两个【Modified Java Script Value】,拖动到主窗口释放鼠标。分别双击打开,重命名为”对公类型修改”和”对私类型修改”。

 

同时,分别创建【过滤记录】和【对公类型修改】,【对私类型修改】的连接。

 

双击【规律记录】打开。

 

第一个<field>里面选择custtype,点击<value>,在Enter value里面写入1,确定


 

在发送true数据给步骤里,选择【对私类型修改】,在发送false数据给步骤里,选择【对公类型修改】,确定保存。

 


 

双击【对公类型修改】,在里面写入javascript脚本语句

 

var custtype_cn=’对公客户交易’

 

在字段中写入custtype_cn,类型选为string。确定。

 


 

同理,在【对私类型修改】中,在里面写入javascript脚本语句

 

var custtype_cn=’对私客户交易’

 

在字段中写入custtype_cn,类型选为string。确定。

 

点击左侧的【Transform】,选中两个【增加常量】,拖动到主窗口释放鼠标。分别双击打开,重命名为”增加对公常量”和”增加对私常量”。

 

分别建立【对公类型修改】和【对私类型修改】与【增加对公常量】和【增加对私常量】的连接

 


 

双击【增加对公常量】,名称写入value,类型选择string,值写入”这是一笔对公客户发生的交易”,确定保存。

 


 

同理,双击【增加对私常量】,名称写入value,类型选择string,值写入”这是一笔对私客户发生的交易”,确定保存。

 

点击左侧的【Output】,选中【文本文件输出】,拖动到主窗口释放鼠标。

 

建立【增加对公常量】,【增加对私常量】和【文本文件输出】的连接。

 


双击打开【文本文件输出】,文件名称写入D:\etltest\etltest.txt

 


 

点击内容标签,根据情况进行修改,例如

 


 

点击字段标签

名称依次写入tradeid,acctno,amt,custno,custname,custid,custtype_cn,value,类型根据各个字段实际类型进行选择

 


 

确定保存

 

点击保存创建好的transformation。

 

点击运行这个转换。

 


 

点击launch,开始运行

 

当所有状态都变成已完成时,则转换完成。


 

另一个简单的ktr例子

 

目的:

将上一个ktr生成的文本导入到数据库中。

 

操作步骤:

创建一个transformation,命名为EtltestTransfile2db.ktr,创建数据库连接etltest,点击【Input】,选中【文本文件输入】,拖到主窗口,释放鼠标,双击打开

 

文件名称里面写入D:\etltest\etltest.txt

 


 

点击内容标签,分隔符写入@@,将头部的钩去掉

 


 

点击字段标签,名称依次写入tradeid,acctno,amt,custno,custname,custid,custtype_cn,value,类型根据各个字段实际类型进行选择

 


 

点击【Transform】,选中【字段选择】,拖到主窗口,释放鼠标

 

点击【Output】,选中【表输出】,拖到主窗口,释放鼠标

 

建立【文本文件输入】和【字段选择】与【字段选择】和【表输出】的连接

 


 

双击【表输出】,目标表中写入trade_all,提交记录数量写成1,确定保存。

 


 

双击【字段选择】,点击 获取选择的字段,再点击Edlt Mapping,点击OK确定,编辑所有字段对应关系,点确定。

 


 

点击运行这个转换。,则将上一个ktr中生成的文本,导入到数据库当中。

 

一个简单的kjb例子

 

目的:

将上两个transformation一起在同一个job里面调用执行。

 

操作步骤:

在EtlscriptJob页面,点击【Core Objects】,点击【Job entries】,选中【START】拖动到主窗口释放鼠标,再选中两个【Transformation】,拖动到主窗口释放鼠标,建立【START】和 【Transformation】与【Transformation】之间的连接。

 

双击第一个【Transformation】,在Transformation filename中写入D:\etltest\EtltestTrans.ktr,确定保存。

 


 

    同时将另外一个【Transformation】,路径指向D:\etltest\EtltestTransfile2db.ktr,保存。

 

点击保存创建好的job。

 

点击运行这个转换。

 

待所有任务都显示成功,则为job调用transformation运行成功。

 


 


posted @ 2008-01-27 13:42 shenxiangl 阅读(16865) | 评论 (15)编辑 收藏

仅列出标题