External REST API Integration

Knowi supports connectivity to internal and external REST API's, with the ability to manipulate, store and join with other datasources.

Connecting

  1. Log in to Knowi and select Queries from the left sidebar.
  2. Click on New Datasource + button at the top right corner.
  3. Click on the REST API from the API & Cloud Services section in the list of data sources.
  4. You will be navigated to a new page providing the REST API connectivity options.

The options to establish connectivity vary based on the authentication type. There are three types of authentication type to choose from:

  • Basic Use basic authentication with valid username/password credentials to access the REST API service.

  • OAuth 2.0: Use OAuth 2.0 flow to authorize and authenticate with REST API service. When working with Knowi, OAuth 2.0 tokens are generated as part of the connection workflow and are stored for future use in your account.

  • Other: Use separate REST API call to do ?login? on each subsequent request. The login endpoint can return a token that can be used on each subsequent REST API request and passed as tokens in required request fields (either Header, POST payload, or URL parameter).

    REST API

Basic Authentication

a. Datasource Name: Unique Identifier for this Datasource

b. REST Host: Host where to connect to. Example: https://yourdomain.cloudant.com

c. Authentication type: Basic

d. User ID for Basic Authentication: Optional username for basic authentication. If this is set, an Authorization header with base64 encoded string of username : password as the value will be appended to the query request headers

e. Password for Basic Authentication: Password for basic authentication

f. Connection Properties: Optional. Additional connection properties/url parameters. For example (in seconds), readTimeout=1800&connectTimeout=30

REST API

OAuth 2.0 Authentication

a. Datasource Name*: Unique Identifier for this Datasource

b. REST Host*: Host where to connect to. Example: https://yourdomain.cloudant.com

c. Authentication type: OAuth 2.0

d. OAuth Client ID: The client identifier issued to the client during the Application registration process

e. OAuth Client Secret: The client secret issued to the client during the Application registration process

f. OAuth Scope: List of space-separated scopes to get access from the OAuth server

g. OAuth callback URL: Please use this callback URL in the OAuth server to send a callback with the code

h. OAuth Authorization URL: The endpoint to the authorization server. This is used to get an authorization code. This is a URL in which will be inserted client_id and other params to open the web browser popup with OAuth flow to begin

i. OAuth Access Token URL: The endpoint to the authentication server. This is used to exchange an authorization code for an access token

j. OAuth Access Token Header: The additional headers to send to the authentication server to exchange authorization code for an access token

k. OAuth Post Data to get Access Token: The Post data to send to an authentication server to exchange an authorization code for an access token

l. Refresh Token Type*: Select Refresh Token Type

  • Long-Lived Refresh Token: the refresh token will be used to get an access token on each REST call

  • Long-Lived Access Token: the access token will be used during the REST call, but when expired then will be a need to update manually in Datasource settings

    Please choose what is best for you and what the REST API server supports

m. OAuth Refresh Access Token URL: The endpoint to call to exchange the refresh token to a newly updated access token.

n. OAuth Refresh Token Header: The additional headers to send to the server to exchange the refresh token to the newly updated access token.

o. OAuth Post Data to get Refresh Access Token: The Post data to send to the server to exchange the refresh token to a newly updated access token

p. Connection Properties: Optional. Additional connection properties/url parameters. For example (in seconds), readTimeout=1800&connectTimeout=30

Here is list of tokens we can use in the REST API OAuth UI:

  • {redirectHost} - will be automatically replaced to the Knowi server external host
  • {clientId} - will be automatically replaced to the OAuth Client ID field value
  • {clientSecret} - will be automatically replaced to the OAuth Client Secret field value
  • {callbackURL} - will be automatically replaced to the OAuth callback URL field value
  • {scope} - will be automatically replaced to the OAuth Scope field value
  • {refresh_token} - will be automatically replaced the the refresh token of this REST datasource received from authentication server.
  • {accesstoken} - will be automatically replaced the the access token of this REST datasource received from authentication server. Usefull in the each call to the REST api in the Headers as header: Authorization: Bearer {accesstoken}
  • {code} - will be automatically replaced the the code received from authorization server

