简介这份围绕 Kettle 循环变量设置的 docx 文档面向已具备基础 ETL 操作经验、需要处理多表循环抽取与统计的数据开发人员重点解决变量一次只能接收一个值、难以批量替换表名的实际问题。压缩包内共 1 个 docx 文件约 282KB内容集中便于快速查阅与留存。文档涉及 Trans 脚本获取表名、获取表数量、循环控制器、计数器累加及 TABLENAME 变量传递等关键环节并结合表行数统计场景说明循环控制思路可帮助读者理解 Kettle 中循环作业的组织方式与表名动态替换方法。目前已有 2546 人学习适合作为多表批量处理与循环任务配置的参考材料。1. 从一张 User_Tables 说起循环执行同一套抽取逻辑卡点从来不在 SQL 上有张系统表叫 User_Tables里面只存了一件事——业务表的表名。要对这批表跑同样的行数统计SQL 本身没什么可纠结的SELECT COUNT(1) FROM A换成FROM B就完事。麻烦的是表会增减写死十几个表输入步骤下个月加两张表就得改脚本重测一遍。Kettle 设置循环变量解决的正是这个把表名从「代码里的常量」变成「运行时读到的变量」让作业自己转圈表多表少都不用改脚本。Kettle 4.4 这个版本做这件事的路子相当原始没有现成的 For Each 组件需要把「复制记录到结果」和「从结果获取记录」当作传接棒的工具用「设置变量」把表名塞进${TABLENAME}再用一条判断连线把循环的出口守好。适合的场景也很明确手上有一批表结构相近、只是表名不同的表需要批量跑同一套抽取或统计逻辑而且后续还会加新表。这套做法不适合表结构差异大、每张表要单独写 SQL 的场合那种情况硬编码反而更快。也不适合超大规模的表清单因为每一轮都要重新装载一次清单表数量到几千条时开销会上来。几百张表以内这套方案足够用。2. 需求拆解与方案选型表名清单、变量传递与作业循环的三层骨架2.1 把「对每张表做同样的事」翻译成可执行的任务清单从数据库表出发的方案第一步不是打开 Kettle 画图而是把需求写成一份能逐条核对的清单。这份清单里要能回答五个问题表名从哪儿来、有几张、每张做什么、结果写哪儿、中途失败怎么办。项目具体内容落地方式输入源User_Tables 的 Table_Name 列表输入步骤一条 SELECT待处理数量动态随业务表增减运行时查询 COUNT(1)单表处理逻辑统计行数SELECT COUNT(1) FROM ${TABLENAME}结果落点一张汇总表含表名和行数表输出步骤异常边界空清单、单表失败前置判断 日志留痕这份清单里最容易含糊的是最后一项。很多方案在作业跑通之后就不管异常了结果清单为空时作业不进循环、日志里什么都没有排查的人只能靠猜。把边界写进需求后面的结构设计才有依据。另外要明确一个问题每张表处理的结果是「追加」还是「覆盖」。统计行数这种场景通常按批次追加需要一张带批次号或者执行时间的汇总表否则第二次跑就和第一次的数据混在一起。这个细节在需求阶段不定下来后面改表结构比改作业还费劲。2.2 硬编码多分支 vs 变量驱动循环两种做法的取舍面对这个需求至少有三条路可选。三条路在不同规模下各有优劣选哪条取决于表数量的稳定性。第一条是硬编码有几个表就拉几个表输入步骤并行跑。优点是结构一眼看穿每张表的 SQL 都能单独调缺点是表数量一变就得改作业表到二三十张时画布会变得难以维护改一个公共逻辑要改二十处。第二条是在一个转换里用 JavaScript 步骤取全部清单然后在脚本里for循环配合「执行 SQL 脚本」步骤逐条跑。这条路的优点是只有一个转换不用处理作业与转换之间的变量传递缺点是整批处理变成一个黑盒中间某张表报错时日志里很难定位是第几轮、哪张表而且长事务容易把连接池占住。第三条是作业循环清单在转换里准备好循环控制在作业层面展开每一轮调用一个独立的内层转换。优点是每一轮的输入输出都是独立的、日志按轮次分得清楚、单轮失败可以单独重跑缺点是作业与转换之间的变量作用域要处理对这是整个方案里最容易翻车的地方。表数量在几十到几百之间、且会持续变化时第三条最稳。它把「遍历」这件事拆成了「准备清单」「判断还有没有下一张」「处理当前这张」「把计数器往前推」四个动作每个动作都能在日志里看到痕迹。这也是下面所有实现细节的出发点。2.3 方案骨架两个转换做数据准备一个作业串起循环整套东西由六个转换和一个作业组成。转换负责单件事作业负责把转换按顺序串起来并在中间加两条判断连线。作业层的执行顺序是这样Start之后先跑t_total查一次总表数把TOTAL_ROWS写进变量用「简单的评估」做第一道前置判断total_rows 0才继续否则直接结束避免空清单在后续步骤里报一堆莫名其妙的错t_load把全部表名读出来加上序号复制到结果集t_check从结果集里取回清单按当前计数器定位下一个表名写入TABLENAME同时输出LOOP_FLAG第二道「简单的评估」判断LOOP_FLAG Y真则进循环体假则结束t_biz是循环体用${TABLENAME}拼 SQL统计出结果写汇总表t_inc把ROW_INDEX加一连线回到t_load。骨架里有两个设计决定值得说清楚。一个是每轮都重新执行t_load而不是在第一轮把清单缓存住。Kettle 的作业结果集在每次「复制记录到结果」时会被覆盖想让清单跨轮存活要额外做手脚容易在第五轮之后突然拿不到数据。每轮重查一次清单代价是一条很小的 SELECT换来的是行为可预测——这个取舍在几百张表以内是划算的。另一个是把「取下一个表名」和「判断还要不要继续」放在同一个转换t_check里。分开写当然更清晰但会多一次结果集传递而结果集传递正是这套方案里最脆弱的一环。能少一次就少一次。3. 表名清单的落地从表输入到复制记录再到变量装载3.1 读取 User_Tables表输入步骤的字段与排序清单来源的 SQL 要写得克制只取需要的列并且一定要排序。SELECT Table_Name FROM User_Tables WHERE Table_Name IS NOT NULL AND Table_Name ORDER BY Table_NameORDER BY不是可选项。后面靠「增加序列」给每行打序号序号是循环定位的依据如果查询不带排序数据库返回的顺序在两次执行之间可能不同第 3 轮取到的表名就可能是上一次的第 5 个。这种错位不会报错只会让某张表被处理两次、另一张表被跳过排查起来极其费劲。WHERE过滤掉空表名同样必要。表名进到${TABLENAME}拼成 SQL 之后空值会变成SELECT COUNT(1) FROM数据库直接报语法错误作业停在一个看不出原因的步骤上。如果表名里可能带空格或者特殊字符SQL 里就要加引号比如SELECT || Table_Name || AS Table_Name FROM ...。这一步在开发环境一般用不上上到生产库才出问题。稳妥的做法是清单里直接带好引号内层统计转换就不再管引号的事。表输入步骤里还有一个勾选框「替换 SQL 语句里的变量」。这个勾选只影响本步骤的 SQL 文本替换和清单查询无关所以在t_load里可以不勾但在t_biz里必须勾上否则${TABLENAME}会被当成普通字符串发给数据库。这个区别后面还会提到。3.2 增加序列与复制记录到结果让清单能在作业里被下一棒接住清单读出来之后要给它加上序号。用「增加序列」步骤Add sequence配置三项Use counter name填一个自定义名字比如seq_table不同转换之间不要重名Start at value1Increment by1Output field nameidx。序号是 1 到 N 的整数和ROW_INDEX做比较时两边都是整数不会出现字符串比较的坑。序号加完接「复制记录到结果」步骤Copy rows to result。这个步骤配置极简只有一个可选的「结果字段名」输入框留空即可它会把上游所有字段原样放进作业的结果集。作业里下一个转换用「从结果获取记录」就能拿到这一批行。这里有个必须说清楚的点Kettle 的一个转换只有一个结果集第二次执行「复制记录到结果」会把上一次的覆盖掉。所以整个作业里同一时刻只能有一个转换的结果是「有用的」。这也是为什么t_biz和t_inc都不能再复制记录到结果——它们一旦复制下一轮t_check取到的就不是表名清单了。顺便说一下字段类型。「增加序列」产出的idx是整数而「从结果获取记录」拿回来之后某些版本的 Kettle 会把它转成字符串。所以在脚本里比较之前统一用parseInt包一层不要直接比较。这个差异在开发机上跑十次都碰不到换一台机器就翻车。3.3 设置变量把一行表名变成 ${TABLENAME}清单有了、序号有了接下来是把「第 n 行」变成「一个变量」。这一步在t_check转换里完成结构是「从结果获取记录」→「JavaScript 代码」→「过滤记录」→「复制记录到结果」。// t_check 转换里的 JavaScript 步骤主体 // 输入字段idx增加序列生成的整数序号、Table_Name表名 // 依赖变量ROW_INDEX已完成轮次、TOTAL_ROWS总表数 var done parseInt(parent_job.getVariable(ROW_INDEX, 0), 10); var total parseInt(parent_job.getVariable(TOTAL_ROWS, 0), 10); var next done 1; // 本轮要处理第几张表 // 命中目标行时把表名写成 JVM 级变量作用域参数 s 是关键 if (parseInt(idx, 10) next) { parent_job.setVariable(TABLENAME, Table_Name, s); } // 只让第一行走完全程保证下游判断步骤每次只收到一行结果 if (parseInt(idx, 10) 1) { var flag (next total) ? Y : N; parent_job.setVariable(LOOP_FLAG, flag, s); }这段代码分两段看。前半段做定位遍历清单的每一行只有序号等于ROW_INDEX 1的那一行才会触发setVariable把表名写进TABLENAME。后半段做收敛只在序号为 1 的那一行上产出LOOP_FLAG判断依据是「下一个位置是否还在总数范围内」。setVariable的第三个参数容易被忽略。Kettle 里变量有三个作用域s表示整个 JVM 内可见r表示根作业内可见p表示父作业内可见。作业里一个转换设置的变量要被另一个转换读到必须用s。用p时在本转换内读得到、出了转换就消失表现出来就是「变量明明设置了表输入里还是原样的${TABLENAME}」——这是这套方案里出现频率最高的问题。后面接「过滤记录」步骤条件写idx 1只放第一行往下走。这样t_check的输出永远是一行下游的「简单的评估」判断起来不会因为多行而行为不确定。最后接「复制记录到结果」把这一行带有idx字段交出去。注意这一行里并没有LOOP_FLAG字段——LOOP_FLAG是用变量方式写的作业层的「简单的评估」直接读变量即可不必走字段。如果习惯走字段可以在 JavaScript 里用var flag_out flag;输出一列然后在评估步骤里比较字段值两条路都通。4. 循环控制的实现条件判断、计数器累加与内层转换的拼装4.1 循环条件写在哪儿简单的评估步骤与 LOOP_FLAGKettle 作业里没有while组件循环是靠「作业项之间的连线 判断分支」实现的。判断由「简单的评估」作业项Simple evaluation承担它读上一步结果里的一个字段或者一个变量按配置的比较符给出真/假两条出线。配置三件事判断依据填LOOP_FLAG比较符选「等于」比较值填Y。真线接到t_biz假线接到结束。整个循环的出口就在这条假线上。有一个细节值得留意LOOP_FLAG是在t_check里设置的变量但「简单的评估」在某些版本里默认去读上一步的结果字段而不是变量。如果配完之后行为不对可以在t_check的 JavaScript 里同时输出一个字段// 在 t_check 的 JavaScript 末尾补一行让判断既能看到变量也能看到字段 var loop_flag_field flag;然后在评估项里比较这个字段名。两条路留一条能用的就行但开发时要明确自己走的是哪条不然调起来会很混乱。循环的起点选在哪也影响行为。把回连的线接到t_load上意味着每一轮都重新读一次表名清单好处是清单始终和数据库一致中途有人加表也不会漏代价是每轮多一条 SELECT。接到t_biz前一步会少查几次但清单会一直沿用第一轮的结果。表数量少、清单稳定的场景下两种都行表数量随时会变的场景下建议接回t_load。判断为假之后循环要干干净净地退出。最常见的翻车是假线上又接了别的收尾转换而那个转换又设置了LOOP_FLAG把刚写好的出口条件改掉了结果作业跑满整个清单还在转。收尾逻辑放在假线之后是可以的但不要再碰循环控制相关的变量。4.2 计数器累加一次取下一个表名 自增的合并写法t_inc这个转换只干一件事把ROW_INDEX加一。它需要一个输入行才能被执行所以在前面接一个「生成记录」步骤产生一行常量。// t_inc 转换里的 JavaScript 步骤 // 前置生成记录步骤产出一行字段 dummy 值为 1 var cur parseInt(parent_job.getVariable(ROW_INDEX, 0), 10); // 累加并写回作用域同样必须是 s parent_job.setVariable(ROW_INDEX, String(cur 1), s); // 顺手把当前轮次打进日志方便事后核对跑了哪些表 var tbl parent_job.getVariable(TABLENAME, ); parent_job.setVariable(LAST_TABLE, tbl, s);setVariable的第二个参数必须是字符串。Kettle 的变量在底层就是字符串类型传数字进去有的版本会静默失败有的会写成1.0这种形式下一轮parseInt之后变成 1看起来对了但再下一轮就乱。统一String()包一层省掉一整类奇怪问题。t_inc里同时把TABLENAME记到LAST_TABLE是为了后面验收的时候对账。日志里能看到「上一轮处理的是哪张表」万一循环提前退出一眼就能看出停在哪。累加步骤和取表名步骤的顺序不能反。如果先自增、再去t_check取表名next就变成了done 2第 1 张表永远被跳过。这个错误不会报异常只会让汇总表少一行数据跑完对比总数才发现。开发时建议在t_check里加一条日志输出next和total的值跑第一轮就能看出有没有错位。如果清单里表的数量到了几百张每一轮都要t_inc转换一次开销主要在转换的启动上。这种量级下可以考虑把「取下一个表名 累加」合并到同一个转换里减少一次转换调度。但在几十张表的规模下没必要提前优化把逻辑拆清楚更重要。4.3 内层统计转换${TABLENAME} 替换 SQL 里的表名t_biz是真正干活的转换里面只有一个表输入步骤或者表输入接表输出。SELECT COUNT(1) AS row_cnt FROM ${TABLENAME}要让${TABLENAME}生效表输入步骤的「替换 SQL 语句里的变量」Replace variables in script必须勾上。不勾的时候Kettle 会把${TABLENAME}原样发给数据库报的是 SQL 语法错误错误信息里能看到花括号这就是没勾选的特征。另外要注意变量的求值时机。表输入的 SQL 是在步骤初始化时替换的也就是转换启动那一刻。如果TABLENAME在此之前还没设置好替换出来的就是空字符串SQL 变成FROM同样报语法错。所以t_check必须在t_biz之前执行完作业里的连线顺序决定了这一点。结果输出这一步如果只是统计行数用「表输出」把结果写进汇总表即可。汇总表至少要两个字段一个表名字段一个行数字段。表名不要直接写${TABLENAME}到字段值里有些步骤的变量替换时机不对最好在 SQL 里带上SELECT ${TABLENAME} AS table_name, COUNT(1) AS row_cnt FROM ${TABLENAME}这样输出时表名和行数在同一行里表输出步骤直接对应字段即可不用在作业层再拼一次。如果单张表统计失败Kettle 默认会让整个转换报错、作业停住。批量场景下更常见的需求是「跳过失败继续跑」。实现方式是在表输入里设置错误处理Error handling把出错行导向一个单独的步骤记录到错误表里同时不中断主流程。这样一轮失败不影响后面的表最后看错误表就知道哪些表没跑成。4.4 作业连线与执行顺序把所有作业项按顺序连起来之后结构是这样的顺序作业项类型关键输出1Start起点—2t_total转换变量 TOTAL_ROWS3前置判断简单的评估真/假两条线4t_load转换结果集含 idx5t_check转换变量 TABLENAME、LOOP_FLAG6循环判断简单的评估真/假两条线7t_biz转换汇总表若干行8t_inc转换变量 ROW_INDEX 加一9回到 t_load连线—前置判断的假线直接接结束不写任何收尾逻辑循环判断的假线也接结束。两条假线不要连到同一个结束节点上否则在日志里看不出是哪条判断结束的流程排查时会多绕一圈。t_inc回到t_load的连线在 Kettle 里就是普通的作业项连线画成环是可以的。画的时候注意别把回连线和主流程线交叉得太复杂节点一多画布就看不清了。建议把回连线拉成一个明显的弧线标注上「下一轮」后面接手的人不至于看半天。作业跑起来之后日志是按作业项逐条输出的。每一轮会看到t_load、t_check、t_biz、t_inc各出现一次轮次越多日志越长。这一点在验收时反而是好事——日志条数除以四就是跑过的轮数和总表数一对就清楚。5. 避坑与排查变量不生效、循环不退出、表名带空格的四类翻车点5.1 现象变量明明设置了表输入里还是原样的 ${TABLENAME}原因setVariable的作用域参数写错了用了p或者没传第三个参数。Kettle 里变量的可见范围是按作用域分的父作业级和根作业级的变量在兄弟转换里读不到只有 JVM 级s才能跨转换可见。这个问题在同一个转换内测试时永远复现不出来因为本转换读本转换的父作业级变量是能读到的。解决所有需要跨转换读的变量setVariable一律带s。检查的时候不要只看代码去「设置变量」步骤的配置界面看「变量作用范围」下拉框选的是不是「Valid in the Java Virtual Machine」。两个地方保持一致才不会出现代码改了一处、步骤配置还是旧值的情况。5.2 现象循环多跑一轮或者干脆停不下来原因通常是三个之一。一是t_inc的连线接错了位置接到了t_check之前导致ROW_INDEX每轮加两次二是判断条件用了而不是边界上多走一轮三是t_check在清单为空时没有产出LOOP_FLAG下游判断读不到值按上一次的Y继续往下走。解决先看日志确认t_inc每轮只出现一次。再看t_check里next total这个条件next是「本轮要处理第几张表」total是总数两者相等时这一轮应该执行所以判断用的是不是——容易记反。最后给前置判断加一道保险TOTAL_ROWS为 0 时直接结束不进循环这样空清单的情况根本不会走到t_check。5.3 现象跑到第五轮之后t_check报错说找不到 Table_Name 字段原因作业结果集被覆盖了。某一轮里某个转换通常是t_biz或者它的下游也执行了「复制记录到结果」把表名清单顶掉了下一轮t_check用「从结果获取记录」拿到的是一批完全不相干的数据字段对不上。解决全作业范围内只有t_load和t_check允许使用「复制记录到结果」其他转换一律不碰。排查时把所有转换打开逐个看凡是出现这个步骤的地方都要问一句「它是不是必须在这里」。另一种彻底规避的方式是不靠结果集传清单每轮直接用一条带序号的 SQL 查第 n 个表名牺牲一点查询次数换掉整类问题表不多的时候这个方案更省心。5.4 现象某张表统计时报语法错误错误信息里的表名带奇怪的字符原因表名里带空格、下划线以外的符号或者大小写敏感。${TABLENAME}拼进 SQL 时是裸的数据库看到一个带空格的标识符会直接报错。这类表名往往只占清单里的一两行前面几十张跑得好好的到这儿突然停住。解决在t_load的 SQL 里就把引号拼好SELECT || Table_Name || AS Table_NameOracle、PostgreSQL 用双引号MySQL 用反引号这样t_biz拿到的表名已经带引号拼出来就是合法的。另外在t_biz上开启错误处理让单表失败不影响整批错误记录落到一张单独的日志表里跑完再统一处理。这类翻车在开发环境基本遇不到因为开发库的表名都很规整只有到了别人的库上才暴露所以提前加引号这一步不要省。6. 验收与交付用日志和对照表证明每一张表都被处理过一次一套循环方案交出去最怕的不是跑不起来而是「跑起来了但少跑了一张表」。这种情况不报错、不告警只看汇总表也看不出问题因为汇总表本来就不会告诉你「应该有几行」。验收的第一步是算清楚基准数。直接从源库查一次SELECT COUNT(1) AS expect_cnt FROM User_Tables WHERE Table_Name IS NOT NULL AND Table_Name 这个数记下来后面所有核对都围绕它。第二步是对账。汇总表按批次查出实际处理数和基准数比SELECT batch_id, COUNT(1) AS actual_cnt, COUNT(DISTINCT table_name) AS distinct_cnt, SUM(row_cnt) AS total_rows FROM table_row_summary WHERE batch_id ${BATCH_ID} GROUP BY batch_idactual_cnt和基准数必须相等distinct_cnt也必须和actual_cnt相等。第二个数比第一个数更能暴露问题——如果某张表被处理了两次、另一张被跳过总数可能刚好对上去重数就对不上了。第三步是抽检。从清单里随机挑三张表手工在数据库里跑一次SELECT COUNT(1)和汇总表里的row_cnt逐个比对。抽检不是为了覆盖全部而是为了确认「汇总表里的数字确实来自那张表」而不是因为变量没替换成功、所有表都统计了同一张。这三步走完再看一眼日志。日志里每一轮应该有四类作业项各出现一次轮数乘以四等于日志行数如果某几轮少了t_biz说明那一轮的判断出了问题得回头查LOOP_FLAG。交付时我会额外附两样东西。一张是源库表名清单和汇总表结果的对照表两个 CSV 拉出来做一次差集差集为空才算过另一份是这套作业的参数说明尤其是TABLENAME、ROW_INDEX、TOTAL_ROWS、LOOP_FLAG四个变量的名字、作用域、由哪个步骤写入写成一行一行的方便接手的人改。这两样东西不难做但省下的沟通时间远超制作成本。从那以后我每次做这类批量循环的作业都会先在小规模数据上把「表数量 1」「表数量 0」这两个极端情况跑一遍。只有一张表的场景最容易暴露判断条件的边界错误零张表的场景最容易暴露空结果集的处理缺口这两个过了剩下几十上百张的常规情况基本不会翻车。希望帮到你。本文还有配套的精品资源点击获取
