Table of Contents
The UnityJDBC SourceBuilder is a simple query utility for cross-database joins and data virtualization. In most cases, you will use UnityJDBC to perform data virtualization with reporting software such as JasperReports and Splunk or query software such as SQuirreL SQL or Aqua Data Studio. Once you have used SourceBuilder to build the virtual source and schema XML configuration files, you use UnityJDBC like any other JDBC driver. This section contains examples on how to install and use UnityJDBC data virtualization in popular software systems.
UnityJDBC is integrated into SQuirreL SQL allowing users to build SQL queries that join data from multiple sources directly within SQuirreL. 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.
Benefits:
-
The multisource plugin powered by UnityJDBC allows SQuirreL SQL to 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.
-
The plugin will perform function translation where a user requests a function that is not supported on a certain source.
UnityJDBC can be installed directly as a plug-in through the SQuirreL SQL plug-in interface. It is also possible to download the UnityJDBC distribution and replace the unityjdbc.jar in the plug-in with the latest from UnityJDBC.
-
Registering the UnityJDBC Driver - By adding a driver.
Adding the UnityJDBC to SQuirreL SQL
-
Registering your Data Sources - Register your data sources as usual. In this example, we will perform data virtualization of multiple databases on Microsoft SQL Server, MySQL, Oracle, and PostgreSQL. Any database with a JDBC driver is supported including those accessible using the JDBC-ODBC bridge.
Registering a Microsoft SQL Server Source in SQuirreL SQL
Registering a MySQL Source in SQuirreL SQL
Registering an Oracle Source in SQuirreL SQL
Registering a PostgreSQL Source in SQuirreL SQL
-
Create a Multiple Database Alias - Make sure you have registered the UnityJDBC driver (during installation). Create an alias consisting of virtual sources. The name field can be any name. It does not have to be virtual. If you are using the virtualization embedded into the plugin, the URL is
jdbc:unity://virtual. If you have previously created a data virtualization using the SourceBuilder utility, then the URL should be the file location of the sources file previously created.
Add a Data Virtualization Alias for Multiple Sources in SQuirreL SQL
-
Add Microsoft SQL Server to Data Virtualization - Right-click on the root object in the object tree, and select
(Virtualization) Add Source. User selects the Microsoft SQL Server source to add to the data virtualization.
Adding Microsoft SQL Server Source to the Data Virtualization in SQuirreL SQL
Prompt to add Microsoft SQL Server Source to the Data Virtualization
Resulting Data Virtualization
-
Add Oracle Database to Data Virtualization - User can add as many sources as they wish. You can also rename the source in the virtual view. It does not have to be the same as the alias name used by SQuirreL. When adding Oracle sources, make sure to specify a schema so that system tables and tables from all schemas are not extracted.
Prompt to add Oracle Source to the Data Virtualization (Note use of SCHEMA).
A Data Virtualization in SQuirreL SQL with Databases MySQL, Oracle, PostgreSQL, and Microsoft SQL Server
-
Execute a Multiple Database Query - The user can execute an SQL query that spans multiple sources and get a single result. The virtualization is transparent to the user and SQuirreL. Below is an example of a query that joins two tables in different databases.
A Multiple Database Query with Join Results Expressed on Previous Data Virtualization
-
Perform SQL Query Translation - The UnityJDBC driver used to perform the virtualization will also translate functions that are not implemented by certain sources. For example, Microsoft SQL Server does not support
TRIM(), but you can do the same result usingRTRIM(LTRIM()). Unity will automatically translate aTRIM()function specified in a MSSQL query to the correct syntax supported by the database.
An example of SQL Query and Dialect Translation - Converting TRIM() function for Microsoft SQL Server
The plugin source code, like all of SQuirreL, is released under the GNU Lesser General Public License. The UnityJDBC virtualization driver is released under a commercial license. However, the UnityJDBC driver included in the plugin is fully functioning with no time limits allowing an unlimited number of sources and queries. The only limitation is the size of the result set is limited to the first 100 rows. (Note there is no limit on the number of rows extracted from each source. So select count(*) from table with a 1 million row table is fine as it only returns one result row.) Use LIMIT 100 to get the first 100 results of a query. A full version of the UnityJDBC driver can be purchased as www.unityjdbc.com.