SPL 复组表

OLAP 业务的数据一般不会出现大量频繁地更新动作。数据变动主要是:1、新增数据的追加,2、数据插入、修改和删除。

SPL 提供了复组表,可以有效缩短数据变动的处理时间,同时保证数据计算的性能。

复组表由多个组表文件构成的,这些组表称为复组表的分表,每个分表都有自己的分表号。

1. 追加型复组表

为了提高性能,SPL 要将数据按照某些字段有序存储。而定期产生的新增数据不一定总能有序地追加到原有数据后面。

比如订单表存储了大量订单数据,包含客户号 cid、订单日期 odate、订单号 oid、雇员号 eid 和订单金额 amt。为了提高性能,订单表可能要按照维字段 cid、odate 有序存储。每天产生的新订单 cid 字段通常还是同一批客户编号,不能直接在历史数据的末尾追加,否则会破坏 cid 的有序性。

如果每天都将历史数据和当天新数据重新排序或者有序归并,耗时过长。SPL 采用复组表可以方便地解决这个问题。

1.1. 存储结构

以订单表为例,复组表 orders 由 2 个分表组成,分表 1 存储历史数据,分表 2 存储当月数据。每天追加数据时,新数据只和分表 2 归并。分表 2 不断归并新数据一个月后,每月 1 日再和分表 1 做全量归并。

需要注意的是,分表号必须是整数,而且必须递增。

orders 的存储结构大致是这样的:

..

1.2. 初始化

在系统初始化的时候,复组表中的数据可能会来自数据库、文本文件或者其他数据源。假设 orders 的数据来自于文本文件 orders.txt,新建复组表并写入历史数据的代码大致是这样的:


A

1

=file("orders.txt").cursor@t(cid,odate,oid,eid,amt).sortx(cid,odate)

2