Click on the Authorize button and you will get an access token

If you are authorized you can proceed to the next step.

REST API

Other Authentication

a. Datasource Name*: Unique Identifier for this Datasource

b. REST Host*: Host where to connect to. Example: https://yourdomain.cloudant.com

c. Authentication type: OAuth 2.0

d. Authentication End Point: The authentication endpoint to get a token, which can be passed into each subsequent query. This is a relative URL. Example /oauth/xyz

e. Auth URL Params: URL parameters for the authentication request, if applicable. Will be auto-encoded. Example: param=someval&anotherparam=Some Value with Space

f. Auth Headers: Enter any optional authentication-related headers. One Header per line

    Example:

    -X-Parse-Application-Id: zjfXOr8WHrJI3GW49koxAPzT48eeWvFetM9Jkw2L
    -X-Parse-REST-API-Key: S2z9zkuurH00hdXq2vXz5n8Xw6LIPs6rURatTKuy

g. Auth POST Payload: Optional POST body, if the authentication endpoint requires a payload as part of the request (typically JSON)

h. Connection Properties: Optional. Additional connection properties/url parameters. For example (in seconds), readTimeout=1800&connectTimeout=30

EXAMPLE:

If the login endpoint return this JSON:

 {
"access_token": "abcd",
"token_type": "bearer"
 }

Then in any field on Query, you can access it via token {accesstoken} and {tokentype} (brackets should be included)

REST API

Select the authentication type of your choice and proceed to test the network connectivity

  1. To test the Network connectivity, click on the Test Connection button

    Note: The connection validity of the network can be tested only if it has been established via Direct Connectivity or an SSH tunnel. For more information on connectivity and data sources, please refer to the documentation on- Connectivity & Datasources

  2. Click on Save and then click on Start Querying

    REST API

Querying

  1. Steps for querying the REST API datasource authenticated via OAuth 2.0:

    • End-point: Specify the REST endpoint for the request

    • POST: For POST requests, check this box (defaults to GET otherwise). Add in any data payloads expected by the endpoint below

    • Headers: Enter any HTTP headers, if applicable. Multiple headers must be separated one per line. Enter any Optional Headers. One Header per line. Example:

      X-Parse-Application-Id: zjfXOr8WHrJI3GW49koxAPzT48eeWvFetM9Jkw2L X-Parse-REST-API-Key: S2z9zkuurH00hdXq2vXz5n8Xw6LIPs6rURatTKuy

      For any auth tokens to pass in on the request from the Auth request on the datasource, use {tokenName}. For example:

      Authorization: Bearer {access_token}

    • URL Params: Add any optional URL params (parameters) that you'd like to add. Parameters will be encoded automatically

      URL parameters. Will be auto-encoded: Example: param=someval&anotherparam=Some Value with Space

      For automating date based requests, you can optionally pass in date tokens using the following sequence: {$c9_today-1d:dd-MM-yyyy} with a token and a date format

      Example: https://dummy.com?startDate={$c9thisyear-1y:yyyy-MM-dd}&endDate={$c9thisyear:yyyy-MM-dd}&blah=blah will be replaced to: https://dummy.com?startDate=2015-01-01&endDate=2016-01-01&blah=blah

      For APIs that use Epoch times, use epoch or epochsecs as the format. Example: https://dummy.com?startDate={$c9thisyear-1y:epoch}&endDate={$c9thisyear:epochsecs}&blah=blah

      Full list of supported date tokens listed here

    • Cloud9QL Transformation: Optional post-processing of the data returned by the REST API. This can be used to transform the data as well as unwind and interact with nested JSON structures

    • ?Paging - Response Field Name: For REST paging: this is the parameter name in server response to read bookmark value or read next page number value. Docs here

    • Paging - URL parameter name: If pagination requires a page number or name injected into the url parameter, specify the parameter name here. We will inject the value of the parameter into the request along with the name. Docs here

    • Paging - boolean flag for more pages: If server response requires a boolean flag check to indicate more pages (like Hubspot), then specify the field name to check (E.g. "hasMore"). Docs here

    • Paging Response type: If server response requires you to pass in the last id into subsequent requests (like Trello) to fetch the next set of records. Docs here

    • Secondary/subsequent POST for ArrayIndex: Used with ArrayIndex pagination type for second and subsequent POST requests, check this box. Add in any data payloads expected by the end point below. Put $PAGE_INDEX variable where page number will be injected. Docs here

    • Limit pages: Limit the number of pages to load for pagination. Defaults to 100 pages if empty

    • Ignore HTTP Errors: Ignore API HTTP error codes (i.e., 404 - Not Found). If checked, this will return an empty dataset in such cases

    REST API

  2. Define data execution strategy by using any of the following two options:

    Direct Execution: Directly execute the Query on the original MongoDB datasource, without any storage in between. In this case, when a widget is displayed, it will fetch the data in real-time from the underlying Datasource

    Non-Direct Execution: For non-direct queries, results will be stored in Knowi?s Elastic Store. Benefits include- long-running queries, reduced load on your database, and more. Non-direct execution can be put into action if you choose to run the Query ?once? or at ?scheduled intervals?.

    For more information, please refer to this documentation- Defining Data Execution Strategy

    REST API

  3. Click on Preview to review the results and fine-tune the desired output, if required

    The result of your Query is called Dataset

    After reviewing the results, name your dataset and then hit the Create & Run button.

    REST API

