Configuring the CData Query Federation Driver

The CData Query Federation Driver allows you to federate and aggregate data across drivers as a single database connection. By combining the Query Federation Driver with other CData JDBC Drivers or CData ADO.NET Providers, you can 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. This article walks through configuring the Query Federation Driver to connect to multiple data sources.

To start working with all your data from a single endpoint, install the Query Federation Driver along with CData drivers for your specific data sources. We will be looking at the JDBC drivers for Salesforce, MySQL, and Excel, but the principals for configuring the Federation Driver extend to any of the supported 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)

Free Trial & More Information

The ability to work with data across sources, whether they are SaaS, Big Data, NoSQL, or even flat file sources is invaluable to the data-driven organization. With our richly featured, high-performance Query Federation drivers, you get a single, uniform experience with all your data, no matter where it is, backed by a world-class support team. Download a free, 30-day trial of our Query Federation Driver to try it for yourself.