多维分析后台实践 2:数据类型优化

【摘要】
用实例、分步骤,详细讲解多维分析(OLAP)的实现。点击了解多维分析后台实践 2:数据类型优化

实践目标

本期目标是练习将数据库读出的数据,尽可能转换为有利于性能优化的数据类型,例如:小整数和浮点数。

实践的步骤:

1、 准备基础宽表:修改上期的代码,完成数据类型优化存为组表文件。

2、 访问基础宽表:修改上期的代码,在传入参数保持不变的前提下,查询数据转换之后的组表文件,结果集也要返回原有的数据显示值。对于这个要求,SQL 是无法实现传入参数和结果集的转换的,所以访问宽表的代码以 SPL 为例。

 

本期样例宽表不变,依然为 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')

准备宽表

一、数值整数化

在 customer 表中有些字段本身就是整数,比如:CUSTOMER_ID、EMPLOYEE_ID、DEPARTMENT_ID。

处理方法:

l  如果从数据库中导出的是整型,就可以直接存储到组表中。

l  如果从数据库中导出的不是整型,要用类型转换函数强制转换为整型。

l  要注意尽量让整数值小于 65536,这样性能最好。如果原字段值被人为的转换成较大的整数,例如:所有的数值都加上了一个 100000,变成 100001、100002…,就要去掉前面的 1。

二、字符串整数化

FLAG1 到 FLAG8 是字符串,但是存储的依然是整型数据,可以用类型转换函数转为整型。

JOB_ID 字段也是字符串,取值是 jobs 维表的主键,属于枚举类型。我们可以用 jobs 表中的序号代替 JOB_ID 字段,实现整数化。

jobs 表结构和样例数据如下:

..

处理方法:

l  取出 jobs 中的 JOB_ID,排好序后构成一个序列 job。customer 宽表中增加 JOB_NUM 字段存储 JOB_ID 在序列 job 中的序号。

三、日期整数化

大多数情况下,日期型数据只是用来比较,并不需要计算间隔,所以也可以用小整数来存储。在多维分析计算中,按照年、月来计算的情况比较常见。小整数化之后的日期,要求能很方便的把年、月拆分出来。

处理方法:

l  我们可以计算出 BEGIN_DATE 字段值与一个日期起点的间隔月数,乘以 100 后加上 BEGIN_DATE 的日值,来代替日期型数据存入组表。起点日期根据日期数据的特征来确定,值越大越好。

例如:我们发现所有的 BEGIN_DATE 都在 2000 年之后,则可以确定日期起点为 2000-01-01。

确定日期起点后,就可以转化 customer 宽表中的 BEGIN_DATE 字段值了。例如:BEGIN_DATE 为 2010-11-20,先计算出和 2000-01-01 相差的整月数是 130,乘以 100 后加上日值 20 即可得到小整数 13020。

以 2000-01-01 为日期起点,BEGIN_DATE 小于 2050 年时,整数化之后的值都小于 65536。可以看到,在业务数据允许的前提下,日期起点尽量晚,可以更大程度避免出现宽表中的日期超出小整数范围的情况。

四、无法整数化的情况

必须用字符串表示的字段,如 FIRST_NAME、JOB_TITLE 等;

必须用浮点数表示的字段,如金额、折扣率等有小数部分的字段;

必须用字符串加整数一起表示的字段,如国际电话号码等。

处理方法:

l  保持字段原值不动。

 

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


A

B

1

=connect@l("oracle")

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

2

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

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

3

=A2.(job_id)

=date("2000-01-01")

4

=B1.new(int(customer_id):customer_id,first_name,last_name,phone_number,int(interval@m(B3,begin_date)*100+day(begin_date)):begin_date,A3.pos@b(job_id):job_num,job_title,float(balance):balance,int(employee_id):employee_id,int(department_id):department_id,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)

5

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

6

=A5.append(A4)

>A5.close(),A1.close()

A1:连接预先配置好的数据库 oracle,@l 是指取出字段名为小写。注意这里是小写字母L

B1:建立数据库游标,准备取出 customer 表的数据。customer 是事实表,实际应用中一般都比较大,所以用游标方式,避免内存溢出。游标的 @d 选项是将 oracle 的 numeric 型数据转换成 double 型数据,而非 decimal 型数据。decimal 型数据在 java 中的性能较差。

A2:从数据库中读 jobs 表,只读取 JOB_ID 字段并排序。jobs 是维表,一般都比较小,所以直接读入到内存中。

