跨库多维分析后台的实现
zhaojp0 2019-06-28
问题的提出
多维分析（BI）系统后台数据常常可能来自多个数据库，这时就会出现跨库取数计算的问题。
例如：从性能和成本考虑，往往会限制生产库的容量，同时将历史数据分库存放，由ETL定期把生产库中新产生的数据同步到历史库中，同步周期根据数据的生成量，可能是1天、一周或者一个月。如果多维分析系统仅仅连上历史库取数，那么用户就只能对历史数据做分析，也就是实现T+1、T+7、T+30的多维分析。如果想要实现T+0的实时分析，就要从生产库和历史库分别取得数据进行计算并最终合并结果。很多时候，生产库和历史库还是异构的数据库，很难直接做跨库混合运算。
即使不是T+0场景，历史数据量很大时也可能分成多个数据库存储，而且也会是是异构数据库的情况。这时，多维分析系统也需要从多个不同数据仓库中取数、计算、合并结果展现。
解决思路与过程
作为数据计算中间件（DCM），构建数据前置层是集算器的重要应用模式。集算器具备可编程网关机制，可以同时连上多个数据库取数，并将结果合并提交给前台展现。
案例场景说明
在下面的案例中，多维分析系统要针对订单数据做自助分析。为了简化起见，我们采用了以下模拟环境：
l 多维分析系统前台用tomcat服务器中的jdbc.jsp进行模拟。Tomcat安装在windows操作系统的C:\tomcat6。
l 集算器JDBC集成在多维分析应用中。jdbc.jsp模仿多维分析应用系统，产生符合集算器规范的SQL，通过集算器JDBC提交给集算器SPL脚本处理。
l 多维分析系统的数据一部分来自于生产数据库（Oracle数据库） demo中的ORDERS表，另一部分来自历史库（Mysql数据库）test。当天数据连接生产库取数，实现实时分析。
l ETL过程每天将当天的最新数据同步到历史库中。日期以订购日期ORDERDATE为准，假设当前的日期是2015-07-18。ORDERDATE的开始和结束日期是多方位分析的必选条件。
案例中包含生产库和1个历史库，实际上集算器也支持一个生产库和同时多个历史库，或者没有生产库但有多个历史库的情况。
后台数据初始化准备
用下面的sql文件在ORACLE数据库中完成ORDERS表的建表和数据初始化。
![]()ordersOracle
数据截止到2017年7月18日。
用下面的sql文件在MYSQL数据库中完成ORDERS表的建表和数据初始化。
![]()ordersMysql
数据截止到2017年7月17日。
集算器实现跨库查询
1、 将下面压缩文件中的 CrossDB 目录复制到 tomcat 的应用目录。
![]()CrossDB
目录结构如下图：
配置文件在 classes 中，在官网上获取的授权文件也要放在 classes 目录中。集算器的 Jar 包要放在 lib 目录中（需要哪些 jar 请参照集算器教程）。
修改 raqsoftConfig.xml 中的主目录配置：
<mainPath>C:\tomcat6\webapps\CrossDB\WEB-INF\dfx</mainPath>
<JDBC>
<load>Runtime,Server</load>
<gateway> CrossDB.dfx</gateway>
</JDBC>
2、 编辑 CrossDB 目录中的 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 between date('2011-07-18') and date('2015-07-18') and AMOUNT>100 ";
out.println("Test page v1<br><br><br><pre>");
out.println("订单 ID"+"\t"+" 客户 ID"+"\t"+" 雇员 ID"+"\t"+" 订购日期 "+"\t"+" 订单金额 "+"
");
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"+"
");
}
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。步骤和一般的数据库完全一样，具有很高的兼容性和通用性。对于多维分析工具来说，虽然是界面操作来连接 JDBC 和提交 SQL，但是基本原理和 jsp 完全一样。
3、 打开 dfx 目录中的 CrossDB.dfx，观察理解 SPL 代码。
传入参数是 sql 例如：
select top 10 ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from ORDERS where ORDERDATE between date('2011-07-18') and date('2015-07-18') and AMOUNT>100。
SPL脚本如下：
| A | B | C | |
|---|---|---|---|
| 1 | =sql.sqlparse@aw() | =A1.pselect("ORDERDATE between*") | |
| 2 | =substr(A1(B1),"date(") | =substr(A1(B1+1),"date(") | |
| 3 | =mid(A2,2,10) | =mid(B2,2,10) | |
| 4 | if between(date(now()),date(A3):date(B3)) | ||
| 5 | =connect("orcl") | =B5.cursor@dx("select ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from orders where orderdate=?",date(now())) | |
| 6 | =connect("mysql") | =B6.cursor@x("select ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from orders") | |
| 7 | =mcs=[C5,C6].mcursor() | =connect().cursor@x("with orders as(mcs)"+sql) | |
| 8 | return C7 | ||
| 9 | else | =connect("mysql") | =B9.cursor@x(sql.sqltranslate("MYSQL")) |
| 10 | return C9 |
说明：
A1：解析 SQL，获取 where 子句，并用空格来拆分成序列。
B1，A2-B3：在 A1 序列找到必选条件订购日期，获取开始和结束日期值。
A4：判断查询范围是否包含当前日期。
B5-C6：如果包含当前日期，就连接生产库和历史库，建立取数游标。
B7：用生产库和历史库游标建立多路游标。
C7、B8：对多路游标进行 sql 查询并返回结果。
A9-C10：如果不包含当前日期，就只连接历史数据库。将 SQL 翻译成符合 MYSQL 数据库规范的 SQL, 执行 SQL 得到游标并返回。
实际业务中，生产库一般都有必要保持一些历史数据，这样生产库和历史库会有重复数据，所以代码中需要给生产库再加上日期条件。如果是多个历史库分库的情况，一般来讲，这些库之间就没有重复的数据，代码能够简化一些。比如，假设例中的 ORACLE 和 MYSQL 没有重复数据，则 CrossDB.dfx 的代码可以简化如下：
| A | B | |
|---|---|---|
| 1 | =connect("orcl") | =A1.cursor@dx("select ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from orders") |
| 2 | =connect("mysql") | =A2.cursor@x("select ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from orders") |
| 3 | =mcs=[B1,B2].mcursor() | =connect().cursor@x("with orders as(mcs)"+sql) |
| 4 | return B3 |
4、 启动 tomcat，在浏览器中访问http://localhost:8080/CrossDB/jdbc.jsp，查看结果。
结果中订购日期格式略有不同，这只需要在多维分析前端设置一下显示格式即可。
我们还可以继续测试如下情况：
1、 仅仅查询历史库
sql ="select top 10 ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from ORDERS where ORDERDATE between date('2011-07-18') and date('2015-07-18') and AMOUNT>100";
2、 分组查询
sql ="select CUSTOMERID,EMPLOYEEID,sum(AMOUNT) S,count(1) C from ORDERS where ORDERDATE between date('2011-07-18') and date('2015-07-18') group by CUSTOMERID,EMPLOYEEID"
ETL过程
在这个案例中，集算器SPL脚本还可以承担ETL的工作。
多维分析系统上线之后，要每天晚上定时同步当天最新的数据。我们假设当天日期是2015-07-18。
SPL语言脚本etl.dfx将当天数据增量补充到历史库中，具体脚本如下：
| A | |
|---|---|
| 1 | =connect("orcl") |
| 2 | =A1.cursor@xd("select ORDERDATE,CUSTOMERID,EMPLOYEEID,ORDERID,AMOUNT from ORDERS where ORDERDATE=?",etlDate) |
| 3 | =connect("mysql") |
| 4 | =A3.update@i(B2, ORDERS,ORDERDATE,CUSTOMERID,EMPLOYEEID,ORDERID,AMOUNT) |
| 5 | >A3.close() |
etl.dfx的输入参数是etlDate，也就是需要新增的当天日期。
etl.dfx脚本可以用windows或者linux命令行的方式执行，结合定时任务，可以定时执行。也可以用ETL工具来定时调用。
windows命令行的调用方式是：
C:\Program Files\raqsoft\esProc\bin>esprocx.exe C: \etl.dfx 2015-07-18
linux命令是:
/raqsoft/esProc/bin/esprocx.sh /esproc/ etl.dfx 2015-07-18
应用推广
作为数据计算中间件（DCM），由集算器提供的后台数据源可以支持各种前端应用，不仅限于前端是多维分析的情况，还可以包括例如大屏展示、管理驾驶舱、实时报表、大数据量清单报表、报表批量订阅等等场景。
另外，集算器形成的后台数据源也可以将数据缓存计算。这时，采用集算器实现的数据计算网关和路由，就可以在集算器缓存数据和数据仓库之间智能切换，从而解决数据仓库无法满足的性能要求问题，例如常见的冷热数据分开计算的场景。（具体做法参见《集算器实现计算路由优化BI后台性能》）。
在另一些应用中，集算器也可以完全脱离数据库，起到轻量级多维分析后台的作用，这时的集算器就相当于独立的中小型数据仓库或者数据集市了。（具体做法参见《集算器实现轻量级多维分析后台》。）
