跨库多维分析后台的实现
问题的提出
多维分析(BI)系统后台数据常常可能来自多个数据库,这时就会出现跨库取数计算的问题。
创新互联建站主营浉河网站建设的网络公司,主营网站建设方案,成都app软件开发,浉河h5微信小程序定制开发搭建,浉河网站营销推广欢迎浉河等地区企业咨询
例如:从性能和成本考虑,往往会限制生产库的容量,同时将历史数据分库存放,由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表的建表和数据初始化。
配置文件在 classes 中,在官网上获取的授权文件也要放在 classes 目录中。集算器的 Jar 包要放在 lib 目录中(需要哪些 jar 请参照集算器教程)。
修改 raqsoftConfig.xml 中的主目录配置:
2、 编辑 CrossDB 目录中的 jdbc.jsp,模拟前台界面提交 sql 展现结果。
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ page import="java.sql.*" %>
<%
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
");");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("
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();
}
%>
在 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后台性能》)。
在另一些应用中,集算器也可以完全脱离数据库,起到轻量级多维分析后台的作用,这时的集算器就相当于独立的中小型数据仓库或者数据集市了。(具体做法参见《集算器实现轻量级多维分析后台》。)
分享标题:跨库多维分析后台的实现
当前链接:http://scyanting.com/article/jcjcep.html