用计算路由的方法优化 BI 后台性能
问题的提出
BI 系统的常见结构是：前端是 BI 应用，负责多维分析的用户操作和结果呈现；后台是数据库 / 数据仓库，负责数据计算和存储。前端和后台之间用 SQL 作为接口。
实际应用中，常常出现后台数据仓库压力过重的问题。问题表现为前端响应时间过长，数据仓库反应速度变慢。
常见的解决方案是在数据仓库和应用之间再增加一个前置数据库。但是前置数据库和后台数据仓库之间很难实现数据的路由和混合计算，例如：访问频次很高的热点数据放在前置数据库，大量冷数据放在数据仓库中，查询时按照一定规则来决定访问前置数据库还是后台数据仓库。而如果前置数据库和后台数据仓库是不同的产品，还要考虑 SQL 的翻译问题。
解决思路与过程
作为数据计算中间件（DCM），构建独立的数据前置层是集算器的重要应用模式。数据前置层将 BI 系统重构为三层结构：数据存储及批量数据计算层由数据库承担；数据前置及缓存层由集算器承担；数据分析展现层由多维分析工具或者报表工具承担。
集算器可以脱离数据库进行数据缓存和独立的复杂计算，同时具备可编程网关机制，可以在缓存计算和 SQL 透传之间自由切换。利用集算器完成前置层数据计算，可以与数据库承担的批量数据计算任务分离，并且不必再建设另外一个数据库。
集算器可以将热点数据、近期数据放在数据前置层，从而起到数据缓存的作用，可以有效提高数据计算的速度，减少用户等待时间。
系统架构图如下：
案例场景说明
前台 BI 系统，要针对订单数据做自助查询。查询的必选条件是订购日期。为了简化起见，前台 BI 系统用 tomcat 服务器中的 jdbc.jsp 来模拟。
集算器 JDBC 和智能网关集成在应用系统中。jdbc.jsp 模仿 BI 应用系统，产生符合集算器简单查询规范的 SQL，通过集算器 JDBC 提交给集算器智能网关处理。
数据来自于 ORACLE 数据库 demo 中的 ORDERS 表。ORDERS 订单表是全量数据，集算器只存储最近三年的数据，比如：2015 年 -2018 年。日期以订购日期为准。
基础数据准备与提取缓存数据
用下面的 orders.sql 文件在 ORACLE 数据库中完成 ORDERS 表的建表和数据初始化。
在集算器中，新建一个数据源 orcl，连接 ORACLE 数据库。用 SPL 语言脚本 etl1.dfx 将最近三年的数据预先读取到集算器集文件 orders.btx 中。SPL 脚本如下：
| A | B | |
|---|---|---|
| 1 | =year(now())-3 | |
| 2 | =connect(“orcl”) | =A2.cursor@d(“select * from orders where to_char(orderdate,‘yyyy’)>=?”,A1) |
| 3 | =file(“C:/tomcat6/webapps/gateway/WEB-INF/data/orders.btx”) | |
| 4 | =A3.export@z(B2) | >A2.close() |
从 SPL 脚本可以看出，只要在 A4 单元格中用一句 export 就可以将数据库中的数据导出到文件中。集文件是集算器内置的二进制文件格式，采用了简单压缩机制，相同数据量比数据库的占用空间会更小。@z 选项表示写出可以分段的文件，很适合常常需要并行的多维分析类运算。
B2 单元格中数据库游标的 @d 选项，表示从 ORACLE 数据库中取数的时候将 numeric 型数据转换成 double 型，精度对于金额这样的常见数值完全足够了。如果没有这个选项就会默认转换成 big decimal 型数据，计算性能会受到较大影响。
脚本可以用 windows 或者 linux 命令行的方式执行，结合定时任务，可以定时执行批量任务。windows 命令行的调用方式是：
C:\Program Files\raqsoft\esProc\bin>esprocx.exe C: \etl1.dfx
linux 命令是:
/raqsoft/esProc/bin/esprocx.sh /gateway/etl1.dfx
解决办法一：应用服务器集成计算
集算器 JDBC 智能网关接收到 SQL 后，转给 gateway1.dfx 程序处理。gateway1.dfx 判断是否三年内的查询，如果是，就把表名换成文件名，查本地文件 orders.btx 返回结果。如果不是，把 SQL 转换成 ORACLE 格式，提交数据库处理。
1、下面的 gateway 目录复制到 tomcat 的应用目录。
目录结构如下图：
注意：配置文件在 classes 中。集算器的 Jar 包要放在 lib 目录中（需要哪些 jar 请参照集算器教程）。另外，还需要检查和修改 raqsoftConfig.xml 中的如下配置：
<mainPath>C:\\tomcat6\\webapps\\gateway\\WEB-INF\\dfx\\</mainPath>
<JDBC>
<load>Runtime,Server</load>
<gateway>gateway1.dfx</gateway>
</JDBC>
这里标签的内容就是网关 dfx 文件。在 BI 系统中调用集算器 JDBC 时，所执行的 SQL 都将交由网关文件处理。如果不配置这个标签，JDBC 提交的语句都被集算器当作脚本直接解析运算，而无法实现希望的路由规则。
2、编辑 gateway 目录中的 jdbc.jsp，模拟前台界面提交 sql 展现结果。
<%@ 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 10 ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from ORDERS where ORDERDATE=date('2015-07-18') and AMOUNT>100";
out.println("Data gateway test page v1 <br><br><br><pre>");
out.println("订单ID"+"\\\t"+"客户ID"+"\\\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("CUSTOMERID");
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>
可以看到，jsp 中先连接集算器的 JDBC，然后提交执行 SQL。步骤和一般的数据库完全一样，具有很高的兼容性和通用性。对于 BI 工具来说，虽然是界面操作来连接 JDBC 和提交 SQL，但是基本原理和 jsp 完全一样。
3、打开 dfx 目录中的 gateway1.dfx，观察理解 SPL 代码。
首先，可以看到 gateway1.dfx 传入参数是 sql 和 args，例如传入 SQL：
select top 10 ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from ORDERS where ORDERDATE=date(‘2015-07-18’) and AMOUNT>100。
接下来，可以看到 SPL 脚本如下：
| A | B | C | |
|---|---|---|---|
| 1 | =filename=“C:/tomcat6/webapps/gateway/WEB-INF/data/orders.btx” | ||
| 2 | =sql.sqlparse@w().split(" ") | =A2.select@1(like(~,“ORDERDATE=date(‘????-??-??’)”)) | |
| 3 | =mid(right(B2,14),3,10) | =year(now())-year(date(A3)) | |
| 4 | if B3<=3 | =connect() | =sql=replace(sql,“from ORDERS”,"from "+filename) |
| 5 | =B4.cursor@x(sql) | return B5 | |
| 6 | else | =connect(“orcl”) | =sql=sql.sqltranslate(“ORACLE”) |
| 7 | =B6.cursor@x(sql) | return B7 |
说明：
A1：定义集算器集文件的绝对路径。
A2：解析 SQL，获取 where 子句，并用空格来拆分成序列。
B2、A3：在 A2 序列找到必选条件订购日期，获取日期值。
B3：计算订购日期的年份和当前日期年份相差几年。
A4：判断相差的年份是否超过 3 年。
B4-C5：如果不超过 3 年，就连接文件系统。将 SQL 中的 from 订单，替换成 from 文件名。执行 SQL 得到游标并返回。
B6-C7：如果超过 3 年，就连接数据库。将 SQL 翻译成符合 ORACLE 数据库规范的 SQL, 执行 SQL 得到游标并返回。
4、启动 tomcat，在浏览器中访问 http://localhost:8080/gateway/jdbc.jsp，查看结果。
还可以继续测试如下情况：
(1) 超出三年的查询
sql =“select top 10 ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from ORDERS where ORDERDATE=date(‘2014-07-18’) and AMOUNT>100”;
由于日期 2014 年已经超出三年的限制，所以在 C6 中 SQL 会被翻译成 ORACLE 规范如下：
SELECT * FROM (select ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from ORDERS where ORDERDATE=TO_DATE(‘2014-07-18’,‘YYYY-MM-DD’) and AMOUNT>100)t WHERE ROWNUM<=10
(2) 分组汇总
sql =“select CUSTOMERID,EMPLOYEEID,sum(AMOUNT) 订单总额,count(1) 订单数量 from ORDERS where ORDERDATE=date(‘2015-07-18’) group by CUSTOMERID,EMPLOYEEID”;
(3) 并行查询
sql="select /*+ parallel (4) */
top 10 ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from ORDERS where ORDERDATE=date(‘2015-07-18’) and AMOUNT>100"
和 ORACLE 类似，集算器简单 SQL 也支持 /*+ parallel (4) */ 这样的并行查询。
解决办法二：独立节点服务器计算
第一种解决办法是利用应用服务器的资源。在并发量很大，或者数据量很大的情况下，应用服务器会出现较大压力。这种情况下，推荐用独立的节点服务器进行数据计算。节点服务器可以进行横向扩展，应对大并发或大数据量计算的压力。
集算器 JDBC 智能网关接受到 SQL 后，转给 gateway2.dfx 程序处理。gateway2.dfx 调用节点服务器上的 gatewayServer2.dfx 进行计算。gatewayServer2.dfx 判断是否三年内的查询，如果是，就把表名换成文件名，查本地文件 orders.btx 返回结果。如果不是三年内的查询，把 sql 转换成 ORACLE 格式，提交数据库处理。
1、下面的 gatewayServer 目录复制到需要的目录。集算器的节点服务器具备跨平台的特性，可以运行在任何支持 Java 的操作系统上，部署方法参见集算器教程。这里假设放到 windows 操作系统的 C 盘根目录。
2、修改前面的 dfx，将 A3 改为 =file(“C:/gatewayServer/data/orders.btx”)，另存为 etl2.dfx。修改好的 etl2.dfx 在 c:\gatewayServer 目录。
3、打开应用服务器中的 C:\tomcat6\webapps\gateway\WEB-INF\dfx\gateway2.dfx，观察理解 SPL 代码。参数不变，还是传入的 sql 和 args。
| A | B | |
|---|---|---|
| 1 | =callx(“gatewayServer2.dfx”,[sql];[“127.0.0.1:8281”]) | |
| 2 | return A1.ifn() |
A1：调用节点机上的 gatewayServer2.dfx。参数是 [sql]，中括号表示序列，此时是只有一个成员的序列。[“127.0.0.1:8281”] 是节点机的序列，采用 IP: 端口号的方式。节点机是集群的时候，可以有多个 IP 地址，例如：["IP1:PORT1″,"IP2:PORT2″,“IP3:PORT3”]。
A2：返回 A1 调用的结果。因为调用结果可以是序列，所以要用 ifn 函数找到序列中第一个不为空的成员，就是 SQL 对应的返回结果。
修改 C:\tomcat6\webapps\gateway\WEB-INF\classes\raqsoftConfig.xml 中的如下配置 gateway1.dfx 改为 gateway2.dfx。
<JDBC>
<load>Runtime,Server</load>
<gateway>gateway2.dfx</gateway>
</JDBC>
4、启动节点服务器。
运行 esprocs.exe, 如下图：
点击配置按钮，配置相关参数：
点击确定后，返回主界面，点击启动按钮。
5、打开 C:\gatewayServer\dfx\gatewayServer2.dfx，观察理解 SPL 代码。
| A | B | C | |
|---|---|---|---|
| 1 | =filename=“C:/gatewayServer/data/orders.btx” | ||
| 2 | =sql.sqlparse@w().split(" ") | =A2.select@1(like(~,“ORDERDATE=date(‘????-??-??’)”)) | |
| 3 | =mid(right(B2,14),3,10) | =year(now())-year(date(A3)) | |
| 4 | if B3<=3 | =connect() | =sql=replace(sql,“from ORDERS”,"from "+filename) |
| 5 | =B4.cursor@x(sql) | return B5 | |
| 6 | else | =connect(“orcl”) | =sql=sql.sqltranslate(“ORACLE”) |
| 7 | =B6.cursor@x(sql) | return B7 |
代码基本和前面的 gateway1.dfx 一致。区别是这个 dfx 是在节点服务器 unitServer 上执行的，数据是存在节点服务器上。
5、重启 tomcat，在浏览器中访问 http://localhost:8080/gateway/jdbc.jsp，查看结果。
解决办法三：集算器组表计算
当数据量很大同时又需要秒级的查询速度时，我们建议采用集算器组表来存储数据。组表适用的场合包括：数据表字段有几十个甚至更多；数据量几千万行，存成集文件在 1G 以上；查询要求秒级响应。
对于简单 SQL 来说，组表文件的用法和集文件没有什么不同, 只是文件名不一样。gatewayServer2.dfx 中只需要把 A1 改为 =filename=“C:/gatewayServer/data/orders.ctx”，另存为 gatewayServer3.dfx。相应的 gateway2.dfx 中的 A1 改为 =callx(“gatewayServer3.dfx”,[sql];[“127.0.0.1:8281”])，另存为 gateway3.dfx。
修改 C:\tomcat6\webapps\gateway\WEB-INF\classes\raqsoftConfig.xml 中的如下配置 gateway2.dfx 改为 gateway3.dfx。
<JDBC>
<load>Runtime,Server</load>
<gateway>gateway3.dfx</gateway>
</JDBC>
我们重点理解如何改写 etl 过程，修改前面的 etl2.dfx，另存为 etl3.dfx。
| A | |
|---|---|
| 1 | =year(now())-3 |
| 2 | =connect(“orcl”) |
| 3 | =A2.cursor@d(“select CUSTOMERID,EMPLOYEEID,ORDERDATE,ORDERID,AMOUNT from ORDERS where to_char(ORDERDATE,‘yyyy’)>=? order by CUSTOMERID,EMPLOYEEID,ORDERDATE,ORDERID",A1) |
| 4 | =file(“C:/gatewayServer/data/orders.ctx”) |
| 5 | =A4.create(#CUSTOMERID,#EMPLOYEEID,#ORDERDATE,#ORDERID,AMOUNT) |
| 6 | =A5.append(A3) |
| 7 | >A2.close() |
组表与集文件不同，默认是采用列式存储的，支持任意分段的并行计算，可以有效提升查询速度。同时，生成组表的时候，要注意数据预先排序和合理定义维字段。本例中，按照经常过滤、分组的字段，将维字段确定为：CUSTOMERID,EMPLOYEEID,ORDERDATE,ORDERID。
A3 取得数据的时候，要按照维字段排序。因为 CUSTOMERID,EMPLOYEEID,ORDERDATE 对应的重复数据多，所以放在前面排序；ORDERID 对应的重复数据少，所以放在后面排序。
A4 中定义组表的时候用 #来表示维字段。
需要说明的是，组表也支持并行查询 /*+ parallel (n) */。
多任务性能调优技巧小结
BI 应用的特点是：
1、响应时间要求高，一般不超过 5-10 秒。
2、查询对应数据量在几百兆到几 G 范围，字段有几十个甚至上百个。
3、并发量较大，几十到几百个并发。
性能优化的方法是：
1、采用组表，提高单任务查询的响应速度。
◇ 根据需求，合理定义维字段。
组表定义的时候，要按照业务的需要确定维字段。要选择经常作为过滤条件或者用来分组的字段作为维字段，维字段前用 #标识。
◇ 按照维字段，预先排序。
要按照维字段做好数据的排序，重复记录数多的字段在前面，例如：按照 order by 省，市，县的字段顺序来排序，而不是反过来。
◇ 根据并发量，选择是否用并行查询。
并发量比较大的时候，单个 SQL 查询就不建议用并行查询了 /*+ parallel (n) */。并行查询会消耗更多的线程数，反而会影响大的并发性能。
2、合理配置节点服务器的参数，发挥每个节点的性能。
每台服务器（实体机或者虚拟机）要启动一个节点服务器，每个节点服务器启动分机的配置界面如下：
◇ 根据硬件资源，配置进程数
进程列表中的进程数（也就是适合作业数）建议是不要超过 CPU 总核数 *2/3。例如：服务器有 8 个 CPU 每个两核，总核数是 8*2=16，那么进程数量就不要超过 16*2/3=10 个。最大作业数推荐是适合作业数 *2，也就是 10*2=20 个。
◇ 尽量多分配内存，但要避免超量
节点服务器每个进程的最大内存要尽量多分配，但是总数加起来要比实际的物理内存小，避免操作系统用硬盘来补充内存的不足。例如，总内存是 32G，进程数量是 8 个，那么每个进程的最大内存就不要大于 4G。配置进程的最大最小内存是在 C:\Program Files\raqsoft\esProc\bin\config.txt 中，例如：
jvm_args=-Xms128m -Xmx4845m 最小内存是 128M，最大是 4G。
3、横向扩展节点服务器，多机应对大并发访问。
◇ 横向扩展，应对大并发。
随着并发量的增大，当性能不能满足要求的时候，要增加节点服务器的数量，通过横向扩展来满足需求。
◇ 增加服务器列表配置项。
这时候要修改 gateway3.dfx 中的 callx 函数的服务器序列参数。可以将服务器序列参数写到配置文件中，这样就可以不必每次都修改 dfx 文件了。
4、使用本机硬盘数据进行计算，避免跨网络访问。
硬盘的 IO 速度是比较有保证的。
节点服务器通过网络去取其他服务器上的数据，或者通过访问共享存储上的数据，经常会出现网络阻塞的情况，降低查询响应速度。因此，尽可能每台节点服务器仅仅执行本机上的数据，不要跨网络访问。
集算器优势总结
可编程数据路由
可编程数据路由是数据计算中间件（DCM）的重要应用场景。
在前述的例子中，数据路由的策略是：最近三年的数据作为热数据放路由到集算器中计算，其他数据作为冷数据，路由到数据库中计算。
类似的路由规则还有：最近三天和最近十二个月的最后一天的数据作为热数据，路由到集算器中计算，其他数据路由到数据库汇总计算。
对于冷热数据计算路由规则，本篇只介绍了一次查询只涉及冷或热数据的情况，如果在一次查询中可能同时涉及冷热两种数据，我们将在后续文章中进行介绍。
实际应用中，数据路由的规则可能会很复杂和多变，通过配置来实现会非常困难，用编程的方式实现是最佳方案。采用集算器的编程语言 SPL 来实现复杂的数据路由规则是最简单和最高效的。集算器支持多样性异构数据源的混合计算，可以编程实现涉及到各种异构数据源的复杂数据路由规则。
SQL 解析与翻译
用作多维分析后台时，数据计算中间件（DCM）要提供必要的 SQL 解析与翻译功能。
数据路由的实现离不开集算器对 SQL 语句的解析和翻译。首先要用集算器的 SQL 解析能力，找到 where 条件中的日期字段，然后根据规则来决定路由到文件还是数据库。如果是路由到数据库，那么要把集算器的标准 SQL 翻译成数据库的 SQL，就要用到集算器的 SQL 翻译能力。
集算器的 SQL 解析用 sqlparse()函数实现，SQL 翻译用 sqltranslate() 函数实现。
SQL 性能优化
SQL 性能优化也是数据计算中间件（DCM）必不可少的能力。
BI 应用允许用户拖拽生成 SQL，就会出现很多性能不高的 SQL。比如直接在明细查询的 SQL 外面加上一层 count 来统计结果总条数：select count(1) from (select f1,f2,f3,f4…f30 from table1 where f1=1 and 1=1)。此时子查询中的 f1 到 f30 如果全部取出，就会降低查询的性能。1=1 这样的过滤条件也会造成没有意义的时间消耗。
集算器简单 SQL 引擎，可以完成自动查询优化。去掉 1=1 这样不必要的条件，也不会取出所有字段来完成 count。从而实现 SQL 解析和优化，有效的提高查询性能。
类似的，还有 select top 10 f1,f2 from table1 order by f1。集算器会采用小结果集比较的方式实现。可以做到无须大排序，只遍历一边数据即可得到需要的结果，有效提升查询速度。
组表列存 / 有序压缩存储
先进的数据存储方式，是数据计算中间件（DCM）成功实施的重要保障。
集算器组表采用列存方式存储数据，对于字段特别多的宽表查询，性能提升特别明显。组表采用的列存机制和常规列存是不同的。常规列存（比如 parquet 格式），只能分块之后，再在块内列存，在做并行计算的时候是受限的。组表的可并行压缩列存机制，采用倍增分段技术，允许任意分段的并行计算，可以利用多 CPU 核的计算能力把硬盘的 IO 发挥到极致。
组表生成的时候，要指定维字段，数据本身是按照维字段有序存放的，常用的条件过滤计算不依赖索引也能保证高性能。文件采用压缩存储，减小在硬盘上占用的空间，读取更快。由于采用了合适的压缩比，解压缩占用的 CPU 时间可以忽略不计。
组表也可以采取行存和全内存存储数据，支持内存数据库方式运行。
集群功能
敏捷的集群能力可以保证数据计算中间件（DCM）的高性能和高可用性。
集算器节点服务器是独立进程，可以接受集算器网关程序的计算请求并返回结果。对于并发访问的情况，可以发给多个服务器同时计算，提高并发容量。对于单个大计算任务的情况，可以分成多个小任务，发给多个服务器同时计算，起到大数据并行计算的作用。
集算器集群计算方案，具备敏捷的横向扩展能力，并发量或者数据量大时可以通过快速增加节点来解决。集算器集群也具备容错能力，即有个别节点失效时还能确保整个集群能工作，计算任务能继续执行完毕，起到多机热备和保证高可用性的作用。
应用推广
作为数据计算中间件（DCM），集算器实现的数据计算网关和路由，可以解决数据仓库无法满足性能要求，冷热数据分开又要混合计算的场景，不仅仅限于前端是 BI 的情况。例如：大屏展示、管理驾驶舱、实时报表、大数据量清单报表、报表批量订阅等等。