=file("orders.ctx":to(2)).create(#cid,#odate,oid,eid,amt;if(month@y(odate)==month@y(now()),2,1))

3

=A2.append@x(A1)

A1 建立文本文件的游标,并按照需要排序。

A2 创建的复组表由 2 个组表文件构成,每个文件会有个编号,称为分表号。同时给出一个计算分表号的表达式 x,这里是 if(month@y(odate)==month@y(now()),2,1)。

A3使用append@x 追加数据时,SPL 会针对每条追加记录计算分表表达式x,根据结果把历史数据追加到分表1中,当月数据追加到分表2中。

复组表生成后,数据会分别存储在各个分表中。例如 4 月份时,第二个分表 2.orders.ctx 中存储的数据大致是下图这样:

..

1.3. 实施计算

用追加型复组表计算时,代码写法和单组表几乎完全一样。比如按照客户分组统计 2023 年全年交易额的代码大致是这样的:


A

1

=file("orders.ctx":to(2)).open().cursor(cid,amt;year(odate)==2023)

2

=A1.group(cid;~.sum(amt))

A1 打开复组表时可以选择分表,这里选择了 2 个分表。

A2 做有序分组计算。分表 1 和分表 2 中的数据都对 cid 有序,两个文件有序归并后再做分组计算。性能比单个组表差一些,但是也很快。

1.4. 追加数据

orders_new.txt 中的每天新增订单数据归并到 orders 的分表 2 中,每月 1 日将分表 2 中的数据合并到分表 1 中。这样,分表 2 中最多只有一个月的数据,每天归并的耗时不会太久。每个月做一次全量归并,耗时长一些也可以接受。

代码大致是这样的:


A

B

1

=file("orders_new.txt").cursor@t(cid,odate,oid,eid,amt).sortx(cid,odate)

2

=file("orders.ctx":to(2))

3

if day(now())==1

=A2.reset@y(A2,if(month@y(odate)==month@y(now()),2,1);A1)

4

else

=file("orders.ctx":2).open()

5


=B4.append@m(A1)

6


>B4.close()

A1:当天新增数据按照维字段排序。

A3 判断如果是当月 1 日,B3 将复组表 orders 分表 1、2 和新增数据一起合并到 orders 中。合并后,上月数据在分表 1 中,本月 1 日数据在分表 2 中。

A3 判断如果不是当月 1 日,B4、B5、B6 将新增数据有序归并到分表 2 中。

用复组表 orders 进行计算的代码不变。

2. 追加型复组表 - 分区多表

前面介绍的追加型复组表由两个分表组成,比较适合中等规模数据量。当数据量非常大的时候,可以考虑用分区多表的方式解决,不仅能提升数据追加性能,还能很快速地批量删除过期历史数据。

2.1. 存储结构

仍以订单表为例,可以将历史数据也分成多个分表,每月一个分表,用年月 yyyyMM 作为分表号。比如 202402、…、202405 这些分表分别存储 24 年 2 月开始到 5 月结束的数据。

分区多表的存储结构大致是下图这样。为了区别前面的订单表,分区多表的订单表命名为 ordersN。

..

同时,要以全局变量 beginMonth、endMonth 表示订单数据开始和结束的月份 202402,202405。

从这个例子可以看到,分表号不一定是从 1 开始的,只要保持递增就可以。

2.2. 初始化

在系统初始化的时候,假设 ordersN 的数据来自于文本文件 orders.txt,新建复组表并写入历史数据的代码大致是这样的:


A

B

1

>env(beginMonth,202403),env(endMonth,202405)

2

=file("orders.txt").cursor@t(cid,odate,oid,eid,amt).sortx(cid,odate)

3

=file("ordersN.ctx":to(beginMonth,endMonth)).create@y(#cid,#odate,oid,eid,amt;month@y(odate))

4

=A3.append@x(A2)

>A3.close()

A1:确定开始月份和结束月份,这两个值可以作为初始化参数传入,也可以计算出历史数据 odate 字段年月的最大、最小值。初始化时,endMonth 一般都是当前月。

A2 将原数据按照 cid 和 odate 排序。

A3 建立一个复组表,分表号从 beginMonth 到 endMonth,分表表达式是计算 odate 的年月值。

A4 将排好序的原数据写入复组表,订单数据根据分表表达式的计算结果分别写入不同的分表。

以分表 202404 为例,数据是这样的:

..

2.3. 实施计算

用追加型复组表计算时,代码写法和单组表几乎完全一样。比如按照客户分组统计 2024 年全部交易额的代码大致是这样的:


A

1

=file("orders.ctx":to(beginMonth,endMonth)).open().cursor(cid,amt;year(odate)==2024)

2

=A1.group(cid;~.sum(amt))

A1 打开复组表时可以选择分表,这里选择了所有分表。

A2 做有序分组计算。每个分表中的数据都对 cid 有序,符合日期条件的多个文件有序归并后再做分组计算。性能比单个组表差一些,但是也很快。

对于批量删除过期数据的情况,只要不让过期数据的分表参加计算就可以了。比如 2 月份的数据过期了,只要将 beginMonth 从 2 改成 3 就可以了。

实际上,复组表分表号也可以不连续。比如仅计算 3 月和 5 月的数据,A1 就可以改为 =file("orders.ctx":[3,5]).open().cursor(cid,amt)。只要保持分表号递增就可以了。也就是说,生成用的复组表对象和计算用的未必是相同分表构成的,可以根据需要来决定哪些分表参与计算。

当然,前提条件是要判断 beginMonth<=3 && 5<=endMonth。

2.4. 追加数据

orders_new.txt 中的每天新增订单数据归并到 ordersN 的最后一个分表中。每月 1 日新建一个分表,将新增数据追加到新增分表就可以了。这样,可以避免大量历史数据和新增数据的归并。

代码大致是这样的:


A

B

1

=file("orders_new.txt").cursor@t(cid,odate,oid,eid,amt).sortx(cid,odate)

2

if day(now())==1

>endMonth=month@y(now())

3


=file("ordersN.ctx":[beginMonth]).open()

4


=B3.create@y(file("ordersN.ctx":[endMonth]))

5


=B4.append@i(A1)

6


>B3.close(),B4.close()

7

else

=file("ordersN.ctx":endMonth).open()

8


=B7.append@m(A1)

9


>B7.close()

A1 将新增数据按照 cid、odate 排序。

A2 判断如果是当月第 1 天,则执行 B2 到 B6。

B2 将结束月改为当月。B4 新建一个当月分表。新增数据添加到新建分表中。

A2 判断如果不是当月第 1 天,则执行 B7 到 B9。

B7 打开结束月分表。B8 将新增数据有序归并到结束月。

追加数据之后,计算代码不需要改变。

3. 更新型复组表

追加型复组表不能用于对历史数据更新(插入、删除、修改)的情况。

如果更新的数据量很小,可以使用单组表的补区方式,详细介绍参见【性能优化】2.6 [外存数据集] 数据更新及复组表

当每次更新数据量都比较大时,补区可能出现装不下的情况,要采用更新型复组表来实现。更新型复组表必须有主键。

我们先看只有插入、修改,没有删除的情况。

3.1. 存储结构

更新型复组表可以用一个分表存储历史数据,另一个分表存储更新数据。

假设用分表 1 存储历史数据,分表 2 存储当月更新数据,更新型订单表存储结构大致是下图这样。为了区别追加型订单表,命名为 ordersModify。

..每天更新数据时,新数据只和分表 2 合并。每月 1 日,分表 2 再和分表 1 做全量合并。

3.2. 初始化

下面来看初始化代码的写法。订单表主键是客户号 cid、订单日期 odate、订单号 oid。客户姓名 cname 和所在城市 ccity 也被冗余到了订单表中。

注意还要新增一个记录更新时间字段 mdate。订单数据大致是这样的:

..

系统初始化时,要先将历史数据写入分表 1,代码大致是这样的:


A

B

1

=file("orders.txt").cursor@t(cid,odate,oid,cname,ccity,eid,amt).sortx(cid,odate,oid)

2

=file("ordersModify.ctx":[1]).create@y(#cid,#odate,#oid,cname,ccity,eid,amt;1)

3

=A2.append@i(A1)

>A3.close()

A2 创建分表 1,A3 写入历史数据。

3.3. 数据更新 - 无删除

假设从数据源过来的更新数据存放在 orders_new.txt。2024 年 4 月 30 日的更新数据大致是这样的:

..

更新数据往往是按照更新时间升序排序的,而且可能会对同样的主键做多次修改。

比如上图中,index 是 5 的记录,要对主键是 [2、2024-04-29、101] 的订单数据做修改,将 ccity 改成 San Francisco。index 是 11 的记录,又改成了 Atlanta。

2024 年 5 月 1 日更新时,更新数据是这样:

..

其中,index 是 10 的记录先插入了主键是 [3、2024-05-01、103] 的订单数据。然后 index 是 11 和 13 的记录,又两次修改这个主键记录,将 ccity 改成 Chicago 和 Houston。

对 ordersModify 进行更新的代码大致是这样的:


A

B

1

=file("orders_new.txt").cursor@t(cid,odate,oid,eid,amt).sortx(cid,odate,oid,mdate)

2

if day(now())==1

=file("ordersModify.ctx":[1,2]).reset@wy(file("ordersModifyNew.ctx":[1]))

3


=movefile@y("1.ordersModifyNew.ctx","1.ordersModify.ctx")

4


=file("ordersModify.ctx":1).open()

5


=B5.create@y(file("ordersModify.ctx":[2]))

6


>B6.close(),B5.close()

7

=file("ordersModify.ctx":[2]).reset@wy(;A1)

A1 是源数据传过来的更新数据,注意:一定要按照主键、更新时间升序排序

A2 判断是不是当月 1 日,如果是,执行 B2 到 B6。否则跳转到 A7。

B2 将分区 1、2 合并到新复组表 ordersModifyNew.ctx 的分区 1 中。reset 的 w 选项表示要自动处理分区 1、2 出现的相同主键,保留 mdate 最大的记录。

B3 将新复组表分区 1 改名,覆盖原复组表分区 1。

B4 到 B6 清空、新建分区 2。

A7 将更新数据游标合并到分区 2 中。reset 的 w 选项表示要自动处理分区 2 和更新游标中重复出现的相同主键,保留 mdate 时间最大的记录。

3.4. 实施计算

对 ordersModify 查询、计算的代码大致是这样的:


A

B

1

=file("ordersModify.ctx":[1,2]).open().cursor@wx().fetch(100)

2

=file("ordersModify.ctx":[1,2]).open().cursor@wx()

3

=A2.group(cid;~.cname,~.sum(amt))

A1:对 ordersModify 表的分表 1、2 进行查询,cursor 的 @w 选项表示要处理分区 1、2 中出现的相同主键,以分区 2 中为准。

5 月 1 日更新后,A1 的计算结果是这样:

..

主键是 [2、2024-04-29、101] 的订单数据,ccity 已经改成 Alanta。

主键是 [3、2024-04-29、102] 的订单数据,ccity 已经改成 New York。

主键是 [3、2024-05-01、103] 的订单数据已经插入到适当的位置,ccity 已经改成 New Huoston。。

A3 中,可以按照 cid 有序分组、汇总。

3.5. 有删除的情况

增加删除标识字段

要处理删除的情况,更新型复组表必须增加一个删除标识字段。删除标识字段是维字段之后的第一个字段,其值为 true 表示删除数据、为 false 表示修改数据、为 null 表示插入。

比如订单数据增加删除标识字段 mflag 后,大致是这样的:

..

系统初始化

系统初始化时,将历史数据加入分表 1 的代码和无删除的情况略有不同,大致是这样的:


A

B

1

=file("orders.txt").cursor@t(cid,odate,oid,mflag,mdate,cname,ccity,eid,amt).sortx(cid,odate,oid)

2

=file("ordersModify.ctx":[1]).create@dy(#cid,#odate,#oid,mflag,mdate,cname,ccity,eid,amt;1)

3

=A2.append@i(A1)

>A3.close()

A2 创建分表 1,create 加 d 选项表示主键后面第一个字段 mflag 是删除标识,SPL 在合并数据的时候,会自动完成数据的插入、删除和修改。

每天从数据源过来的订单更新数据 orders_new.txt 也要包含删除标记字段 mflag。比如 4 月 30 日的更新数据是这样的:

..

更新数据是按照修改时间 mdate 有序的,也存在订单主键重复出现的情况,比如:

index 是 2 的记录表示要修改主键是 [2、2024-04-29、101] 的订单数据。

index 是 4 的记录表示要删除主键是 [3、2024-04-29、102] 的订单数据。

index 是 11 的记录表示要删除主键是 [2、2024-04-29、101] 的订单数据。

其他都是追加的数据。

更新和实施计算

有删除的更新、实施计算代码,和无删除的情况是一样的。

删除标识的三种状态

前面说过删除标识有三种状态,那么为什么要用 false 和 null 来区分修改和插入呢?我们以 4 月 30 日的更新数据为例,来说明原因。

这一天并不是当月 1 日,只要将更新数据合并到分表 2 中就可以了。和无删除时一样,也要用 reset@w 函数自动合并计算。

上图中,更新数据 index 为 11 的记录主键是 [2、2024-04-29、101],删除标识是 true,表示要删除这个主键。

由于分表 1 不参加计算,reset@w 无法判断其中有没有这个主键的记录。这时候,要在更新数据中找 mdate 更靠前的记录,来决定这个删除操作如何处理:

1、图中,前面的更新数据 index 为 2 的记录主键是 [2、2024-04-29、101],且删除标识是 false,表示修改。由此可推断出分表 1 中有这个主键,所以这个删除记录要保留,前面的修改要舍弃。

2、假设前面有主键是 [2、2024-04-29、101] 的更新记录,但删除标识是 null,表示插入。由此可推断出分表 1 中没有这个主键,所以这个删除记录和前面的插入记录都要舍弃。

3、假设前面没有主键是 [2、2024-04-29、101] 的更新记录。由此可推断出分表 1 中有这个主键,所以删除记录要保留。

从这个例子可以看出,删除标识必须有三个状态:true、false 和 null,否则 reset@w 无法自动处理有删除的各种情况。

4. 更新型复组表 - 分区多表

有些场景中,历史数据量特别大,而且更新又相对比较频繁。

这种情况下,更新也可以采用分区多表的方式。比如:每天的更新都产生一个新的分表,适当的时候再合并、重整。

4.1. 存储结构

采用每天一个分表时,分区多表的存储结构大致是这样的:

..

分表 1 中存放历史数据。

4 月 30 日更新的数据存入新建的分表 20240430。5 月 1 日、2 日的更新数据存入对应日期的分表。

4.2. 初始化

下面来看初始化代码的写法,订单数据大致是这样的:

..

系统初始化时,要先将历史数据加入分表 1,代码大致是这样的:


A

B

1

=file("orders.txt").cursor@t(cid,odate,oid,mflag,mdate,cname,ccity,eid,amt).sortx(cid,odate,oid)

2

=file("ordersModify.ctx":[1]).create@dy(#cid,#odate,#oid,mflag,mdate,cname,ccity,eid,amt;1)

3

=A2.append@i(A1)

>A3.close()

A2 创建分表 1,这里 create 加了参数 d,如果是没有删除的情况,可以不加这个参数。

A3 写入历史数据。

4.3. 数据更新

多区分表的更新,也是用 reset@w 来实现。比如 2024 年 5 月 2 日更新数据是这样处理的:


A

1

=int(string(now(),"yyyyMMdd"))

2

=file("orders_new.txt").cursor@t().sortx(cid,odate,oid,mdate)

3

=file("ordersModify.ctx":1).open()

4

=A3.create@y(file("ordersModify.ctx":[A1]))

5

>A4.close(),A3.close()

6

=file("ordersModify.ctx":[A1]).reset@wy(;A2)

A1 计算出分表号是 20240502。将 A2 更新数据按照主键和更新日期时间 mdate 排序。

A3 到 A5,用分表 1 新建分表 20240502。A6 将更新数据合并到分表 20240502 中。

4.4. 分表合并和实施计算

随着时间推移,分表会越来越多,要在合适的时候合并分表。合并的方式有两种:

方式 1:全合并。

将所有分表合并到分表 1 中。大致是下图这样:

..

方式 1 的代码大致是这样的:


A

1

=file("ordersModify.ctx":[1,20240430,20240501,20240501]).reset@wy(file("ordersModifyNew.ctx":[1]))

2

=movefile@y("1.ordersModifyNew.ctx","1.ordersModify.ctx")

A1,所有分表合并到一个新的复组表。A2 用新的复组表第 1 分区,覆盖原复组表第 1 分区。

方式 1 合并后,实施计算的代码大致是这样的:


A

1

=file("ordersModify.ctx":[1]).open().cursor@x().fetch(100)

2

=file("ordersModify.ctx":[1]).open().cursor@x()


=A2.group(cid;~.cname,~.sum(amt))

方式 2:部分合并。

任意选择几个更新分表合并,也可以达到减少分表的目的。比如合并分表 20240430 和 20240501 为新的分表 2,大致是下图这样。

..

或者也可以只合并 20240501 和 20240502 为新的 20240502,只要保持分表号递增就可以了。

方式 2 的代码大致是这样的:


A

1

=file("ordersModify.ctx":[20240430,20240501]).reset@wy(file("ordersModifyNew.ctx":[2]))

2

=movefile@y("2.ordersModifyNew.ctx","2.ordersModify.ctx")

A1,需要的分表合并到一个新的复组表。A2 用新的复组表第 2 分区,覆盖原复组表第 2 分区。

方式 2 合并后,实施计算的代码大致是这样的:


A

1

=file("ordersModify.ctx":[1,2,20240502]).open().cursor@x().fetch(100)

2

=file("ordersModify.ctx":[1,2,20240502]).open().cursor@x()


=A2.group(cid;~.cname,~.sum(amt))

注意:实施计算的时候,分表号一定要包含当前所有的有效分表。比如 A1 中如果写成 [1, 20240502] 就会漏掉分表 2 中的更新,结果会出错。

这一点和追加型复组表是不同的。追加型多表分区时,复组表分表号可以不连续,可以根据需要选择分表号,比如:过滤条件只需要计算 3、5 月数据,那么 4 月份分表是可以跳过的。

5. 复组表应用例程

本文主要讲解复组表的原理和基本应用。前面介绍的追加和更新都是在业务计算暂停的时候进行的,比如每天下班以后不对外营业的时候进行计算。这种方式的追加和更新可以称为冷模式。

在实际应用中,复组表还可以用于热模式。也就是在追加和更新的同时,还可以进行业务计算。

具体的做法要复杂一些,参见数据维护例程