esProc SPL:Java中的SQLite替代方案

发表时间: 2022-08-06 18:00

很多免费开源数据处理引擎都可以嵌入 Java 应用中,其中 SQLite 历史悠久、用户众多;后起之秀 esProc SPL 功能也较强,下面对两者进行多方面的比较。


基本特征

语言风格

SQLite 使用传统的 SQL 代码(两者在本文等价),SQL 在业界很流行,不必更多介绍。SPL 是现代的数据计算语言,属于简化的面向对象的语言风格,有对象的概念,可以用点号访问属性并进行多步骤计算,但没有继承重载这些内容,不算彻底的面向对象语言。

运行模式

SQLite 是用 C 语言开发的跨平台小型数据库,可嵌入其他开发语言,也可在单机执行。SPL 是用 Java 开发的跨平台的数据计算语言,可嵌入 Java,可在单机执行,可以数据计算服务的形式被远程调用。两者的代码都是解释执行的。

IDE

SQLite 官方只提供了命令行工具,图形化工具要由第三方提供,但由于 SQL 的特性,这些工具大都没有断点调试、变量观察等基本的 IDE 功能。SPL 提供了图形化 IDE,包括完整的调试功能、表格形式的变量观察功能。

学习难度

SQL 历史悠久资料丰富,入门学习时难度较低,但复杂运算时难度很大。而且,SQL 缺乏流程处理能力(分支结构和循环结构),要借助 Java 的相关功能才能实现完整的业务逻辑,所以通常还要学习 Java。

SPL 的目标是简化 Java 和 SQL 的编码,刻意简化了许多概念,学习难度较低。SPL 兼具结构化数据计算能力和流程处理能力,可以实现完整的业务逻辑,不必借助其他语言。

代码量

SQL 进行简单计算时代码量很低,如果遇到较复杂计算,代码量会急剧增长。SQL 经常要用 Java 配合实现完整的业务逻辑,Java 的流程处理功能没有为结构化数据对象做优化,代码通常较繁琐。

SPL 是现代计算语言,避免了 SQL 的诸多弱点,无论简单还是复杂计算,代码量都很低。SPL 的流程处理功能为结构化数据对象做了优化,相关代码更加简单。


数据源读取

自有数据格式

SQLite 有自有(私有)数据格式,即库文件,一个库文件包含多个表。可以用命令行命令或 API(JDBC URL)创建库文件,但不能用 SQL 代码直接创建库文件。比如,用 Java 新建一个 SQLite 库文件,如果已存在则打开库文件

Class.forName("org.sqlite.JDBC");Connection conn = DriverManager.getConnection("jdbc:sqlite: d:/ex1.db");

SPL 有两种自有数据格式,其中一种是组表文件,专用于大数据高性能计算,不是本文重点;另一种是集文件,专用于中小数据量和嵌入式计算。SPL 不需要建库,也没有库的概念,每个集文件对应一个表。

外部数据源

SQLite 只支持文本数据文件,包括 TAB 分隔的 txt、逗号分隔的 csv,也可自定义其它分隔符。

SPL 支持多种数据源,包括:

  • JDBC(即所有的 RDB)
  • csv、TXT、JSON、XML、Excel
  • HBase、HDFS、Hive、Spark
  • Restful、WebService、Webcrawl
  • Elasticsearch、MongoDB、Kafka、R2dbc、FTP
  • Cassandra、DynamoDB、influxDB、Redis、SAP

这些数据源都可以直接使用,非常方便。对于其他未列入的数据源,SPL 也提供了接口规范,只要按规范输出为 SPL 的结构化数据对象,就可以进行后续计算。

访问自有数据格式

SQLite 通过 SQL 访问库文件中的表,生成 SQL 结果集,即内存中的结构化数据对象。读取表时一般伴随着查询:

select * from Orders where Amount>2000 and Amount<=3000

SPL 通过 SPL 的 T 函数或 import 函数读取集文件,生成序表(相当于 SQL 结果集)。等价的代码:

T("d:/Orders.btx").select(Amount>2000 && Amount<=3000)

读取 csv 文件

SQLite 读取 csv 文件需要三步,第一步:进入管理命令行。

