Connecting QuerySurge to RESTful Web Services with the CData REST JDBC Driver

Versions: 8.0+

Numerous data-rich systems in modern computing environments expose data via RESTful Web Services. Such systems may be part of an ETL/LT architecture or may lend themselves to a data testing approach for an ETL/LT environment. In order to test RESTful data using QuerySurge, a JDBC driver is required to manage the REST "conversation", and to present a JDBC-based interface to QuerySurge. In this article we show the connection setup for the CData RESTful JDBC driver. 

Note: RTTS, the vendor of QuerySurge, partners with CData to make a broad range of JDBC drivers available to QuerySurge users. For information about our partnership, click here. See all of CData's JDBC offerings here. For questions related to ordering, contact us here.

Adding a RESTful Web Service Connection using the CData JDBC Driver

To connect to a RESTful web service, you'll need to use the Connection Extensibility feature of the QuerySurge Connection Wizard.

1. Download the CData REST JDBC driver.
   
   
2. Deploy the REST JDBC driver to your Agent(s). To install the CData driver, unzip the driver download and run their driver installer (setup.jar) to install the driver on your Agent box(es) (instructions for doing so can be found in the readme.txt file). We recommend that you install the driver in the default location. Once installed, copy the driver file(s) and the license file from the installation directory to your QuerySurge Agent jdbc directory (see the relevant Knowledge Base article for deploying drivers to Agents on Windows or Agents on Linux). You'll need to deploy a driver for each Agent box on which you intend to use the driver. 
     Note: For this example the deployed files are: a) cdata.jdbc.rest.jar, b) rssbus.jar, and c) cdata.jdbc.rest.lic.
   
   
3. Login to QuerySurge as a QuerySurge Admin user, and navigate to the Admin view. Select Connections in the leftnav tree, and click the Add button (at the bottom left of the main panel). Leave the Advanced Mode checkbox unchecked. You'll need the following standard information for the next few steps.
   

   Driver Class: cdata.jdbc.rest.RESTDriver

   Connection URL template: jdbc:rest:DataModel=<model_type>;URI=<resource_location>;Format=<response_format>

   
   We suggest using a DataModel of 'Relational', but see the CData driver documentation for more information. The Format is either XML or JSON. An actual URL (using the prototyping public REST service http://dummy.restapiexample.com/) looks like:

   jdbc:rest:DataModel=Relational;URI=http://dummy.restapiexample.com/public/api/v1/employees;Format=JSON

4. Enter a Connection Name, and select * All Other JDBC Connections (Connection Extensibility) from the Data Source dropdown menu.
   
   
5. Enter the Driver Class.
   
   
6. Enter the Connection URL. We're using a publicly accessible API in this example, so we'll leave the Username and Password blank. For your connection you'll need to enter the appropriate URL, and most likely, you'll need to supply credentials. Note that a REST service can handle authentication by a variety of different methods; consult the driver documentation for the different options.
   
   
   You can enter an optional Test Query. A test query should only return one row and one column. You'll need to check how the driver builds the metadata to see the table and column names in order to write a query (see below).
   
   
7. Verify the connection details, and (optionally) test the connection before saving.
   
   
   
   
8. A critical step in using your CData driver with your RESTful service is to understand how the driver "views" the response in terms of query metadata. In other words: what is the table and column structure that the driver builds for the response data coming back from your request? Use QuerySurge's View Metadata to see what tables/columns are available for any RESTful request. You can View table metadata for any connection either in the Admin view (after logging in as an Admin user) or via the Design menu -> View Connections List (available in QuerySurge v. 8.3)

You're all set! You can write and execute QueryPairs against the RESTful web service of choice. The CData documentation discusses different driver configuration options, advanced settings, and supported SQL syntax and functions.