REST API pagination

Sometimes, your API may require you to iterate through a number of pages for a given request. Typically, it'll also contain a bookmark or a page number field to indicate how to load next set. To do this, set up the "Paging - Field Name" field with bookmark field name of response.

If the API requires a next page bookmark or page number as a url argument, use the "Paging REST URL parameter name" field.

Examples:

All examples include an clean call, and call containing an user-defined payload parameters which are not related to pagination.

First call

First call is without any bookmark or page, like this two examples:

parameter-less: https://an-api.com/v1/contacts

with parameters: https://an-api.com/v1/contacts?x=val1&y=val2

Response contains "bookmark" as "nextPageWillBe" field in this example:

{
 someData: ["data1", "data2"],
 nextPageWillBe: "bmABCD"
}

Fill "Pagination - Response Field Name" in query settings with "nextPageWillBe".

Bookmark inside url path

subsequent requests:

Without parameters: https://an-api.com/v1/contacts/bmABCD

With parameters: https://an-api.com/v1/contacts/bmABCD?x=val1&y=val2

Bookmark inside url parameters

If the API requires "nextPageWillBe" within url parameters, use the "Pagination - URL parameter name" field with required parameter name. Example:

subsequent requests:

Without parameters: https://an-api.com/v1/contacts?nextPageAttrLink=bmABCD

With parameters: https://an-api.com/v1/contacts?x=val1&y=val2&nextPageAttrLink=bmABCD

(where nextPageAttrLink is the parameter name for the url).

If the next page is a number within the request on the payload:

{
 someData: ["data1", "data2"],
 nextPage: 2
}

Subsequent request if the URL parameter is "page":

Without parameters: https://an-api.com/v1/contacts?page=2

With parameters: https://an-api.com/v1/contacts?x=val1&y=val2&page=2

Example when "has more" flag used

Some APIs may contain a "hasMore" entry in the response (like Hubspot), along with a bookmark to the next page. Example:

{
 someData: ["data1", "data2"],
 nextPageWillBe: "bmABCD"
 hasMore: true
}

In this case, specify the field name for bookmark ("nextPageWillBe"), as well as the "Pagination - boolean flag for more pages" field ("hasMore").