B2:将 A2 的数据存储成集文件,待后面使用。

A3:将 A2 转化为序列。

B3:定义日期 2000-01-01。

A4:用 new 函数定义三种计算。

1、  CUSTOMER_ID 等确定是整数的数值,从 double 或者 string 转换为 int。方法是直接用 int 函数做类型转换。注意 int 不能大于 2147483647,对于数据量超过这个数值的事实表,序号主键要用 long 型。

2、  将 JOB_ID 从字符串转化为整数,提高计算性能。方法是用 pos 函数找到 job_id 在 A3 中的序号,定义为 JOB_NUM 字段。

3、  用 interval 计算 begin_date 和 2000-01-01 之间相差的整月数,乘以 100 加上 begin_date 的日值,用 int 转换为整数存储为新的 begin_date。

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

A6:边计算游标 A4,边输出到组表文件中。

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

 

数据量为一千万,导出组表文件约 344MB。和第一期未做数据类型优化的文件比较如下:

期数

文件大小

说明

备注

第一期

3.5GB

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


第二期

3.0GB

完成数据类型优化


 

从上表可以看出,完成数据类型优化之后,文件大小减少了 12%(49M)。文件变小,能减少磁盘读取数据量,有效提高性能。

访问宽表

如上所述,宽表的很多字段已经优化转换,没有办法用原来的 SQL 进行查询了。我们采用执行脚本的方式,提交过滤条件、分组字段等参数,后台将参数值转换成优化后的数据类型,再对组表进行计算。这样做,可以保证通用多维分析前端传入的参数保持不变。最后,计算结果也需要转换为对应的显示值。

例如:传入的参数 flag1='1',需要转换为 flag1=1;计算结果中的 job_num 和 begin_date,还要从整数转换为字符串 job_id 和日期。

 

第一步:编写 init.dfx,实现全局变量的加载。

为了实现这个计算,要先在节点服务器主目录中编写 init.dfx 文件,预先加载全局变量 job,用于后续的转换计算。

init.dfx 代码如下:


A

B

1

=file("data/job.btx").import@ib()

=env(job,A1)

A1:取出集文件中的数据,@i 表示只有一列时读成序列。

B1:存入全局变量 job。

写好的 init.dfx 要放入节点机主目录,启动或重启节点机时会被自动调用。

 

第二步:改写 customer.dfx。

改写目标是:增加代码将参数值转换成优化后的数据类型,并且增加代码准备结果集转换为显示值的字符串。

参数设置窗口如下,和第一期完全一致:

..

 

参数值 arg_json 样例也不变:

{

       aggregate:

              [

                     {

                            func:"sum",

                            field:"balance",

                            alias:"sum"

                     },

                     {

                            func:"count",

                            field:"customer_id",

                            alias:"count"

                     }

              ],

       group:

              [

                     "department_id",

                     "job_id",

                     "begin_yearmonth"

              ],

       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:"flag8",

                            value:"1"

                     }

              ]

}

customter.dfx 的返回结果是五个字符串,除了上期的四个字符串:“本次计算所用到的字段名”、聚合表达式、分组表达式和切片(过滤条件)表达式,还要增加一个结果集显示值转换表达式。

SPL 代码修改如下:


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

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

A1 到 C5 是子程序,在调用的时候才会执行。为了方便说明,我们按照执行的顺序来讲解。

A6:将 arg_json 解析成序表。解析的结果是多层嵌套的序表,如下图:

..

其中的 aggregate 为:

..

其中的 group 为:

..

其中的 slice 为:

..

C6:定义起点日期 2000-01-01,用于参数和结果中的日期值转换。

A7:先将 aggregate 计算成冒号相连的字符串序列,再将序列用逗号连接成一个字符串:sum(balance):sum,count(customer_id):count,也就是聚合表达式。

A8:将 group 中的 job_id 替换为 job_num。

A9:将 A8 中的 begin_yearmonth 替换成表达式 begin_date\100:begin_yearmonth。再把 A8 成员用逗号连接成一个字符串 department_id,job_num,begin_date\100:begin_yearmonth,也就是分组表达式。

A10:取得 aggregate 中的 field 字段,也就是聚合表达式用到的所有字段名。

C10:将 group 中的 begin_yearmonth 替换成 begin_date,结果就是分组表达式所用到的所有字段名。

