【摘要】
无需构建前置数据 (仓) 库，用更低的成本就可以轻松应对项目中各类大数据集报表查询的难题！让我们一起去乾学院看个究竟吧：基于文件系统实现可追加的数据集市！

一 问题背景

绝大多数的应用系统中，一开始数据的存储和计算基本都是由数据库来完成的，同时服务于业务交易和报表查询；不过在经过几年信息化建设和数据积累后，常常都会遇到数据库压力变大，从而导致性能瓶颈的问题。

究其原因，往往发现针对历史数据查询的报表在其中占了很大比重。进一步分析会发现，这类报表通常都有如下特征：

1、数据变化小：供查询的历史数据几乎不再发生变化；

2、数据量大：数据量随时间不断增加；

由于大多数数据库的JDBC性能都很低下(JDBC取数过程要做数据对象转换，比从文件中读取数据会慢一个数量级)，如果数据始终存放在数据库中，当涉及数据量较大或并发较多的时候，报表的性能会急剧下降，进一步还会严重影响相关的业务操作，如市场营销、数据整理再汇报等。

针对这一问题，常见的解决方案是在生产库和应用之间再增加一个前置数据库，利用ETL工具定时从生产库中提取数据，清洗后再导入到前置数据库中，所有的历史报表查询都基于前置数据库，从而和生产库分离，缓解生产库压力。

不过这种方案增加了很多不必要的成本、多余的组件和工作量，同时也加大了后期的管理和维护难度；更为重要的是，当数据量比较大时，报表查询还是很慢，因为上面已经提到过的根本问题并没有得到解决，大多数数据库的IO性能远低于文件系统，而报表性能又严重依赖于数据库取数环节，也就是说，没能从根子上解决问题。

二 解决思路

要从根子上解决问题，我们可以假设如果文件拥有计算能力的话，将这些变化不大的历史数据搬出数据库，采用文件系统存储，而不是前置数据库，那么将可能获得比数据库高得多的IO性能，这样不仅能够解决大数据量报表查询慢的难题，我们还将获得如下这些好处：

1、管理方便；文件天然支持多级目录，而且复制、转移、拆分都比数据库简单、高效得多，这样，用户就可以按照业务模块、时间顺序等规则分类管理数据，在应用程序下线时，也可以按照目录删除该应用对应的数据。数据管理因此变得简单清晰，工作量显著降低。

2、成本低廉；既然是文件，那就可以简单地存储在廉价硬盘中，无需购买昂贵的数据库专用软硬件。

3、降低数据库扩容压力；数据库吞吐负担降低，就可以显著推迟扩容临界点的到来，数据库可以继续服役，也可以节省大量的扩容成本。

4、资源利用率高；用文件来存储数据并非要抛弃数据库，相反的，文件应当只存储安全要求不高、但数据量巨大的外围数据以及库外文件，而数据库仍然存储核心数据。如此一来，文件存储和数据库存储各司其职，资源利用率显著提高。

那么，如何才能有效地为文件赋予计算能力呢？下面将要介绍的润乾集算器，就是这样一款利器，通过集算器，可以实现复杂计算与报表展现的分离，其内置的集算引擎可以使文件拥有计算能力，轻松应对各种疑难杂症。下图显示了常规情况和引入集算器后的报表系统结构对比，应该说，引入集算器后，整个体系架构变得更加清新与合理了：

三 场景说明

接下来，我们通过一个典型的场景来说明集算器的作用和用法：

A表“商品销售明细”的数据量上亿，其中字段areaid与B表“区域表”的主键id关联。A表称为事实表，B表称为维表。A表中与B表主键关联的字段称为A指向B的外键，B也称为A的外键表。外键表是多对一的关系。如下图示：

下面，我们就通过制作“各区域销售员每日销售额日增长率报表”，来看一下集算器是如何利用文件实现数据外置，从而提升报表查询效率的。报表最终的展示效果如下图：

在这张报表中，根据选择开始日期、结束日期进行查询，报表先按照区域名称、销售员代码、销售日进行分组，统计每个销售员每天的销售额，以及每个销售员每天销售额的日增长率（算法为“（当日销售额-上一日的销售额）/上一日的销售额”）。报表上部的查询按钮是报表工具提供的“参数模板”功能，具体做法参见教程，这里不再赘述。

