kettle 使用中的一些常见问题

kettle 使用中的一些常见问题

问题1：

从excel 中抽取数据,插入到oracle 9 数据库中,报下面的错误.

2008/06/25 13:30:57 - 插入更新数据表.0 - Insert row: ![field1=1.3965E8, field2=1,798,

field3=2002/05/27 00:00:00.000]

2008/06/25 13:30:57 - oracl - ERROR : eption: ORA-01722: 无效数字

2008/06/25 13:30:57 - oracl - ERROR : at

qlException(:125)

原因:

从错误信息可以看出，字段 "field2" 对应的数据是 1,798，该字段在数据库中是 Number

类型，Oracle 不能将 1,798 格式的字符串转换为数字。

解决方法：

在 【Excel输入步骤】-【字段】 标签下对应的字段设置为正确的类型。将 "field2" 设置为 "Number" 类型（默认是 String 类型）。

问题2: 由 sqlserver 向 mysql 迁移数据, 迁移以后的数据是乱码

原因: 这是由于mysql 的默认编码是 utf-8, 而中文环境下客户端的默认编码一般都是

gbk 或 gb18030, 由于客户端和服务器编码不一样导致保存到 mysql 的数据是乱码

解决方法: 在新建连接窗口中有一个选项(option) 标签, 在这个标签下可以设置数据库的一些连接参数. 在这里我们要设置 mysql 的客户端编码参数, 参数名characterEncoding, 参数值gbk.

问题3: 在 SQLServer 表输入步骤中使用以问号作为参数的 sql 语句 (参数的值从以前的步骤中获得),向 mysql 数据库导入数据 (使用 mysql 表输出步骤).

在执行的时候报告错误 Unable to get queryfields for SQL: AND _TIME>? 00909

PRS INC Invalid character.

原因: 从错误语句看,是参数没有被替换掉, 因为发送给 mysql 服务器的是这样的语句

"AND _TIME>?". 参数没有被替换掉是因为从以前步骤中读取的参数值是 null.

解决方法: 增加对 null 值的判断, 可以有两种方法, 如果这个值是从数据库获取的,一般的数据库都提供了处理null值的函数,如 mysql 的ISNULL 函数. 如果这个值是从其他步骤获得的,可以通过 Javascript 步骤进行判断并转换.

问题4: 使用 PDI 3.x 版本连接 DB2 数据库时会报下面的错误

Error connecting to database: (using class 2Driver)

encoding not supported!!

原因: PDI 自带的DB2 JDBC Driver 的版本比较低,无法连接 GBK 编码的 DB2 数据库。

解决方法: 将 DB2 数据库的编码改为 utf-8 编码。或者找高版本的 DB2 JDBC Driver 来代替 PDI 自带的DB2 JDBC Driver。

问题5: win2000 下运行，命令行太长导致spoon 或 kitchen 无法运行：

原因: spoon 运行时加载了太多的 jar，win2000 下对命令行的长度有限制

解决方法：1. 使用 参数，将lib，libext，等 jar 路径都设置到这个参数下。

/?t=57843&page=2

2. 删除libext 目录下某些不需要的 jar，如各种数据库的jdbc driver

3. 使用 launcher 见

/browse/PDI-559

问题6: 关于资源文件 ty。

资源文件里的变量值如果有汉字，应该使用 unicode 字符方式表示，即 uxxxx 的形式

变量值里如果有 "" 符号，要转义为 ""

文本文件输入

从文本文件中获得数据，常见的文本文件包括csv 、txt、文件等。用户要在该步骤指定文件名、文件内容、错误处理方式、过滤器、字段等项目。

参数说明：

l 指定文件名的三种方式

1. 指定一个具体的文件名。

2. 指定一个正则表达式，来匹配一个目录下的文件。

3. 将其它步骤的运行结果作为文件名。

l 文件内容设置

分隔符：指定字段之间的分隔符号

