"问题描述系统结构前端应用是 BI 系统，主要实现条件过滤查询，后台是某 MPP 数据库。前端根据用户在页面上的选择、操作来生成针对宽表 ordes（订单表）的 SQL 提交给后台，并展现后 .."

terminator 北京
陈虎。真心想用集算器这样的工具降低程序员们的劳动强度，真心想成为高性能复杂计算、大数据计算编程难题的终结者。
2 回帖 • 594 浏览 • 11 个月前

SPL 实践：计算路由提升并发能力

计算＆AI

集算器(484)

问题描述

系统结构

前端应用是 BI 系统，主要实现条件过滤查询，后台是某 MPP 数据库。前端根据用户在页面上的选择、操作来生成针对宽表 ordes（订单表）的 SQL 提交给后台，并展现后台返回的结果。

SQL 大致是这样的：

select top 1000 ORDERID,CUSTOMERNAME,EMPLOYEEID,ORDERDATE,AMOUNT 
    from orders 
    where ORDERDATE=date('2023-11-22') and AMOUNT>100 
    and (CUSTOMERNAME='GIUPK' or EMPLOYEEID=87041)

where 条件中，日期字段是必选的（每次只查询一天），其他字段和过滤条件可以自由组合，每次查询返回 1000 条记录。

数据结构与规模

订单表 orders 数据结构：

字段名称	字段类型	字段注释	示例数据
ORDERID	String	订单号	10777
CUSTOMERNAME	String	客户名	ERNSH
EMPLOYEEID	String	员工号	7
ORDERDATE	Date	日期	2023-10-16
AMOUNT	Number	金额	96.5
…

orders 订单表中存储到目前为止多年的订单数据，每日数据量三千万条。

环境和期望

由于后台数据库还承担跑批等其他计算任务，负载很重，因此仅能给前端提供 5 个连接。而前端有上百个用户使用，集中访问时响应速度非常慢。

统计发现前端 90% 以上的请求是针对热点数据的，也就是最近三天的数据。

期望实现：将热点数据放到数据库以外（比如前端应用本地），根据日期判断针对最近三天的查询，则访问热点数据；否则就仍然访问数据库。

问题分析

解决这个问题常见的办法是增加前置数据库。这样做存放热点数据没有问题，但是计算上却面临很大的困难。

前端提交查询请求时，需要根据条件判断数据应该来自前置库，还是后台库。这种计算实际上是数据的路径选择，可以称为计算路由。

数据库的计算能力是封闭的，只能计算库内的数据，很难实施计算路由规则、查询转发和结果整合等。

前端 BI 系统是不能修改的套装软件，无法在前端根据查询条件自动选择不同的数据库。除非由使用者人工选择，但这会导致用户体验恶劣。

可编程计算路由

实际应用中，计算路由的规则可能会很复杂和多变，通过配置来实现会非常困难，用编程的方式实现是最佳方案。

SPL 是专门针对结构化、半结构化数据计算设计的，编程更高效。可以很简洁的进行判断、分支、循环等流程控制，且支持代码热切换，用来实现复杂的计算路由规则是最简单和最高效的。

统一接口

在用 JDBC 模式调用集算器时，可以配置一个 SPL 脚本文件作为JDBC 网关，在 JDBC 中执行的所有 SQL 语句都交由这个脚本处理执行。这样，前端提交的 SQL 语句有了统一的入口，可以很方便的将计算路由规则，写在这个 SPL 脚本中。或者写在其他脚本中，由这个脚本调用。

如果前端提交的并非 SQL 语句，那么 SPL 也支持 JDBC 存储过程或者 restful 方式，直接调用 SPL 脚本。

SQL 解析和翻译

SPL 提供了开放的计算能力，很容易利用SQL 解析函数对前端传入的 SQL 语句进行分拆，得到其中 WHERE 子句中的日期参数，识别出该查询涉及的数据范围是哪些。

不同类型的数据库，会存在一些方言。计算路由的实现离不开对 SQL 语句的翻译。

SPL 提供了 SQL 翻译函数，把前端提交的标准 SQL 翻译成对应数据库的 SQL。

实践过程

后台数据库准备

执行 orders.sql 将样例数据导入到 Oracle 数据库中。

点击下载 orders.sql

前端应用准备

前端用 tomcat 服务器中的 jsp 来模拟 BI、报表或者其他查询系统。

请准备 tomcat 服务器，在其中部署 SPL 的 jdbc 驱动，具体方法参见 SPL 教程。

原始数据准备

实际应用中，每天的数据是由调度系统从数据库中定时导出文本文件提供给 SPL。为了方便验证，假设当前日期是 2023 年 11 月 24 日。

这里直接生成文本文件：

	A	B	C
1	=[date(2023,11,21),date(2023,11,22),date(2023,11,23)]
2	for A1	=file("orders"/string(A2,"yyyyMMdd")/".txt")
3		=movefile(B2)	=0
4		for 30	=to(1000000).new(C3+~:ORDERID,rands("ABCDEFGHIJKLMNOPQRSTUVWXYZ",5):CUSTOMERNAME,rand(100000):EMPLOYEEID,A2:ORDERDATE,rand()*10000:AMOUNT)
5			>B2.export@at(C4)
6			>C3+=1000000

这段代码生成三个文本文件：

orders20231121.txt

orders20231122.txt

orders20231123.txt

每个文本文件包含一天的订单数据，3000 万条。

ETL 过程

文本文件占用磁盘空间大，性能较差，我们可以将本地文件存成 SPL 集文件格式。

