多维分析后台实践 4:预汇总和冗余排序

【摘要】
用实例、分步骤,详细讲解多维分析(OLAP)的实现。点击了解多维分析后台实践 4:预汇总和冗余排序

实践目标

本期目标,是在前期基础上实现预汇总和冗余排序,继续提升计算速度。

实践的步骤:

1、 预处理数据:在已有宽表基础上,按照计算需要实现“部分预汇总”和“切片维度的冗余排序”。

2、 多维分析计算:利用预处理的结果,实现多维分析计算。

本期基础数据是上期形成的宽表 customer.ctx,用集算器的数据文件工具查看如下:

..

 

多维分析计算的主要目标不变,用下面 Oracle 的 SQL 语句表示:

SQL1:

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')

针对 SQL1,要在预处理数据时,生成包含所有分组字段和切片字段的 cube 文件 cu_1。在用集算器实现 SQL1 对应的多维分析计算时,可以正好用上 cu_1 来提高性能。

 

在 SQL1 基础上增加两个计算目标,用来验证预汇总的效果:

SQL2:

select department_id,job_id,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

用集算器实现 SQL2 对应的多维分析计算时,分组字段比 cu_1 少了一个 begin_yearmonth, 不能用 cu_1 直接得到结果,还要在 cu_1 的基础上再次汇总。

 

SQL3:

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'

and flag3='1' and flag6='1'

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

用集算器实现 SQL3 对应的多维分析计算时,切片字段比 cu_1 多了 flag3 和 flag6, 不能用 cu_1 了,只能在原宽表 customer.ctx 上做汇总。

 

除此之外,再增加两个计算目标 SQL4、SQL5,分别用来验证冗余排序和时间段预汇总的效果。

SQL4:

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

from customer

where begin_date>=to_date('2006-01-01','yyyy-mm-dd')

and begin_date<=to_date('2006-01-12','yyyy-mm-dd')

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

 

SQL5:

Select sum(balance) sum,count(customer_id) count

from customer

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

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

 

预处理数据

一、部分预汇总

改写 etl.dfx,在最后加入下面的代码:


A

1

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

2

=A1.cuboid(cu_1,department_id,job_num,begin_date\100,begin_date,flag1,flag8;sum(balance):sum,count(customer_id):count)

A1:打开组表文件。

A2:为组表文件建立部分预汇总的 cube 文件 cu_1。注意:切片字段和分组字段都要包含,也要包含 begin_date\100 这样的表达式。

 

每天产生新增数据时,我们需要追加到组表中。无论是按照第一期直接 append 追加数据,还是按照第三期建立补文件,每月重整的方式追加数据,集算器都会自动更新 cube 文件。

 

生成的 cube 文件和组表文件 customer.ctx 在同一目录中,文件名为:customer.ctx__CUBOID@cu_1。当组表文件有 1 亿条数据时,cube 文件大小为 16M。

 

二、冗余排序

改写 etl.dfx,在最后加入下面的代码:


A

B

1

=file("data/customer.ctx").open().cursor()

2

=A1.sortx(begin_date,department_id,job_num,   employee_id,customer_id)

3

=A2.new(begin_date,department_id,job_num,employee_id,customer_id   ,first_name,last_name,phone_number,job_title,balance,department_name,flag1,flag2,flag3,flag4,flag5,flag6,flag7,flag8)

4

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

5

=A4.append(A3)

>A4.close()

A1:建立组表文件的游标。

A2:对组表文件,按照 begin_date,department_id,job_num, employee_id,customer_id,外存排序。

A3:对游标字段调整顺序。

A4:新建一个组表文件 customer_begin_date.ctx,排序字段是 begin_date,department_id,job_num, employee_id,customer_id。

A5:将排好序的数据输出到新组表文件中。B5:关闭新组表文件。

 

当组表文件有 1 亿条数据时,冗余文件大小是 2.4G,和原组表 customer.ctx 大小基本相同。

 

三、时间段预汇总

时间段预汇总需要利用日期型字段,因此 begin_date 就不能预处理成小整数了。要用 customer_begin_date.ctx 生成一个新的 customerDate.ctx,begin_date 还原成日期型,其他字段不变。

改写 etl.dfx,在最后加入下面的代码:


A

1

=file("data/customerDate.ctx").open()

2

=A1.cuboid(cu_date_year_month,month@y(begin_date);sum(balance),count(customer_id))

3

=A1.cuboid(cu_date_year,year(begin_date);sum(balance),count(customer_id))

