Aqua Data Studio can query multiple databases with different SQL queries. However, you cannot query more than one database at the same time in one SQL query. UnityJDBC allows a user to write one query to join, aggregate, and summarize data across any number of databases. It also performs translation of SQL dialects and functions.
UnityJDBC allows Aqua Data Studio to support multiple source queries.
No data source or server changes are required.
UnityJDBC supports standard SQL including joins, group by, aggregation, LIMIT, and ordering where tables may come from one or more sources.
UnityJDBC will perform function translation where a user requests a function that is not supported on a certain source.
This example assumes that the installation of UnityJDBC has already been completed. The following is an example of creating sources and executing multiple database queries with Aqua Data Studio.
Create a New Source Group - Under File Menu, Select New Source Group.
Select Sources File Name and Location - Use the default sources.xml or select a file location.
Select New Source - Under File Menu, Select New Source.
Add a Microsoft Database - Enter connection information for Microsoft SQL Server to add it to the data virtualization.
Add a MySQL Database - Enter connection information for MySQL database to add it to the data virtualization.
Viewing Data Virtualization of Sources - Select Exit to return to the main screen and see data virtualization of MySQL and Microsoft SQL Server sources.
Multiple Database Query and Cross-Database Join Example - Type in a cross-database query and execute it to view results.
Finding Sources and Schema Files - Now that the data virtualization is complete, find the sources.xml file and schema files for your sources. The image below shows the default location which would be inside the UnityJDBC installation directory.
Moving Sources and Schema Files - Optionally,
move the sources and schema files to a permanent location. In this example, they are
Adding UnityJDBC data source to Aqua Data Studio
unityjdbc.jar and all JDBC drivers for databases into
jre/lib/ext in Aqua Installation directory.
Start Aqua and Register a Generic JDBC Source - The configuration information is:
jdbc:unity://<path to sources.xml file>
Multiple Database Query in Aqua Data Studio - Create a query like usual except it can contain multiple databases.
Function and SQL Dialect Translation with Aqua Data Studio - Forget what functions you can use on each database? No problem – UnityJDBC will translate automatically. This translation is supported for common databases and can be freely extended by user-defined functions and translations for each database dialect.