Pagination with response arrays

Some APIs may return array of objects, each of which contain could some sort of an identifier, where the API expects max or min of that identifier as a paramater into the next batch (for example, Trello API). For this please use the "Pagination Response type" combobox and select appropriate sorting kind.

Example:

[
  {
    someData: { }
    id: 70
  },
  {
    someData: { }
    id: 60
  },
  {
    someData: { }
    id: 50
  }
]

In the example above, API expects the last element from the result for field "id" and pass that in as a "before" parameter for the next call into the URL. For this, set the "Pagination Response type" to "Response is Array and get last element", the "Response Field Name" to "id", and "URL parameter name" to "before". The next call will be:

https://an-api.com/v1/contacts?before=50

where "50" is the id of last element from previous response that we'll inject automatically.

Pagination when field contains full url to next page

For that kind of pagination please specify in "Pagination - Response Field Name" the field name which will contain in response the full url to next page, and choose "Response url contains full url to next page" in "Pagination Response type" selector. The result of that field will be used as is as url to navigate to grab next page. Pagination will stop read next pages when there will be no such field or field value will be "null".

In the example bellow the field name is "nextPageWillBe":

{
 someData: ["data1", "data2"],
 nextPageWillBe: "http://someUrlToNextPage.com/conains/full/path?including=any&params=there"
}

ArrayIndex pagination

Special kind of pagination, where first call return json which contains field with count of total pages to load. Then we need to load all the pages using separate json POST payload passing page number in it which is run in subsequent requests iterating over pages numbers.

Select ArrayIndex pagination type. Please put field name to read count of pages in first call in 'Response Field Name' field. Select "Secondary/subsequent POST for ArrayIndex" checkbox and put payload for subsequent calls to load each page, use $PAGE_INDEX keyword (including $) where the current page number will be automatically placed during calls.

Example: "Response Field Name" set to "Pages", the "Pagination Response type" to "ArrayIndex1toZ", "Secondary/subsequent POST for ArrayIndex": {"SomeAdditionalPostData":"1234", "PageIndex": $PAGE_INDEX}

First call return this:

{
    "Pages": 45,
    "someNonMeaningfulData": { }  (this will be skipped, only Pages Count field is meaningful)
}

Subsequent calls with page index from 1 to 45 will return actual pages data, example:

{
    someData: { } 
}

Array size pagination

In this pagination the bookmark/offset value which will be passed in consecutive api calls is based on previously fetched rows count. So for example you have 120 rows and every api call to api return for you 50 rows. So you need 3 calls (pages) to get whole data where first page is 50, second 50 and last 20 of size, and we pass it as offset url parameter, where to get first page offset is 0, second page offset 50 and last page offset is 100. Note that we support 2 cases: one is when response is JSON array. In this case please leave the "Response Field Name" empty. Second case is when response is an JSON object and only one field contains items of data which need to count for offset/bookmark. In this case please fill "Response Field Name" with this field name which contains items in response.

Example settings: set the "Pagination Response type" to "Response is Array and get count of elements", leave the "Response Field Name" empty, and "URL parameter name" to "offset".

Pagination with just page numbers - The Request contain page number starting 1, the Response contain field with total pages count.

In this pagination the bookmark/offset value which will be passed in consecutive api calls is just a page number starting from page 1. The page number will be passed with the "Pagination - URL parameter name" field name. The "Response Field Name" should contain field name in the response with Total Pages count.

Page number inside POST payload

Some of REST API's require page number or token to be not inside URL but inside POST payload. For this please leave the "Paging - URL parameter name" field empty, fill other required fields and required pagination kind, then fill your POST payload with content and use inside it the token $C9_POST_PAGE_INDEX where should be inserted page number on the subsequent requests. For first request it will be replaced with 0.

For example this is POST payload:

{
    "limit": 100,
    "after": "$C9_POST_PAGE_INDEX"
}