A1:打开组表。

A2:建立 cube 名为 cu_date_year_month,采用年月为时间段预汇总的汇总维度。

A3:建立 cube 名为 cu_date_year,采用年为时间段预汇总的汇总维度。

多维分析计算

一、部分预汇总

按照 SQL1 的需求改写 olap.dfx,将普通的分组计算改为部分预汇总计算。

olap.dfx 参数是 arg_table 和 arg_json 不变。

SPL 代码修改如下:


A

1

=call(arg_table/".dfx",arg_json)

2

=file("data/"/arg_table/".ctx").open()  

3

=A2.cgroups@m(${A1(3)};${A1(2)};${A1(4)})

4

=A3.new(${A1(5)})

5

return A4

除 A3 之外的代码不变。

A3:用预先生成的 cube 文件对组表计算条件过滤和小结果集分组汇总,这里没有指定 cube 的名字,集算器会自动选择最适合的 cube,也就是 cu_1。@m 选项是指多线程并行计算,函数的最后一个参数 2 是指 2 线程。

实际执行的语句是:

=A2.cgroups(department_id,job_num,begin_date\100:begin_yearmonth; sum(balance):sum,count(customer_id):count; [10,20,50,60,70,80].contain(department_id) && [5,7,2,15,16].contain(job_num) && between(begin_date,2401:25131) && flag1==1 && flag8==1;2)

 

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

期数

单线程

二线程并行

备注

第一期

84

42


第二期

31 秒

14 秒


第三期

9

5


第四期

0.1 秒

0.1 秒

部分预汇总

通过上表的对比可以看出,部分预汇总对计算性能提升非常明显。

 

对于 SQL2 来说,上述代码无需改动,只要把参数 arg_json 的 group 改为:

                        group:

                        [

                                    "department_id",

                                    "job_id"

                        ]

集算器计算时,要在 cu_1 的基础上再次汇总。

 

对于 SQL3 来说,也不需要改动代码,只要把参数 arg_json 的 slice 增加两个条件:

                        slice:

                        [

                                    {

                                                dim:"department_id",

                                                value:[10,20,50,60,70,80]

                                    },

                                    {

                                                dim:"job_id",

                                     value:["AD_VP","FI_MGR","AC_MGR","SA_MAN","SA_REP"]

                                    },

                                    {

                                                dim:"begin_date",

                                                interval:[date("2002-01-01"),date("2020-12-31")]

                                    },

                                    {

                                                dim:"flag1",

                                                value:"1"

                                    },

                                    {

                                                dim:"flag3",

                                                value:"1"

                                    },

                                    {

                                                dim:"flag6",

                                                value:"1"

                                    },

                                    {

                                                dim:"flag8",

                                                value:"1"

                                    }

                        ]

集算器计算时,因为有两个字段 flag3、flag6 不在 cu_1 中,所以不能利用 cu_1,只能用原宽表来做过滤汇总计算。

 

SQL1、SQL2、SQL3 对应集算器 SPL 代码执行时间对比如下:

期数

单线程

二线程并行

备注

SQL1

0.1

0.1


SQL2

0.1 秒

0.1 秒


SQL3

4

4


cu_1本身较小,所以在其基础上再汇总执行时间变化不大,所以 SQL1 和 SQL2 时间差不多。SQL3 无法使用 cu_1,要用原组表做计算,所以时间比较长。可以通过增加 cu_1 字段,或者再建立新的 cube 文件 cu_2 来进一步提速。

需要说明的是:因为 SQL3 增加了两个过滤条件,从组表中取出数据较少,所以比第三期计算时间短。

 

组表文件和 cube 文件大小对比如下:

文件名

文件大小

备注

组表

2.4GB


cube 文件

16MB


从上表中可以看出,cube 文件相对组表来说是比较小的。Cube 文件多占用的存储空间并不大,却能换取显著的计算性能提升,是比较值得的。因此,我们可以根据多维分析系统的实际情况,针对出现频次较多的计算,在硬盘允许的条件下尽可能多的建立 cube 文件,实现空间换时间。

 

二、冗余排序

按照 SQL4 的需求调整网格参数 arg_json 中的 slice:

            slice:

                        [

                                    {

                                                dim:"begin_date",

                                                interval:[date("2006-01-01"),date("2006-01-12")]

                                    }

                        ]

按照 SQL4 的需求改写 customer.dfx,增加一个返回参数,也就是冗余排序组表的文件名称。


A

