QuerySurge and Web Services: RESTful API Queries

Versions: 8.0+

QuerySurge users know QuerySurge's strengths in connecting to a broad variety of back-end data systems, from traditional databases, to Big Data products, to Flat Files and XMLs. However, users are increasingly interested in using QuerySurge to test data from Web Services. This article describes a worked example of calling into a RESTful API from QuerySurge to compare data obtained from the webservice call to other up- or down-stream data stores.

In this example, we use a custom function plugged into QuerySurge's Flat File JDBC driver to issue a RESTful API request and to write the results to a CSV file. The resulting Flat File is then queried using the standard approach as part of a QueryPair. 

The basics of setting up a Flat File for QuerySurge are discussed in these Knowledge Base articles: Configuring Connections Delimited Flat Files. This example produces a simple comma-delimited file, for which this setup is appropriate. A worked example for writing a Custom Function for the Flat File driver is available in this article.

REST Client

For a RESTful webservice client, we use the Jersey client, which is part of the full Jersey webservice framework. Details of its use are available on the Jersey website; we don't discuss Jersey usage deeply here beyond the illustrations in the sample code (see the download in the Resources section at the end of this article).

REST Webservice

For the worked example shown here, we use the public GeoNames RESTful webservice. If you plan to work with this service, please read and follow their terms and conditions here. GeoNames documentation is available here.

The specific query that we'll use to illustrate using a REST service with QuerySurge is:

http://api.geonames.org/postalCodeSearchJSON?postalcode_startsWith=9136&maxRows=30&username=<registered-username-here>

This query returns all postal codes listed starting with the digits passed. For safety, we also limit the number of rows returned. If you plan to execute this call, note that you'll need to sign up for a GeoNames username.

The style of response returned by this query is a JSON array, with the root called postalCodes:

{"postalCodes":
  [
     {"adminCode2":"094",
      "adminCode3":"09474",
      "adminName3":"Landkreis Forchheim",
      "adminCode1":"BY",
      "adminName2":"Upper Franconia",
      "lng":11.174297332763672,
      "countryCode":"DE",
      "postalCode":"91362",
      "adminName1":"Bayern",
      "ISO3166-2":"BY",
      "placeName":"Pretzfeld",
      "lat":49.754543524813435},
      ...
   ]
}

Create a Custom Function to call a Webservice and Process the Response

Note: The JDK level used to compile the jar containing your custom function file must match the JDK level used by QuerySurge. You can find your QuerySurge Java Version in Administration > Server Properties > Java Version.

Create a custom Java method to return the JSON converted to a CSV file. As illustrated here, custom methods must always be public and static. Our method takes five arguments:

• webserviceURL - the full RESTful URL for the query
• csvFilePath - the file path (including the file name) that the resulting csv file is written to.
• jsonResultArrayRoot - the JSON  response's array root
• jsonArrayElementNames - the JSON elements that correspond to the csv columns
• verbose - a 'true' value dumps the response JSON to System.out; this is for debugging outside of QuerySurge

   public static void restQuery(String webserviceURL, String csvFilePath,
      String jsonResultArrayRoot, String jsonArrayElementNames, boolean verbose)
      throws URISyntaxException, JsonParseException, JsonMappingException,                   IOException {
      // handle the json array element names - these will be column names
      String [] jsonArrayElements = jsonArrayElementNames.split(",");
      // create a rest handler
      JerseyRestHandler rest = new JerseyRestHandler(jsonResultArrayRoot,                   jsonArrayElements);
      // handle the REST URL
      String url = (!webserviceURL.startsWith(JerseyRestHandler.HTTPPROTOCOL)) ?            JerseyRestHandler.HTTPPROTOCOL + webserviceURL : webserviceURL;
      URI uri = new URI(url);
      String resp = null;
      if ((uri.getPath() != null) && (uri.getQuery() != null)) {
          String query = uri.getQuery();
          String [] kvpairs = query.split("&");
          String [] searchKeys = new String[kvpairs.length];
          String [] vals = new String[kvpairs.length];
          for (int n = 0; n < kvpairs.length; n++) {
             String [] kvpair = kvpairs[n].split("=");
             searchKeys[n] = kvpair[0];
             vals[n] = kvpair[1];
          }
          // make the request and trap the JSON response
          resp = rest.getRestResponse(uri.getHost(), uri.getPath(),
            searchKeys, vals);
       } else {
          // a helper method for simple REST URLs
          resp = rest.getRestResponse(uri.getHost(), uri.getPath());
       }
       // verbose = print the JSON
       if (verbose) {
          System.out.println(resp);
       }
       // convert the JSON to a csv file
       rest.jsonToCsv(resp, csvFilePath);
    }

