跨库运算的手段
完整数据有时会存储在多个数据库中，这种情况就要进行跨库的混合运算，本文深度对比了四类跨库手段，包括 DBLink\ 集算器 SPL\ Scala\Calcite，重点考察这些工具的跨库代码写法、部署配置、计算性能方面的差异，详情点击跨库运算的手段
出于各种原因，完整数据有时会存储在多个数据库中，这种情况就要进行跨库的混合运算。跨库运算的手段种类多样原理不同，下面将对比这些手段，尤其是代码写法、部署配置、性能方面的差异。
DBLink
DBLink是Oracle提供的跨库运算手段，可以直接使用Oracle SQL语法，代码因此简单易懂。
比如：订单表Orders在MySQL库里，需要先汇总出每个SellerId的销售额，再和Oracle库的员工表Employees关联，将销售员的名字和销售额对应起来。
只需编写SQL：select e.name, o. subtotal from Employees e, (select SellerId, sum(Amount) subtotal from Orders@mysql1 group by SellerId) o where o.Sellerid=e.EId
SQL中的mysql1为MySQL的数据源名，表和数据源的关系可以简单地用@来表示。
DBLink的代码虽然简单，但配置起来却很复杂，不仅要安装不同厂商的组件，还要注意版本之间的搭配，之后还需在多处进行配置，除了少数配置项有界面，大多数配置项只能手工修改文件。主要配置步骤有：
1. 分别配置好Oracle Client和 MySQL ODBC，确保可单独访问Oracle和MySQL。
2. 安装MySQL数据库网关，即Oracle Database Gateway for ODBC。
3. 启动Oracle Heterogeneous Service(HA)及相关的Agent。
4. 在Oracle中新建异构数据库ODBC配置文件，在该文件中填入MySQL ODBC相关信息。
5. 在listener.ora和tnsnames.ora里分别配置MySQL数据源，两者须一致。
6. 使用DDL命令建立指向MySQL的DBlink（比如叫mysql1）。
DBLink的性能较差，主要是因为不能很好地利用其它数据库的计算能力。可能是出于语法兼容的考虑，Oracle会把涉及远程表的计算翻译为两部分：在远程服务器执行的SQL，即无函数的条件查询、计算列、取全部数据这三种；其他绝大部分计算都转为本地执行，包括带函数的查询或计算列、排序、分组汇总等等。这样一来，有很多在远端可以很快完成的计算，都不得不转移到本地来缓慢执行，比如分组汇总这种结果比源数据小的计算，也要额外花费传输成本，把源数据取到本地再算。
上面的例子中，select SellerId, sum(Amount) subtotal from Orders@mysql1 group by SellerId是涉及远程表的计算。如果在远程完成这个计算，只需把很小的结果集传到本地就可以，但Oracle会把它翻译为2部分，远程实际执行的是select SellerId, Amount from Orders，也就是取全部源数据；从远程取来数据存为本地临时表（假设叫$Orders）之后，本地再执行select SellerId, sum(Amount) subtotal from $Orders group by SellerId。
除了Oracle之外，DB2（Federated Database）和MSSQL（Linked Server）也有类似的跨库计算手段，由于原理相近，因此优缺点也是类似的。
Scala
Scala的设计初衷虽然是通用开发语言，但实际上多应用于结构化数据计算。Scala提供了三种数据类型（ DataFrame、DataSet、RDD）和两种计算语法（DSL、SQL），都可以实现跨库计算，且互相可以转换。比如，前面提到的计算可以写成：
package test import org.apache.spark.sql.SparkSession import org.apache.spark.sql.DataFrame object testJoin { def main(args: Array[String]): Unit = { //create spark session on local val spark = SparkSession.builder() .master("local") .appName("example") .getOrCreate() //load Employees from oracle val Employees = spark.read .format("jdbc") .option("url", "jdbc:oracle:thin:@127.0.0.1:1521:ORCL") .option("query", "select EId,Name from employees") .option("user", "scott") .option("password", "password") .load() //load Orders group from MySql val O = spark.read .format("jdbc") .option("url", "jdbc:mysql://127.0.0.1:3306/mysql1") .option("query", "select SellerId, sum(Amount) subtotal from Orders group by SellerId") .option("user", "root") .option("password", "password") .load() //join using dataframe val join=O.join(Employees,O("SellerId")===Employees("EId"),"Inner") .select("Name","subtotal") join.show() } } |
可以看出，Spark整体代码虽然较长，但核心用于计算的代码却很简单。另外，Spark底层是JAVA，二者互相调用的代码也很简单。
Scala跨库计算的配置比Oracle容易得多，只需将数据库驱动Jar包加入类路径。从这一点可以看出，Scala的跨库计算更加开放更加专业，而Oracle的跨库计算封闭且不成熟。
相对来讲，Scala性能表现更好，因为它可以利用数据库的计算能力。从上面代码可以看出来，涉及远程表的计算可以真正在远程执行，比如MySQL端执行的是select SellerId, sum(Amount) subtotal from Orders group by SellerId。如此便只需返回少量的数据集，而不是全量表数据，传输速度因此显著加快。
上面的代码是全内存计算，只适合数据量较小的情况。如果数据库返回的数据量较大，还应开启内外存自动交换功能，具体代码是O. persist()。应该注意的是，Scala鼓励内存计算，在外存计算方面并不成熟，很容易发生故障，因此不推荐这种用法。
Calcite
Calcite是Apache软件基金会旗下的开源项目，其目标是“one size fits all”，即一种查询引擎适应所有计算平台和数据源。Calcite支持多种风格的SQL（并非实际执行的方言SQL），比如，用MySQL风格实现前面的跨库计算：
package Test; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class testCalcite { public static void main(String[] args)throws Exception { Properties config = new Properties(); config.put("model", "d:\\ds.json"); //using built-in lexical policy of the MySQL style config.put("lex", "MYSQL"); String sql ="select e.name, o. subtotal from orcl.Employees e, (select SellerId, sum(Amount) subtotal from mysql1.Orders group by SellerId) o where o.Sellerid=e.EId"; Connection con = DriverManager.getConnection("jdbc:calcite:", config)); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql)); if(con!= null) con.close(); } } |
代码中的orcl和mysql1是数据源名，分别指向Oracle和MySQL。可以看到，Calcite语法简单易懂，和普通JDBC在表面上没有太大差别。
Calcite的配置也很简单，除了将数据库jar包加入类路径之外，只需编写一个数据源文件，即上面代码中的ds.json。
在计算性能方面，Calcite和Oracle类似，都不能真正利用异构数据库的计算能力，因此表现较差。Calcite的风格化SQL语句经过层层翻译，最后会分为2部分，一部分是在数据库上执行的计算方言（比如MySQL的SQL），另一部分是获取数据库的计算结果后，在Calcite执行的本地计算。计算方言的翻译工作难度很大，需要兼容不同的数据库，只能翻译得简单些，比如group by只翻译成取记录，而不是实质的分组汇总，这就浪费了数据库的计算能力。本地计算的翻译工作相对受控，也没有兼容性的顾虑，可以尽量多翻译一些，比如真正的分组汇总。
Calcite还有一个缺点：只能做全内存计算，数据量大的容易内存溢出。
集算器 SPL
集算器 SPL是专业的开源结构化计算引擎，也具有鲜明的one size fits all特性，但集算器 SPL更“轻”， 层次更少，语法更简单。比如，前面提到的计算可以写成脚本文件（cross.dfx）：
A |
|
1 |
=orcl.query("select EId,Name from employees") |
2 |
=mysql1.query("select SellerId, sum(Amount) subtotal from Orders group by SellerId") |
3 |
=join(A1:O,SellerId; A2:E,EId) |
4 |
=A3.new(O.Name,E.subtotal) |
上述脚本可在IDE中执行或调试，也可以JDBC的形式嵌入JAVA，如下：
package Test;
|
简单计算也可直接将计算语句写在JAVA代码中，而无须脚本文件。比如下面这样：
ResultSet result = statement.executeQuery("=join(orcl.query(\"select EId,Name from employees\"):O,SellerId; mysql1.query(\"select SellerId, sum(Amount) subtotal from Orders group by SellerId\"):E,EId).new(O.Name,E.subtotal)"); |
在计算性能方面，SPL与Scala类似，都可以很好地利用数据库的计算能力，因此计算性能较佳。比如上面代码中，MySQL只需返回少量的数据集，而不是全量表数据。
如果数据量较大，还可改为游标进行计算。游标是SPL专门为大数据量计算设计的，成熟稳定。比如按Employees表的Dept字段分组，对Orders表的Amount字段汇总：
A |
|
1 |
=orcl.cursor("select EId, Dept from Employees order by EId") |
2 |
=mysql1.cursor("select SellerId, Amount from Orders order by SellerId") |
3 |
=joinx(A2:O,SellerId; A1:E,EId) |
4 |
=A3.groups(E.Dept;sum(O.Amount)) |
上面代码还利用了有序归并进行关联，性能会更好。
对于分库等特殊的跨库计算，SPL还支持并行计算语法，可显著提升性能：
A |
B |
C |
|
1 |
=[connect("mysql1"),connect("mysql2"),connect("mysql3")] |
/连接多个mysql |
|
2 |
select * from orders where amount>=10000 |
/SQL |
|
3 |
fork A1 |
=A3.query(A2) |
/并行执行SQL |
4 |
=A3.conj() |
/合并结果 |
集算器跨库计算的配置较为简单，只需将数据库驱动Jar包加入类路径，并设置数据库的数据源名。
通过上述比较可以看出：在语法表达方面，各种跨库运算手段都比较简单，在配置方面，除DBLink外都比较方便；在计算性能上，Scala和SPL更加优秀；在计算丰富和成熟性上，SPL更胜一筹。
英文版