3.1设计数据存储组织

在利用文件系统存储数据的诸多优势之前，我们首先应该先定义文件的目录存储结构：

历史数据的特征是交易成型后数据落地不再变化，而且数据量庞大，由此我们可以将每年的数据按照业务模块、月份等规则进行划分，即每个月份的数据存一份集文件(集文件利用集算器提供的压缩格式，具有更好IO性能)。目录结构就是：/业务模块/数据明细表/年月文件名，如下图所示：

同时，我们还需要设定每天凌晨时段定时执行数据同步脚本，把前一天的数据追加到当月集文件中；而在每月1号，脚本还会根据规则自动生成一个新的以年月命名的集文件。

3.2同步数据

3.2.1同步历史数据到文件

先把2017年1到10月的历史数据按不同月份搬出来(假定已有10个月的历史数据)，集算器的SPL脚本如下：

	A	B	C
1	=connect("demo")
2	=10.("SELECT   * FROM sdrpts WHERE filedate>='2017-"/~/"-01'AND   filedate<'2017-"/(~+1)/"-01'")
3	for A2	=file("D:/进销存/商品销售明细/2017"+string(#A3,”00”))
4		=A1.cursor(A3)	>B3.export@ab(B4)
5	>A1.close()

A1：连接数据库

A2：生成由10个SQL组成的集合，每个SQL分别查询当月（1到10）范围内的数据。写法上，“10.”表示从1循环到10，在括号内的字符串中用相应的1到10替换 ~符号。

A3：按照A2中的序列循环执行

B3：按路径打开每月数据的集文件，路径命名规则是4位年和2位月，利用string()函数进行格式化，其中#号代表循环序号

B4：根据每段sql创建数据库游标

C4：将游标执行计算后的结果写入到集文件中。其中export()函数使用了@ab的选项，@b代表写成集文件格式，而由于在for循环里面，需要执行多次，所以用@a指明追加方式，把结果逐步保存到文件中，保证文件的完整性。循环生成完之后，文件的存储目录结构如下图：

A5：关闭数据库连接

3.2.2同步昨天数据到文件

编写单次执行脚本，获取昨天的历史数据追加到当月集文件中，每天执行，当下月1号时，会自动生成新文件，脚本如下：

	A	B	C
1	=after(date(now()),-1)	=year(A1)	=month(A1)
2	=file("D:/进销存/商品销售明细/"+string(B1)+string(C1,”00”))
3	=connect("demo")	=A3.cursor("SELECT   * FROM sdrpts WHERE DATE_FORMAT(fildate,'%Y-%m-%d')=?",A1)	>A2.export@ab(B3)
4	>A3.close()

A1：根据当前系统时间，获取昨天的日期

B1-C1：分别获取到年、月

A2：按路径打开需要导出的集文件，路径规则是以4位年2位月命名，利用string()函数进行格式化

A3：连接数据库

B3：根据sql创建数据库游标，获取昨日数据，参数为昨天日期

C3：执行结果追加写入到集文件中

A4：关闭数据库

3.3日志追溯

在上面的步骤中，已经可以同步昨天的历史数据到集文件中；但总是有意外情况发生，假如历史数据没有同步成功怎么办呢？我们是不是可以通过记录日志信息的方式，追溯历史原因？这样能够及时发现问题，及时采取补救措施；比如：很小概率下脚本可能会执行失败，这时如果及时发现，就可以先手动执行脚本重新生成集文件，然后再排查原因，从而避免影响业务查询。(由于集文件目前不支持回滚动作，一旦导出出错，需要重新导出数据生成当月集文件。集算器高版本的组表支持回滚，以后会有专门的文章详细介绍)。

3.3.1构造日志表

第一步，可以先在数据库中定义一张日志表，包含五个字段(事件名称/状态/异常信息/执行时间/执行时长)，数据结构如下图示：

第二步，在集算器脚本中定义4个参数名，分别是事件名称/状态/异常信息/执行时长，参数定义如下图所示：

第三步，当集算器脚本接收来自外界参数信息时，将参数值填写到日志表中：

	A
1	=connect("demo")
2	=A1.execute("INSERT   INTO sys_dfx_task_log (task_name,status,error_msg,excute_time,sec_num) VALUES   (?,?,?,?,?)",taskName,status,errorMsg,now(),secNum)
3	>A1.close()

A1：连接数据库

A2：接收来自外界传入的参数值后，向数据库的日志表中执行SQL插入语句，包含五个字段(事件名称/状态/异常信息/执行时间/执行时长)，其中now()函数代表获取当期时间

A3：关闭数据库

3.3.2设定日志规则

我们将判断同步操作是否成功的规则设定为：当每天定时导出到集文件的数据条数与查询出来需要同步数据的总条数相差小于5条的时候，我们认为同步动作是成功的，否则认定同步失败，然后把关键信息写入到日志表中。按此规则改造同步数据的集算器脚本如下：

	A	B	C
1	=after(date(now()),-1)	=year(A1)	=month(A1)
2	=file("D:/进销存/商品销售明细/ "+string(B1)+string(C1,”00”))	=now()	=A2.cursor@b().skip()
3	=connect("demo")	=A3.cursor("SELECT   * FROM sdrpts WHERE DATE_FORMAT(fildate,'%Y-%m-%d')=?",A1)	>A2.export@ab(B3)
4	=A3.query("SELECT   COUNT(1) FROM sdrpts WHERE DATE_FORMAT(fildate,'%Y-%m-%d')=?",A1)	=A2.cursor@b().skip()-C2	=interval@ms(B2,now())
5	if A4.#1-B4<5	>call("log.dfx","同步sdrpts"+string(A1)+"的数据完成,总记录条数："+string(A4.#1)+"总计导出："+string(B4),"完成","",C4/1000)
6	else	>call("log.dfx","同步sdrpts"+string(A1)+"的数据，导出的数据量跟数据库中的相差超过5条","失败","",0)
7	>A3.close()

前面已经解释过的格子的代码这里不再赘述。

B2：获取当前系统时间，用于后面计算导出操作的执行时长

C2：统计写入前集文件的记录数

A4：执行sql查询需要同步的昨天的数据总条数

B4：当数据追加写入到集文件后，再统计一遍记录数，同时减去写入前的数量，得到实际写入成功的记录条数

C4：计算整个同步过程的执行时长，其中interval()函数通过选项@ms指定返回毫秒数

A5：判断数据库中需要同步数据的总条数与导出到集文件的数据总条数，两者之差小于5条时，认为任务是执行成功的，在日志表中写入成功记录，否则认定任务执行失败，在日志表中写入失败记录。

B5-B6：根据执行成功或失败的判断，log.dfx网格文件，在日志表中写入相应的记录。

A7：关闭数据库

3.3.3查询日志报表

为了方便管理，我们还可以通过报表工具，做一张关于日志信息的查询报表，这样就能通过web端及时发现问题、解决问题，效果如下：

3.4定时任务

3.4.1利用 Quartz

Quartz 是 OpenSymphony开源组织在Job scheduling领域的一个开源组件，利用Quartz可以简便地创建定时执行任务，而集算器原本就是独立的计算引擎，两者结合起来，再提供一些可视化的配置和管理页面，就能比较容易的实现轻量级ETL的功能。如下图所示：

3.4.2使用操作系统工具创建计划任务

windows操作系统下，可以利用自带的任务计划程序实现定时任务，比如可以先新建一个bat文件，写入需要执行的命令：

@echo off

"D:/esProc/bin/esprocx.exe" C:/20180713/synclastday.dfx

再配置一个计划任务定时执行即可，如下图所示：

 

而在Linux操作系统下，可以借助crontab实现定时任务，命令如下:   /raqsoft/esProc/bin/esprocx.sh /esproc/synclastday.dfx

3.5数据查询

前面是一个比较完整 ETL 数据准备过程，下面我们将在这些准备工作的基础上，完成“各区域销售员每日销售额日增长率报表”的制作，通过集算器利用文件实现数据外置，从而提升报表查询效率。

3.5.1查询一个月内数据

我们先通过传入开始日期、结束日期，只查询一个月内的数据，也就是访问某个月的集文件即可。(值得一提的是：集算器不仅能够降低复杂业务运算的实现难度，同时，对于单文件的运算还提供了“简单SQL”方式，让懂SQL的用户对文件的操作更容易上手。简单SQL的特性不是本文的重点，有兴趣的读者可以参考相关文档，这里不再赘述。)

第一步，分组汇总；根据起止日期过滤后，按照区域ID、销售、日期分组，并汇总销售金额(销售数量*单价)，同时区域ID，需要显示成区域名称。编写集算器脚本如下：

	A
1	=connect("demo")
2	=A1.query@x("SELECT   id,city FROM area")
3	=file("D:/进销存/商品销售明细/ "+string(year(Bfiledate))+string(month(Bfiledate),”00”)).cursor@b()
4	=A3.select(filedate>=Bfiledate   && filedate<Efiledate)
5	>A4.switch(areaid,A2:id)
6	=A4.groups(areaid,account,date(filedate):filedate;sum(salqty*salamt):subtotal)
7	=A6.new(areaid.city:areaname,account,filedate,subtotal)
8	return A7

A1：连接数据库

A2：通过SQL查询外键表area，共两个字段id,city，其中函数query()使用了@x选项，代表查询结束时自动关闭数据库连接，执行结果如下图：

A3：打开集文件对象，根据文件创建游标返回，其中cursor()函数使用@b选项代表从集文件中读取。我们事先在脚本设置中定义了2个参数，开始日期、结束日期，如下图：

这里根据传入的开始日期参数Bfiledate，就能够准确的找到指定的集文件对象，比如：当Bfiledate的参数值为2017-09-01时，分别获取年、月，拼在一起就是集文件的名称，全路径为：D:/进销存/商品销售明细/201709

A4：通过起止日期过滤出符合条件的记录

A5：通过switch()函数在A4表的areaid字段上建立指向A2表中id字段的指针引用记录，实现关联，如下图：

A6：按区域ID、销售、日期分组，并汇总销售金额(销售数量*单价)

A7：计算字段值，生成新序表；其中利用A5建立的关联关系通过“外键字段.维表字段”的方式进行引用，用 “areaid.city”生成新的字段areaname，（将维表记录看做外键的的属性，这便是外键属性化的由来），返回关联后的结果集如下图：

 

第二步，计算销售日增长率；在第一步的基础上，计算出每个区域下每个销售员每天销售额的日增长率； 修改后的脚本如下：

	A
1	=connect("demo")
2	=A1.query@x("SELECT   id,city FROM area")
3	=file("D:/进销存/商品销售明细/ "+string(year(Bfiledate))+string(month(Bfiledate),”00”)).cursor@b()
4	=A3.select(filedate>=Bfiledate   && filedate<Efiledate)
5	>A4.switch(areaid,A2:id)
6	=A4.groups(areaid,account,date(filedate):filedate;sum(salqty*salamt):subtotal,sum(0):rate)
7	=A6.run(if(areaid==areaid[-1]&&account==account[-1],rate=(subtotal-subtotal[-1])/subtotal[-1]))
8	=A7.new(areaid.city:areaname,account,filedate,subtotal,rate)
9	return A8

前面已经解释过的格子代码这里不再赘述。

A6：按区域ID、销售、日期分组，并汇总销售金额(销售数量*单价),同时构造一个空的列叫rate，结果如下图：

   A7：在A6分组后的基础上，针对每一行记录，判断相邻行的areaid、account是否相等，相等的情况下，计算销售员每天的销售额的日增长率，算法为“（当日销售额-上一日的销售额）/上一日的销售额”。可以看到，集算器用subtotal[-1]来表示上一日的销售额，可以轻松进行相对位置的计算。

A8：返回关联后结果集如下图：

A9：返回结果集给报表工具

3.5.2查询跨月 / 跨年数据

上一步已经实现了计算每个销售员销售额的日增长率，不过只能在一个集文件中查询，也就是只能查询一个月的数据。那如何跨多个集文件，从而实现跨月、跨年，适用于大数据量的报表查询呢？

首先，我们需要写一个工具脚本，主要功能是能够根据传入的开始日期、结束日期，过滤出需要查询跨月度范围的多个集文件路径，同时判断路径下的集文件对象是否存在。脚本如下：

	A
1	=periods@m(startDate,endDate,1)
2	=A1.(path+string(year(~))+string(month(~),”00”))
3	=A2.id()
4	=A3.select(file(~).exists())
5	return A4

脚本接收3个参数，起止日期，集文件的存储路径，如下图：

 

   A1：根据起止日期，按月间隔获取日期，其中periods()函数的选项@m代表按月间隔计算，比如，开始日期：2017-01-03，结束日期：2017-11-23，执行结果如下图：

   A2：循环A1，通过集文件的存储路径与该日期段内的年月进行拼接。月份要始终保持两位，利用string()函数进行格式化，结果如下图：

A3：去重，执行结果如下图：

A4：判断路径下的文件是否真实存在，由A5返回实际存在的文件路径，结果如下图：

然后，我们需要对上面数据查询的脚本做一些改造，值得注意的是这里将采用多路游标的概念，将多个游标合并成一个游标使用，改造后的脚本如下：

	A	B	C	D
1	=connect("demo")	=[]
2	=A1.query@x("SELECT   id,city FROM area")
3	=call("D:/进销存/商品销售明细/判断读取文件的范围.dfx",Bfiledate,Efiledate,"D:/进销存/商品销售明细/")
4	for A3	=file(A4)	=B4.cursor@b()	>B1=B1|C4
5	=B1.mcursor()
6	=A5.select(filedate>=Bfiledate   && filedate<Efiledate)
7	>A6.switch(areaid,A2:id)
8	=A6.groups(areaid,account,date(filedate):filedate;sum(salqty*salamt):subtotal,sum(0):rate)
9	=A8.run(if(areaid==areaid[-1]   && account==account[-1],rate=(subtotal-subtotal[-1])/subtotal[-1]))
10	=A9.new(areaid.city:areaname,account,filedate,subtotal,rate)
11	return A10

 

前面已经解释过的格子代码这里不再赘述。

A3：调用”判断读取文件的范围.dfx”,传入脚本参数开始日期、结束日期的值，获得起止日期内的所有集文件的集合

A4-C4：循环A3，分别打开每个集文件对象，根据文件创建游标，其中cursor()函数使用@b选项代表从集文件中读取。

D4：将多个游标对象保存到B1预留的序列中

A5：利用集算器提供的多路游标概念，把数据结构相同的多个游标合并成一个游标使用。使用时，多路游标采用并行计算来处理各个游标的数据，可以通过设置cs.mcursor(n) 函数中的n来决定并行数，当n空缺时，将按默认自动设置并行数。

A11：最后返回结果集给报表工具使用，而结果集的计算过程A6到A10与前面一个集文件时完全一样。

3.6作为报表数据源

利用集算器完成了数据查询工作后，可以在报表中直接将集算器设置为数据源，和使用数据库一样简单地完成报表呈现，具体做法包括：

1、在报表中定义参数(Bfiledate、Efiledate),

2、设置集算器数据集，并传递报表参数，

3、设计报表表样

如下图所示。随后，输入参数计算，即可得到希望的报表了。

如果再结合文章<<秒级展现的百万级大清单报表怎么做>>，那么基本上就可以轻松应对项目中遇到的各类大数据集报表、大清单列表了。

3.7总结

1、简易版、轻量级ETL

集算器是独立的计算引擎，搭配上定时执行程序，很容易就能实现简单、轻量级的ETL功能。

2、高性价比、高性能

无需构建数仓，很好的解决关系型数据库中数据量大而导致的报表慢的难题。

3、不影响原有系统构架、实现简单、易维护

使用润乾集算器的集文件存储大表数据，独立于原有系统构架，将原有数据水平切割，显著提高查询效率，不影响业务操作。

4、降低应用耦合度

集算器脚本、集文件、报表模板等可以随应用一起管理和维护，完全和数据库解耦合，数据管理因此变得简单清晰。