The multiple source query plugin allows SQuirreL users to create a virtual data source that may consist of multiple data sources on different servers and platforms. The user can enter one SQL query to combine and join information from multiple sources. Any database that has a JDBC driver is supported including NoSQL databases such as MongoDB.
Benefits
-
The plugin allows SQuirreL to natively support multiple source queries.
- No data source or server changes are required.
---对现有数据库无任何影响
- The plugin supports standard SQL including joins, group by, aggregation, LIMIT, and ordering where tables may come from one or more sources.
---支持对异构数据库进行join ,group by等操作
- The plugin will perform function translation when a user requests a function or SQL feature/syntax that is not supported by a certain source.
---支持函数解析/转化,即使目标数据库不支持该函数。
-
Users can export the virtualization information and use the virtualization driver in other Java programs and reporting software.
这个插件涉及的内容比较多,查看plugin下的文档,会比我解释的清楚点。
在此,看下两个最突出的特点:
1、异构数据库间联合查询
上图中,
SQL Query joining tables in MySQL, Microsoft SQL Server, Oracle,PostgreSQL, and MongoDB。
2、函数转化
The plugin uses features of the UnityJDBC driver to perform virtualization and translate functions that are not implemented by certain sources. For example, MSSQL does not support TRIM(), but you can do the same result using RTRIM(LTRIM()). Unity will automatically translate a TRIM() function specified in a MSSQL query to the correct syntax supported by the database.
Example TRIM() Translation for MSSQL
This translation is supported for common databases and can be freely extended by user-defined functions and translations for each database dialect.
EXPLAIN can be used to understand how a SQL query is translated to queries on individual sources.
第二个例子:
我们都知道,每个数据库的分页操作各有不同。
mysql-->limit
oracle--> rownum
MSSQL-->top
但是,使用了这个插件,分页操作都是 limit 了,也就是统一了部分数据库方言。
SELECT *
FROM oracle.BASE_USER
limit10;
***一定是在multisource下,这个语句才能执行。
3、注意事项
①、Make sure to add other database JDBC jars into thesquirrel/libfolder or JRE classpath
INSERTinto xxTest.base_user
select *
from oracle_dev.BASE_USER a
where a.USER_ID=
'admin';
select *
from xxTest.base_user;
select *
from oracle_dev.BASE_USER;