B

C

1

func



2


if A1.value==null

return   "between("/A1.dim/","/A1.interval(1)/":"/A1.interval(2)/")"

3


else if ifa(A1.value)

return   string(A1.value)/".contain("/A1.dim/")"

4


else if   ifstring(A1.value)

return A1.dim/"==\""/A1.value/"\""

5


else

return   A1.dim/"=="/A1.value

6

=json(arg_json)

=date("2000-01-01")

7

=A6.aggregate.(~.func/"("/~.field/"):"/~.alias).concat@c()

8

=A6.group.(if(~=="job_id","job_num",~))

9

=A8.(if(~=="begin_yearmonth","begin_date\\100:begin_yearmonth",~)).concat@c()

10

=A6.aggregate.(field)

=A8.(if(~=="begin_yearmonth","begin_date",~))

11

=(A10|C10).id().concat@c()


12

=[]



13

for A6.slice

if   A13.dim=="begin_date" && A13.value!=null

>A13.value=int(interval@m(C6,A13.value)*100+day(A13.value))

14


else if   A13.dim=="begin_date" && A13.value==null

=A13.interval.(~=int(interval@m(C6,eval(~))*100+day(eval(~))))

15


else if   A13.dim=="job_id"

>A13.dim="job_num"

16



>A13.value=A13.value.(job.pos@b(~))

17


else if like(A13.dim,   "flag?")

>A13.value=int(A13.value)

18


=[func(A1,A13)]

>A12|=B18

19

=A6.group|A6.aggregate.(alias)

=A19(A19.pos("job_id"))="job(job_num):job_id"

20

=A19(A19.pos("begin_yearmonth"))="month@y(elapse@m(date(\""/C6/"\"),begin_yearmonth)):begin_yearmonth"

21

=A19(A19.pos("begin_date"))="elapse@m(B4,begin_date\\100)+(begin_date%100-1):begin_date"

22

=if(A6.slice.(dim).pos("begin_date")   &&   !A6.slice.(dim).pos("department_id"),"data/customer_begin_date.ctx","data/customer.ctx")

23

return A11,A7,A9,A12.concat("  &&"),A19.concat@c(),A22

A1-A21 不变。

A22:判断 arg_json 参数中的切片 slice,如果有 begin_date 维度并且没有 department_id 维度,那么就返回冗余排序组表文件名 customer_begin_date.ctx。否则还返回原组表文件名 customer.ctx。

A23:增加一个返回参数,返回 A22 中组表的文件名。

 

按照 SQL4 的需求改写 olap.dfx,将普通的分组计算改为冗余排序汇总计算。

olap.dfx 参数是 arg_table 和 arg_json。

SPL 代码修改如下:


A

1

=call(arg_table/".dfx",arg_json)

2

=file(A1(6)).open()

3

=A2.cursor@m(${A1(1)};${A1(4)};2)

4

=A3.groups(${A1(3)};${A1(2)})

5

=A4.new(${A1(5)})

6

return A5

只有 A2、A3 有变化,其他不变。

A2: 用 customer.dfx 返回的第六个参数,也就是文件名来打开组表文件。如果切片字段只有 begin_date,没有 departmen_id,这里实际执行的语句是:

=file("data/customer_begin_date.ctx").open()

否则就执行:

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

 

A3:实际执行的语句是:

=A2.cursor@m(department_id,job_num,begin_date\100:begin_yearmonth; sum(balance):sum,count(customer_id):count; between(begin_date,7201:7212));2)

 

Java 代码和上期相比只需要改变参数 arg_json 中的 slice,其他部分没有变化。Java 代码加上后台计算返回结果总的执行时间,和不做冗余排序的情况比较如下:

期数

单线程

二线程并行

备注

无冗余排序

2.3

1.5


冗余排序

0.2 秒

0.2 秒


通过上表的对比可以看出,冗余排序对计算性能有明显提升。

 

冗余排序的适用范围:

1、 在宽表列存的情况下,如果切片字段在排序字段比较靠后的位置,比较适合冗余排序。

例如:宽表 customer.ctx 的排序字段是 department_id,job_num,employee_id,begin_date,customer_id,这时候用 begin_date 做切片字段需要遍历 begin_date 列,计算速度较慢。而冗余排序组表 customer_begin_date.ctx 的排序字段是 begin_date,employee_id,department_id,job_num,customer_id,切片字段 begin_date 是组表排序的第一个维度,此时用 begin_date 做切片时不需要遍历 begin_date 列,计算速度会快很多。

