使用开源 SPL 创建简单高效的实时报表

实时报告问题

实时报告（也称为 T+0 报告）是指实时查询和汇总数据，包括最新数据。当涉及的数据量相对较小时，可以直接基于生产数据库轻松处理。然而，当数据累积到一定程度时，对生产数据库中的大表进行查询会消耗大量数据库资源，当资源消耗过多时，甚至会影响业务事务。这是不可接受的，因为事务是最高优先级。为了防止事务受到查询处理的影响，通常会将用于分析查询的海量历史数据从生产数据库移出，存储在单独的数据库中进行查询。这就是我们所说的冷热数据分离。

分离会导致实时问题。当数据存储在两个独立的数据库中时，查询整个数据会涉及跨数据库查询。我们知道，用于事务的大多数生产数据库是支持事务一致性的 RDB，但更倾向于使用专门的分析数据库或数据平台来存储分离的冷数据（其体积庞大且保持静态）。即使使用 RDB 来存储冷数据，它也可能是不同类型的。这也涉及不同类型数据库或数据源之间的查询。不幸的是，目前所有跨数据库/数据源的技术都有其弱点。

通常，数据库自身的跨数据库/数据源功能（如 Oracle 的 DBLink、MySQL 的 FEDERATED 和 MSSQL 的 Linked Server）会将数据从远程数据库检索到本地计算机，并在本地执行大部分计算，包括过滤操作。整个过程效率极低。该方法还有其他缺点，例如数据传输不稳定、不支持大对象处理以及可扩展性低。

另一种方法是使用高级语言进行“万能”的硬编码。它很灵活，但使用起来非常困难，尤其是在当今大多数应用程序都使用 Java 编写程序的情况下。高级语言没有足够的结构化数据计算类库，除了简单的列表式查询外，很难处理跨数据库查询后的计算。所有涉及分析和汇总的查询都变得异常复杂。

实际上，解决冷热数据分离产生的实时查询问题并不难。拥有一定能力的计算引擎就足够了。这样的引擎应该能够连接和访问多样化的数据源；在数据从不同源提取后，具备数据库无关的、全方位的计算能力；能够通过利用数据库/数据源的优势来挖掘数据库的计算资源；提供简单的 DPL 数据处理接口，并至少具有令人满意的性能。

SPL 作为解决方案

开源 SPL 正是我们所期望的数据计算引擎。它提供了丰富的结构化数据计算函数，具备完整的计算能力，支持跨异构源的混合计算，并且可以同时连接存储热数据的生产数据库和存储冷数据的历史数据库，从而对全部数据进行实时查询。

凭借独立且全方位的计算能力，SPL 可以分别从不同的数据库检索数据，这使得它非常适合处理涉及不同类型数据库的场景，并根据需要确定计算在哪里执行——是在数据库内部还是在数据库外部的 SPL 中。在实现方面，SPL 凭借其敏捷的语法和过程式编程，可以极大地简化实时查询中的复杂计算逻辑并加快开发速度。此外，它支持解释执行和热交换。更重要的是，SPL 还可以利用其强大的计算能力来处理分离的冷热数据上的 ETL 任务。

SPL 提供专有的高性能二进制存储格式。对于对性能要求很高的场景，您可以将历史冷数据存储在文件中，并利用 SPL 的高性能算法和便捷的并行处理技术来提高查询效率。SPL 封装了标准的应用程序接口（JDBC、ODBC 和 RESTful），供应用程序集成和调用。SPL 代码还可以嵌入到应用程序中，方便地使后者具备处理实时查询和复杂数据处理任务的能力。SPL 轻松满足当今计算和存储分离的应用框架的需求。

冷热数据之间的混合计算

处理存储在单独数据库中的冷热数据的实时查询非常简单。以下是一个示例

=[[connect@l("oracle"),"ORACLE"],_
			[connect@l("mysql"),"MYSQL"]]

=SQL="select month(orderdate) ordermonth,_
			sellerid,sum(amount) samount,_
			count(amount) camount _
            from sales group by month(orderdate),sellerid"

=A3.conj().groups(ordermonth,sellerid;_
			sum(samount):totalamount,_
			sum(camount):totalcount)_

在此示例中，Oracle 用作存储当前热数据的生产数据库，MySQL 用于存储历史冷数据。当前端传递一个标准 SQL（A2）时，SPL 使用其 sqltranslate 函数（B3）将其翻译成相应数据库的语法，并将其发送到数据库进行查询（B4），最后，合并中间结果集并执行所需的聚合（A5）。SPL 代码尝试使用多线程处理（A3）来执行两个线程的 SQL，从而提高效率。

SPL 不仅完成了两个数据库之间的跨数据库查询，还使用 SQL 翻译方法方便前端应用程序，并在合并查询两个数据库的结果集后，在该示例中处理后续计算、分组和聚合。SPL 还提供了专门的结构化数据对象及其上的众多操作。它为分组与聚合、循环与分支、排序、过滤和集合运算等基本计算，以及获取位置、基于顺序的排名和不规则分组等复杂计算提供了直接便捷的支持。

