如何用 esProc 实现 Oracle 和 MySQL 的混合运算

逻辑数仓可以实现多源混算，但需要配置视图、预处理数据，结构太沉重。duckdb 是轻量级的方案，但没有内置 Oracle 的 connector，自己开发难度又太高。同为轻量级方案，esProc 支持 JDBC 公共接口，可以实现任何 RDB 之间的混算。

先下载并安装 esProc，推荐标准版：免费下载

把数据库的 JDBC 放到目录 "[安装目录]\common\jdbc"，这是 esProc 的类路径之一。下面是 mysql 和 Oracle 的 JDBC：

打开 esProc IDE，找到菜单 "Tool->Connect to Data Source"，新建 JDBC 数据源，填入 MySQL 和 Oracle 的连接信息。

返回到数据源界面，连接刚才配置的 2 个数据源，如果数据源名变成粉色，说明配置成功。

在 IDE 中新建脚本，写 SPL 语句，连接 oracle，通过简单 SQL 加载数据：

=connect("orcl_21c").query@x("select * from ALifeSurvey")

按 ctrl-F9 执行，可以在 IDE 右边看到执行结果，以数据表的形式呈现，这对调试 SPL 代码很方便。

如果数据可以正常加载，就可以写正式的跨库混算代码了：

	A	B
1	=connect("orcl_21c").query@x("select telNo,age,marital from ALifeSurvey")	=connect("mysql").query@x("select telNo,price from AVehicleSurvey where purchase_year>=? and purchase_year<=?",arg1,arg2)
2	=join(A1:L, long(TELNO); B1:V, telNo)
3	=A2.groups(L.AGE, L.MARITAL; avg(V.price):avgPrice)

用参数过滤 MySQL 的车险调查表，再通过电话号码与 Oracle 的寿险调查表进行内关联，按寿险调查表的年龄、婚姻状态分组，统计该组人群在车险调查表里的平均购车价格。

把上面脚本保存在某个目录中，比如 D:\data\SurveyCross.splx，运行后可以看到结果：

上面代码把数据取到内存后再关联，适合数据量较小的情况，如果数据量较大，应该在 SQL 里排序，再一边取数一边进行关联计算，也就是归并关联。代码这样写：

	A	B
1	=now()
2	=connect("orcl_21c").cursor@x("select telNo,age,marital from ALifeSurveyB order by telNo")	=connect("mysql").cursor@x("select telNo,price from AVehicleSurveyB order by telNo")
3	=joinx(A2:L, long(TELNO); B2:V, telNo)
4	=A3.groups(L.AGE, L.MARITAL; avg(V.price):avgPrice)
5	=output("time cost(seconds):",interval@s(A1,now()))

代码变化不大，取数时用游标函数 cursor，关联时用归并关联函数 joinx，A1 和 A5 加了时间统计。

右下角日志窗口打印了执行时间，可以看到，对于两个数据量一千万以上的表，跨库混合运算大约在 100 秒左右。

在 IDE 中调试通过后，下面配置 Java 应用环境。
从目录 "[安装目录]\esProc\lib" 下找到 esProc JDBC 相关的 jar 包：esproc-bin-xxxx.jar、icu4j_60.3.jar。

将这两个 jar 包和数据库的 JDBC 部署到 Java 开发环境的类路径下。
再从目录 "[安装目录]\esProc\config" 下找到 esProc 配置文件 raqsoftConfig.xml，同样部署到 Java 开发环境的类路径下。

配置文件中要改的配置项是 mainPath，这表示脚本等文件的默认路径。注意数据源的信息也在配置文件中。
接下来，就可以编写 Java 代码，通过 esProc JDBC 执行 SPL 脚本了：

Class.forName("com.esproc.jdbc.InternalDriver");
Connection con= DriverManager.getConnection("jdbc:esproc:local://");
PreparedStatement st = con.prepareCall("call SurveyCross(?,?)");
st.setInt(1,10);
st.setInt(2,20);
ResultSet rs = st.executeQuery();

运行后可以看到结果：

可以看到，esProc JDBC 调用 SPL 脚本过程和数据库 JDBC 调用存储过程一样。
SPL 脚本不是必须的，可以把 SPL 脚本转为 SPL 代码，像 SQL 那样嵌入 Java。先在 IDE 中打开脚本文件，选中有代码的单元格 A1-B3，再点击菜单 "Edit->Copy->Code copy"，这样就把多行多列的网格代码转成了单行的 SPL 代码，暂存在粘贴板里。

将转换后的 SPL 代码复制到 Java 代码中，将参数名替换成问号并执行，

PreparedStatement st = con.prepareStatement("==connect(\"orcl_21c\").query@x(\"select telNo,age,marital from ALifeSurvey\")\t=connect(\"mysql\").query@x(\"select telNo,price from AVehicleSurvey where purchase_year>=? and purchase_year<=?\",?,?)\n=join(A1:L, long(TELNO); B1:V, telNo)\t\n=A2.groups(L.AGE, L.MARITAL; avg(V.price):avgPrice)\t");
st.setInt(1,5);
st.setInt(2,10);
ResultSet rs = st.executeQuery();

可以看到，Java 调用 SPL 代码的过程和调用 SQL 代码一样。运行后可以看到结果：

esProc 支持的数据源非常丰富，除了 RDB，还有 NoSQL、BigData、云存储、消息队列、本地文件，都可以混合运算，感兴趣的可以去乾学院了解更多。