多维分析后台实践 1:基础宽表

【摘要】
用实例、分步骤,详细讲解多维分析(OLAP)的实现。点击了解多维分析后台实践 1:基础宽表

实践目标

本期目标是练习如何搭建多维分析后台的基础宽表,并通过 SPL 和 SQL 访问基础宽表。

实践的步骤:

1、 准备基础宽表:将基础宽表数据从数据库中取出,存成组表文件。

2、 访问基础宽表:用 SPL 或者 SQL 语句访问。

本期样例宽表为 customer 表。从 Oracle 数据库中取出宽表数据的 SQL 语句是 select * from customer。执行结果如下图:

 ..

其中字段包括:

CUSTOMER_ID NUMBER(10,0), 客户编号

FIRST_NAME VARCHAR2(20), 名

LAST_NAME VARCHAR2(25), 姓

PHONE_NUMBER VARCHAR2(20), 电话号码

BEGIN_DATE DATE, 开户日期

JOB_ID VARCHAR2(10), 职业编号

JOB_TITLE VARCHAR2(32), 职业名称

BALANCE NUMBER(8,2), 余额

EMPLOYEE_ID NUMBER(4,0), 开户雇员编号

DEPARTMENT_ID NUMBER(4,0), 分支机构编号

DEPARTMENT_NAME VARCHAR2(32), 分支结构名称

FLAG1 CHAR(1), 标记 1

FLAG2 CHAR(1), 标记 2

FLAG3 CHAR(1), 标记 3

FLAG4 CHAR(1), 标记 4

FLAG5 CHAR(1), 标记 5

FLAG6 CHAR(1), 标记 6

FLAG7 CHAR(1), 标记 7

FLAG8 CHAR(1), 标记 8

多维分析计算的目标可以用下面 Oracle 的 SQL 语句表示:

 

select department_id,job_id,to_char(begin_date,'yyyymm') begin_month ,sum(balance) sum,count(customer_id) count

from customer

where department_id in (10,20,50,60,70,80)

and job_id in ('AD_VP','FI_MGR','AC_MGR','SA_MAN','SA_REP')

and begin_date>=to_date('2002-01-01','yyyy-mm-dd')

and begin_date<=to_date('2020-12-31','yyyy-mm-dd')

and flag1='1' and flag8='1'

group by department_id,job_id,to_char(begin_date,'yyyymm')

 

准备宽表

编写 etl.dfx,从数据库中取出数据生成组表文件 customer.ctx,即存储为宽表。代码示例如下:


A

B

1

=connect@l("oracle")

=A1.cursor("select   * from customer")

2

=file("data/customer.ctx").create@y(customer_id,first_name,last_name,phone_number,begin_date,job_id,job_title,balance,employee_id,department_id,department_name,flag1,flag2,flag3,flag4,flag5,flag6,flag7,flag8)  

3

=A2.append(B1)

>A2.close(),A1.close()

A1:连接预先配置好的数据库 oracle。@l 选项是将字段名处理成小写,l 是字母 L 的小写,不是数字 1。

B1:建立数据库游标,准备取出 customer 表的数据。customer 是事实表,实际应用中一般都比较大,所以用游标的方式,避免内存溢出。

A2:定义列存组表文件。字段名和 B1 完全一致。

A3:边取出游标 B1,边输出到组表文件中。

B3:关闭组表文件和数据库连接。

 

当宽表数据量有一亿行时,导出组表文件约 3.5GB。

部署集算服务器

按照教程部署好集算器节点机。将 meta.txt 放入主目录中,文件内容是表名和文件名的对应关系,如下:

Table      File  Column  Type

customer       data/customer.ctx

 

文件名是相对于主目录的,假如节点机主目录是 d:/esproc/,那么完整的文件名就是 d:/esproc/data/customer.ctx。

访问宽表

多维分析后台需要被通用的前端所调用。调用的方式有两种,第一种是使用 SQL;第二种是执行脚本,提交过滤条件、分组字段等参数给后台执行。

一、SQL

我们先来看第一种,以 Java 调用集算器 JDBC 为例来说明。Java 示例代码如下:

public void testOlapServer(){

       Connection con = null;

       java.sql.Statement st;

       try{

              // 建立连接

              Class.forName("com.esproc.jdbc.InternalDriver");

              // 根据 url 获取连接

              con= DriverManager.getConnection("jdbc:esproc:local://?onlyServer=true&sqlfirst=plus");

              st = con.createStatement();

              // 直接执行 SQL+ 语句,获取结果集

              ResultSet rs = st.executeQuery("select department_id,job_id, string(begin_date,'yyyyMM'),sum(balance) sum,count(customer_id) count from  customer where department_id in (10,20,50,60,70,80) and job_id in ('AD_VP','FI_MGR','AC_MGR','SA_MAN','SA_REP') and begin_date>=date('2002-01-01','yyyy-mm-dd') and begin_date<=date('2020-12-31','yyyy-mm-dd') and flag1='1'and flag8='1'group by department_id,job_id, string(begin_date,'yyyyMM')");

              // 继续处理结果集,将结果集展现出来

             

       }

       catch(Exception e){

              out.println(e);

       }

       finally{

              // 关闭连接

              if (con!=null) {

                     try {con.close();}

                     catch(Exception e) {out.println(e);       }

              }

       }

}

这里的 customer.ctx 会被完全读入内存,不适合大数据量的情况。对于大数据量,可以在表名前面加 /*+ external*/,组表会被处理成游标。或者也可以采用下面执行脚本的方法。

加上 /*+ external*/ 之后的 SQL 如下:

select department_id,job_id, string(begin_date,'yyyyMM'),sum(balance) sum,count(customer_id) count

from /*+ external*/ customer

where department_id in (10,20,50,60,70,80) and job_id in ('AD_VP','FI_MGR','AC_MGR','SA_MAN','SA_REP') and begin_date>=date('2002-01-01','yyyy-mm-dd') and begin_date<=date('2020-12-31','yyyy-mm-dd') and flag1='1' and flag8='1'

group by department_id,job_id, string(begin_date,'yyyyMM')

 

用游标时,Java 程序执行的总时间是 92 秒。

需要说明的是,这里的执行时间绝对数值并不重要。记录执行时间是为了后面的实践中采用多种优化方法时,可以将时间缩短多少。

 

我们可以用多线程并行的方式执行 SQL,方法是给表名加上/*+parallel (n) */。2 线程并行完整的 SQL 如下:

select department_id,job_id,string(begin_date,'yyyyMM'),sum(balance) sum,count(customer_id) count

from /*+ external*/  /*+parallel (2) */ customer

where department_id in (10,20,50,60,70,80) and job_id in ('AD_VP','FI_MGR','AC_MGR','SA_MAN','SA_REP') and begin_date>=date('2002-01-01','yyyy-mm-dd') and begin_date<=date('2020-12-31','yyyy-mm-dd') and flag1='1' and flag8='1'

group by department_id,job_id,string(begin_date,'yyyyMM')

 

2 线程并行时,Java 程序执行的总时间是 50 秒。

 

二、执行脚本

编写 olap-spl.dfx,用 SPL 代码访问宽表并进行过滤和分组汇总计算。

先 olap-spl.dfx,用 SPL 代码访问宽表并进行过滤和分组汇总计算。

定义网格参数,将文件名、部门编号、工作编号、标志位、日期范围、分组字段、聚合表达式分别传入。参数设置窗口如下:

..

参数值样例:

filename="data/customer.ctx"

arg_department_id ="10,20,50,60,70,80"

arg_job_id="AD_VP,FI_MGR,AC_MGR,SA_MAN,SA_REP"      

arg_begin_date_min = "2002-01-01"

arg_begin_date_max ="2020-12-31"

arg_flag ="flag1==\"1\"&& flag8==\"1\" "

group="department_id,job_id, string(begin)begin_yearmonth"

aggregate="sum(balance):sum,count(customer_id):count"

说明:group 中如果是 begin_date 则按照日期分组,如果是 begin_yearmonth 则按照年月分组。对于多维分析前端来说,可以认为有两个字段。

 

 

SPL 代码示例如下:


A

B

1

=file(filename).open()  

=replace(arg_group,"begin_yearmonth","string(begin_date,\"yyyyMM\"):begin_yearmonth")

2

