SPL:被 Kettle 集成
目标
集算器能直接针对数据文件写SQL查询,也能写SPL脚本支持复杂计算,为了容易给外部程序提供计算结果,提供了嵌入式JDBC驱动。本篇介绍Kettle中通用JDBC集成SPL的方法,及不同的取数方式。
集成
下载Pentaho安装包,安装后其下的design-tools\data-integration\Spoon.bat就是Kettle启动程序。启动前,先把集算器程序esproc-*.jar放入design-tools\data-integration\lib\目录。
新建一个名为TEST的Transformation,在TEST下新建一个名称为esproc的Database connection,连接类型为Generic database,连结方式为Native(JDBC),集算器的驱动类是com.esproc.jdbc.InternalDriver,URL是jdbc:esproc:local://?config=d:/esProcData/raqsoftConfig.xml,URL中用参数指定了集算器的配置文件raqsoftConfig.xml。
raqsoftConfig.xml中主要看两个配置,主目录mainPath,字符集charSet。
查询文件数据
集算器主目录下有数据文件emp.txt(注意编码格式需是UTF-8,和上面配置一致,否则可能导致查询失败)。
Kettle设计器里,拖拽一个Input分类下的Table input
设置Table input,选择之前创建的esproc连接,SQL语句直接查询emp.txt:
点击下面的Preview,能看到查询数据成功了:
再尝试条件查询、分组查询、多表连接查询,都能成功查得数据:
SELECT * FROM emp.txt where id<5 and hire_date<date('2019-01-01')
SELECT department, sum(salary) FROM emp.txt group by department
SELECT e.*,d.name dept_name FROM emp.txt e join dept.txt d on e.department=d.id
数据文件还支持excel(这时需要java读写excel的一些poi相关的jar包,poi*.jar,可以从集算器lib下拷贝,可能存在不同版本的各种jar,注意剔除,并试验是否兼容)。
SELECT * FROM emp.xlsx
参数查询
查询文件的SQL中也可以使用参数,Table input前增加一个Set variables步骤:
SQL中的参数前后都用两个%标识,同时注意勾选上替换参数的选项:
调用SPL脚本
除了直接查询数据文件,也可以调用写好的SPL脚本文件。这适合复杂计算的情况,把复杂计算封装到SPL脚本文件*.dfx中。在集算器的设计器中编写一个calc.dfx,放到主目录下:
执行call calc(),能查得calc.dfx中A2返回的数据:
使用参数
SPL脚本语句也支持定义(多)参数,定义最大ID(maxid)、最小ID(minid):
Kettle中同样定义maxid,minid两个参数:
如下,Kettle中调用calc.dfx时,动态地传入参数:
多种数据源
在设计器中编写一段SPL脚本,简单示范一个Mysql库表与文件数据表的关联计算,首先定义Mysql数据源:
A1中连接上面定义的Mysql数据源emp;A2加载emp库的dept表;B2关闭A1的数据源;A3加载emp.txt文本数据表;A4把两种来源的表关联在一起;A5从关联数据中取出最终计算结果(雇员姓名、部门名称);A6把结果返回给调用程序。
此时,还不能直接在kettle中调用这个SPL脚本,首先需要把Mysql的JDBC驱动jar(mysql-connector-java-*.jar)放入design-tools\data-integration\lib\,再编辑集算器配置文件raqsoftConfig.xml,加入emp数据源的定义:
之后在Kettle中调用,就能执行成功了:
SPL脚本中支持的数据源比较丰富,主要有三大类:
JDBC/ODBC连接的各种关系数据库,Oracle/Mysql/DB2/sqlserver…;
文本数据,txt、excel、xml、csv、json…;
hadoop、mongodb、hbase、spark…等一些非关系数据库,通过内置的扩展程序读取这些外部库数据。参考下面读取mongodb的脚本。
结语
SPL脚本是一个JAVA实现的计算引擎,容易嵌入任何JAVA环境。上面制作的Kettle任务文件如果要部署到tomcat、weblogic等WEB服务上时,涉及的SPL相关jar、配置文件、脚本文件也都带过去就可以了(jar放入WEB-INF/lib;配置文件、脚本文件放入服务器规划的目录,并正确配置主目录)。