Run Queries Across Data Sources with CData Query Federation

The CData Query Federation Driver, allows you to federate and aggregate data across drivers as a single database connection. You can combine the Query Federation Driver with other CData JDBC Drivers or CData ADO.NET Providers to access all your SaaS, Big Data, and NoSQL sources as if they were one database. You can query or update data from a single source and even execute complex queries that JOIN data across sources.

In this article, we will walk through the Query Federation Driver configuration to connect to multiple data sources. We will be looking at the JDBC drivers for Salesforce, MySQL, and Excel, but the principles for configuring the CData Query Federation Driver extend to all 250+ data sources that CData supports.

Download and Configure the Query Federation Driver

Start working with all your data from a single endpoint by installing the Query Federation Driver along with CData drivers for your specific data sources.

Like any other database driver, the Federation Driver connects to data using connection string properties. There are two key connection properties for the Query Federation Driver:

• Database Configuration: Defines the location of the JSON document that defines which CData drivers the Query Federation Driver uses to connect to data. Typically: %AppData%/Roaming/CData/Query Federation Data Provider/DatabaseConfiguration.json
  
• Default Schema: Which of the database schema defined in the Database Configuration document to use by default
  

The Database Configuration document contains JSON objects with fields for information about the drivers available to the Query Federation Driver. Each JSON object represents a database driver, and its name represents the schema name, like salesforcedb or mysqldb. The fields and values in the objects vary depending on whether you are using a Java-based Query Federation Driver (i.e. JDBC) or a C#-based Query Federation Driver (i.e. ADO.NET).

JDBC Schema Fields

• driverClass: The name of the JDBC class for the driver, (e.g. cdata.jdbc.salesforce.SalesforceDriver)
  
• connectionUri: The JDBC URL to connect to data, (e.g. jdbc:cdata:salesforce:User=user@domain.com;Password=password;)
  
• driverPath: The path on disk to the location of the driver file, (e.g. C:\Program Files\CData\CData JDBC Driver for Salesforce\lib\cdata.jdbc.salesforce.jar)
  

Sample DatabaseConfiguration.json File (JDBC)

{
"salesforcedb": {
"driverClass": "cdata.jdbc.salesforce.SalesforceDriver",
"connectionUri": "jdbc:cdata:salesforce:User=user@domain.com;Password=password;",
"driverPath": "/PATH/TO/cdata.jdbc.salesforce.jar"
},
"exceldb": {
"driverClass": "cdata.jdbc.excel.ExcelDriver",
"connectionUri": "jdbc:cdata:excel:Excel File=/PATH/TO/MyExcelFile.xlsx;",
"driverPath": "/PATH/TO/cdata.jdbc.excel.jar"
},
"mysqldb": {
"driverClass": "cdata.jdbc.mysql.MySQLDriver",
"connectionUri": "jdbc:mysql:Server=localhost;Database=test;User=root;Password=pwd",
"driverPath": "/PATH/TO/cdata.jdbc.mysql.jar"
}
}

ADO.NET Schema Fields

• providerName: The name of the ADO.NET class for the Provider, (e.g. System.Data.CData.Salesforce)
  
• connectionString: The connection string used to connect to data, (e.g. User=user@domain.com;Password=password;)
  
• providerPath: The path on disk to the location of the driver file, (e.g. C:\Program Files\CData\CData ADO.NET Driver for Salesforce\lib\System.Data.CData.Salesforce.dll)
  

Sample DatabaseConfiguration.json File (ADO)

{
"salesforcedb": {
"providerName": "System.Data.CData.Salesforce",
"connectionString": "User=user@domain.com;Password=password;",
"providerPath": "/PATH/TO/System.Data.CData.Salesforce.dll"
},
"exceldb": {
"providerName": "System.Data.CData.Excel",
"connectionString": "Excel File=/PATH/TO/MyExcelFile.xlsx;",
"providerPath": "/PATH/TO/System.Data.CData.Excel.dll"
},
"mysqldb": {
"providerName": "System.Data.CData.MySQL",
"connectionString": "Server=localhost;Database=test;User=root;Password=pwd",
"providerPath": "/PATH/TO/System.Data.CData.MySQL.dll"
}
}

Sample Query Federation Driver Connections

To connect to the Query Federation Driver from a JDBC-capable tool or application, simply create the JDBC URL for the Query Federation Driver:

JDBC URL

jdbc:queryfederation:DatabaseConfiguration=/PATH/TO/DatabaseConfiguration.json;DefaultSchema=salesforcedb

For an ADO.NET-capable tool, create the connection string for the Query Federation Driver:

ADO.NET Connection String

DatabaseConfiguration=/PATH/TO/DatabaseConfiguration.json;DefaultSchema=salesforcedb

With the Query Federation Driver configured, we can connect to and begin working with data from multiple sources as if they were in a single database. Below, you can see the database design based on the above schema settings file, as displayed in DBVisualizer.

Query Federation Driver Schema Layout (in DBVisualizer)

More Information

Working with data across sources, whether from SaaS, Big Data, NoSQL, or even flat-file sources is invaluable to the data-driven organization. Developers can pick multiple data processing systems and access all of them with a single SQL-based interface.

The CData Query Federation Driver offers a single, uniform experience with all your data, no matter where your data is housed or generated. The CData Query Federation Driver provides a universal data access layer that simplifies application development and data access. Using the Query Federation Driver, analysts can query data across systems through a common driver interface — all with standard SQL.