除了 RDB，SPL 还支持 NoSQL 和 Hadoop 等其他数据源。其多样化的源混合计算能力使其能够实现跨异构数据源的实时查询。例如，要同时查询 MongoDB 和 MySQL

=A2.new(Orders.OrderID:orderid,_
			Orders.Client:client,_
			Dept:dept,Amount:amount).fetch()

=mysql.query@x(“select ordered,client,_
			dept,amount from orders”)

凭借其卓越的计算能力，SPL 可以处理 ETL 任务并将热数据迁移到历史数据库，通常伴随某些转换操作。例如，有时我们需要根据特定的参考表将某些代码字段转换为另一种代码类型（以便使用一致的代码规则，通过整理数据类型提高性能等）。但参考表通常不存储在生产数据库中，因此您无法直接在数据库内执行计算。这里，我们需要面对跨数据源的计算。

高性能

历史冷数据的规模可能非常庞大。使用 RDB 存储它很容易受到许多因素的影响，例如资源容量，而且数据检索速度极慢。然而，文件存储具有优势。它具有更快的数据检索速度，可以使用压缩和并行处理等多种机制来提高性能，并且不像数据库那样容易受到资源容量的影响。然而，开放文本格式效率低下（因为它未压缩且解析速度慢），并且主要使用二进制文件。文件存储的最大问题是文件没有计算能力，并且硬编码很困难。在这方面，数据库可以方便地使用 SQL 处理数据。

所有问题都可以用 SPL 解决。SPL 提供两种高性能二进制存储格式——bin 文件和复合表。结合 SPL 的独立计算能力，这使得通过直接在文件和数据库上进行混合计算，可以实现高效的 T+0 查询。以上述案例为例，我们可以使用 SPL 文件来存储历史冷数据，并对其与生产数据库中的热数据进行混合查询。

=A1.query@x("select sellerid, sum(amount) totalamount,_
			count(amount) countamount,max(amount) maxamount,_
			min(amount) minamount from sales group by sellerid")

=A3.groups(sellerid;sum(amount):totalamount,_
            count(amount):countamount,_
			max(amount):maxamount,min(amount):minamount)

=[A3,A4].conj().groups(sellerid;sum(totalamount):totalamount,_
			sum(countamount):countamount,max(maxamount):maxamount,_
            min(minamount):minamount)

将历史数据存储在文件中，并对文件和生产数据库进行混合查询。SPL 还支持使用游标来处理历史数据查询量巨大的大数据计算场景。A4 对 A3 的文件游标进行分组和汇总。A5 合并 A2 和 A4 的结果集，并在合并集上执行分组与聚合。代码使用 SPL 二进制 bin 文件（btx）来获得更高的效率。*bin* 文件是压缩的（占用空间更小，检索速度更快），存储了数据类型（无需解析即可快速检索），并采用了双增量分段技术来划分可追加文件并促进并行处理，从而确保了高计算性能。

复合表是 SPL 提供的另一种高效率存储格式。它在处理只涉及少量列（字段）的场景时具有显著优势。复合表配备了 minmax 索引，并支持双增量分段技术，使得计算既能享受列式存储的优势，又能更轻松地并行处理，从而获得更好的性能。

SPL 为各种计算提供高性能算法，例如获取 TopN。它将计算 TopN 视为一种聚合操作，成功地将高度复杂的全排序转换为低复杂度的聚合操作，同时扩展了应用领域。

SPL 语句不包含任何与排序相关的关键字，也不会触发全排序。从整个数据集获取 Top N 的语句和从子集获取 Top N 的语句基本相同，并且都具有很高的性能。SPL 还提供许多类似的、高性能的算法。

在 SPL 中实现并行处理非常容易，并且可以充分发挥多 CPU 的优势。许多 SPL 函数，如文件检索、过滤和排序，都支持并行处理机制。只需添加一个 @m 选项即可简单方便地实现多线程处理。

易于集成

SPL 封装了标准的 JDBC 驱动程序和 ODBC 驱动程序，以便其他应用程序调用。特别是对于 Java 应用程序，SPL 代码可以嵌入其中执行。这使得应用程序端可以实现数据源无关的 T+0 查询，从而完全解耦应用程序和数据源，创建易于迁移和可扩展的代码。

以下是通过 JDBC 调用 SPL 代码的示例

Class.forName("com.esproc.jdbc.InternalDriver");
Connection conn =DriverManager.getConnection("jdbc:esproc:local://");
Statement st = connection.();
CallableStatement st = conn.prepareCall("{call splscript(?, ?)}");
st.setObject(1, 3000);
st.setObject(2, 5000);
ResultSet result=st.execute();

SPL 支持解释执行，自然支持热交换。用 SPL 编写的数据计算逻辑及其修改可以实时生效，无需重启应用程序，从而使程序的开发、运行和维护变得便捷高效。

与其他实现实时查询的技术相比，SPL 更方便，因为它具有独立、强大的计算能力和跨数据源混合计算的特性；通过其高性能存储格式和算法，它更能实现高查询效率；并且更容易集成，使应用程序端能够发挥这些优势。总之，SPL 是实现实时查询的最佳和最理想的工具。

历史

• 2022 年 6 月 24 日：初始版本