简介面向使用 Kettle 进行 ETL 数据抽取与转换的开发人员这份文档围绕循环变量设置与作业循环控制展开重点解决多表结构相同、SQL 仅表名不同时的批量处理问题。内容结合 User_Tables 场景梳理表名变量传递、Trans 脚本获取表名、循环控制器、获取表行数与计数器累加等关键环节并说明如何将查询出的表名逐个赋值驱动相同抽取流程重复执行。资源包共 1 个 docx 文件约 282KB篇幅集中适合作为 Kettle 循环作业实现的速查材料。已有 2545 人学习下载读者可从中了解循环变量从取数、赋值到控制循环的整体思路以及表名替换和批量统计场景的排错线索便于在类似 ETL 任务中复用。1. 一张 SQL 模板跑几十张表Kettle 设置循环变量为什么要绕这么大一圈系统里有一张User_Tables存着一批业务表的名字这些表的抽取逻辑完全一样唯一不同的就是表名。手写十张表还能忍写一百张就是纯体力活。Kettle 设置循环变量要解决的正是这件事把「表名」变成变量让同一个转换被反复调用每轮换一个表名。它适合已经会用表输入、表输出但一碰到「批量表、动态表名、按清单跑作业」就得手工复制粘贴的 ETL 从业者。难点不在 SQL而在 Kettle 的变量作用域和作业连线——这也是后面几章真正要拆开的地方。2. 变量作用域与循环控制for 循环为什么必须拆到作业层2.1 变量存在哪转换作用域、作业作用域与 JVM 作用域Kettle 的变量不是一个全局字典而是挂在具体对象上的属性集。转换Transformation自己有一份作业Job自己有一份再往上还有根作业和 JVM 级别的一份。你在转换里设了一个变量默认它只活在这个转换的Properties里一旦这个转换跑完被销毁变量就跟着没了。所以循环场景下变量必须设到「父作业」这一层后续每一轮执行的转换才能读到同一个值。设置变量有两个入口转换里的「设置变量」Set Variables步骤和作业里的「设置变量」作业项。两者写的是同一套底层 API区别是作用域下拉框的选项。转换里的设置变量步骤有Valid in the current transformation、Valid in the parent job、Valid in the root job、Valid in the Java VM四档循环里几乎永远选Valid in the parent job。作业里的设置变量作业项作用域选项少一些但同样要注意别落在默认档位上。这里有个很多人第一次踩的坑变量的${}替换发生在步骤初始化阶段不是执行阶段。也就是说你在序号靠后的步骤里设了变量前面已经初始化完的表输入并不会「回头再取一次」。Kettle 的初始化顺序和执行顺序不是一回事把设置变量和消费变量塞进同一个转换里结果往往取决于步骤初始化的先后非常玄学。稳妥的做法只有一条设置变量放在一个转换里消费变量放在另一个转换里中间靠作业连线隔开。2.2 循环三件套把 for(int i0;isize;i) 拆到作业连线上Kettle 的作业没有for语句作业项之间靠 hop连线驱动所以循环只能靠「判断 回环连线」模拟出来。对应关系其实很直白for 语句片段作用Kettle 里的对应物int i 0初始化下标初始化转换里的设置变量步骤写入i与v_totali size循环条件「简单评估」Simple Evaluation作业项 success/failure 两条分支i迭代计数器累加转换读写作业变量循环体每轮处理取表名转换、统计转换等顺序执行的作业项「简单评估」这个作业项的配置就三个关键点比较字段、比较类型、比较条件。字段填i类型选Integer条件选比较值填变量v_total。它执行完会根据结果走两条不同的 hop一条回到循环体开头形成回环一条走向结束。回环是这个方案的核心Kettle 允许作业连线上出现环这条环就是你的for。回环能不能稳全靠一个容易被忽略的作业项属性Run Next Entries in Sequence。作业项默认可能是并行执行后续节点一旦并行循环体的几个转换会同时跑起来变量互相覆盖日志乱成一锅粥。手工在循环体的每个作业项上把执行方式改成顺序是最省事的做法。2.3 表名清单放在哪结果集、临时表还是 CSV原帖的思路是「先把表名复制到结果Copy rows to result再从结果里取」。这个思路本身没错但结果集有两个硬限制一是它更像队列而不是数组被下游「从结果获取记录」消费后就不再完整二是它不支持按下标随机访问你没法说「给我第 3 行」。循环跑到第二轮取不到数据八成就是栽在这儿。三种载体的差别我一般这么看载体随机访问断点续跑典型翻车点结果集Copy rows to result不支持不支持第二轮读不到数据临时工作表支持按主键查支持记录状态列忘记加批次号多批次互相污染CSV 文件需要自己解析支持并发写覆盖、编码不一致我一般会把清单落到一张临时工作表里加id、table_name、batch_no、status四列。id当循环下标用status记录这一轮跑成功还是失败——后面要做断点续跑和补跑全靠这一列。多花一个建表语句省掉后面无数次「为什么第二轮没数据」。3. 把表名清单做成可寻址的序列建表、初始化与设置变量3.1 清单落库ROWNUM 与 ORDER BY 的执行顺序坑第一步是把User_Tables里要处理的表名连同一个自增下标落到工作表里。Oracle 环境下最直觉的写法是用ROWNUM但ROWNUM是在ORDER BY之前赋值的直接写SELECT ROWNUM, table_name FROM user_tables ORDER BY table_name得到的是「先乱序编号再排序」编号和顺序对不上。正确做法是套一层子查询让排序先发生。-- 建立循环清单表id 当循环下标status 记录每轮结果 CREATE TABLE etl_table_list ( id NUMBER(10) PRIMARY KEY, table_name VARCHAR2(64), batch_no VARCHAR2(32), status VARCHAR2(8) DEFAULT WAIT, upd_time DATE DEFAULT SYSDATE ); -- 把待处理的表名写进清单先排序再编号顺序才可控 INSERT INTO etl_table_list (id, table_name, batch_no) SELECT ROWNUM, t.table_name, ${BATCH_NO} FROM ( SELECT table_name FROM user_tables WHERE table_name LIKE T\_% ESCAPE \ ORDER BY table_name ) t; COMMIT;batch_no用变量${BATCH_NO}传入是为了让同一张清单表能承载多个批次重跑时按批次清理或过滤不会互相干扰。id从 1 开始连续正好可以当i的取值区间。如果目标库不是 Oracle把ROWNUM换成窗口函数ROW_NUMBER() OVER (ORDER BY table_name)逻辑完全一致。提示清单表建在业务库里会带来权限和残留问题条件允许就建在独立的 ETL 元数据库里只把表名当字符串用不跨库查业务表。3.2 初始化转换一次取回总数、起始下标循环要跑先得知道跑几轮。初始化转换负责把v_total、i、BATCH_NO这几个变量一次性写好后面所有判断和取数都依赖它们。-- trans_init.ktr 中的表输入一次拿到总数与下标边界 SELECT COUNT(1) AS total_cnt, MIN(id) AS min_id, MAX(id) AS max_id FROM etl_table_list WHERE batch_no ${BATCH_NO} AND status DONE;这条 SQL 只返回一行下游接「设置变量」步骤就够了。四列的映射关系建议写成表格存档后面接手的人不用再猜上游字段变量名类型默认值作用域total_cntv_totalInteger0Valid in the parent jobmin_idv_startInteger0Valid in the parent jobmin_idiInteger0Valid in the parent job常量batch_noBATCH_NOString空Valid in the parent jobi初始化为min_id而不是 0是为了兼容「清单里中间被删过行、id 不连续」的情况。v_total存的是待处理总数判断条件用i v_start v_total - 1更严谨如果清单是干净的连续 id直接用i max_id也行。我一般偏向max_id实现简单出问题时看日志也直观。注意设置变量步骤里的「默认值」不是摆设。上游字段为 NULL 时变量会拿到默认值而不是空串正好用来兜住COUNT查不到行的极端情况。3.3 手工验证初始化转换别急着接作业初始化转换写完之后先别急着往作业里拼。单独用Pan跑一遍确认变量真的被设进去了这一步能省掉后面大量排查。# 用 Pan 单独执行初始化转换观察日志里的变量与行数 ./pan.sh \ -file/opt/etl/trans_init.ktr \ -param:BATCH_NO20240115 \ -levelDetailed \ -logfile/tmp/trans_init.log三个参数值得解释。-param:用来传批次号转换里用${BATCH_NO}接收-levelDetailed会把步骤级的读写行数打进日志方便确认表输入到底返回了几行-logfile把日志落盘比在终端里翻找舒服。跑完先看日志里total_cnt是多少再打开 Spoon 的「查看变量」或者直接在作业里挂一个写日志步骤确认v_total的值。这一步不做后面循环只跑一轮的时候你根本分不清是判断条件写错了还是变量压根没设进去。取表名的转换也用同样方式验证。它的 SQL 只有一句但变量拼进去之后必须能命中一行-- trans_pick_table.ktr按循环下标取当前这一轮要处理的表名 SELECT id, table_name FROM etl_table_list WHERE batch_no ${BATCH_NO} AND id ${i};${i}外面绝对不能加引号它是数字${BATCH_NO}里面必须加引号它是字符串。这个引号规则在表输入里错一次就报一次 ORA 错误代价很低但很烦人。这个转换的最后也挂一个设置变量步骤把table_name写到v_table_name作用域同样是父作业。到这一步一个「输入下标、输出表名」的函数就算做好了剩下的就是把它们串成环。4. 作业串联判断、累加与动态表名 SQL 的落地细节4.1 作业连线从 Start 到简单评估的分支结构作业的结构大致是这样一条链Start→ 初始化转换 → 简单评估 → 成功分支取表名转换 → 统计转换 → 累加转换 → 回到简单评估失败分支结束。这里有两个容易画错的地方。第一个是简单评估的两条出口。Kettle 的作业项执行完只有 true/false 两种结果Simple Evaluation的判定为真走success线为假走false线。循环体要挂在success线上结束要挂在false线上接反了就是「条件满足时直接结束、不满足时无限循环」。第二个是回环的目标节点。连线要指回Simple Evaluation本身而不是指回初始化转换。指回初始化会把i重置成 0形成一个永远从第一张表开始的死循环这个错误在日志里表现为同一张表被反复统计看行数增长就能发现。提示Spoon 里画回环连线时把 hop 从累加转换拖到简单评估即可Kettle 允许成环但会在作业图上显示一条反向箭头别以为是画错了。4.2 动态表名 SQL${v_table_name} 的三种翻车写法统计转换的 SQL 是整条链路的目的地也是最容易写错的一句-- trans_count_rows.ktr表名走变量其余部分是固定模板 SELECT ${v_table_name} AS table_name, COUNT(1) AS row_cnt, SYSDATE AS run_time FROM ${v_table_name};三种常见错误写法值得挨个说清楚。第一种是把变量包进引号FROM ${v_table_name}Kettle 替换后变成FROM T_ORDER数据库会把它当字符串字面量报「表或视图不存在」。第二种是拼上了 schema 却漏了点FROM ${SCHEMA}${v_table_name}替换完是FROM DW T_ORDER语法直接崩。第三种是在表名后面顺手加了空格或换行清洗阶段没注意报错信息里表名看起来完全正确实际带着不可见字符。表输入步骤默认会替换 SQL 里的变量但这个开关在部分版本里是可以关掉的步骤属性里那个「替换脚本中的变量」勾选框必须勾上。另外SQL 里的SELECT ${v_table_name} AS table_name这一列不是多余的它是给结果表留的追溯字段——后面按表名对账、按批次统计全靠这一列。4.3 计数器累加用 JavaScript 读写作业变量作业里的「设置变量」作业项只能赋常量做不了i i 1。所以累加必须放进一个转换用 JavaScript 步骤读写父作业变量。这个转换需要一个输入行来触发执行「生成记录」步骤设成 1 行就够。// trans_incr.ktr 的 JavaScript 步骤把作业变量 i 自增后写回 // parent_job 在 Spoon 里单独预览时为 null这里做一次保护 var job (parent_job ! null) ? parent_job : null; var i parseInt((job ! null) ? job.getVariable(i, 0) : 0, 10); var total parseInt((job ! null) ? job.getVariable(v_total, 0) : 0, 10); i i 1; // 对应 for 循环里的 i if (job ! null) { job.setVariable(i, String(i)); // 写回父作业作用域下一轮判断才读得到 } // 输出到流里方便在 Spoon 预览时核对当前轮次 dummy round i , total total;几个细节决定了这段脚本能不能跑通。getVariable的第二个参数是默认值变量不存在时返回它而不是抛异常这在第一轮刚启动、变量还没写进去的时候很关键。setVariable只写内存不会持久化作业一结束就没了所以断点续跑必须另外落一张表。parseInt的第二个参数10别省Kettle 变量永远是字符串遇上以 0 开头的值会被当成八进制解析08直接变NaN。还有一点这段脚本放在转换里转换的执行顺序由作业连线保证所以它一定跑在统计转换之后、下一轮判断之前。如果把它和统计转换并行执行i会在统计还没跑完时就加上去导致第一张表被跳过。这种 bug 不会报错只会让结果少一行最容易漏。4.4 结果落盘与状态回写让每一轮都留下痕迹每轮统计完结果要有地方去。最简单的做法是表输出到一个etl_table_stat表字段是批次号、表名、行数、执行时间再配一个「更新」步骤把清单表里对应id的status改成DONE。这一步不做循环跑完你只知道「跑完了」不知道「哪些跑成了」。-- 统计结果落库表输出用字段顺序与流一致 -- etl_table_stat(batch_no, table_name, row_cnt, run_time) INSERT INTO etl_table_stat (batch_no, table_name, row_cnt, run_time) VALUES (${BATCH_NO}, ${v_table_name}, ?, ?);表输出步骤一般用「不指定数据库字段」的多字段插入模式或者在步骤里显式映射。两个细节一是batch_no用变量传入而不是从前一行取保证同一批次的记录一定能对上二是行数这一列用参数占位别在 SQL 里用字符串拼接数字类型转换出问题时错误信息会非常难读。状态回写用一个「执行 SQL 脚本」作业项勾上「每一行都执行」是不对的这里要的是每轮执行一次UPDATE etl_table_list SET status DONE, upd_time SYSDATE WHERE batch_no ${BATCH_NO} AND id ${i};跑完一轮就在清单表里留一条DONE事后补跑只要筛status DONE就行。这套「清单表 状态列 统计表」的三件套比任何日志都管用——日志会被轮转、会被覆盖表里的记录不会。5. 循环跑不动时的排查清单五类高频翻车现场5.1 现象只跑了第一张表就退出循环原因通常是判断条件里的变量没读到。Simple Evaluation的比较值写的是${v_total}但这个变量在当前作用域里不存在比较时按空串或 0 处理1 0直接为假循环立刻走 false 分支结束。变量没生效的根源多半是设置变量步骤的作用域选了Valid in the current transformation转换跑完变量就销毁了。解决分两步。先确认作用域改成Valid in the parent job再在初始化转换后面挂一个「写日志」作业项把v_total打出来日志里是空值就说明设置变量那一步压根没执行到。还有一种小概率情况初始化转换的表输入返回 0 行设置变量步骤因为没有输入行而不执行变量自然不存在——这种情况用「生成记录」在表输入后面补一行常量或者给变量步骤配好默认值都能兜住。5.2 现象第二轮开始报「表或视图不存在」第一轮跑得好好的第二轮 SQL 里的表名变成了上一张或者空串。这基本可以确定是变量覆盖顺序出了问题累加转换在取表名之前执行了或者循环体里的几个转换被并行调度。Kettle 作业项的默认执行方式在部分版本里是并行一旦并行v_table_name会被多个转换同时写谁最后写谁生效。解决办法是把循环体里所有作业项的执行方式统一改成Run Next Entries in Sequence并且在设计上保证「取表名 → 用表名 → 累加」这个严格顺序。另一个隐蔽的可能是取表名转换返回了 0 行id ${i}没命中设置变量步骤不执行v_table_name保留着上一轮的值SQL 就会拿旧表名去查——这时的报错信息看起来完全正常只有对比日志里的轮次和表名才能发现。5.3 现象作业 CPU 跑满日志疯狂刷同一张表典型的死循环回环连线指错了目标或者判断条件永远为真。最常见的一种是指回初始化转换i每轮被重置条件恒成立。另一种是累加转换没真正写回变量——JavaScript 里用了setVariable但漏了parent_job前缀写到了转换自己的作用域父作业的i一直不变。排查顺序先看日志里连续几轮的表名是不是同一个是就去看累加转换的输出字段round的值有没有递增最后检查回环连线的目标节点。Kettle 作业跑起来之后强行中断可能留下锁排查前先把作业停干净再改。这类问题最省时间的做法是给循环加一个硬上限比如在 JavaScript 里判断if (i 10000) { job.setVariable(i, 0); }至少不会把机器跑废。5.4 现象表名看着对SQL 就是匹配不到数据变量值两端带了空格或者大小写和数据库里的实际对象名不一致。Oracle 里不加引号的表名默认大写如果清单表里存的是小写比如从别的系统导进来的FROM t_order会被解析成T_ORDER而去查一个不存在的对象反过来加了双引号FROM t_order又要求对象名严格小写。解决方式是在清单落库那一步就统一大小写UPPER(table_name)一把梭别指望运行时再修。空格问题用「设置变量」步骤里的Trim勾选项处理或者在 SQL 里直接TRIM(table_name)。这类问题最烦的地方是报错信息往往只给一个 ORA 编号不告诉你到底查的哪个名字所以我在统计转换里一定保留${v_table_name} AS table_name这一列出错时对比结果表和清单表一眼就能看出差异。5.5 现象单跑没问题放进定时调度就串批次两个批次同时在跑清单表里batch_no过滤没生效v_table_name被另一个批次覆盖。Kettle 的变量是 JVM 级别的内存对象同一个 Spoon 或 Carte 实例上并发跑两个作业变量名一样就会互相踩。三条防线清单表和统计表都必须带batch_no并且所有 SQL 都带上这个过滤条件变量名带批次后缀不现实那就保证同一个 Carte 实例一次只跑一个批次用调度器串行化作业开头加一个「检查是否已有同批次在跑」的查询有就直接结束。第三条最土但最有效尤其是补跑和正跑撞车的时候。6. 让循环可验证行数对账与断点续跑循环类作业最怕的不是跑失败是「跑成功了但结果不对」。所以我一般会在作业末尾挂一个对账转换把清单表的DONE条数和统计表的记录数拉出来比一比两个数不相等就直接让作业失败。-- trans_check.ktr循环结束后对账不一致就让作业标红 SELECT (SELECT COUNT(1) FROM etl_table_list WHERE batch_no ${BATCH_NO} AND status DONE) AS done_cnt, (SELECT COUNT(1) FROM etl_table_stat WHERE batch_no ${BATCH_NO}) AS stat_cnt FROM dual;拿到两列之后接一个「过滤记录」步骤条件写done_cnt stat_cnt把不匹配的行送到「中止」步骤作业就会以失败状态退出。这比人肉翻日志可靠得多——调度系统只认退出码你让作业自己喊疼比事后发现少统计了三十张表要划算。断点续跑靠的是i的持久化。每轮结束除了更新status再把当前i写进一张etl_job_state(batch_no, cur_i, upd_time)表。重跑时初始化转换改成优先读这张表有记录就从cur_i 1继续没记录才从min_id开始。配上status DONE的过滤条件重跑会自动跳过已完成的表只补没跑的。这个机制在批量几百张表、中途网络抖一下的场景里能省掉整批重来的时间。还有一个验证技巧把v_table_name和i一起写进统计表的备注列事后按id排序看一遍表名是不是按预期顺序出现、有没有重复行。Kettle 的日志默认只记步骤级统计单条数据的流转不落日志指望日志排查等于缘木求鱼。我最早做这类批量抽取时图省事把表名清单塞进结果集跑第一轮完美第二轮直接空转排查了大半天才想明白结果集被消费掉了。从那以后我每次做循环作业都强制先建清单表、先写状态列、先加对账步骤三样缺一样不开工。变量作用域、执行顺序、结果载体这三件事看着都是配置项实际上决定了这套循环能不能在生产上跑一整年。希望帮到你。本文还有配套的精品资源点击获取