系统上线准备阶段，需要从数据库中导出三天的热点订单数据到文本文件中，然后转换为 SPL 集文件。

上线后，用调度工具每天晚间从数据库中导出当天的订单数据到文本文件中，再调用 SPL 的 ETL 代码，转换为 SPL 组表。

ETL 代码 etl.splx 代码大致是下面这样，输入参数是订单日期 orderdate：

	A
1	=file("orders"/string(orderdate,"yyyyMMdd")/".txt")
2	=A1.cursor@t()
3	=file("orders"/string(orderdate,"yyyyMMdd")/".btx")
4	=A3.export@b(A2)

调度工具可以用命令行的方式调用 etl.splx。windows 命令行方式大致是下面这样：

C:\Progra~1\raqsoft\esProc\bin\esprocx etl.splx 2023-11-22

esprocx.exe 是 SPL 命令行执行程序（linux 版本中是 esprocx.sh）。

etl.splx 是要执行的 SPL 脚本文件，2023-11-22 是传入参数。

前端网页代码

我们采用 jsp 来模拟前端应用：

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ page import="java.sql.*" %>

<body>
<%
String driver = "com.esproc.jdbc.InternalDriver";
String url = "jdbc:esproc:local://";
try {
Class.forName(driver);
Connection conn = DriverManager.getConnection(url);
Statement statement = conn.createStatement();

String sql="select top 1000 ORDERID,CUSTOMERNAME,EMPLOYEEID,ORDERDATE,AMOUNT from orders where ORDERDATE=date('2023-11-22') and AMOUNT>100 and (CUSTOMERNAME='GDPAL' or EMPLOYEEID=47811)";

out.println("订单ID"+"\t"+"客户姓名"+"\t"+"雇员ID"+"\t"+"订购日期"+"\t"+"订单金额"+"<br>");
ResultSet rs = statement.executeQuery(sql);
int f1,f6;
String f2,f3,f4;
float f5;
while (rs.next()) {
f1 = rs.getInt("ORDERID");
f2 = rs.getString("CUSTOMERNAME");
f3 = rs.getString("EMPLOYEEID");
f4 = rs.getString("ORDERDATE");
f5 = rs.getFloat("AMOUNT");
out.println(f1+"\t"+f2+"\t"+f3+"\t"+f4+"\t"+f5+"\t"+"<br>");
}
out.println("</pre>");

rs.close();
conn.close();
} catch (ClassNotFoundException e) {
System.out.println("Sorry,can`t find the Driver!");
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
%>
</body>

前端配置

前端应用 tomcat 中的 raqsoftConfig.xml 中有下面这样的配置：

<JDBC>

<load>Runtime,Server</load>

<gateway>gateway.splx</gateway>

</JDBC>

配置后，在用 Java 调用集算器 JDBC 时，所执行的 SQL 都将交由网关脚本 gateway.splx 处理。

注意 gateway.splx 要放到 raqsoftConfig.xml 中配置的主目录中，详见教程。

网关脚本

网关脚本gateway.splx 传入参数必须包含 sql 和 args，代码大致是这样的：

	A	B	C
1	=today=date(2023,11,24)	=sql.sqlparse@w().split(" ")
2	=B1.select@1(like(~,"ORDERDATE=date('????-??-??')"))
3	=date(mid(right(A2,14),3,10))	=today-A3
4	if B3<=3	=connect()	=replace(sql,"from orders","from orders"/string(A3,"yyyyMMdd")/".btx")
5		=B4.cursor@x(C4)	return B5
6	else	=connect("orcl")	=sql.sqltranslate("ORACLE")
7		=B6.cursor@x(C6)	return B7

A1 假定当天是 2023,11,24，实际应用中需要改为 =today=now()

B1 用SQL 解析函数得到 arg_sql 中的 where 条件部分。

A2、A3 得到查询的日期。B3 求当前日期和查询日期相差几天。

A4 判断日期差，实现计算路由。

如果差三天以内，则执行 B4 到 C5，从本地 btx 中查询取数且返回结果。

如果差三天以上，则执行 B6 到 C7，从后台数据库查询取数且返回结果。其中 C6 使用SQL 翻译函数，将 sql 中的标准 SQL 语句翻译成 Oracle 的 SQL 语句。

实践效果

用 SPL 实现上述计算路由代码非常简单，调试也很方便。从开始编程到测试结束仅用一周时间。SPL 本身非常轻，安装、配置简单易学，系统上线仅用一天。

系统上线后，热点数据放到前端应用本地存储，90% 的查询请求不再受后台数据库 5 个连接数的限制，上百用户访问毫无压力。只有 10% 的请求转发给后台，后台数据库压力也有所降低。

后记

实际业务中的计算路由规则复杂多样，比如：

本地数据库存放热点数据、后台数据库存放冷数据；

后台有两个数据库，一个存放历史数据，另一个存放当期数据；

前端 BI、报表或查询应用要连接多个应用的不同数据库，还可能有多库的混合计算；

…

计算路由的规则还会常常发生变化，比如：

仅将最近三天数据作为热点放到应用本地还不是最佳方案。希望改成最近 1 天的数据常驻内存，最近 2 到 4 天的存储在本地磁盘，最近一个月的存储在独立的 SPL 服务器上，其他数据还存放在后台数据库中。

这些情况下采用 SPL 实现计算路由，可以快速的实现各种各样复杂的规则，以及规则的频繁的变化。

特别是 SPL 脚本支持热切换，脚本修改、部署后不用停机重启服务即可生效。

集算器(484)