问题的提出
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脚本如下：
从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中，在官网上获取的授权文件也要放在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>
这里<gateway>标签的内容就是网关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脚本如下：
说明：
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。
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代码。
代码基本和前面的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。
组表与集文件不同，默认是采用列式存储的，支持任意分段的并行计算，可以有效提升查询速度。同时，生成组表的时候，要注意数据预先排序和合理定义维字段。本例中，按照经常过滤、分组的字段，将维字段确定为：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的情况。例如：大屏展示、管理驾驶舱、实时报表、大数据量清单报表、报表批量订阅等等。