实践目标本期目标是练习如何搭建多维分析后台的基础宽表,并通过 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 多维分析计算的目标可以用下面的 SQL 语句表示: select department_id,job_id,begin_date,sum(balance) sum,count(customer_id) count from customer where department_id in (10,20,50,60,70,80) and flag1='1' and flag8='1' group by department_id,job_id,begin_date 准备宽表编写 etl.dfx,从数据库中取出数据生成组表文件 customer.ctx,即存储为宽表。代码示例如下:
A1:连接预先配置好的数据库 oracle。@l 选项是将字段名处理成小写,l 是字母 L 的小写,不是数字 1。 B1:建立数据库游标,准备取出 customer 表的数据。customer 是事实表,实际应用中一般都比较大,所以用游标的方式,避免内存溢出。 A2:定义列存组表文件。字段名和 B1 完全一致。 A3:边取出游标 B1,边输出到组表文件中。 B3:关闭组表文件和数据库连接。 当宽表数据量有一千万行时,导出组表文件约 393MB。 部署集算服务器按照教程部署好集算器节点机。将 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_num,begin_date,sum(balance) sum,count(customer_id) count from customer where department_id in (10,20,50,60,70,80) and flag1='1'and flag8='1'group by department_id,job_num,begin_date"); // 继续处理结果集,将结果集展现出来 } 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_num,begin_date,sum(balance) sum,count(customer_id) count from /*+ external*/ customer where department_id in (10,20,50,60,70,80) and flag1='1' and flag8='1' group by department_id,job_num,begin_date 用游标时,Java 程序执行的总时间是 8 秒。 需要说明的是,这里的执行时间绝对数值并不重要(它和硬件环境相关)。记录执行时间是为了后面的实践中采用多种优化方法时,看看可以将时间缩短多少。 我们可以用多线程并行的方式执行 SQL,方法是给表名加上/*+parallel (n) */。2 线程并行完整的 SQL 如下: select department_id,job_num,begin_date,sum(balance) sum,count(customer_id) count from /*+ external*/ /*+parallel (2) */ customer where department_id in (10,20,50,60,70,80) and flag1='1' and flag8='1' group by department_id,job_num,begin_date 2 线程并行时,Java 程序执行的总时间是 4 秒。 二、执行脚本 编写 olap-spl.dfx,用 SPL 代码访问宽表并进行过滤和分组汇总计算。 先定义网格参数,将文件名、过滤条件、分组字段、聚合表达式分别传入。 参数设置窗口如下: 参数值设置如下: filename="data/customer.ctx" where="[10,20,50,60,70,80].contain(department_id) && flag1==\"1\"&& flag8==\"1\" group="department_id,job_id,begin_date" aggregate="sum(balance):sum,count(customer_id):count" SPL 代码示例如下:
A1:打开组表对象。 B1:建立游标,定义游标前过滤,条件是 where 变量的值。 A2:对游标做分组汇总计算,分组字段是 group 变量的值。汇总计算是 aggregate 变量的值。因为分组之后数据量较小,所以用 groups,结果集直接放在内存中。 执行结果如下图: A3:将结果集返回给调用者。 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 olap-spl(?,?,?,?)"); st.setObject(1, "data/customer.ctx"); st.setObject(2, "[10,20,50,60,70,80].contain(department_id) && flag1==\"1\"&& flag8==\"1\" "); st.setObject(3, "department_id,job_id,begin_date"); st.setObject(4, "sum(balance):sum,count(customer_id):count"); // 执行存储过程 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 程序执行的总时间是 12 秒。 我们也可以用多线程并行的方式执行脚本。2 线程并行的 olap-spl.dfx 脚本如下:
B1:cursor 函数的 @m 是指多路游标,最后一个参数 2 表示 2 路,也就是 2 线程并行。 2 线程时,Java 程序执行的总时间是 6 秒。 |
|
来自: raqsoft > 《集算器&润乾报表》