2、 在满足上一条的情况下,切片时满足过滤条件的记录数越少,冗余排序的效果会更明显。例如:begin_date 的日期过滤条件如果是 2000 年到 2021 年,那么宽表中的数据基本都符合这个条件,这样的切片计算耗时和遍历也差不多了,冗余排序的效果就不会很明显。缩小日期范围时,如果切片条件能过滤掉 90% 以上的数据,冗余排序的效果会比较明显。

 

三、时间段预汇总

按照 SQL5 的需求调整网格参数 arg_json:

'{

            aggregate:

                        [

                                    {

                                                func:"sum",

                                                field:"balance",

                                                alias:"sum"

                                    },

                                    {

                                                func:"count",

                                                field:"customer_id",

                                                alias:"count"

                                    }

                        ],

            group:

                        [

                        ],

            slice:

                        [

                                    {

                                                dim:"begin_date",

                                                interval:[date("2002-01-01"),date("2020-12-31")],

                                                value:null

 

                                    }

                        ]

}

重新编写一个 customerDate.dfx,处理 arg_json,代码如下:


A

B

C

1

func



2


if A1.value==null

return "between("/A1.dim/","/A1.interval(1)/":"/A1.interval(2)/")"

3


else if ifa(A1.value)

return   string(A1.value)/".contain("/A1.dim/")"

4


else if   ifstring(A1.value)

return   A1.dim/"==\""/A1.value/"\""

5


else

return   A1.dim/"=="/A1.value

6

=json(arg_json)


7

=A6.aggregate.(~.func/"("/~.field/"):"/~.alias).concat@c()

8

=A6.group.(if(~=="job_id","job_num",~))

9

=A8.(if(~=="begin_yearmonth","begin_date\\100:begin_yearmonth",~)).concat@c()

10

=A6.aggregate.(field)

=A8.(if(~=="begin_yearmonth","begin_date",~))

11

=(A10|C10).id().concat@c()


12

=[]



13

for A6.slice

if   A13.dim=="job_id"

>A13.dim="job_num"

14



>A13.value=A13.value.(job.pos@b(~))

15


else if like(A13.dim,   "flag?")

>A13.value=int(A13.value)

16


=[func(A1,A13)]

>A12|=B16

17

=A6.group|A6.aggregate.(alias)

=A17(A17.pos("job_id"))="job(job_num):job_id"

18

=if(A6.slice.(dim).pos("begin_date")   &&   !A6.slice.(dim).pos("department_id"),"data/5customer_test1亿 _date.ctx","data/4customer_test1 亿.ctx")

19

return   A11,A7,A9,A12.concat("&&"),A17.concat@c(),A18






与前面的 customer.dfx 相比,去掉了将 begin_date 从日期型转为整数,最后再转换为日期型的代码,其他不变。

 

按照 SQL5 的需求改写 olap.dfx,将普通的分组计算改为时间段预汇总计算。

olap.dfx 参数是 arg_table(值为 customerDate)和 arg_json。

SPL 代码修改如下:


A

1

=call(arg_table/".dfx",arg_json)

2

=file(A1(6)).open()  

3

=A2.cgroups@m(${A1(3)};${A1(2)};${A1(4)};2)

4

=A3.new(${A1(5)})

5

return A4

A2,A3 之外的代码不变。

A2: 用 customerDate.dfx 返回的第六个参数,也就是文件名来打开组表文件。如果切片字段只有 begin_date,没有 departmen_id,这里实际执行的语句是:

=file("data/customerDate.ctx").open()

否则就执行:

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

A3:用预先生成的时间段预汇总 cube 文件对组表计算全表汇总,这里没有指定 cube 的名字,集算器会自动选择最适合的 cube,也就是 cu_date_year。

实际执行的语句是:

=A2.cgroups@m(;sum(balance):sum,count(customer_id):count;between(begin_date, date("2001-01-01"):date("2020-12-31"));2)

 

Java 代码和上期相比也没有变化。Java 代码加上后台计算返回结果总的执行时间,和不使用时间段预汇总比较如下:

计算方法

单线程

二线程并行

备注

无时间段预汇总

29

18


时间段预汇总

0.1 秒

0.1 秒


通过上表的对比可以看出,时间段预汇总对计算性能提升也非常明显。

组表文件和 cube 文件大小对比如下:

文件名

文件大小

备注

组表

2.4GB


cube 文件

16MB

cube文件

16MB

年月