Skip to main content

REST API Connections

The REST API connection type provides a generic interface to relational data available through REST APIs. Use this connection type to create read-only connections between Alteryx Analytics Cloud (AAC) and individual endpoints across hundreds of REST-based applications.

Note

The REST API Connection type supports JSON parsing by default or through the XPath configuration. This results in a tabular dataset that can be used in AAC applications. If additional parsing is needed, use the JSON Parse tool in Designer Desktop.

Limitations

  • Import-only connection type.

  • Supports a limited set of request methods.

  • Using a passphrase when generating an SSH key is not supported.

  • JSON response from API endpoint is required. API endpoints that return XML responses aren't supported.

  • After you make the initial connection to an endpoint, AAC caches the schema. The schema isn't updated again until you edit the connection.

  • By default, the number of endpoints that you can specify to use an individual connection is 10. You can modify this limit. For more information, go to Workspace Settings Page.

Prerequisites

  • You should identify the tables and (optional) data models for them that you wish to access.

  • You should acquire the credentials to access your target endpoints for one of the supported authentication methods.

    • If you are using a key or token to access the endpoints, you should generate this token before you begin.

Configure

To create this connection, go to the Connections Page and then select Create Connection. Search for REST API and then select the REST API card.

Modify the following properties as needed:

Property

Description

Base URI

The base URI for the endpoints that you want 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 REST API.

A default string has been provided for you. For more information, go to the Connection String Options section later on this page.

Authentication Type

The method by which you authenticate to the endpoint. For more information, go to the Authentication Types section later on this page.

API Endpoints

Specify the endpoints to which to connect. For more information, go to the Configure Endpoints section later on this page.

Test Connection

After you define the REST API credentials and connection string, you can validate those credentials.

Connection Name

Display name of the connection.

Connection Description

Description of the connection, which appears in the application.

Authentication Types

AAC supports these types of authentication for REST API connections...

Note

For each authentication type, additional properties might require configuration.

Basic Authentication

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

Property

Description

Username

Username to access the endpoints.

Password

The password associated with the username.

HTTP Header Based Authentication

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

Property

Description

Header Key

Key for the header parameter used in authentication.

Header Value

Credential associated with header authentication key.

HTTP Query Based Authentication

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

Property

Description

Query Key

Key for the query parameter used in authentication.

Query Value

Credential associated with the query parameter authentication key.

OAuth 2.0 Based Authentication

Use OAuth 2.0 authentication for REST API connections. Note that OAuth 2.0 authentication requires additional configuration. For more information, go to OAuth 2.0 for REST API.

Configure Endpoints

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

These are the endpoint properties you must configure...

Property

Description

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

Warning

In some target systems, the PUT and POST methods are required for generating datasets for import. These methods shouldn't be used for other use 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 AAC. 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 AAC.

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

Pagination

Select the type of pagination to request to the API endpoint. For more information, go to the Pagination section later on this page.

Advanced Options: Custom Header

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

To add more headers, select 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, select Add.

Advanced Options: XPath

(Pptional) 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 AAC to retrieve larger sets of records when pagination is in use. For more information on these pagination methods, go to 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.

  • Page URL path defines the XPath in the response to the attribute containing the URL of the next page.

Paging token:

A paging token might 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.

  • Page token path is the XPath to the token that must be submitted with the next request.

  • Page token param is the parameter in the request into which the page token must be submitted.

  • More pages param (Optional) is used when the page token path must be submitted as a query parameter. This value defines the query parameter for which it is submitted.

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 offset param defines the query parameter where you can specify the page offset to query.

  • Page size param defines the parameter in the request where you define the size in records of each request (page) of records.

  • Page size defines the number of records to request in a page.

Page number:

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

  • Page number param defines the query parameter where you can specify the page number to query.

  • Page size param defines the parameter in the request where you define the size in records of each page of records.

  • Page size defines the number of records to request in a page.

Connect String Options

Too Many Requests Error

During execution, you might receive an error similar from the driver like this

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'

Theis 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, go to https://cdn.cdata.com/help/DWE/ado/Connection.htm.

Create via API

You can also create this connection using the API.

  • Type: jdbc_rest

  • Vendor: jdbc_rest

Example: Single GET Method

This example request creates a REST API connection with these characteristics...

  • Query parameters are used for authentication.

  • A single endpoint is enabled:

    • Method: GET

    • Target: table1

    • dataModel: Document

{
  "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

This example creates a REST API connection to polygon.io with these characteristics...

  • Query-based authentication using key/value pair.

  • Connect String Options:

    Other='RetryWaitTime=15000';
    • Wait for retry: 15000 milliseconds.

  • Single endpoint to GET stock ticker information...

    • DataModel: Document.

    • XPath: $./results.

    • Rate limiting on the endpoint (maximum queries per minute): 1000.

    • queryParams.date is a parameter that is passed in for this specific connection type.

    • Pagination: nextPageURL method.

{
  "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, go to the API reference docs.

Use

You can import datasets from REST API through the Import Data page. For more information, go to Import Data Page.

Tip

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

Using REST API Connections

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

Uses

AAC can use REST API connections for these tasks...

  • Import datasets

Before You Begin

  • Read Access: You must have credentials to create access the specific endpoints required to retrieve your data.

  • Write Access: Not supported.

Secure Access

SSL is available over HTTPS for REST API connections.

Reading data

You can create a Alteryx dataset from the following data models:

  1. Documents

  2. Flattened Documents

  3. Relational Tables

For more information, go to Database Browser.

Writing Data

Not supported.

Note

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

Reference

  • Read: Supported

  • Write: Not Supported