Kettle循环字面替换SQL模板字段名

Posted by VonXXGhost on April 17, 2021

本文目录

近日遇到的一个需求,需要对 10+ 张表的所有字段做定期的统计分析,将几张表的统计结果汇总到单独一张汇报表里,并且尽量基于 Kettle 完成。因此,需要在 Kettle 的“表输入”组件中,实现 SQL 模板字面替换变量值的功能,也就是能实现 SELECT ${field} from table where ${field} > 0 这种替换,而并非自动处理成字符串的一般占位符替换: SELECT field FROM table where id = ?,并且这个变量值是可以循环赋值的,一次流程要跑几百上千条这种对应不同字段名模板。

这个需求每一点单独拿出来做其实都很简单的,字面替换可以使用“变量替换”实现、循环赋值可以使用“执行每一条”实现,但结合在一起就不太好搞了,因为 Kettle 的变量作用域是非常大的,没办法直接在一个 klr 里完成 “读取字段集 → 将每一行赋值为变量 → 替换 SQL 模板中的变量值 → 完成 ETL 操作” 这一系列的操作(或者也许有,但我没发现)。查阅了很多资料,大部分人的需求都只是需要替换右值,需要依赖前一步骤的结果集进行字段名本身的替换的非常少,Kettle 也不算使用热度非常高的软件,因此搜了很久也没搜到想要的答案。

平心而论,如果不是因为还有其他因素的存在,在经过这样的大致了解之后,我是倾向于自己编码实现 ETL 过程而非在这工具上一直耗下去的。不过总归觉得本软件的完成度还是不算低的,没理由这么简单的功能都实现不了,于是继续试错了一段时间,总算研究出了实现的方法……试错的过程就不提了,直接说下实现方法。

需要注意的是,我从第一次接触软件到研究出实现方法前后间隔不超过 8 小时,所以可能是有更简单的方法只是我没搜到资料而已。以下提到的一些问题点也可能并非真正的问题点,时间有限我也没太过去细究。

实现

TL;DR

将赋值过程拆分成“字段名输出”和“SQL模板替换执行”两个 ktr 转换,在 kjb 作业层串联起来,通过结果记录传递 + “设置变量”的方式达到串行循环赋值变量的效果,进而可以正常地对 SQL 模板进行变量的字面替换。

作业的整体结构

image-20210418193814332

image-20210418194047222

在这里我因为要对数值型字段和字符型字段做不同的处理,所以有 4 个转换,两者除了一些细节差异以外整体结构都是相同的。这里的设置关键点是 ETL 步骤需要勾选“执行每一个输入行”。

需要注意的一点是,因为变量的作用域广度问题,这个作业的两个 select 部分不能都连在 Start 节点上并行执行,会出现变量并发问题,需要串行流程,等上一类的任务跑完再跑下一类。

字段名输出

整体:

image-20210418195701767

获取字段列表:

image-20210418195750404

因为我的数据源是 Impala,所以可以直接用 SHOW COLUMN STATS 拿到字段集。这里的 table_name 是全局的命名参数,非动态的(作业层可以修改),也无需多说了。不同的数据库使用不同的 SQL 即可,差不了太多。再不济,也可以直接定义一个常量列表在这里。

image-20210418200203094

“分流选择”定义了一个新字段,根据上一步拿到的字段结果进行对应的赋值,顺便把 Column 重命名了一下(没太大意义,之前试错留下来的结果)。后面的“过滤记录”判断 sql_type ,这两步其实应该也可以通过 Switch / Case 组件实现。

其他都无需多言,关键是最后的“复制记录到结果”,将前面处理过的结果集在作业层中传递给下一步转换。

SQL变量赋值

image-20210418201114228

image-20210418201223804

承上启下,首先自然是“从结果获取记录”,然后把字段值设为环境变量。到这里,我们便实现了将前一个结果集无冲突地循环赋值变量这一根本目的。之后的事就非常简单了。

image-20210418201436839

示例,实际上我要跑的 SQL 比这个还要多一些东西

SQL 的表输入开始执行真正的 ETL 步骤,关键点是要勾上“替换 SQL 语句里的变量”,把前面设置的环境变量替换进来。此外,这里的“从步骤插入数据”是不需要配置的,因为对于这个转换步骤本身来说,它的输入值只有一行数据,循环执行是在父层的作业层实现的。

最后一步数据写入常规操作也不必多说了。

整个流程就是这样子,还是相当简单的,不然我也没法一天内就研究出来。而这套流程的缺陷也是很明显的——完全串行执行,失去了自带的并行执行的优点。但是因为作业本身是可以并行跑的,因此可以通过拆分字段输出步骤的逻辑同样地来达到并行执行的效果,不算太麻烦不过总归本无必要。

个人对 Kettle 的接触时间与经验都非常少,以上仅供参考。