shenxiangl's blog

2008年1月28日

ETL高级教程

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 阅读(4351) | 评论 (6) | 编辑收藏

2008年1月27日

ETL工具Kettle介绍和一个例子

    因为公司近期招了几个实习生，需要我来做一个关于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，确定保存。