跨库多维分析后台的实现

问题的提出

        多维分析(BI)系统后台数据常常可能来自多个数据库,这时就会出现跨库取数计算的问题。

例如:从性能和成本考虑,往往会限制生产库的容量,同时将历史数据分库存放,由ETL定期把生产库中新产生的数据同步到历史库中,同步周期根据数据的生成量,可能是1天、一周或者一个月。如果多维分析系统仅仅连上历史库取数,那么用户就只能对历史数据做分析,也就是实现T+1T+7T+30的多维分析。如果想要实现T+0的实时分析,就要从生产库和历史库分别取得数据进行计算并最终合并结果。很多时候,生产库和历史库还是异构的数据库,很难直接做跨库混合运算。

即使不是T+0场景,历史数据量很大时也可能分成多个数据库存储,而且也会是是异构数据库的情况。这时,多维分析系统也需要从多个不同数据仓库中取数、计算、合并结果展现。

解决思路与过程

作为数据计算中间件(DCM),构建数据前置层是集算器的重要应用模式。集算器具备可编程网关机制,可以同时连上多个数据库取数,并将结果合并提交给前台展现。

                                              undefined

       

案例场景说明

在下面的案例中,多维分析系统要针对订单数据做自助分析。为了简化起见,我们采用了以下模拟环境:

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-18ORDERDATE的开始和结束日期是多方位分析的必选条件。

案例中包含生产库和1个历史库,实际上集算器也支持一个生产库和同时多个历史库,或者没有生产库但有多个历史库的情况。

后台数据初始化准备

        用下面的sql文件在ORACLE数据库中完成ORDERS表的建表和数据初始化。

       

ordersOracle

        数据截止到2017718日。

用下面的sql文件在MYSQL数据库中完成ORDERS表的建表和数据初始化。

       

ordersMysql

        数据截止到2017717日。

集算器实现跨库查询

1、 将下面压缩文件中的 CrossDB 目录复制到 tomcat 的应用目录。

 

CrossDB


目录结构如下图:

undefined

配置文件在 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"+" 订单金额 "+"<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。步骤和一般的数据库完全一样,具有很高的兼容性和通用性。对于多维分析工具来说,虽然是界面操作来连接 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,查看结果。

undefined

结果中订购日期格式略有不同,这只需要在多维分析前端设置一下显示格式即可。

我们还可以继续测试如下情况:

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后台性能》)。

在另一些应用中,集算器也可以完全脱离数据库,起到轻量级多维分析后台的作用,这时的集算器就相当于独立的中小型数据仓库或者数据集市了。(具体做法参见《集算器实现轻量级多维分析后台》。)