A11:将 A10、C10 合并后,用逗号连接成字符串,求得本次计算所需要的所有字段名:balance,begin_date,customer_id,department_id,job_num。

A12:定义一个空序列,准备存放切片(过滤条件)表达式序列。

A13:循环计算 slice,循环体是 B13 到 C18。其中:B13 到 C17 是对 slice 的 value 或者 interval 做性能优化的转换。

B13:如果 A13(当前 slice)的 dim 是 begin_date,并且 value 不为空,也就是 begin_date 等于指定日期的情况,例如:begin_date==date("2010-11-01")。此时 C13 计算出 date("2010-11-01") 的转换后的整数值,赋值给 A13 的 value。

B14:如果 A13 的 dim 是 begin_date,并且 value 为空,也就是 begin_date 在两个日期之间的情况,例如:begin_date 在 date("2002-01-01") 和 date("2020-12-31") 之间。此时 C14 计算出两个日期的转换后整数值,赋值给 A13 的 interval 的两个成员。

B15:如果 A13 的 dim 是 job_id,也就是 job_id 取枚举值的情况。例如:["AD_VP","FI_MGR","AC_MGR","SA_MAN","SA_REP"].contain(job_id)。此时 C15 要将 A13 的 dim 改为 job_num。C16 要将 A13 的 value 枚举值转换为在全局变量 job 序列中的位置,也就是 job_num 整数序列,例如:[5,7,2,15,16]。

B17:如果 A13 的 dim 是 flag1、flag2…flag8,也就是标志位等于 "1" 或者 "0" 的情况。此时 C17 要将 A13 的 value 值从字符串转化为整数。

B18:用 B13 到 C17 对 slice 的 value 或者 interval 做性能优化的转换的结果作为参数,调用子程序 A1。

子程序 A1(B2 到 B5),和第一篇的 func 代码相同,不再赘述。

C18:func A1 的返回结果追加到 A12 中。继续 A13 中的循环,到循环结束,就准备好了切片表达式的序列。

A19:从这里开始准备结果集显示值转换的表达式。将 A6.group 和 A6.aggregate.alias 序列合并,如下图:

..

C19:将 A19 中的 job_id 替换成转换语句。语句的作用是:将结果集中的 job_num 转换为 job_id。

A20:将 A19 中的 begin_yearmonth,替换为转换语句,作用是:将分组字段中的整数值 begin_yearmonth 从整数转化为 yyyymm。

A21:将 A19 中的 begin_date,替换为转换语句,作用是:将分组字段中的整数化日期值转换为日期型。此时 A19 就是准备好的结果集显示值转换表达式:

..

A13:返回 A10,A7,A8,A11.concat("&&"),A19.concat@c(),依次是:

本次计算用到的字段名:balance,begin_date,customer_id,department_id,job_num

聚合表达式:sum(balance):sum,count(customer_id):count

分组表达式:department_id,job_num,begin_date\100:begin_yearmonth

切片表达式:

[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

结果集显示值转换表达式:

department_id,job(job_num):job_id,month@y(elapse@m(date("2000-01-01"),begin_yearmonth)):begin_yearmonth,sum,count

 

第三步:改写 olap.dfx。

按照数据类型优化要求改写 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.cursor@m(${A1(1)};${A1(4)};2)

4

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

5

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

6

return A5

代码增加了A5,其他不变:

A5:对 A4 的结果做显示值的转换,实际执行的语句是:

=A4.new(department_id,job(job_num):job_id,month@y(elapse@m(date("2000-01-01"),begin_yearmonth)):begin_yearmonth,sum,count)。

执行结果如下图:

..

A6:返回结果集 A5。

 

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 是 dfx 的文件名

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

st.setObject(1, "customer");

st.setObject(2, "{aggregate: [{func:\"sum\",field:\"balance\",alias:\"sum\"},{func:\"count\",field:\"customer_id\",alias:\"count\"}],group:[\"department_id\",\"job_id\",\"begin_yearmonth\"],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:\"flag8\",value:\"1\"}]}");//arg_json

// 执行存储过程

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 代码加上后台计算返回结果总的执行时间,和第一期比较如下:

期数

单线程

二线程并行

备注

第一期

84

42


第二期

31 秒

14 秒


如上期所述,表中的执行时间硬件配置相关,其绝对数值并不重要。重要的是,通过上表的对比可以看出,数据类型优化有效提高了计算性能。

以下是广告时间

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



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