一个SQL查询，用于访问Java中的多个数据源(来自oracle、excel、sql server)

Question

我需要开发能够使用一个Oracle, Excel, Microsoft Sql Server从多个数据源( SQL query等)获取数据的应用程序。例如：

 SELECT o.employeeId, count(o.orderId) 
    FROM employees@excel e. customers@microsoftsql c, orders@oracle o 
    WHERE o.employeeId = e.employeeId and o.customerId = c.customerId 
    GROUP BY o.employeeId;

这个sql和数据源必须通过java程序进行动态更改。我的客户希望在我的应用程序的web接口中，与sql-like query group by, having, count, sum、等同时从不同的数据库和存储中编写和运行。的其他要求是完善的、重量轻的。

我找到了这样做的方法(我看到了什么缺点，如果我做错了，请把我治好)：

1. 阿帕奇火花 (缺点：沉重的解决方案，对于BigData更好，如果您需要更新信息而不将其缓存在火花中，则缓慢)，
2. 在SQL server (Oracle的数据库链接，Microsoft SQL server的链接服务器，Excel的Power查询)中的分布式查询-缺点：java程序动态更改数据源的问题和使用Excel的问题
3. Prestodb (缺点：沉重的解决方案，更适合BigData)，
4. 阿帕奇钻 (缺点：相当年轻的解决方案，一些不是最新的odbc驱动程序的问题和工作时的一些bug)，
5. 阿帕奇方解石 (Apache钻机使用的ligth框架，缺点：非常年轻的解决方案)，
6. 手动从数据源进行连接(缺点：在结果集中开发正确的联接、“按组”、查找最佳执行计划等大量工作)

也许，您是否知道其他方法(使用免费开源解决方案)，或者从您的经验中为我提供关于上述方法的任何建议？任何帮助都将不胜感激。

Answer 1

适当的解决方案之一是包含JDO、JPA和REST的DataNucleus平台。它支持几乎所有的关系数据库管理系统(PostgreSQL、MySQL、SQLServer、Oracle、DB2等)和NoSQL数据存储(如基于地图、基于图形、基于Doc等)、数据库web服务、LDAP、XLS、ODF、XML等文档。

或者，您也可以使用EclipseLink，它还支持关系数据库管理系统、NoSQL、数据库web服务和XML。

通过使用JDOQL的一部分JDOQL，可以满足一个查询访问多个数据存储的要求。这两种解决方案都是开源的、相对轻量级的和性能良好的.

我为什么要提出这个解决方案？

• 从您的需求来看，它理解数据存储将是您的客户选择，而您并不是在寻找大数据解决方案。
• 你更喜欢开源的解决方案，它重量轻，性能好.
• 考虑到您的用例，您可能需要一个具有polyglot持久性行为的数据管理平台，它能够基于您/客户的用例利用多个数据存储。

阅读有关polyglot持久性的更多信息 https://dzone.com/articles/polyglot-persistence-future https://www.mapr.com/products/polyglot-persistence

Answer 2

UnityJDBC是一个商用 JDBC驱动程序，它封装了多个数据Driver，并允许您将它们当作是同一个数据库的一部分。它的工作如下：

您可以定义一个“架构文件”来描述每个数据库。模式文件类似于以下内容：

...
<TABLE>
    <semanticTableName>Database1.MY_TABLE</semanticTableName>
    <tableName>MY_TABLE</tableName>
    <numTuples>2000</numTuples>
 <FIELD>
    <semanticFieldName>MY_TABLE.MY_ID</semanticFieldName>
    <fieldName>MY_ID</fieldName>
    <dataType>3</dataType>
    <dataTypeName>DECIMAL</dataTypeName>
    ...

您还拥有一个中央“源文件”，该文件引用所有模式文件并提供连接信息，如下所示：

<SOURCES>
    <DATABASE>
        <URL>jdbc:oracle:thin:@localhost:1521:xe</URL>
        <USER>scott</USER>
        <PASSWORD>tiger</PASSWORD>
        <DRIVER>oracle.jdbc.driver.OracleDriver</DRIVER>
        <SCHEMA>MyOracleSchema.xml</SCHEMA>
    </DATABASE>
    <DATABASE>
        <URL>jdbc:sqlserver://localhost:1433</URL>
        <USER>sa</USER>
        <PASSWORD>Password123</PASSWORD>
        <DRIVER>com.microsoft.sqlserver.jdbc.SQLServerDriver</DRIVER>
        <SCHEMA>MySQLServerSchema.xml</SCHEMA>
    </DATABASE> 
</SOURCES>

然后，您可以使用unity.jdbc.UnityDriver允许您的Java代码运行跨数据库连接的SQL，如下所示：

String sql = "SELECT *\n" +
"FROM MyOracleDB.Whatever, MySQLServerDB.Something\n" +
"WHERE MyOracleDB.Whatever.whatever_id = MySQLServerDB.Something.whatever_id";
stmt.execute(sql);

因此，看起来UnityJDBC提供了您所需要的功能，但是，我不得不说，任何允许用户执行跨不同数据库连接表的任意SQL的解决方案，听起来都是让您的数据库屈服的秘诀。实际上，我向您的需求类型推荐的解决方案是，将do ETL 进程从所有数据源转换为单个 数据仓库，并允许用户对此进行查询；如何定义这些流程和数据仓库，对于堆叠溢出问题来说肯定太宽泛了。

Answer 3

SQL与数据库管理系统相关。server将需要Oracle SQL服务器以外的其他SQL语句。

我的建议是使用JPA。它完全独立于您的数据库管理系统，使Java的开发更加高效。

缺点是，无法将几个数据库系统与JPA开箱即用(类似于Server和Oracle SQL server之间的1:1关系)。但是，您可以创建几个EntityManagerFactories (每个数据库一个)，并将它们链接到代码中。

在这个场景中，JPA的优点：

• 编写独立于JPQL查询的数据库管理系统
• 减少所需的java代码

JPA的缺点：

• 不能将来自不同数据库的实体关联起来(例如在1:1的关系中)
• 不能用一个查询来查询多个数据库(将来自不同数据库的表组合在group by或类似的数据库中)

更多信息：

• 维基百科