Editing 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 editing an existing API Profile to enable more functionality.

Editing an API Profile

By default, existing API Profiles grant read access to APIs. We can edit the API Profiles to gain write access to APIs (where supported).

Unzip the Existing Profile

API Profiles (.apip files) are compressed folders that contain a collection of schema files representing the endpoints available for the API. When you decompress (unzip) the file, you can edit the schema files individually to extend the functionality.

Open the Schema File

Once the Profile is decompressed, open the schema file for the API endpoint you wish to modify (in your preferred text editor). 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.

Below are the full contents of a the schema file for retrieving Zoom Meetings.

Meetings.rsd


  
    <attr name="Id"         xs:type="string"   readonly="true" key="true"   other:xPath="id"         />
    <attr name="CreatedAt"  xs:type="datetime" readonly="true"              other:xPath="created_at" />
    <attr name="Duration"   xs:type="int"      readonly="true"              other:xPath="duration"   />
    <attr name="HostId"     xs:type="string"   readonly="true"              other:xPath="host_id"    />
    <attr name="JoinUrl"    xs:type="string"   readonly="true"              other:xPath="join_url"   />
    <attr name="StartTime"  xs:type="datetime" readonly="true"              other:xPath="start_time" />
    <attr name="Timezone"   xs:type="string"   readonly="true"              other:xPath="timezone"   />
    <attr name="Topic"      xs:type="string"   readonly="true"              other:xPath="topic"      />
    <attr name="Type"       xs:type="int"      readonly="true"              other:xPath="type" desc="1 - Instant. 2 - Scheduled. 3 - Recurring with no fixed time. 8 - Recurring with fixed time"      />
    <attr name="Uuid"       xs:type="string"   readonly="true"              other:xPath="uuid"       />
    <attr name="UserId"     xs:type="string"   desc="Required. Must be set to a user's Id or email address. All meetings retrieved will be for that user." other:pseudocolumn="true" />
    
    <input  name="MeetingType"     xs:type="string"   desc="Filters on meetings with the given type. Allowed values are scheduled, live, and upcoming. Default is live." values="live,scheduled,upcoming" default="active" other:filter="type" />
    <input  name="rows@next" desc="Identifier for the next page of results. Do not set this value manually." />
  
  
  
  <api:set attr="ContentType" value="application/json" />
  <api:set attr="RepeatElement" value="/" />
  <api:set attr="EnablePaging" value="true" />
  <api:set attr="RepeatElement" value="/meetings" />
  <api:set attr="pagenumberparam" value="page_number" />
  <api:set attr="pagesize" value="300" />
  <api:set attr="pagesizeparam" value="page_size" />
  <api:validate attr="_input.UserId" desc="You must specify a user Id when using the Meetings table." />
  <api:set attr="uri" value="https://api.zoom.us/v2/users/[_input.UserId | urlencode()]/meetings" />
  
  
    <api:set attr="method" value="GET" />
    
      <api:push/>
    
  
  
  
  
    <api:throw code="500" desc="Inserts are not supported." />
  
  
    <api:throw code="500" desc="Updates are not supported." />
  
  
    <api:throw code="500" desc="Deletes are not supported." />

Update Column Definitions

If we are going to implement write functionality, we need to modify the schema to allow write permissions to the appropriate columns. Before we update a column definition for the Profile, let's examine a single column definition and discuss the different attributes.

Id Column Definition

    <attr name="Id"         xs:type="string"   readonly="true" key="true"   other:xPath="id"         />

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

We can see from the schema file that most of the columns (which correspond to various API fields) are flagged as read-only. The first edit we need to make is to change those columns so the API Driver can write to them (based on the API specification) by removing the read-only flags:

...
    <attr name="Id"         xs:type="string"   readonly="true" key="true"   other:xPath="id"         />
    <attr name="CreatedAt"  xs:type="datetime" readonly="true"              other:xPath="created_at" />
    <attr name="Duration"   xs:type="int"                                   other:xPath="duration"   />
    <attr name="HostId"     xs:type="string"   readonly="true"              other:xPath="host_id"    />
    <attr name="JoinUrl"    xs:type="string"   readonly="true"              other:xPath="join_url"   />
    <attr name="StartTime"  xs:type="datetime"                              other:xPath="start_time" />
    <attr name="Timezone"   xs:type="string"                                other:xPath="timezone"   />
    <attr name="Topic"      xs:type="string"                                other:xPath="topic"      />
    <attr name="Type"       xs:type="int"                                   other:xPath="type" desc="1 - Instant. 2 - Scheduled. 3 - Recurring with no fixed time. 8 - Recurring with fixed time"      />
    <attr name="Uuid"       xs:type="string"   readonly="true"              other:xPath="uuid"       />
...

Add INSERT Functionality

In this section, we walk through modifying the Schema file to support INSERT statements like the following:

INSERT INTO Meetings 
  (Topic,Type,StartTime,Duration,TimeZone,UserId) 
VALUES 
  ('Test Meeting',2,'2019-12-31T00:00:00',60,'EST','user@domain.com')

NOTE: Topic, Type, StartTime, Duration, and TimeZone are all columns in our schema. The UserId attr is a pseudocolumn, which behaves like a table column but is not actually stored in the table. In this case, the UserId is used to POST the new meeting to a specific user account.

In the schema file, the SELECT functionality is implemented in the <api:script method="POST"> element:

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

To implement INSERT functionality, we need to modify the <api:script method="POST"> element, setting the method attribute value to "POST", setting the input values in a data attribute and calling the appropriate operation (apisadoExecuteJSONGet in this case).

Setting the method Attribute

  
    <api:set attr="method" value="POST" />

Setting the data Attribute

The Zoom Meetings API endpoint allows you to create a new meeting by submitting a JSON object with various fields for the new meeting. Values from an INSERT statement are mapped to corresponding fields in an _input object and are used to create the data attribute, which will be POSTed to the Zoom API.


{
  "topic": "[_input.Topic]",
  "type": [_input.Type],
  "start_time": "[_input.StartTime]",
  "duration": "[_input.Duration]",
  "timezone": "[_input.TimeZone]"
}

Calling the apisadoExecuteJSONGet Operation

Once the data attribute is set, call the apisadoExecuteJSONGet operation using an api:call keyword and push the operation to the API Driver with an api:push keyword.

...

Using the Edited Profile

With the edits made to the schema file, you are ready to start INSERTing Meetings to a Zoom account. Save the schema file, recompress (zip) the schema files, and change the extension of the compressed file to .apip. Use an application that supports standard connectivity to connect to the API Profile using the API Driver, then submit your INSERT statement.

INSERT Statement

INSERT INTO Meetings 
  (Topic,Type,StartTime,Duration,TimeZone,UserId) 
VALUES 
  ('Test Meeting (Edited Profile)',2,'2019-12-31T00:00:00',60,'EST','user@domain.com')

Meeting in Zoom

A new Zoom Meeting added through the API Driver

More Information & Free Trial

At this point, you have added write functionality to an API Driver Profile. To learn more about the API Driver, visit the API Driver page or read more about setting up the API Driver or creating a new Profile in our corresponding Knowledge Base articles (Setting up the API Driver and Creating an API Driver Profile). Download API Driver and start working with live data from RESTful APIs instantly and codelessly.

CData Software is a leading provider of data access and connectivity solutions. Our standards-based connectors streamline data access and insulate customers from the complexities of integrating with on-premise or cloud databases, SaaS, APIs, NoSQL, and Big Data.

Connect With Us

Get Started

Data Connectors

ETL/ ELT Solutions

Cloud & API Connectivity

OEM & Custom Drivers