This feature is disabled by default. For more information about enabling REST API connectivity in your environment, please contact .

The connection type provides a generic interface to relational data available through REST APIs. Using this connection type, you can create connections to individual endpoints across hundreds of REST-based applications.


Limitations

Prerequisites


Configure

To create this connection, in the Connections page , select the Applications tab. Click the  card. See Connections Page.

Modify the following properties as needed:

PropertyDescription
Base URI

The base URI for the endpoints to which you wish to connect through this connection. Example:

https://exampleserver.sharepoint.com/sites/SharePointTest

Tip: SSL access is supported over the HTTPS protocol.

Connect String Options

Apply any connection string options that are part of your authentication to .

A default string has been provided for you. For more information, see below.

Authentication TypeThe method by which you wish to authenticate to the endpoint. See "Authentication types" below.
API endpointsSpecify the endpoints to which to connect. For more information, see "Configure endpoints" below.
Test Connection

After you have defined the credentials and connection string, you can validate those credentials.

Connection NameDisplay name of the connection
Connection DescriptionDescription of the connection, which appears in the application.

Authentication types

The following types of authentication are supported for REST API connections. For each type, additional properties may require configuration. 

Basic auth

A username/password combination is submitted as part of any request for authentication.

PropertyDescription
UsernameUsername to access the endpoints.
PasswordPassword associated with the username.

HTTP Header Based Auth

Authentication is submitted using a key/value pair submitted in the HTTP request header.

PropertyDescription
Header KeyKey for the header parameter used in authentication
Header ValueCredential associated with header authentication key

HTTP Query Based Auth

Authentication is submitted using a query parameter key/value pair submitted as part of the URL.

PropertyDescription
Query KeyKey for the query parameter used in authentication
Query ValueCredential associated with the query parameter authentication key

Configure endpoints

Each endpoint and method combination must be configured. To add an endpoint, click Add endpoint

The properties are described below.

PropertyDescription
Method

API request method to use. Supported methods:

  • GET - read from the endpoint
  • POST - create a new instance of an object in the target application through the endpoint
  • PUT - modify an existing instance

In some target systems, the PUT and POST methods are required for generating datasets for import. These methods should not be used for other uses cases, such as writing data back to the target system. REST API connections are supported for import only.


NOTE: Other methods are not supported for use.

URL Endpoint

The endpoint that you are accessing using the specified method.

Tip: The Base URI value and this value should form a complete URL.

Table Name(required) The name of the table with which you are interacting through this endpoint.
Data Model

Select the type of model used for the selected table:

  • Document - Data is returned as a document containing top-level elements which are represented as columns in the . Nested data is returned in aggregated form.
  • Relational - Data is returned in tabular form, in which each returned XPath represents an individual table containing a primary key and a foreign key linking to the parent document.
  • Flattened Documents - Data is stored as FlattenedArrays in the source system. A separate table is returned for each object array and is joined to its parent table. The parent table and each child table are joined into a single table for use in the .

For more information on these data model types, see https://cdn.cdata.com/help/DWE/jdbc/pg_RESTParsing.htm.

Pagination

Select the type of pagination to request to the API endpoint. See "Pagination" below.

Advanced options: Custom Header

(optional) You can insert a custom header in the request as a key/value pair.

To add more headers, click Add.

Advanced options: Query Parameter

(optional) You can append a query parameter and value to the URL. These values are appended in the following form:

<endpoint_url>?<key1>=<value1>&<key2>=<value2>

To add more query parameters, click Add.

Advanced options: XPath(optional) You can specify an XPath to be queried of the URL.

Pagination

For the selected endpoint, you can specify the type of pagination in use by the target application. Specifying the pagination allows the  to retrieve larger sets of records when pagination is in use. For more information on these pagination methods, see http://cdn.cdata.com/help/DWE/ado/pg_customschemaselect.htm.

None:

(default) No pagination is applied by the endpoint.

Next page URL: 

When this method is selected, the URL of the next page of results is returned as part of the response body. 

Paging token:

A paging token may be returned as part of the response. To acquire the next page of results, this token must be submitted in the subsequent request as the value associated with the paging parameter.

Record offset: 

Under this pagination method, subsequent pages of results can be queried based on defining the number of results (records) to offset with the query.

Page number:

Similar to record offset, this method queries results based on specified page numbers.

Connect string options

Too many requests error

During execution, you may receive an error similar from the driver to the following:

PlatformErrors: Retries errors based on the exception message. E.g. Other=PlatformErrors="Too Many Requests"
MaximumRequestRetries: The number of times the driver will attempt to retry the request (Default 4)

In this case, the default wait time for retrying a request (2 seconds) is not enough time, and the requests are piling up. You can address this issue by inserting the following connect string options:

Other='RetryWaitTime=15000'

The above option sets the wait time before retrying to 15000ms (15 seconds). You can experiment with this value as needed.

For more information on available connect string options, see https://cdn.cdata.com/help/DWE/ado/Connection.htm.

Create via API

This connection can also be created using the API.

Example - single GET method

The following example request creates a REST API connection with the following characteristics:

{
  "vendor": "jdbc_rest",
  "vendorName": "jdbc_rest",
  "name": "REST API test",
  "description": "",
  "type": "jdbc",
  "params": {
    "base_URI": "some base URI",
    "connectStrOpts": ""
  },
  "credentialType": "httpQueryBasedAuth",
  "credentials": [
    {
      "queryKey": "user",
      "queryValue": "token"
    }
  ],
  "endpoints": [
    {
      "tableName": "table1",
      "httpMethod": "get",
      "endpoint": "endpoint1",
      "requestBody": "",
      "xPath": "",
      "dataModel": "document",
      "headers": {},
      "queryParams": {}
    }
  ]
}

Example - rate limiting

The following example creates a REST API connection to polygon.io with the following characteristics:


{
  "vendor": "jdbc_rest",
  "vendorName": "jdbc_rest",
  "name": "REST API",
  "description": "",
  "type": "jdbc",
  "params": {
    "base_URI": "https://api.polygon.io/",
    "connectStrOpts": "Other='RetryWaitTime=15000';"
  },
  "credentialType": "httpQueryBasedAuth",
  "credentials": [
    {
      "queryKey": "apiKey",
      "queryValue": "someKey"
    }
  ],
  "endpoints": [
    {
      "tableName": "tickers",
      "httpMethod": "get",
      "endpoint": "/v3/reference/tickers",
      "requestBody": "",
      "xPath": "$./results",
      "dataModel": "document",
      "headers": {},
      "queryParams": {
        "limit": "1000",
        "date": "2021-11-04T00:00:00Z"
      },
      "pagination": {
        "pageurlpath": "$./next_url",
        "paginationType": "nextPageURL"
      }
    }
  ]
}

For more information, see

#operation/createConnection

Use

You can import datasets from  through the Import Data page. See Import Data Page.

Tip: You can perform joins and unions using custom SQL as part of your initial request for data. It may be easier to import the tables as separate datasets and then to perform the join or union within the .


Using REST API Connections

This section describes how you interact through  with your REST data.

Uses

 can use  connections for the following tasks:

  1. Import datasets

Before you begin

Secure access

SSL is available over HTTPS for  connections.

Reading data

You can create a  from the following data models: 

  1. documents
  2. flattened documents
  3. relational tables

These source objects are represented as tables in the import browser and as grid data in the Transformer page.

For more information, see Database Browser.

Writing data

Not supported.

NOTE: Do not use the PUT and POST methods to write data back into the target system.

Reference