#在Windows命令行,打开或新建名为dbname的数据库sqlite3.exe new.db

第二步,在管理命令行新建库表,并导入数据:

#当字段类型都是字符串时,不必手工建表。create table Orders(OrderID int,Client varchar(100),SellerID int,Amount float,OrderDate date);.import --csv d:/Orders.csv Orders

第三步,在 Java 代码中嵌入 SQL 查询代码:

select * from Orders where Amount>2000 and Amount<=3000

上面的方法需要人工参与,比较繁琐。也可以全部用 Java 代码实现,第一步,先在操作系统建立一个文本文件(比如 importOrders.txt),内容即上面第二步。

第二步,在 Java 代码调用操作系统命令行,即用 Runtime.getRuntime().exec(…) 执行命令:

sqlite3.exe dbname.db < importOrders.txt

第三步不变。

这种方法需要额外的操作系统执行权限,安全风险比较大。如果想降低安全风险,就要用循环语句执行 SQL 一条条插入记录,代码冗长,修改麻烦。

SPL 读取 csv 文件,只需一步,在 Java 里嵌入下面的 SPL 代码:

T("d:/Orders.csv").select(Amount>2000 && Amount<=3000)

函数 T 不仅可以读取集文件,也可以读取 csv 文件,并生成序表。序表是 SPL 的结构化数据对象,可类比为 SQL 结果集。SPL 导入数据时,数据类型会自动解析,不必手工指定。整个过程无需人工参与,权限要求低,代码简短,比 SQLite 方便多了。

如果 csv 格式不规范,还可以使用 import 函数指定分隔符、字段类型、跳过行数,并处理转义符、引号、括号等,比 SQLite 提供的功能丰富多了。

对于 csv 之外的数据源,SQLite 都没有提供方便的导入方法,取数过程非常繁琐,而 SPL 支持多种数据源,取数过程简单方便,可以显著提高开发效率。

读取多层结构数据

Json 和 XML 是常用的多层结构数据。SQLite 架构简单,又有 Json 计算能力,有时会承担 Json 文件 /RESTful 的计算功能,但 SQLite 不能直接解析 Json 文件 /RESTful,需要用 Java 代码硬写,或借助第三方类库,最后再拼成 insert 语句插入数据表,代码非常繁琐,这里就不展示了。

SPL 架构同样简单,且可以直接解析 Json 文件 /RESTful,可以大幅简化代码,比如:

json(file("d:/xml/emp_orders.json").read()).select(Amount>2000 && Amount<=3000)json(httpfile("http://127.0.0.1:6868/api/orders").read()).select(Amount>2000 && Amount<=3000)

SQLite 没有 XML 计算能力,也不能直接解析 XML 文件 /WebService,只能借助外部 Java 代码解析计算,非常繁琐。

SPL 可以直接读取 XML 文件:


A

1

=file("d:/xml/emp_orders.xml").read()

2

=xml(A1,"xml/row")

3

=A2.select(Amount>1000 && Amount<=2000 && like@c(Client,"*business*"))

也可以方便地读取 WebService:


A

1

=ws_client("http://127.0.0.1:6868/ws/RQWebService.asmx?wsdl")

2

=ws_call(A1,"RQWebService":"RQWebServiceSoap":"getEmp_orders")

3

=A2.select(Amount>1000 && Amount<=2000 && like@c(Client,"*business*"))

SPL 序表支持多层结构数据,比 SQL 库表的二维结构更容易表达 Json/XML,计算代码也更简单。这部分内容不是本文重点,就此略过。

跨源计算

SQLite 的外部数据源只支持 csv 文件,跨源计算就是 csv 文件和库表间的关联、交集、子查询等计算。SQL 是封闭的计算语言,不能直接计算库外数据,需要经过一个入库的过程,把 csv 文件变成库表之后才能进行跨源计算。参考前面的代码可知,入库的过程比较麻烦,不能只用 SQL,还要借助 Java 或命令行。

SPL 开放性较好,可以直接计算多种数据源,数据源之间可以方便地进行跨源计算。比如 csv 和 RESTful 左关联:

=join@1(json(httpfile("http://127.0.0.1:6868/api/orders").read()):o,SellerId; T("d:/Emp.csv"):e,EId)

写成多步骤的形式更易读:


A

1

=Orders=json(httpfile("http://127.0.0.1:6868/api/orders").read())

2

=Employees=T("d:/Emp.csv")

3

=join@1(Orders:o,SellerId;Employees:e,EId)

只用 SPL 语言就可以实现跨源计算,不必借助 Java 或命令行,代码简短易懂,比 SQL 的开发效率高得多。


数据计算

基础计算

SQLite 支持常见的基础计算:

#选出部分字段select Client,Amount from Orders#模糊查询select * from Orders where Amount>1000 and Client like '%s%'#排序select * from Orders order by Client, Amount desc#去重select distinct Client from Orders#分组汇总select strftime('%Y',OrderDate) as y, Client, sum(Amount) as amt from Orders group by strftime('%Y',OrderDate), Client having amt>3000#并集select * from Orders9 where Amount>3000 union select * from Orders9 where strftime('%Y',OrderDate)='2009';#子查询select * from (select strftime('%Y',OrderDate) as y, Client, sum(Amount) as amt from Orders group by strftime('%Y',OrderDate), Client) where  Client like '%s%';


SPL 实现常见的基础计算:


A

B

1

=Orders.new(Client,Amount)

// 选出部分字段

2