文本限定符：指定一个字符串左右的限定符号，有限定符的字符串里可以使用分隔符。有限定符的字符串内部如果要使用限定符，要将限定符加倍。

转义符：指定文本中的转义符号，用来将其后的字符转义。

页眉：指定页眉的行数，页眉行不作为数据行处理。

页脚：指定页脚的行数，页脚行不作为数据行处理。

回卷：说明一个数据行是否被回卷为多行。

l 错误处理设置

忽略错误：是否忽略解析过程中产生的错误。

跳过错误行：是否跳过发生错误的行。如果不跳过，那么发生错误的字段值会被置为空。

记录错误数的输出字段：指定一个输出字段用来记录解析错误的字段的个数。

记录错误描述的输出字段：指定一个输出字段用来记录发生错误的所有字段的名字。

记录错误字段名的输出字段：指定一个输出字段用来记录错误的描述信息。

警告文件目录：当发生警告时，警告将保存在这个指定的目录下。

错误文件目录：当发生警告时，警告将保存在这个指定的目录下。

失败行数文件目录：当读取行失败时，读取失败的行号将保存在这个指定的目录下。

l 过滤器

过滤器：用来过滤输入行，符合过滤器条件的输入行将被忽略掉。

过滤字符串：用来去匹配输入数据的字符串（不支持正则表达式）

过滤开始位置：指定字符串里开始匹配的位置，负数或0表示从第一个字符开始匹配。

停止处理：当遇到了匹配的字符串时，是否停止处理

l 字段

设定字段名称和数据类型

使用kettle设计一些ETL任务时一些常见问题

2009-07-27 10:55

摘要：本文主要介绍使用kettle设计一些ETL任务时一些常见问题，这些问题大部分都不在官方FAQ上，你可以在kettle的论坛上找到一些问题的答案

1. Join

我得到A 数据流（不管是基于文件或数据库），A包含field1 , field2 , field3

字段，然后我还有一个B数据流，B包含field4 , field5 , field6 , 我现在想把它们 ‘加’ 起来, 应该怎么样做.

这是新手最容易犯错的一个地方，A数据流跟B数据流能够Join，肯定是它们包含join key ,join key 可以是一个字段也可以是多个字段。如果两个数据流没有join key ,那么它们就是在做笛卡尔积，一般很少会这样。比如你现在需要列出一个员工的姓名和他所在部门的姓名，如果这是在同一个数据库，大家都知道会在一个sql 里面加上where 限定条件，但是如果员工表和部门表在两个不同的数据流里面，尤其是数据源的来源是多个数据库的情况，我们一般是要使用Database Join 操作，然后用两个database table input 来表示输入流，一个输入是部门表的姓名，另一个是员工表的姓名，然后我们认为这两个表就可以 ”Join” 了，我们需要的输出的确是这两个字段，但是这两个字段的输出并不代表只需要这两个字段的输入，它们之间肯定是需要一个约束关系存在的。另外，无论是在做Join , Merge , Update , Delete 这些常规操作的时候，都是先需要做一个compare 操作的，这个compare 操作都是针对compare key 的，无论两个表结构是不是一样的，比如employee 表和department 表，它们比较的依据就是employee 的外键department_id , 没有这个compare key 这两个表是不可能连接的起来的.. 对于两个表可能还有人知道是直接sql 来做连接，如果是多个输入数据源，然后是三个表，有人就开始迷茫了，A表一个字段，B表一个字段，C表一个字段，然后就连Join操作都没有，直接database table

output , 然后开始报错，报完错就到处找高手问，他们的数据库原理老师已经

在吐血了。如果是三个表连接，一个sql 不能搞定，就需要先两个表两个表的连接，通过两次compare key 连接之后得到你的输出，记住，你的输出并不能代表你的输入. 下面总结一下:

1. 单数据源输入，直接用sql 做连接

2. 多数据源输入，（可能是文本或是两个以上源数据库），用database join 操作.

3. 三个表以上的多字段输出.

2. Kettle的数据库连接模式

