KETTLE 工具使用

1 介绍

1.1 ETL

ETL，是英文 Extract-Transform-Load 的缩写，用来描述将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至目的端的过程。ETL 一词较常用在数据仓库，但其对象并不限于数据仓库。

ETL 是构建数据仓库的重要一环，用户从数据源抽取出所需的数据，经过数据清洗,最终按照预先定义好的数据仓库模型，将数据加载到数据仓库中去。

1.2 KETTLE

Kettle 是一款国外开源的 ETL 工具，纯 java 编写，可以在 Window、Linux、Unix 上运行，数据抽取高效稳定。中文名称叫水壶，该项目的主程序员 MATT 希望把各种数据放到一个壶里，然后以一种指定的格式流出。

Kettle 这个 ETL 工具集，它允许你管理来自不同数据库的数据，通过提供一个图形化的用户环境来描述你想做什么，而不是你想怎么做。

Kettle 中有两种脚本文件，transformation 和 job，transformation 完成针对数据的基础转换，job 则完成整个工作流的控制。

1.3 PENTAHO SERVER

Pentaho 是基于 Java 平台的商业智套件，之所以说它是套件是因为它包括一个 Web Server 平台和几个工具软件：报表、分析、图表、数据集成、数据挖掘等。功能强大，需要付费，我们在这里只将它用作是资源库，用来存储、发布、运行、管理客户端编辑的转换或者作业。

2 安装

2.1 下载安装包

下载客户端地址：

https://sourceforge.net/projects/pentaho/files/Data%20Integration

下载服务端地址：

pentaho 各版本官方文档：https://help.pentaho.com/Documentation pentaho

社区版合集下载：https://sourceforge.net/projects/pentaho/files/

2.2 安装环境

如介绍所说，kettle 是纯 java 编写，故需要安装 jdk 才可以运行，但这里不做 java 的安装说明。Pentaho Server 同样需要 jdk,且内嵌 tomcat 默认端口为 8080.

2.3 安装

①客户端：将下载的文件 pdi-ce-kettle-7.1.0.0-12.zip 解压到任意目录下，双击 data-integration 目录下的 spoon.bat 即可运行 kettle 工具。

②服务端：直接解压下载文件到任意目录，点击根目录下的 start-pentaho.bat 启动即可，启动以后可以在浏览器中输入http://server-ip:port/pentaho来访问管理界面，客户端也可以用此地址来进行资源库连接。

注：在 data-integration\samples 下有许多组件使用示例，可以参考学习。

2.4 界面介绍

客户端：

服务端：

3 客户端重要组件介绍

3.0 主对象树/核心对象

主对象树种显示的转换或者作业的信息，包含的数据库连接和步骤，子服务器等；

核心对象中显示的是所有的转换或者作业可用的组件。

3.1 输入

输入包含多个输入组件，用于从不同的数据源输入数据，类如 CSV，Excel，文本文件，系统信息，配置文件，随机数，表输入等。下面重点介绍部分组建

3.1.1 生成记录

默认生成有限条记录，如果勾选了 Never stop generating rows 选项，将持续生成记录，并记录每行的生成时间，还有前一行的生成时间？？？

记录的字段自定义，有名称类型精度值等等，完成之后可预览生成的记录。

3.1.2 生成随机数

可以选择要生成的随机数类型，生成的随机数将加入到流中的每行记录中。

3.1.3 获取系统信息

获取系统日期，特定时间，转换的信息，主机名，kettle 版本，jvm 信息等。

3.1.4 表输入

表输入组件用于读取已经建立连接的数据库表或视图，利用 sql 获取数据，支持变量替换，可以从之前的步骤中插入数据。

3.1.5 配置文件输入

这个步骤用于读取 java 配置文件（.properties），文件选项卡李曼可以配置文件的路径，可以输入多个配置文件，内容选项卡中用来配置文件类型，编码等。字段选项卡中配置读取的字段加入流中。

3.2 输出

3.2.1 删除

选择数据库连接和目标模式、目标表，“查询值所需的关键字”，根据这个选项中配置的条件进行删除值，即如果满足了各个表字段和流字段的比较符关系，将删除该行数据。

3.2.2 插入/更新

这个步骤首先用查询关键字查询表里的每一行，如果没有匹配到就插入新的一行，如果匹配到了并且更新的字段值不一样，就更新这一行数据，如果更新的字段值也一样，那什么也不会做。

3.2.3 数据同步

该步骤经常用于合并记录之后，合并记录步骤会为每行数据追加一个标志位，这个标志位有四中值："identical", "changed", "new"或者"deleted"。这个标志位将被数据同步这个步骤用来增删改数据库表。

3.2.4 更新

这个步骤与“插入/更新”步骤类似，只不过只关心查询关键字的值相等，而更新字段的值不等的情况下更新行，而不会在没有找到查询关键字的时候新增行。

3.2.5 表输出

该步骤允许你加载数据到一张数据库表中。相当于 DML 操作中的 INSERT。

4 服务端功能介绍

4.1 Browse Files

在该功能下，可以浏览作业和转换的文件，可进行上传与下载，在进行部分文件的修改时，可以通过上传将修改的文件进行更新。遗憾的是只支持单文件上传。

4.2 Manage Data Sources

管理数据源，在这里设置编辑任何你在同步中需要用到的数据源，包括源数据库和目标数据库，也可以测试数据源是否可以正常连接。

4.3 Home>Schedules

管理定时任务，所有设置好的定时任务都在这里进行管理，可以进行任务的开启、暂停、删除，设置过滤条件查看任务。任务基本信息的展示，任务名称、用到的作业或者转换、重复规则、最后一次运行时间、下一次运行时间、当前状态等。

4.4 Home>Administration

管理权限、设置邮件、管理临时生成文件，这里主要介绍对临时文件的管理，因为定时任务在运行的时候，每一次都会以原始作业或者转换为模板复制出来一个副本，用这个副本来进行真正的同步，所以定时任务每一次的运行都会生成这样一个副本，这样这些临时文件就会不断增加，在该模块下除了手动删除超过 N 天的临时文件，并且可以设置定时任务对其进行自动删除。

5 效率提升

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）kettle 本身的性能绝对是能够应对大型应用的，一般的基于平均行长 150 的一条记录，假设源数据库，目标数据库以及 kettle 都分别在几台机器上（最常见的桌面工作模式，双核，1G 内存），速度大概都可以到 5000 行每秒左右，如果把硬件提高一些，性能还可以提升 ， 但是 ETL 过程中难免遇到性能问题，下面一些通用的步骤也许能给你一些帮助

　①尽量使用数据库连接池

　②尽量提高批处理的 commit size

　③尽量使用缓存，缓存尽量大一些（主要是文本文件和数据流）

　④Kettle 是 Java 做的，尽量用大一点的内存参数启动 Kettle.

　⑤可以使用 sql 来做的一些操作尽量用 sql，Group ， merge ， stream lookup ，split field 这些操作都是比较慢的，想办法避免他们.，能用 sql 就用 sql

　⑥插入大量数据的时候尽量把索引删掉

　⑦尽量避免使用 update ， delete 操作，尤其是 update ， 如果可以把 update 变成先 delete ，后 insert。

　⑧能使用 truncate table 的时候，就不要使用 delete all row 这种类似 sql

　⑨要知道你的性能瓶颈在哪，可能有时候你使用了不恰当的方式，导致整个操作都变慢，观察 kettle log 生成的方式来了解你的 ETL 操作最慢的地方