Creating API Driver API Profiles

RESTful APIs offer a consistent, straightforward way for businesses to work with external data and offer access to their own data. In June 2019, the number of public APIs grew beyond 22,000, with a growth rate of more than 2,000 APIs per year since 2015. APIs continue to provide value to organizations and developers, and with the CData API Driver, it is easier than ever to integrate with APIs.

Why use the API Driver?

The CData API Driver is built with the same goal as all the other CData Drivers: to ease the process of integrating your data. With the API Driver and the available API Profiles, you can instantly and codelessly query dozens of APIs (including any APIs built using the CData API Server) from BI, data integration, and custom applications. Thanks to its extensible design, organizations and developers can modify API Profiles from API Server to customize integrations and even create their own API Profiles. Easily expand the connectivity offered by API Driver to any RESTful API.

This article walks through creating a new API Profile to enable SQL access to a new API.

Creating an API Profile

API Profiles grant SQL access to APIs. While there are dozens of APIs supported out of the box, you are also able to create a new Profile to enable SQL access to any API.

Create a Schema File (.rsd)

API Profiles contain a collection of schema files that represent the endpoints available for the API. For this article, we will start a Profile for the TripPin OData v4 API by creating a schema file for the People endpoint. Each schema file has several parts that define how SQL access is enabled for an API endpoint.

• api:info: This keyword maps API fields to table columns through scripted column definitions.
• attr: This element represents a column definition (details are below).
• api:set attr="...": This keyword (attribute) sets various parameters for the API integration, including paging functionality and information on how to parse the API response (via the RepeatElement attribute).
• api:script method="...": This keyword defines how read and write functionality is implemented for the API endpoint, including which internal operation is called and how specific functionality like filtering is managed.

Start by creating a new text file called People.rsd. From here, we walk through creating each section of the schema file, based on the API specification.

Create Column Definitions

API Driver schema files enable SQL access to API endpoints, and this starts with creating column definitions for corresponding API fields for the given endpoint, using API Script keywords and other functionality. An api:script keyword contains the entire schema definition. An api:info keyword provides the table name & description and contains the column definitions, where each API field is mapped to a table with an attr (attribute) element.

The People endpoint of our API returns a series of people, where each entry is represent by a JSON object similar to the following:

{
   "UserName" : "russellwhyte",
   "FirstName" : "Russell",
   "LastName" : "Whyte",
   "MiddleName" : null,
   "Gender" : "Male",
   "Age" : null,
   "Emails" : ["[email protected]","[email protected]"],
   "FavoriteFeature" : "Feature1",
   "Features" : ["Feature1","Feature2"],
   "AddressInfo" : [
      { "Address" : "187 Suffolk Ln.",
        "City":{
           "Name" : "Boise",
           "CountryRegion" : "United States",
           "Region" : "ID"
        }
       }
   ],
   "HomeAddress" : null
}

We can using path definitions based on the JSON structure to drill down into each of the values in the response, effectively flattening the response into a SQL table model. Create the column definitions based on the API specification and inferred information based on the response. An explanation of the column definition follows.

<api:script xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:api="http://apiscript.com/ns?v1" >
  
  
    
    <attr name="UserName" key="true" xs:type="string" readonly="true" other:xPath="UserName" />
    <attr name="FirstName"           xs:type="string" readonly="true" other:xPath="FirstName" />
    <attr name="LastName"            xs:type="string" readonly="true" other:xPath="LastName" />
    <attr name="MiddleName"          xs:type="string" readonly="true" other:xPath="MiddleName" />
    <attr name="Gender"              xs:type="string" readonly="true" other:xPath="Gender" />
    <attr name="Age"                 xs:type="int"    readonly="true" other:xPath="Age" />
    <attr name="Emails"              xs:type="string" readonly="true" other:xPath="Emails" />
    <attr name="FavoriteFeature"     xs:type="string" readonly="true" other:xPath="FavoriteFeature" />
    <attr name="Feature1"            xs:type="string" readonly="true" other:xPath="Features[0]" />
    <attr name="Feature2"            xs:type="string" readonly="true" other:xPath="Features[1]" />
    <attr name="Address"             xs:type="string" readonly="true" other:xPath="AddressInfo/Address" />
    <attr name="City"                xs:type="string" readonly="true" other:xPath="AddressInfo/City/Name" />
    <attr name="CountryRegion"       xs:type="string" readonly="true" other:xPath="AddressInfo/City/CountryRegion"/>
    <attr name="Region"              xs:type="string" readonly="true" other:xPath="AddressInfo/City/Region" />
  
...
    

Column Definition Attributes

• name: The name of the column in the SQL interface for the API endpoint
• xs:type: The data type associated with the column (e.g.: string, datetime, int, etc.)
• readonly: Whether the column allows writes (by default, this is always true)
• key: Whether a column is intended to be a unique identifier for the elements in a table/view
• other:xPath: The path (exact or relative to theRepeatElement) in the API response to the column value

Specific Columns

Here, we examine specific column definitions and explain how the different attributes create the SQL mapping for the API fields.

Add Global Parameters

After creating the column definitions, we need to set the global parameters for integrating with the API, including the API endpoint to request data from, any required values for connecting, specific fields or headers for the API request, and the repeated element in the API response that represents individual entries for the API endpoint.

...
  
  <api:set attr="ContentType" value="application/json" />
  <api:set attr="EnablePaging" value="true" />
  <api:set attr="RepeatElement" value="/value" />
  <api:set attr="pagenumberparam" value="page_number" />
  <api:set attr="pagesize" value="300" />
  <api:set attr="pagesizeparam" value="page_size" />
  <api:set attr="uri" value="https://services.odata.org/TripPinRESTierService/People" />
...

Add Read/Write Functionality

With the columns and global parameters defined, we can complete the schema file by scripting the read and write functionality. SELECT functionality is implemented in the <api:script method="GET"> keyword, setting the HTTP method to GET and calling the apisadoExecuteJSONGet operation to retrieve and process the data.

  
    <api:set attr="method" value="GET" />
    
      <api:push/>
    
  

To implement INSERT / UPDATE / DELETE functionality, we need to add additional <api:script> elements with the POST, MERGE, and DELETE methods and implement the specific functionality with further scripting. For this article, we will only implement SELECT functionality and throw an error message if we try to write to the API endpoint.

Setting the method Attribute

...
  
    
  
  
    
  
  
    
  
...

With the API functionality implemented, we can use the profile with the API Driver in any tool or application that supports JDBC or ADO.NET connectivity, granting SQL access to the API.

Using the Profile in DbVisualizer

The API Driver has two connection properties used to connect to an API:

• Profile: The folder containing the schema files for your API.
• ProfileSettings: A semi-colon separated list of name-value pairs for connection properties required by your chosen Profile — since we are connecting to an open API, we can leave this property blank.

Because we are connecting in DbVisualizer, we will configure a JDBC connection string to connect to the API through the Profile:

jdbc:apis:Profile=/PATH/TO/TripPin/;

In DbVisualizer, we create a new Connection using the API Driver and set the Database URL to the connection string. From there, we can connect to the Profile, expand the data model, and explore the data in the People "table" (which represents the People API endpoint).

More Information & Free Trial

At this point, you have started creating an API Profile and connected to and queried the API through the API Driver. To learn more, visit the API Driver page or read more about setting up the API Driver or editing an existing Profile in our corresponding Knowledge Base articles (Setting up the API Driver and Editing an API Driver Profile). Download API Driver and start working with live data from RESTful APIs instantly — no code required.