Kettle的数据库连接是一个步骤里面控制一个单数据库连接，所以kettle的连接有数据库连接池，你可以在指定的数据库连接里面指定一开始连接池里面放多少个数据库连接，在创建数据库连接的时候就有Pooling 选项卡，里面可以指定最大连接数和初始连接数，这可以一定程度上提高速度.

3. transaction

我想在步骤A执行一个操作（更新或者插入），然后在经过若干个步骤之后，如果我发现某一个条件成立，我就提交所有的操作，如果失败，我就回滚，kettle提供这种事务性的操作吗？

Kettle里面是没有所谓事务的概念的，每个步骤都是自己管理自己的连接的，在这个步骤开始的时候打开数据库连接，在结束的时候关闭数据库连接，一个步骤是肯定不会跨session的（数据库里面的session）, 另外，由于kettle是并行执行的，所以不可能把一个数据库连接打开很长时间不放，这样可能会造成锁出现，虽然不一定是死锁，但是对性能还是影响太大了。ETL中的事务对性能影响也很大，所以不应该设计一种依赖与事务方式的ETL执行顺序，毕竟这不是OLTP，因为你可能一次需要提交的数据量是几百GB都有可能，任何一种数据库维持一个几百GB的回滚段性能都是会不大幅下降的.

4. 我真的需要transaction 但又不想要一个很复杂的设计，能不能提供一个简单一点的方式

Kettle 在3.0.2GA版中将推出一种新功能，在一个table output 步骤中有一个Miscellaneous 选项卡，其中有一个Use unique connections 的选项，如果你选中的话就可以得到一个transaction 的简单版，

由于是使用的单数据库连接，所以可以有错误的时候回滚事务，不过要提醒一点是这种方式是以牺牲非常大的性能为前提条件的，对于太大的数据量是不适合的(个人仍然不建议使用这种方式)

5. temporary 表如何使用

我要在ETL过程中创建一个中间表，当某个条件成立的时候，我要把中间表的数据进行转换，当另一条件成立的时候我要对中间表进行另一个操作，我想使用数据库的临时表来操作，应该用什么步骤。

首先从temp 表的生命周期来分，temp分为 事务临时表和会话临时表，前面已经解释过了，kettle是没有所谓事务的概念的，所以自然也没有所谓的事务临时表。Kettle的每个步骤管理自己的数据库连接，连接一结束，kettle也就自然丢掉了这个连接的session 的handler , 没有办法可以在其他步骤拿回这个

session 的handler , 所以也就不能使用所谓的会话临时表，当你尝试再开一个连接的时候，你可以连上这个临时表，但是你想要的临时表里面的数据都已经是空的(数据不一定被清除了，但是你连不上了)，所以不要设计一个需要使用临时表的转换

之所以会使用临时表，其实跟需要 ”事务” 特性有一点类似，都是希望在ETL过程中提供一种缓冲。临时表很多时候都不是某一个源表的全部数据的镜像，很多时候临时表都是很小一部分结果集，可能经过了某种计算过程，你需要临时表无非是基于下面三个特性：

1. 表结构固定，用一个固定的表来接受一部分数据。

2. 每次连接的时候里面没有数据。你希望它接受数据，但是不保存，每次都好像执行了truncate table 操作一样

3. 不同的时候连接临时表用同一个名字，你不想使用多个连接的时候用类似与temp1 , temp2 , temp3 , temp4 这种名字，应为它们表结构一样。

既然临时表不能用，应该如何设计ETL过程呢？（可以用某种诡异的操作搞出临时表，不过不建议这样做罢了）

如果你的ETL过程比较的单线程性，也就是你清楚的知道同一时间只有一个这样的表需要，你可以创建一个普通的表，每次连接的时候都执行truncate 操作，不论是通过table output 的truncate table 选项，还是通过手工执行truncate

table sql 语句（在execute sql script 步骤）都可以达到目的（基于上面的1，2 特性）