It is important to note that this code creates a JerseyRestHandler object with the getRestResponse() method. The JerseyRestHandler is where the REST client specifics for this example are found. This is quite a simple implementation, designed only to illustrate the basics of calling into a RESTful webservice.

Note: For the full set of classes for this example, see the download in the Resources section at the end of this article.

Once your custom function is exported as a jar file, you're ready to set it up on your QuerySurge Agent.

Custom Function Set-Up on the Agent

There are two main steps required to set up your custom function on your Agent. The Agent service must be shut down while performing these steps, and restarted after the steps are completed.

1. Copy the jar file to your QuerySurge Agent; it should be copied to the <QuerySurge Install Dir>\QuerySurge\agent\jdbc directory. Also, be sure to include any external dependencies on the classpath.
   
   
2. The next step is to edit the agentconfiguration.xml so that your Agent can "see" the custom function. The file is found in the <QuerySurge Install Dir>\QuerySurge\agent\config directory. Make a copy of the file before you modify it, and modify with care.
   
   Find the <connectionProps> tag, and add the following <driverProp> child tag:
   
   

   <connectionProps>
   ...
   

   <driverProp driver="jstels.jdbc.csv.CsvDriver2" prop="function:restQuery" 
   type="void" value="com.rttsweb.querysurge.rest.JerseyRestClient.restQuery" />
   

   ...
   </connectionProps>

After you've made these changes, re-start your Agent. You Agent should be ready to query the REST webservice, and compare the results to another data store in your environment.

A Query Against the REST Service

Create a QueryPair, and following the connection instructions for Flat Files, create a Flat File connection for the file that your REST data will be written to. Your Source (or Target) REST "query" consists of two statements:

• a call into the custom function
• a SQL query to query the resulting Flat File

The "query" is shown below. The call into the RESTQUERY function, takes five arguments; the third and fourth argument require brief comment. The third argument, the jsonResultArrayRoot argument, is the name of the JSON array, which you can see from the response sample above is "postalCodes". The fourth argument, jsonArrayElementNames, is a comma-separated list of JSON elements that the custom function pulls out from the JSON to create the csv file. There are 12 elements, so the csv file has 12 columns.  Finally, the last argument should be false when the method is called in QuerySurge.

CALL RESTQUERY(
'http://api.geonames.org/postalCodeSearchJSON?postalcode_startsWith=9136&maxRows=100&username=<registered-username-here>',
'C:\some\directory\json-geo.csv',
'postalCodes',
'adminCode2,adminCode3,adminName3,adminCode1,adminName2,lng,countryCode,postalCode,adminName1,ISO3166-2,placeName,lat',
false);

SELECT g.adminCode2
,g.adminCode3
,g.adminName3
,g.adminCode1
,g.adminName2
,g.lng
,g.countryCode
,g.postalCode
,g.adminName1
,g."ISO3166-2"
,g.placeName
,g.lat
FROM "json-geo" g;

The SQL here has to follow the regular Flat File syntax. If you're familiar with the syntax, if follows the H2 grammar. Finally, note that each of the two statements in the "query" needs to be semi-colon terminated.

Note: The file that is produced by the custom function in this example has no headers, so for this SQL query to work, headers are imposed on the Connection. You can use the list of JSON elements in the fourth argument to RESTQUERY as the header list: adminCode2,adminCode3,adminName3,adminCode1,
adminName2,lng,countryCode,postalCode,adminName1,ISO3166-2,placeName,lat

Building This Worked Example

You can build this worked example as described here with the files in the Resources section below. In order to compile the code, you'll need the following maven dependencies:

<dependency>
     <groupId>org.glassfish.jersey.core</groupId>
     <artifactId>jersey-client</artifactId>
     <version>2.25.1</version>
</dependency>
<dependency>
     <groupId>com.fasterxml.jackson.core</groupId>
     <artifactId>jackson-databind</artifactId>
     <version>2.7.4</version>
</dependency>

Resources