=Orders.select(Amount>1000 && like(Client,\"*s*\"))

// 模糊查询

3

= Orders.sort(Client,-Amount)

// 排序

4

= Orders.id(Client)

// 去重

5

=Orders.groups(year(OrderDate):y,Client;sum(Amount):amt).select(amt>3000)

// 分组汇总

6

=[Orders.select(Amount>3000),A1.select(year(OrderDate)==2009)].union()

// 并集

7

=Orders.groups(year(OrderDate):y,Client;sum(Amount):amt).select(like(Client,\"*s*\"))

// 子查询

SQLite 和 SPL 都有丰富的函数进行基础计算,学习掌握都不难。

综合计算

先看简单些的,计算 TopN,SQLite:

select * from Orders order by Amount limit 3

SQLite 代码简短,但因为不支持 top 函数,也不支持序号伪列(比如 Oracle 的 rownum),只能改用 limit 实现,理解起来不够直观。

SPL 实现 TopN:

Orders.top(3;Amount)

SPL 支持真正的行号,也支持 top 函数,代码更短,且易于理解。

组内 TopN,SQLite:

select * from (select *, row_number() over (partition by Client order by Amount) as row_number from Orders) where row_number<=3

SQL 代码略显复杂,主要因为 SQLite 不支持 top 函数,而 limit 函数只能限制总记录数,不能限制各组记录数,这种情况下必须用组内行号。SQLite 没有真正的行号字段,要用窗口函数生成伪列再用,代码自然复杂。事实上,缺乏行号并非 SQLite 自身的问题,而是整个 SQL 体系的问题,其他数据库也没有行号。

SPL 实现组内 TopN:

Orders.group(Client).(~.top(3;Amount)).conj()

SPL 代码简单多了,而且很好理解,先按 Client 分组,再对各组(即符号 ~)计算 TopN,最后合并各组的计算结果。SPL 之所以代码简单,表面上是因为直接支持 Top 函数,本质是因为 SPL 有真正的行号字段,或者说,SPL 支持有序集合。SPL 代码简单,还因为集合化更加彻底,可以实现真正的分组,即只分组不汇总,这就可以直观地计算组内数据。SQL 集合化不彻底,分组时必须汇总,不能直观地计算组内数据,只能借助窗口函数。

再看复杂些的计算,某支股票的最大连续上涨天数,SQLite:

select max(continuousdays)from (select count(*) continuousdaysfrom (select sum(risingflag) over (order by day) norisingdaysfrom (select day, case when price>lag(price) over (order by day) then 0 else 1 end risingflag from tbl)) group by norisingdays)

上面代码冗长复杂。SQL 很难直接表达连续上涨的概念,只能换个方法变相实现,即通过累计不涨天数来计算连续上涨天数,这种方法技巧性强,编写难度大且不易理解。而且,SQL 难以调试,导致维护困难。

SPL 求最大连续上涨天数:


A

1

=tbl.sort(day)

2

=t=0,A1.max(t=if(price>price[-1],t+1,0))

上面代码简单多了。SPL 容易表达连续上涨的概念,先按日期排序;再遍历记录,发现上涨则计数器加 1。这里既用到了循环函数 max,也用到了有序集合,代码中 [-1] 表示上一条,是相对位置的表示方法,price[-1]表示上一个交易日的股价,比整体移行(lag 函数)更直观。

找出销售额占到一半的前 n 个客户,并按销售额从大到小排序。SQLite:

with A as(select client,amount,row_number() over (order by amount) ranknumberfrom sales)select client,amountfrom (select client,amount,sum(amount) over (order by ranknumber) accfrom A)where acc>(select sum(amount)/2 from sales)order by amount des

上面代码复杂。SQL 很难处理恰好要过线的客户,只能换个方法变相实现,即计算销售额从小到大的累计值,反过来找出累计值不在后一半的客户。这种方法技巧性强,代码冗长,而且难以调试。

SPL 求销售额占到一半的前 n 个客户:


A

B

2

=sales.sort(amount:-1)

/销售额逆序排序,可在SQL中完成

3

=A2.cumulate(amount)

/计算累计序列

4

=A3.m(-1)/2

/最后的累计即总额

5

=A3.pselect(~>=A4)

/超过一半的位置

6

=A2(to(A5))

/按位置取值

上面代码相对简单。SPL 集合化成更彻底,可以用变量方便地表达集合,并在下一步用变量引用集合继续计算,因此特别适合多步骤计算。将大问题分解为多个小步骤,可以方便地实现复杂的计算目标,代码不仅简短,而且易于理解。此外,多步骤计算天然支持调试,无形中提高了开发效率。

从上面例子可以看出,SQL 只适合较简单的计算,而 SPL 支持有序集合,集合化更彻底,从简单到复杂的计算都可以很好的完成。此外,SPL 还支持游离记录,可以用点号直观地引用关联表,从而简化复杂的关联计算。

日期和字符串函数。SQLite 支持日期和字符串函数,比如日期增减、截取字符串等,但还不够丰富,很多常用函数并不直接支持,比如季度增减、工作日计算等。

SPL 提供了更丰富的日期和字符串函数,在数量和功能上远远超过了 SOLite。比如:

季度增减:elapse@q("2020-02-27",-3)      //返回2019-05-27N个工作日之后的日期:workday(date("2022-01-01"),25)  //返回2022-02-04判断是否全为数字:isdigit("12345")      //返回true取子串前面的字符串:substr@l("abCDcdef","cd")    //返回abCD按竖线拆成字符串数组:"aa|bb|cc".split("|")    //返回["aa","bb","cc"]

SPL 还支持年份增减、求季度、按正则表达式拆分字符串、拆出 SQL 的 where 或 select 部分、拆出单词、按标记拆 HTML 等大量函数。

SPL 函数选项和层次参数

值得一提的是,为了进一步提高开发效率,SPL 还提供了独特的函数语法。

有大量功能类似的函数时,SQL 要用不同的名字或者参数进行区分,使用不太方便。而 SPL 提供了非常独特的函数选项,使功能相似的函数可以共用一个函数名,只用函数选项区分差别。比如,select 函数的基本功能是过滤,如果只过滤出符合条件的第 1 条记录,可使用选项 @1:

T.select@1(Amount>1000)

对有序数据用二分法进行快速过滤,使用 @b:

T.select@b(Amount>1000)

函数选项还可以组合搭配,比如:

Orders.select@1b(Amount>1000)

结构化运算函数的参数有些很复杂,SQL 需要用各种关键字把一条语句的参数分隔成多个组,但这会动用很多关键字,也使语句结构不统一。SPL 使用层次参数简化了复杂参数的表达,即通过分号、逗号、冒号自高而低将参数分为三层:

join(Orders:o,SellerId ; Employees:e,EId)

数据持久化

SQLite 通过直接处理库表来实现数据持久化,分为增、改、删三种:

insert into Orders values(201,'DSL',10,2000.0,'2019-01-01')update Orders set Client='IBM' where orderID=201delete from Orders where orderID=201

批量新增是常见的需求,SQLite 代码如下:

insert into Orders(OrderID,Client,SellerID,Amount,OrderDate) select 201,'DSL',10,2000.0,'2019-01-01'union allselect 202,'IBM',10,3000.0,'2019-01-01'

SPL 集文件的持久化有两种方式。第一种方式:直接处理集文件,可实现记录新增(及批量新增)。比如:


A

1

=create(OrderID,Client,SellerID,Amount,OrderDate)

2

=A1.record([201,"HDR",9,2100.0,date("2021-01-01"),
202,"IBM",9,1900,date("2021-01-02")])

3

=file("d:/Orders.btx").export@ab(A2)

第二种方式:先处理内存里的序表,再将序表覆盖写入原来的集文件。这种方式可实现增、改、删。只要将上面 A3 格里的 export@ab 改为 export@b 即可,@a 表示追加,@b 表示集文件格式。这种方式性能不如 SQLite,但嵌入计算的数据量普遍不大,覆写的速度通常可接受。

SPL 组表支持高性能增删改,适用于大数据量高性能计算,不是本文重点。

SQLite 只支持库表的持久化,不支持其他数据源,包括 csv 文件,硬要实现的话只能借助 Java 硬编码或第三方类库,代码非常繁琐。

SPL除了支持集文件的持久化,也支持其他数据源,同样是通过序表为媒介。

file("d:/Orders.csv").export@t(A2)      //csv文件file("d:/Orders.xlsx").xlsexport@t(A2)      //xls文件file("d:/Orders.json").write(json(A2))      //json文件

特别地,SPL 支持任意数据库的持久化。比如:


A

B

1

=connect("orcl")

/连接外部 oracle

2

=T=A1.query("select * from salesR where SellerID=?",10)

/批量查询,序表 T

3

=NT=T.derive()

/复制出新序表 NT

4

=NT.field("SELLERID",9)

/批量修改新序表

5

=A1.update(NT:T,sales;ORDERID)

/持久化

数据库的持久化依然以序表为媒介,可以明显看出这种方式的优点:函数 update 自动比对修改(增改删)前后的序表,可方便地实现批量数据地持久化。

流程处理

SQLite 缺乏流程处理能力,无法实现完整的业务逻辑,只能将 SQL 数据对象转为 Java 的 resultSet/List<EntityBean>,再用 for/if 语句处理流程,最后再转回 SQL 的数据对象,代码非常繁琐。复杂的业务逻辑要在 SQL 对象和 Java 对象之间转换多次,更加麻烦。

SPL 提供了流程控制语句,配合内置的结构化数据对象,可以方便地实现各类业务逻辑。

分支结构:


一个

2


3

if T.AMOUNT>10000

=T.BONUS=T.AMOUNT*0.05

4

else if T.AMOUNT>=5000 && T.AMOUNT<10000

=T.BONUS=T.AMOUNT*0.03

5

else if T.AMOUNT>=2000 && T.AMOUNT<5000

=T.BONUS=T.AMOUNT*0.02

循环结构:


一个

1

=db=connect("db")


2

=T=db.query@x("select * from sales where SellerID=? order by OrderDate",9)

3

for T

=A3.BONUS=A3.BONUS+A3.AMOUNT*0.01

4


=A3.CLIENT=CONCAT(LEFT(A3.CLIENT,4), "co.,ltd.")

5


上述代码之外,SPL 还有更多针对结构化数据的流程处理功能,可进一步提高开发效率,比如:每轮循环取一批而不是一条记录;某字段值变化时循环一轮。

应用结构

Java 集成

SQLite 提供了 JDBC 接口,可以被 Java 代码方便地集成:

Class.forName("org.sqlite.JDBC");Connection conn = DriverManager.getConnection("jdbc:sqlite: d:/ex1.db");Statement statement = conn.createStatement();ResultSet results = statement.executeQuery("select * from Orders where Amount>1000 and Client like '%s%'");

SQLite 的内核是 C 语言编写的,虽然可以被集成到 Java 应用中,但不能无缝集成,和 Java 主程序交换数据时要耗费额外的时间,在数据量较大或交互较频繁时性能就会明显下降。同样因为内核是 C 程序,SQLite 会在一定程度上破坏 Java 架构的一致性和健壮性。

SPL 同样提供了 JDBC 接口,集成方法和 SQLite 类似:

Class.forName("com.esproc.jdbc.InternalDriver");Connection conn =DriverManager.getConnection("jdbc:esproc:local://");Statement statement = conn.createStatement();ResultSet result = statement.executeQuery("=T(\"D:/Orders.csv\").select(Amount>1000 && like(Client,\"*s*\"))");

SPL 是纯 Java 编写的,可被 Java 应用无缝集成,架构一致性强,系统更稳定,不需要耗费额外时间交换数据,性能更有保障。

业务逻辑外置

一般的 RDB 支持存储过程,可将业务逻辑外置于 Java 程序,但 SQLite 不支持存储过程,完整的业务逻辑通常要借助 Java 的流程处理功能才能实现,也就不能外置于 Java 程序。业务逻辑不能外置于 Java 代码,导致两者耦合性过高。

SPL 可实现完整的业务逻辑,业务逻辑(或复杂的、经常变化的计算代码)可保存为脚本文件,并外置于 Java 程序。Java 程序以存储过程的形式引用脚本文件名:

Class.forName("com.esproc.jdbc.InternalDriver");Connection conn =DriverManager.getConnection("jdbc:esproc:local://");CallableStatement statement = conn.prepareCall("{call queryOrders()}");statement.execute();

外置的 SPL 脚本不仅可以有效降低系统耦合性,还具有热切换的特点。SPL 是解释型代码,修改后不必编译就可直接运行,也不必重启 Java 应用,可有效降低维护成本。

内存计算

SQLite 可用于内存计算,一般在应用启动时将数据加载至内存(URL 有变化):

Connection conn= DriverManager.getConnection("jdbc:sqlite:file::memory:?cache=shared");Statement st =conn.createStatement();st.execute("restore from d:/ex1");

需要进行业务计算时,就可以直接利用之前加载好的内存数据:

Class.forName("org.sqlite.JDBC");Connection conn= DriverManager.getConnection("jdbc:sqlite:file::memory:?cache=shared");Statement statement = conn.createStatement();ResultSet results = statement.executeQuery("select OrderID,Client,Amount,Name,Gender Dept from Orders left join Employees on Orders.SellerId=Empoyees.EId");

SPL 同样可用于内存计算,在应用启动时执行脚本,将数据加载至内存(URL 不变):


A

1

= connect("orcl").query@x("select OrderID,Client,SellerID,OrderDate,Amount from orders order by OrderID")

2

>env(Orders,A1)

3

>env(Employees,T("d:/Employees.csv"))

需要进行业务计算时,可直接利用之前加载好的内存数据:

Class.forName("com.esproc.jdbc.InternalDriver");Connection conn =DriverManager.getConnection("jdbc:esproc:local://");Statement statement = conn.createStatement();ResultSet result = statement.executeQuery("=join@1(Orders:O,SellerId; Employees:E,EId).new(O.OrderID, O.Client,O.Amount,E.Name,E.Gender,E.Dept)");

关联计算如果频繁发生,性能必然下降,改用宽表的话又太占内存,SQL 没有好办法解决这个问题。SPL 独有预关联技术,可大幅提升关联计算的性能,且不占用额外内存。SPL 还有更多内存计算技术,通常比 SQLite 性能好得多,包括并行计算、指针式复用、内存压缩等。

SQLite 可以方便地嵌入 Java,但数据源加载繁琐,计算能力不足,无法独立完成业务逻辑,架构上弱点颇多。SPL 也很容易嵌入 Java,且直接支持更多数据源,计算能力更强,流程处理方便,可独立实现业务逻辑。SPL 还提供了多种优化体系结构的手段,代码既可外置也可内置于 Java,支持解释执行和热切换,可进行高性能内存计算。

SPL下载地址:
http://c.raqsoft.com.cn/article/1595816810031

SPL开源地址:
https://github.com/SPLWare/esProc