=A1.cursor(;arg_department_id.split(",").(int(~)).contain(department_id)   && arg_job_id.split(",").contain(job_id) && begin_date>=date(arg_begin_date_min)   && begin_date<=date(arg_begin_date_max) && ${arg_flag})

3

=A2.groups(${B1};${aggregate})

4

return A3

A1:打开组表对象。B1:将 begin_yearmonth 字段转换为 begin_date 的表达式。

A2:建立游标,定义游标前过滤,用传入参数计算出过滤条件。

A3:对游标做分组汇总计算,分组字段是 group 变量的值。汇总计算是 aggregate 变量的值。因为分组之后数据量较小,所以用 groups,结果集直接放在内存中。

执行结果如下图:

..

A4:将结果集返回给调用者。

 

olap-spl.dfx 编写好之后,可以在多维分析中作为存储过程调用,Java 代码如下:

public void testOlapServer(){

       Connection con = null;

       java.sql.PreparedStatement st;

       try{

              // 建立连接

              Class.forName("com.esproc.jdbc.InternalDriver");

              // 根据 url 获取连接

              con= DriverManager.getConnection("jdbc:esproc:local://?onlyServer=true&sqlfirst=plus");

              // 调用存储过程,其中 olap-spl 是 dfx 的文件名

st =con.prepareCall("call 2olap-spl(?,?,?,?,?,?,?,?)");

st.setObject(1, "data/2customer_test.ctx");//arg_filename

st.setObject(2, "10,20,50,60,70,80");//arg_department_id

st.setObject(3, "AD_VP,FI_MGR,AC_MGR,SA_MAN,SA_REP");//arg_job_id      

st.setObject(4, "2002-01-01");//arg_begin_date_min

st.setObject(5, "2020-12-31");//arg_begin_date_max

st.setObject(6, "flag1==\"1\"&& flag8==\"1\" ");//arg_flag

st.setObject(7, "department_id,job_id,begin_yearmonth");//arg_group

st.setObject(8, "sum(balance):sum,count(customer_id):count");//arg_aggregate

// 执行存储过程

st.execute();

// 获取结果集

ResultSet rs = st.getResultSet();

// 继续处理结果集,将结果集展现出来

             

       }

       catch(Exception e){

              out.println(e);

       }

       finally{

              // 关闭连接

              if (con!=null) {

                     try {con.close();}

                     catch(Exception e) {out.println(e);       }

              }

       }

}

此时,我们是采用单线程进行的计算。

单线程时,一亿条数据,Java 程序加后台 SPL 执行的总时间是 120 秒。

 

我们也可以用多线程并行的方式执行脚本。2 线程并行的 olap-spl.dfx 脚本如下:


A

B

1

=file(filename).open()  

=replace(arg_group,"begin_yearmonth","string(begin_date,\"yyyyMM\"):begin_yearmonth")

2

=A1.cursor@m(;arg_department_id.split(",").(int(~)).contain(department_id)   && arg_job_id.split(",").contain(job_id) && begin_date>=date(arg_begin_date_min)   && begin_date<=date(arg_begin_date_max) && ${arg_flag};2)

3

=A2.groups(${B1};${aggregate})

4

return A3

A2:cursor 函数的 @m 是指多路游标,最后一个参数 2 表示 2 路,也就是 2 线程并行。

 

2 线程时,Java 程序加后台 SPL 执行的总时间是 75 秒。


新增数据

客户表每天都会有新增数据,需要每天定时添加到组表文件中。我们可以编写 etlAppend.dfx,网格参数如下:

1png


SPL 代码如下:

 


A

1

=connect@l("oracle")

2

=A1.cursor("select   * from customer where begin_date=?",today)

3

=file("data/customer.ctx").open().append(A2)

4

>A3.close(),A1.close()

 

A1:连接 oracle 数据库。

A2:建立游标,取当天数据。

A3:将今天新增数据追加到组表文件的末尾。

A4:关闭文件和数据库连接。

 

etlAppend.dfx 需要每天定时执行。执行的方法是用 ETL 工具或者操作系统定时任务,通过命令行调用集算器脚本。

例如:

C:\Program Files\raqsoft\esProc\bin>esprocx d:\olap\etlAppend.dfx