MultiSource Virtualization Plugin

Overview

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

Installation

  1. Download and install SQuirreL. Add the MultiSource plugin during installation.
  2. The plugin contains unityjdbc.jar in the folder squirrel/plugins/multisource. Copy this jar into the squirrel/lib folder. Make sure to add other database JDBC jars into the squirrel/lib folder or JRE classpath. The plugin cannot access drivers in custom classpaths used by SQuirreL.
    UnityJDBC JAR Location Put UnityJDBC JAR in squirrel/lib folder
  3. Start SQuirreL. The multisource plugin should be visible in the plugin list.

How It Works

  1. Register data source aliases in SQuirreL as usual. This example has connections to a Microsoft SQL Server database, a MySQL database, an Oracle database, a PostgreSQL database, and a MongoDB database all containing the TPC-H benchmark data. Note that any database with a JDBC driver is supported including those accessible using the JDBC-ODBC bridge. For MongoDB support, the latest MongoDB Java driver should also be in the squirrel/lib folder.
    Microsoft SQL Server MySQL
    Oracle Postgres
    Register MongoDB DriverMongoDB
  2. Make sure you have registered the UnityJDBC MultiSource Virtualization driver (during installation). Create an alias consisting of virtual sources. The name field can be any name. It does not have to contain "virtual".
    Create Virtual Source Connect to Virtual Source
  3. Add existing JDBC connections (SQuirreL aliases), to the virtual source. The example adds all five of the sources created above. On the source name (in this case virtualDemo), right click and select (Virtualization) Add source.

    Before add Microsoft SQL (mssql) source:

    Select the source to add and click the Add button. Progress is shown.

    After adding the Microsoft source. Tables are visible in the object tree view.

  4. Users 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. You can also filter tables added by catalog name, schema name, and table filters. The table filters to include are specified in SQL (JDBC) syntax with a % as a wild-card match. The table exclusion filters are specified as Java regular expressions.

    Adding an Oracle Source with a Schema

    Object Tree View with all Five Sources Added

  5. 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.

    SQL Query joining tables in MySQL, Microsoft SQL Server, Oracle, PostgreSQL, and MongoDB

    A Translation Example

    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.

    Encryption

    The plugin saves its configuration information in the folder multisource under the SQuirreL user folder. If no password is specified when creating an alias, schema and connection informtaion (including passwords) are stored in plain text. To encrypt the configuration files, specify a password during connection (user id is ignored).

    Configuration File Location

    Specify Encryption by Providing a Password

    Virtualization Export for use with Other Programs and Reporting Software

    Once a virtualization has been created, all the information necessary is available in the XML files created and stored in the plugin/multisource folder in the SQuirreL user directory. These files can be copied and moved to another location and used with any Java/JDBC program or reporting software. These files can be copied directly or exported from within SQuirreL.

    Existing XML configuration files can be used by specifying an absolute or relative path in the JDBC URL. For example, if the user has saved the sources.xml file (and the associated schema files for sources in the directory C:\tmp, then a JDBC connection URL is: jdbc:unity://c:\tmp\sources.xml.

    Plugin Limits

    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 works as it only returns one result row.) Use LIMIT 100 to get the first 100 results of a query.


    For more information and technical support for the MultiSource plugin contact:
    Dr. Ramon Lawrence, ramon.lawrence@ubc.ca, 250-807-9390
    Associate Professor, Computer Science, University of British Columbia Okanagan, Canada
    UnityJDBC driver information: www.unityjdbc.com