摘要:本文主要介绍使用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 来做连接如果是多个输入数据源然后是 3个表有人就开始迷茫了A表个字段B表个字段C表个字段然后就连Join操作都没有直接 database table output , 然后开始报错报完错就到处找高手问他们数据库原理老师已经在吐血了如果是 3个表连接个sql 不能搞定就需要先两个表两个表连接通过两次compare key 连接的后得到你输出记住你输出并不能代表你输入. 下面整理总结下:
1. 单数据源输入直接用sql 做连接
2. 多数据源输入(可能是文本或是两个以上源数据库)用database join 操作.
3.  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过程中提供种缓冲临时表很多时候都不是某个源表全部数据镜像很多时候临时表都是很小部分结果集可能经过了某种计算过程你需要临时表无非是基于下面 3个特性:
1. 表结构固定用个固定表来接受部分数据
2. 每次连接时候里面没有数据你希望它接受数据但是不保存每次都好像执行了truncate table 操作样
3. 区别时候连接临时表用同个名字你不想使用多个连接时候用类似和temp1 , temp2 , temp3 , temp4 这种名字应为它们表结构样
既然临时表不能用应该如何设计ETL过程呢？(可以用某种诡异操作搞出临时表不过不建议这样做罢了)
如果你ETL过程比较单线程性也就是你清楚知道同时间只有个这样表需要你可以创建个普通表每次连接时候都执行truncate 操作不论是通过table output truncate table 选项还是通过手工执行truncate table sql 语句(在execute sql script 步骤)都可以达到目(基于上面12 特性)
如果你ETL操作比较多线程性同时间可能需要多个表结构样并且里面都是为空表(基于上面123特性)你可以创建个 “串+序列”  模式每次需要时候就创建这样表用完的后就删除你自己不定知道你需要多少个这种类型表所以删除会比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 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
尽量使用缓存Cache缓存Cache尽量大些(主要是文本文件和数据流)
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任务完成任务比如创建表填充数据创建物化视图等等.

10. 集
Kettle使用Java 通常使用UTF8 来传输集所以无论你使用何种数据库任何数据库种类集kettle 都是支持如果你遇到了集问题也许下面这些提示可以帮助你:
1. 单数据库到单数据库是绝对不会出现乱码问题不管原数据库和目标数据库是何种种类何种集
2. 多种区别集原数据库到个目标数据库你首先需要确定多种源数据库集最大兼容集是什么如果你不清楚最好办法就是使用UTF8来创建数据库.
3. 不要以你工作环境来判断集:现在某个测试人员手上有个oracle 基于xxx 集已经存在数据库并且非常不幸是xxx 集不是utf8 类型于是他把另个基于yyy集oracle 数据库要经过某个ETL过程转换到oracle , 后来他发现无论如何样设置都会出现乱码这是你数据库本身集不支持无论你如何设置都是没用. 测试数据库不代表最后产品运行数据库尤其是有时候为了省事把多个区别项目不相关数据库装在同台机器上测试时候又没有分析清楚这种环境所以也再次强调描述物理环境重要性.
4. 你所看到不定代表实际储存:mysql 处理集时候是要在jdbc 连接参数里面加上集参数而oracle 则是需要服务器端和客户端使用同种集才能正确显示所以你要明确你所看到集乱码不定代表真就是集乱码这需要你检查在转换的前集是否会出现乱码和转换的后是否出现乱码你桌面环境可能需要变动些参数来适应这种变动
5. 不要在个转换中使用多个集做为数据源.

11. 预定义时间维
Kettle提供了个小工具帮助我们预填充时间维这个工具在kettle_home / samples / transformations / General – populate date dimension. 这个举例产生数据不定能满足各种需要不过你可以通过修改这个举例来满足自己需求.

12. SQL tab 和 Options tab
在你创建个数据库连接时候除了可以指定你次需要化连接池参数的外(在 Pooling 选项卡下面)还包括个Options 选项卡和个 SQL 选项卡, Options 选项卡里面主要设置些连接时参数比如autocommit 是on 还是off , defaultFetchSize , useCursorFetch (mysql 默认支持)oracle 还支持比如defaultExecuteBatch , oracle.jdbc.StreamBufferSize, oracle.jdbc.FreeMemoryOnEnterImplicitCache ,你可以查阅对应数据库所支持连接参数另外个小提示:在创建数据库连接时候选择你数据库类型然后选到Options 选项卡下面有个Show help text _disibledevent="_blank">Kettle