跨库运算的手段

出于各种原因，完整数据有时会存储在多个数据库中，这种情况就要进行跨库的混合运算。跨库运算的手段种类多样原理不同，下面将对比这些手段，尤其是代码写法、部署配置、性能方面的差异。

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;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class test1 {
public static void main(String[]   args)throws Exception {
Class.forName("com.esproc.jdbc.InternalDriver");
Connection connection   =DriverManager.getConnection("jdbc:esproc:local://");
Statement statement =   connection.createStatement();
ResultSet result = statement.executeQuery("call   cross()");
if(connection != null)   connection.close();
}
}

简单计算也可直接将计算语句写在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更胜一筹。