如果你的ETL操作比较的多线程性，同一时间可能需要多个表结构一样并且里面都是为空的表（基于上面1，2，3特性），你可以创建一个 “字符串+序列” 的模式，每次需要的时候，就创建这样的表，用完之后就删除，因为你自己不一定知道你需要多少个这种类型的表，所以删除会比truncate 好一些。

下面举个例子怎么创建这种表：

你可以使用某种约定的表名比如department_temp 作为department 的临时表。或者

把argument 传到表名，使用 department_${argument} 的语法，

如果你需要多个这种表，使用一个sequence 操作+execute sql script 操作，execute sql script 就下面这种模式

Create table_? („„„„..)

在表的名字上加参数，前面接受一个sequence 或类似的输入操作.

需要注意的是这种参数表名包括database table input 或者execute sql

script ，只要是参数作为表名的情况前面的输入不能是从数据库来的,应为没有办法执行这种preparedStatement 语句，从数据库来的值后面的操作是 “值操作” ,而不是字符串替换，只有argument 或者sequence 操作当作参数才是字符串替换. (这一点官方FAQ也有提到)

6. update table 和execute sql script 里面执行update 的区别

执行update table 操作是比较慢的，它会一条一条基于compare key 对比数据，然后决定是不是要执行update sql , 如果你知道你要怎么更新数据尽可能的使用execute sql script 操作，在里面手写update sql （注意源数据库和目标数据库在哪），这种多行执行方式（update sql）肯定比单行执行方式（update

table 操作）快的多。

另一个区别是execute sql script 操作是可以接受参数的输入的。它前面可以是一个跟它完全不关的表一个sql :

select field1, field2 field3 from tableA

后面执行另一个表的更新操作：

update tableB set field4 = ? where field5=? And field6=?

然后选中execute sql script 的execute for each row .注意参数是一一对应的.(field4 对应field1 的值，

field5 对应field2 的值， field6 对应field3 的值)

7. kettle的性能

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

合理的分区

如果删除操作是基于某一个分区的，就不要使用delete row 这种方式（不管是delete sql 还是delete 步骤）,直接把分区drop 掉，再重新创建

尽量缩小输入的数据集的大小（增量更新也是为了这个目的）

尽量使用数据库原生的方式装载文本文件(Oracle 的sqlloader , mysql 的bulk loader 步骤)

尽量不要用kettle 的calculate 计算步骤，能用数据库本身的sql 就用sql ,不能用sql 就尽量想办法用procedure , 实在不行才是calculate 步骤.

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

远程数据库用文件+FTP 的方式来传数据 ，文件要压缩。（只要不是局域网都可以认为是远程连接）

8. 描述物理环境

源数据库的操作系统，硬件环境，是单数据源还是多数据源，数据库怎么分布的，做ETL的那台机器放在哪，操作系统和硬件环境是什么，目标数据仓库的数据库是什么，操作系统，硬件环境，数据库的字符集怎么选，数据传输方式是什么，开发环境，测试环境和实际的生产环境有什么区别，是不是需要一个中间数据库

(staging 数据库) ，源数据库的数据库版本号是多少，测试数据库的版本号是多少，真正的目标数据库的版本号是多少„„. 这些信息也许很零散，但是都需要一份专门的文档来描述这些信息，无论是你遇到问题需要别人帮助的时候描述问题本身，还是发现测试环境跟目标数据库的版本号不一致,这份专门的文档都能提供一些基本的信息

9. procedure

为什么我不能触发procedure?

这个问题在官方FAQ里面也有提到，触发procedure 和 http client 都需要一个类似与触发器的条件，你可以使用generate row 步骤产生一个空的row ,然后把这条记录连上procedure 步骤，这样就会使这条没有记录的空行触发这个procedure (如果你打算使用无条件的单次触发) ，当然procedure 也可以象table input 里面的步骤那样传参数并且多次执行.

另外一个建议是不要使用复杂的procedure 来完成本该ETL任务完成的任务，比如创建表，填充数据，创建物化视图等等.

变量使用：

/topic/946152