多维分析后台实践 3:维度排序压缩

【摘要】

用实例、分步骤,详细讲解多维分析(OLAP)的实现。点击了解多维分析后台实践 3:维度排序压缩

实践目标

本期目标,是在完成数据类型转化基础上,实现维度排序压缩,进一步提升计算速度。

实践的步骤:

1、 准备基础宽表:修改上期的代码,完成维度排序压缩存成新组表。

2、 访问基础宽表:上期代码不必修改,直接应用于新组表。

3、 新增数据追加:每日新增业务数据追加,每月重新排序重整。要尽可能不影响访问基础宽表的性能,同时减小每日新增数据所需要的时间。

 

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

 ..

 

假设当天日期是 2021-01-12,则取出当日新增数据的 SQL 是:

select * from customer where begin_date=to_date('2021-01-12','yyyy-mm-dd')

 

多维分析计算的目标也不变,用下面 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')

 

准备宽表

维度排序压缩:有序列存。列存是指列式存储,集算器新建组表时,默认列存。

有序是指字段值物理有序存储,也就是用维度字段排序后存入组表。用于排序的维度字段顺序非常关键,要将重复度高的维度排在前面。

本例的维度包括:department_id,job_num,employee_id,begin_date,customer_id。其中,部门编号 department_id 总数最少(事实表中只出现 11 个部门),所以重复度最高。其他字段中,job_num,employee_id,begin_date,customer_id 重复度依次降低。

同时,考虑实际应用中,分组字段出现的情况也和重复度基本一致,所以排序的字段顺序可以确定为:department_id,job_num,employee_id,begin_date,customer_id。

我们用数据库来做排序。示例如下:

select department_id,job_id,employee_id,begin_date,customer_id,first_name,last_name,phone_number,job_title,balance,department_name,flag1,flag2,flag3,flag4,flag5,flag6,flag7,flag8

from customer order by department_id,job_id,employee_id,begin_date,customer_id。

执行结果如图:

..

 

根据以上要求改写 etl.dfx,从数据库中取出排序数据,类型转化后,生成组表文件,存储基础宽表。代码示例如下:


A

B

1

=connect@l("oracle")


2

=A1.cursor@d("select   department_id,job_id,employee_id,begin_date,customer_id,first_name,last_name,phone_number,job_title,balance,department_name,flag1,flag2,flag3,flag4,flag5,flag6,flag7,flag8   from customer order by department_id,job_id,employee_id,begin_date,customer_id")

3

=A1.query@d("select   job_id from jobs order by job_id")

=file("data/job.btx").export@z(A3)  

4

=A3.(job_id)

=date("2000-01-01")

5

=A2.new(int(department_id):department_id,A4.pos@b(job_id):job_num,int(employee_id):employee_id,int(interval@m(B4,begin_date)*100+day(begin_date)):begin_date,int(customer_id):customer_id,first_name,last_name,phone_number,job_title,float(balance):balance,department_name,int(flag1):flag1,int(flag2):flag2,int(flag3):flag3,int(flag4):flag4,int(flag5):flag5,int(flag6):flag6,int(flag7):flag7,int(flag8):flag8)

6

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

7

=A6.append(A5)

>A6.close(),A1.close()

其中:A2 的 SQL 语句增加了排序,A6 的组表指定了排序字段。

其他代码和上期一致。

 

数据量为一亿,导出组表文件和前几期的组表文件比较如下:

期数

文件大小

说明

备注

第一期

3.5GB

直接从数据库导出,未做优化


第二期

3.0GB

完成数据类型优化


第三期

2.4GB

完成前面的优化和维度排序压缩


从上表可以看出,完成数据类型优化之后,文件大小减少了14%(0.5GB)。再完成维度排序压缩后,又减少了 20%(0.6GB), 总体减少 31%(1.1GB)。文件变小,能减少磁盘读取数据量,有效提高性能。

访问宽表

访问宽表的 SPL 代码、Java 代码和上期相比没有变化。

Java 代码加上后台计算返回结果总的执行时间,和前期比较如下:

期数

单线程

二线程并行

备注

第一期

84

42


第二期

31 秒

14 秒


第三期

9

5


通过上表的对比可以看出,维度排序压缩进一步提高了计算性能。

新增数据

客户表每天都会有新增数据,需要每天定时添加到组表文件中。如果是按日期有序的组表文件,将每天产生的新数据追加到文件的最后即可。但是我们的客户组表是按照部门等字段有序的,如果在结尾直接追加数据,就不是整体有序了。如果每天都将原有数据和新数据一起重新排序,计算的时间会比较长。

我们可以从数据库中读取新数据并按照部门等字段排序,再用 T.append@a() 函数追加,集算器会自动新建一个补文件,每天新数据都有序归并到补文件中,补文件相对较小,有序归并耗时较短。每月只需对客户表文件和补文件重整一次,将补文件有序归并到组表文件中。

 

采用这个方法,编写 etlAppend.dfx,网格参数如下:

..

SPL 代码如下:


A

B

1

if day(today)==1

=file("data/customer.ctx").reset()

2

=connect@l("oracle")


3

=A2.cursor@d("select   department_id,job_id,employee_id,begin_date,customer_id,first_name,last_name,phone_number,job_title,balance,department_name,flag1,flag2,flag3,flag4,flag5,flag6,flag7,flag8   from customer where begin_date=? order by department_id,job_id,employee_id,begin_date  ",today)

4

=A2.query@d("select   job_id from jobs order by job_id")

5

=A4.(job_id)

=date("2000-01-01")

6

=A3.new(int(department_id):department_id,A5.pos@b(job_id):job_num,int(employee_id):employee_id,int(interval@m(B5,begin_date)*100+day(begin_date)):begin_date,int(customer_id):customer_id,first_name,last_name,phone_number,job_title,float(balance):balance,department_name,int(flag1):flag1,int(flag2):flag2,int(flag3):flag3,int(flag4):flag4,int(flag5):flag5,int(flag6):flag6,int(flag7):flag7,int(flag8):flag8)

7

=file("data/customer.ctx").open().append@a(A6)

8

>A7.close(),A2.close()


A1:判断输入日期是否是每月第一天,如果是,就执行 B1 对客户组表进行重整,将新数据形成的补文件有序归并进入客户组表文件。

A2:连接 oracle 数据库。

A3:取出当天数据。

A4:取出 jobs 表数据,用于类型转换。

A5、B5、A6 与上期 etl.dfx 相同。

A7:将今天新增数据有序归并到补文件中。

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

 

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

例如:

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

以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