做数据抽取的兄弟应该都有这种体验Kettle也叫PDI用久了你会发现真正麻烦的不是写SQL而是怎么把查询出来的结果在转换之间、作业之间、甚至和外部接口之间来回传递。尤其是当你同时碰到变量、结果集、JSON这三个东西的时候很多老手也会卡壳。今天我把这三块掰开揉碎讲清楚顺便给几个直接能抄作业的案例帮你在做增量抽取、接口对接、动态SQL这类任务时少走点弯路。先说一个最常见的痛点你在“表输入”里跑了一条SQL查出来一个时间字段或者一个编号接下来另一个“表输入”要用这个值去拼SQL怎么传新手第一反应是写死但数据每次都在变写死就是埋雷。这时候就得靠Kettle的变量机制把查询结果“带出去”。但Kettle里的变量又不止一种命名参数、环境变量、内部变量、自定义变量光名字就能绕晕不少人。还有结果集很多人用了很久Kettle都不知道在作业里可以把上一个转换的查询结果当成一个“临时表”传给下一个转换。至于JSON现在接口对接越来越常见REST Client拉回来的数据怎么拆、怎么取字段、怎么再拼成新的JSON也是问得最多的问题。这篇文章不聊虚的主要围绕“查询后拿到的数据如何用变量、结果集、JSON三种方式传出去并在后续环节用起来”这条主线。我尽量把原理、步骤、坑位一起写清楚适合正在做ETL数据处理、接口对接、定时任务同步的兄弟参考。版本方面我以Kettle 8.x/9.x的界面为准老版本界面略有差异但思路通用。1. 先搞清楚Kettle里的“变量”到底是什么体系很多人一上来就问“Kettle变量怎么用”其实Kettle的变量不是一个东西而是好几套东西混在一起。你不把这几套东西分清楚后面一定会出现“我明明设了变量怎么用不了”的问题。1.1 Kettle的变量家族命名参数、环境变量、内部变量、自定义变量Kettle里的变量大概可以分成四类第一类是命名参数Named Parameters。这玩意儿长得像变量但它更像是在跑转换或作业之前由外部传进来的“入参”。比如你做定时任务时调度平台给你传一个日期字符串你在转换设置里定义一个P_DATE参数运行时通过命令行或者作业项把值传进来。在转换内部你用${P_DATE}去引用它。注意命名参数是在转换运行前就要定好的不是运行过程中动态算出来的。第二类是内部变量Internal Variables。这是Kettle自带的系统变量比如${Internal.Entry.Current.Directory}表示当前作业项所在目录${Internal.Job.Filename.Directory}表示作业文件所在目录${Internal.Transformation.Filename.Directory}表示转换文件所在目录。这些变量不需要你定义直接用就行常用于拼接相对路径、读取同目录下的配置文件。第三类是环境变量Environment Variables。这是操作系统层面的变量或者你在Kettle的.properties文件里定义的全局变量。比如你可以在Kettle安装目录的kettle.properties里写一个DB_INSTANCE_NAME然后在任意转换里用${DB_INSTANCE_NAME}引用。这类变量的特点是全局生效但改起来麻烦一般用于存放环境级别的配置不适合存放业务数据。第四类是自定义变量Custom Variables。这一类才是我们日常用得最多的也是本文的重点。它的定义方式有两种一是用“Set Variables”步骤在转换运行中将某个字段值写入变量二是用作业里的“设置变量”作业项。自定义变量的核心特点是“运行中动态产生”比如我先查一个表拿到最新的更新时间然后把这个时间写进变量后面的步骤再拿这个变量去查增量数据。这里要特别提醒一句上面四类变量虽然用起来都是${xxx}的写法但生命周期完全不一样。命名参数和环境变量在转换开始前就固定了内部变量只在Kettle文件路径相关的场景有用真正能承载“查询结果”的只有自定义变量。所以在动手之前先问自己一句你要传的值是什么时候确定的如果是查询之后才能确定那就别想着用参数或环境变量老老实实走Set Variables。1.2 三种方式把查询结果写入变量把查询结果写入变量说到底是同一个套路先用“表输入”查出一行记录然后用“Set Variables”步骤把记录里的某个字段映射为变量。但具体实现时有几种不同的做法适用场景不太一样我分别说一下。第一种最简单直接用“Set Variables”步骤接在“表输入”后面。在“表输入”里写好SQL查询结果只返回一行、一个字段。然后“Set Variables”步骤里指定字段名和变量名设置变量的作用域。这样后面跟着的步骤就能用${变量名}引用这个值了。这里有个关键点就是“Set Variables”步骤里的**变量作用域Variable Scope**设置。Kettle里作用域选项有当前转换Transformation、父作业Parent Job、根作业Grandparent Job等。如果你只是在同一个转换内部后续步骤要使用选“Transformation”就够了。但如果你是在一个作业里先跑A转换设置变量再跑B转换要使用这个变量那必须把作用域设置成“Parent Job”或者“Root Job”。很多人的变量“传不出去”十有八九是作用域选错了后面还浑然不觉。我的经验是如果你是在作业里做跨转换传参统一把作用域设为“Root Job”这样无论中间隔着几个作业项变量都能一路传到最外层避免出现“下层能吃、同层吃不到”的诡异情况。第二种方式是用“表输入”“Get Variables”步骤反向验证。这个方法其实不是写变量而是读变量但在调试时很有用。你先用“Set Variables”把值写进去再用“Get Variables”把变量读出来放到流里用“预览”看一眼是否正常。这样做的好处是能把变量的值可视化不用靠猜。第三种是动态SQL拼接。比如我要先查一个批次时间再拿这个时间去查增量数据。可以把两步合并成一步在“表输入”的SQL里用一个子查询直接写成SELECT * FROM t_order WHERE create_time (SELECT MAX(create_time) FROM t_order) 这样确实方便但可读性差而且如果“查最大值”和“查增量数据”是两个数据源这种写法就不灵了。所以我还是建议先查再写变量再用变量拆成两步逻辑清晰出了Bug也好排查。1.3 变量写入了但下游就是读不到怎么回事这个问题我在技术群里被问过无数次总结下来无非这几种原因。第一种变量作用域没选对。刚才说过在转换里设置的变量默认只对当前转换有效作业里另一个转换要使用必须在Set Variables里把作用域设为“Parent Job”或“Root Job”。很多人默认选“Transformation”结果其他转换读出来是空的。第二种变量的替换开关没开。Kettle里很多步骤比如表输入的SQL默认会对SQL做变量替换但有些步骤是不自动替换的或者你在配置里手动关掉了。如果你发现SQL里的${xxx}原样被送到了数据库执行先检查一下步骤属性面板里有没有“Enable lazy conversion”或者“Variable substitution”之类的开关。第三种变量名拼写或大小写问题。Kettle的变量名是区分大小写的${p_date}和${P_DATE}不是同一个变量。我吃过这个亏在“设置变量”里写的变量名是大写到后面引用时习惯性地写成小写折腾了半天。第四种执行顺序问题。Kettle转换里的步骤是并行执行的不是严格按连线顺序跑完一个再跑下一个。如果你没有在“表输入”和“Set Variables”之间建立正确的流连接或者后面用变量的步骤其实和“Set Variables”是两条独立的流那就有可能出现“后面的步骤先跑了变量还没写入”的竞态问题。解决办法是确保下游步骤的数据流是从“Set Variables”的出口连过去的或者干脆把整个逻辑拆分成两个转换中间用作业串联保证执行顺序。2. 结果集被低估的跨步骤传数据方式变量适合传标量值一个字段一个值。但如果你查询出来的是一张表、几十行数据变量就无能为力了——总不可能搞几十个变量吧这时候结果集Result Set就派上用场了。2.1 结果集到底是个什么东西很多刚接触Kettle的人会把“结果集”理解成数据库里的游标或者临时表其实不太准确。Kettle里的结果集是跑在内存里的一组行数据在作业级别流通。它有几个特殊之处结果集不是数据库表不需要建表、不需要连接字符串。结果集只在作业的上下文中有效。你在一个转换里把数据Copy to Result然后该转换正常结束数据暂存在作业的内存里下一个转换启动时可以通过Get rows from result把这份数据读取出来。结果集在转换内部也能用但意义不大因为转换内部本来就是行流式传递步骤之间不需要结果集中转。结果集的真正价值是跨转换。我用一个场景来说明。假设你要定期读取某个文件夹下所有CSV文件然后逐个解析入库。你可以先写一个转换A用“获取文件名”步骤扫描文件夹得到文件名列表然后把每一行“复制到结果集”。接着在作业里写一个循环根据结果集中的文件数决定循环多少次每次循环启动转换B先从“获取结果集”得到当前需要处理的文件名再用这个文件名去拼接路径、读取CSV内容。这样文件列表和实际解析就解耦了作业结构非常清晰。2.2 实战查询结果如何放进结果集、再取出来把查询结果放进结果集操作比变量还简单。接在“表输入”后面的是“复制到结果集”这个步骤它不需要配置什么复杂参数就是把你当前流的字段原样打进内存结果集里。我一般这样用“表输入”查出一批数据比如要同步的订单列表。连一个“复制到结果集”这一步什么都不用改默认会把所有字段都带进结果集。保存这个转换然后在作业里把它作为第一个作业项运行。在作业里再放一个转换作为第二个作业项第一个步骤用“从结果集获取记录”把之前的数据读回来。这中间有几点要注意。第一“从结果集获取记录”读取数据后它会一次性把结果集里的数据全部输出为行流这些行会进入下一步骤比如写入数据库、生成JSON文件等。第二结果集在作业中只能被消费一次。也就是说如果两个转换都要读这个结果集第二个转换可能读到空数据。因为消费过就没了。解决办法是如果需要多份拷贝在写入结果集之前先做一个“克隆行”或者让它输出到多个“复制到结果集”内存副本方式不同——不过说实话这种做法比较绕不如直接重新查询一次。Kettle的处理结果是在内存中的如果结果集特别大比如几十万行建议你用“表输出”或“SQL脚本”直接落地而不是全部塞进结果集再让另一个转换来拿。我见过有同事把100万行数据塞进结果集结果作业直接内存溢出Kettle卡死最后只能重启。结果集适合存小批量的临时数据比如文件清单、待处理ID列表、刚刚生成的批量单据号这种不适合当数据库用。2.3 结果集在作业里和变量的配合结果集和变量不是互斥的实际项目中经常混着用。比如这样一套组合拳先跑一个转换查询出所有今天需要同步的客户ID列表每一行都写入结果集。同时在转换里用“Set Variables”把这批数据的规模大小写入一个变量比如TOTAL_COUNT。然后作业进入“循环”环节通过${TOTAL_COUNT}控制循环次数每次循环启动一个转换转换第一步从结果集读一行数据再用这一行的客户ID作为查询条件去拉取明细数据。这种“变量控制循环次数结果集提供每轮数据”的方案在Kettle里非常实用。你要做的就是在“作业-循环”里设置最大循环次数为变量TOTAL_COUNT然后在循环体里启动处理转换。这里有个经验从结果集读取数据时读出来的字段名和写进去时保持一致。如果你在第一个转换里对字段做了别名调整比如把CUSTOMER_NO改名为CUST_ID那第二个转换用“从结果集获取记录”时字段名就会变成CUST_ID。建议在结果集的两端保持字段名统一或者明确记住别名免得后面字段找不到。2.4 结果集相关的“坑位”盘点第一个坑位是“结果集被过早清空”。Kettle的作业里如果前一个转换输出到结果集后你又对该转换开启了“过期处理”或者设置了“清除结果集”那结果集可能跑完就被清掉了。检查一下作业项属性里有没有勾选类似“Clear result set after execution”的选项如果有摘掉。第二个坑位是“多个分支同时读结果集”。前面提到结果集只能消费一次如果在作业里画了两条线都从第一个转换分支出去两个下游转换同时读结果集其中一个可能读到空。这种并发读取的场景Kettle处理得并不好。我的做法是如果确实需要多份数据就在第一个转换里把数据同时写进一个临时表Table Output下游各转换都去查这个临时表反而更稳。第三个坑位是“结果集字段丢失或类型变化”。有时候“从结果集获取记录”读出来的字段类型会变得很奇怪比如你写入时是字符串结果读出来变成了长整数。这跟Kettle对元数据推断的机制有关。如果类型不对方便的做法是在“从结果集获取记录”后面加一个“字段选择”步骤手动指定类型和长度让它强制转换。3. JSON处理解析、提取、再输出现在做数据处理完全绕不开JSON。上游系统丢给你一段JSON里面有数组、有嵌套对象你得把需要的内容取出来或者反过来你要把数据库查出来的多行记录拼成一个JSON数组发送给下游接口。这两件事Kettle都能干但组件和写法有讲究。3.1 Kettle处理JSON的组件选型Kettle里和JSON相关的组件不算少我按用途帮你筛一遍JSON Input或旧版的Get data from JSON用来读取JSON流把里面的字段解析成输出行。核心配置是“Field”里的JSONPath路径。JSON Output把输入流的行写入JSON文件或字段。可以生成数组形式的JSON也可以生成对象形式的JSON。JSON Fields JSON Output Value这两个经常配合先用JSON Fields定义好字段映射再用JSON Output Value把流数据拼成一个JSON字符串。REST Client用于调用HTTP接口返回的响应体通常是JSON字符串。REST Client输出一个字段里面是完整的JSON原文。HTTP Post / HTTP Client老牌的接口调用组件也能配合JSON使用但做复杂鉴权和Header配置不如REST Client直观。以我的习惯处理外部接口返回的JSON时标准流程是REST Client拉取接口 → 拿到响应体的JSON字符串 → 用“JSON Input”解析 → 提取需要的字段 → 再决定是写库、写文件、还是用“Set Variables”变成变量。如果是自己生成JSON返回给下游标准流程是“表输入”查出多行数据 → 对字段做清洗和类型转换 → 用“JSON Output”把行流写成JSON数组文件或者把JSON字符串放入某个字段交给REST Client发送出去。3.2 动态参数拼JSON怎么把查询结果“塞”进请求体接口对接时最常见的需求是我先查一下数据库拿到几个参数然后把这些参数拼成一个JSON请求体POST给某个系统再把返回结果解析后写回库。这里的关键在于“拼JSON”这一步。我通常有两种做法。做法一用“JSON Output”步骤自动生成。把“表输入”查出来的字段直接连到“JSON Output”它会自动生成一个数组结构的JSON每个输入行对应数组里的一个对象。如果接口要求的JSON不是数组而是一个单对象你可以先在“字段选择”里把行数控制为一行或者用“聚合记录”把多行聚合成一组再输出。做法二手工拼接JSON字符串。如果你不想引入复杂的JSON构建步骤可以用“增加常量”或者“字符串操作”步骤把字段值拼进一个模板字符串里。比如SQL里查出来的用户ID叫USER_ID账户ID叫ACCOUNT_ID你可以用“增加常量”步骤写一个字段JSON_BODY值设为{userId:${USER_ID},accountId:${ACCOUNT_ID}}。这里的${USER_ID}会在运行时被替换成当前行的实际值。有人可能会问手工拼JSON字符串会不会很脆弱确实如果字段值里含有双引号、反斜杠等特殊字符拼出来就是非法JSON。所以我更推荐能用JSON Output就用JSON Output少手工拼。只有那些结构非常简单、确定不含特殊字符的请求体才用手工拼接。3.3 JSONPath写不对什么都白搭JSON Input里最核心的配置项是JSONPath路径。它就相当于JSON世界的XPath用来定位你要取的字段。我见过太多人在这里卡住其实搞懂几个基本概念就够了。$表示根节点。$.store.book表示根节点下的store节点的book属性。.表示子节点连接符。$.order.id表示根节点下order对象里的id字段。[]表示数组索引。$.data.list[0]表示data下list数组的第一个元素。[*]表示遍历数组所有元素。在JSON Input里如果你要解析一个数组并输出多行记录用[*]或者直接写数组字段的路径它会自动展开数组一行对应一个元素。举个例子接口返回的JSON是{ code: 0, message: success, data: [ {orderId: 1001, amount: 200}, {orderId: 1002, amount: 300} ] }如果你要取出订单ID列表在JSON Input里字段设置可以这样写字段名orderIdJSONPath$.data[*].orderId类型String。运行后会自动输出两行每行一个orderId。如果JSONPath写成$.data输出两行但每行都拿到整个数组对象——这不是我们想要的。这里有一个小细节Kettle的JSON Input执行JSONPath时输出行数是由路径匹配到的节点数量决定的。如果你的JSONPath路径匹配到两个节点那就会输出两行。如果你只写一个具体的索引$.data[0].orderId那就只输出一行。用好这个特性可以在一个JSON Input里同时取多个不同层级的字段非常灵活。3.4 JSON特殊字符与编码问题JSON处理还有一个绕不开的坑编码。我遇到最多的情况是REST Client拉回来的接口返回的是UTF-8编码的JSON但里面包含中文解析出来之后在预览里看着正常一旦写入文件或者数据库变成乱码。排查思路是先确认REST Client的返回值字段类型是不是String再确认后面接的“JSON Input”有没有设置正确的编码。如果你是把JSON字符串写入文件建议在“文本文件输出”里把编码设为UTF-8并且在文件头部写上BOM如果接收方要求的话。还有一个坑是JSON里的\n、\t这类转义字符。接口返回的JSON原文中如果某字段的值是带换行的长文本那在JSONInput解析后换行符会被解析成标准换行后面写库时会不会把字段值截断要看目标库的处理方式。稳妥做法是在解析后再加一个“字符串操作”步骤把换行符替换为空格或转义标签防止下游系统出幺蛾子。4. 三者联动查询→变量→结果集→JSON的完整链路前文把变量、结果集、JSON分开讲了实际项目里它们经常是同一条链路上的不同环节。我拿两个我曾经做过的任务当例子把完整链路串一遍你就会对“什么时候用变量、什么时候用结果集、什么时候用JSON”有更直观的判断。4.1 场景一动态增量抽取接口数据假设你要每天从一个数据接口同步订单数据但接口要求传入“开始时间”和“结束时间”并且每次查询结果最多返回500条超出部分需要翻页。你的做法是第一步写一个转换A用“表输入”查询源库中订单表的最大更新时间MAX_UPDATE_TIME把结果值用“Set Variables”写入变量LAST_SYNC_TIME作用域设为“Root Job”。第二步回到作业中以转换A作为第一个作业项。接着用“循环”控制翻页逻辑每轮用“表输入”或“REST Client”去请求接口URL或请求体中的开始时间变量就是${LAST_SYNC_TIME}结束时间变量是${CURRENT_TIME}。请求返回的JSON用“JSON Input”解析出订单明细。第三步把解析后的明细行通过“表输出”写入临时表或者直接更新到正式表。最后在作业的最后一个作业项里更新同步时间把最新的MAX_UPDATE_TIME写回元数据表。这条链路的精妙之处在于变量承担了“跨步骤传递最后一个时间点”的功能结果集虽然没有显式出现但它承载了订单明细在转换内部的正常流式传输JSON则是外部接口和Kettle之间的数据交换格式。三者互相配合没有谁可以缺。4.2 场景二多表汇总后再输出JSON文件另一个常见场景要从多张表里汇总数据最后生成一个标准的JSON文件交给下游系统。我的做法是在转换A里分别用多个“表输入”查多张表的数据然后用“记录集连接”按照关联字段做左连接或内部连接得到一个汇总后的宽表。之后用“JSON Output”把宽表输出为标准JSON数组文件。这一步的字段映射可以在JSON Output的配置里手工指定也可以自动获取所有输入字段。如果下游要求的JSON结构比宽表复杂比如要求按部门分组每组下面是员工列表那么仅靠JSON Output直接输出是做不到的因为JSON Output只能生成“行→数组元素”这种扁平的JSON。解决办法是先用“排序行”和“分组”把数据按部门分组再用“JavaScript代码”步骤或“JSON构建器”拼复杂的嵌套结构。不过这个场景已经比较进阶了大部分需求用扁平JSON就能满足。4.3 链路中各环节的选择建议用多了之后我自己总结了一个简单的选择标准需要传递一个值后续大量步骤要引用用变量。需要传递一批行但数据量不大且只在作业内使用一次用结果集。需要和外部系统交换数据或者需要保存成通用文件格式用JSON。数据量大多步骤多转换都要反复查询同一批数据别塞结果集写临时表用变量传表名。这套标准不是什么金科玉律但帮我少踩了很多坑。尤其是在Kettle里最怕的就是“用一种方案硬扛”比如明明该写临时表的数据非要塞结果集或者明明该用变量的值非要每次重新查一遍库导致性能崩盘。5. 常见坑位与排查技巧最后这部分是压箱底的东西。下面这几个问题都是我实际运行Kettle作业时踩过的或者帮别人排查时遇到的高频问题。我按“现象-原因-解法”的方式整理成表方便你直接对照。现象可能原因排查/解决方法查询结果已经出来了但Set Variables步骤之后的表输入里${}没被替换变量作用域设置不对或步骤里没有启用变量替换开关检查Set Variables的作用域是否选到根作业/父作业检查表输入的连接属性里是否勾选了变量替换选项作业里两个转换之间传变量第二个转换读出来是空第一个转换中Set Variables作用域默认是Transformation将作用域改为Root Job或Parent Job优先Root Job结果集在第二个转换里读取时没有数据结果集已经被消费过一次或该转换被并发打开同时读取确认结果集只被消费一次多份需求时改查临时表JSON Input解析出来的行数不匹配预期JSONPath路径写错或数组层级没理解对在Kettle里先复制JSON样例到JSONPath在线测试工具里验证路径再回填到JSON InputREST Client返回的JSON有中文乱码编码设置不正确REST Client响应字段编码改为UTF-8后续文本文件输出或写库时同样设UTF-8手工拼接JSON后接口报非法JSON字段值里有双引号/反斜杠或者空格没处理改用JSON Output或“JSON Fields”组件自动构建JSON必须手工拼时对非法字符做替换SQL里的变量没生效原样字符串进了数据库变量未定义或者表输入的SQL写在了一个不支持变量替换的步骤确认变量名拼写确认是在表输入步骤写的SQL测试一下在SQL里写死值能否运行转换运行太快后面的步骤还没等到变量就执行了转换内步骤并行执行执行顺序不受连线严格约束把“设置变量”和“使用变量”拆到两个转换用作业串行控制或用“阻塞直到步骤完成”步骤强制等待排查的综合思路就一句话先用Preview看每一步的实际输出再决定怀疑对象。Kettle的“表输入”有预览功能“JSON Input”也有预览功能很多问题你用预览跑一遍一眼就能看出是路径错了还是数据源错了根本不需要瞎猜。再多说一句调试技巧把作业日志调成Basic级别甚至Detailed级别跑的时候盯日志。Kettle的日志虽然啰嗦但里面会把“字段值”“转换行数”“错误原因”都打出来。尤其是JSON解析错误日志里通常会直接告诉你第几行第几列处理失败这对我们排查问题非常关键。我做Kettle开发这些年最大的体悟是工具再灵活思路得清晰。变量、结果集、JSON这三样东西本质上都是“数据在不同环节之间流转的载体”。你只要想清楚每个环节的数据形态是什么下一环节需要的又是什么然后用对应的载体去衔接就不会出大问题。反过来如果只是照抄网上的片段今天用变量传数组明天用JSON拼SQL那只会越搞越乱。希望这篇文章能帮你把这条链路彻底打通遇到类似需求时能少花点冤枉时间。