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 with the OAuth provider

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

f. OAuth Scope: List of space-separated scopes to get access from the OAuth server. This specifies the permissions that the client application is requesting from the user

g. OAuth callback URL: Please use this callback URL in the OAuth server to send a callback with the code. This is URL that the OAuth provider redirects the user to after they have granted permission to the client application. This URL must be registered with the OAuth provider in advance.

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 clientid and other params to open the web browser popup with OAuth flow to begin. User can sign in and authorize the requested permissions. Please use tokens to insert clientid and other required field values from fields above, except responsetype whose value usually is "code". Example: https://example.com/oauth2/authorize?clientid={clientId}&redirecturi={callbackURL:url}&scope={scope}&responsetype=code&state=mystate:{knowiSessionToken}

i. OAuth Access Token URL: The endpoint to the authentication server. This is used to exchange an authorization code for an access token. This field is used together with "Access Token Header" and "Post Data" fields to make a request. Example: [https://example.com/oauth2/token/bearer] (https://example.com/oauth2/token/bearer)

j. OAuth Access Token Header: The additional headers to send to the authentication server to exchange authorization code for an access token. This field used together with "Access Token URL" and "Post Data" fields. This can include for example an additional authorization, and content type. Content type for example is Content-Type: application/x-www-form-urlencoded or Content-Type: application/json depending on the "Post Data".

Example: Content-Type: application/x-www-form-urlencoded;charset=utf-8 Authorization: Basic someCode

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. This field is used together with "Access Token URL" and "Access Token Header" fields. Please use {code} token to include code returned from Authorization flow via callback URL. The "granttype" usually is "authorizationcode".

Example: granttype=authorizationcode&redirect_uri={callbackURL:url}&code={code}

Second Example: {"granttype": "authorizationcode", "code": "{code}", "clientid": "{clientId}", "clientsecret": "{clientSecret}", "redirect_uri": "{callbackURL}", "scope": "read" }

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

  • Limited-Use Refresh Token: It is useful for Authorization apps where refresh token often get expired. The access token will be used during the REST call, but when expired will get a new access token. Meanwhile, if the refresh token is updated as well, will store it. In this way, the refresh token will keep updating. 

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

m. OAuth Refresh Access Token URL: The endpoint to call authentication server to exchange refresh token to new updated access token and in sometimes to receive new refresh token. New refresh token will be stored only if Refresh Token Type set to "Limited-Use Refresh Token". Please read docs for your OAuth provider to get know if refresh token can be expired and updated. This field used together with "Refresh Token Header" and "Post Data" fields to make a request. Example: https://example.com/oauth2/token/refresh

n. OAuth Refresh Token Header: The additional headers to send to authentication server to exchange refresh token to new updated access token. This field used together with "Refresh Token URL" and "Post Data" fields. This can include for example an additional authorization, and content type. Content type for example is Content-Type: application/x-www-form-urlencoded or Content-Type: application/json depending on the "Post Data". Example: Content-Type: application/x-www-form-urlencoded;charset=utf-8 Authorization: Basic someCode

o. OAuth Post Data to get Refresh Access Token: The Post data to send to authentication server to exchange refresh token to new updated access token. This field used together with "Access Token URL" and "Access Token Header" fields. Please use {refreshtoken} token to include refresh code returned from Authorization flow. The "granttype" usually is "refresh_token".

Example: granttype=refreshtoken&refreshtoken={refreshtoken}

Second Example: {"granttype": "refreshtoken", "refreshToken": "{refreshtoken}", "clientid": "{clientId}", "clientsecret": "{clientSecret}", "redirecturi": "{callbackURL}", "scope": "read" }

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:

  • {clientId} - Represents the client ID of the application that is trying to authenticate with OAuth provider.
  • {clientSecret} - Represents the client secret that was issued to the application by the authorization server.
  • {callbackURL:url} - The URL to which OAuth provider will redirect the user after the authentication process is complete. This URL must be registered with OAuth provider when the application is created.
  • {scope} - Represents the permissions that the application is requesting from the user.
  • {refresh_token} - The refresh token stored at datasource, received in previous calls or during initial OAuth flow. The application will exchange this refresh token for new access token and in some Providers for a new refresh token (if refresh token can be updated then use Limited-Use Refresh token type value).
  • {knowiSessionToken} - Some randomly generated session token. If OAuth provider requires it, then use it, usually in "state" parameter for request. It is used to maintain state between the request and the callback.
  • {code} - The authorization code that was issued to the application by the authorization server after the user grants permission. The application will exchange this code for access token and refresh token.

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: Other

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: Endpoint to specify the URL. Defaults to datasource URL if left empty

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

    • Headers: Enter any optional 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 SQL-like syntax, for additional processing/transformations on the data returned. Docs here.
      Note: For unwinding nested objects, use select expand(fieldName)

    • 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.

    • 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.

    • 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").

    • Paging Response type: Choose the paging response type. If the server response requires you to pass in the last ID into subsequent requests to fetch the next set of records. It is a dropdown with these options:  

      • Page Token: Accepts the page token

      • Next Page Full URL: Accepts the full URL

      • Relative Page URL: Accepts the relative URL

      • Token is Array Minimum Element: Accepts token minimum array element

      • Token is Array Maximum Element: Accepts token maximum array element

      • Token is Array First Element: Accepts the first array element

      • Token is Array Last Element: Accepts the last array element

      • Offset is a sum of Array Element Count starting page 0: Offsets by the sum of array elements count starting from 0

      • Offset is a sum of Array Element Count starting page 1: Offsets by the sum of array elements count starting from 1

      • Read Pages Count then sequential paging from 0 to Z: Reads page counts from 0 to Z

      • Read Pages Count then sequential paging from 1 to Z: Reads page counts from 1 to Z

      • Sequential Page Number starting page 1: Page counts from 1

    • 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.

    • 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

    • HTTP response headers pagination: REST API pagination information can be found in a given API call's response body or response headers. The HTTP response headers pagination checkbox enables users to access pagination information for REST APIs via the response header, which is more readily available.

      To enable the checkbox, click HTTP response headers pagination.

      REST API

    • Preview pagination data: To preview the response header pagination data, check the Preview pagination data checkbox. This option to view in Preview the pagination data used to get the pagination token. Useful for debugging the pagination parameters. This checkbox is not savable and does not affect the actual data that goes into the dataset.

      REST API

    • Retry on errors: If checked and a REST API error occurs, it re-tries up to 3 times with intervals (10,20,60 seconds). This feature allows users to bypass some REST API datasources with rate limit errors. Also, in case of a non-stable internet connection.

      REST API

      Note: Runtime Tokens (Runtime Parameters) can be passed through the endpoint, POST, URL Parameters, and Cloud9QL Transformation sections.

    • Pagination Start Value The pagination start value signifies the bookmark or token used for the first call. The default value is 0 for the POST payload when the inject token is $C9POSTPAGE_INDEX. For sequential page number, the starting value is 1, and it is Offset by a sum of array element count starting at 1. In all the other cases, it defaults to 0.

  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.

Pagination types

  • Page Token - Response contains a field for the page number or token
  • Next Page Full URL - Response contains the full URL to retrieve the next page of results
  • Relative Page URL - Response contains the relative URL to retrieve the next page of results, relative to the host
  • Token is Array Minimum Element - Response is an array and next token is the minimum element
  • Token is Array Maximum Element - Response is an array and next token is the maximum element
  • Token is Array First Element - Response is an array and next token is the first element
  • Token is Array Last Element - Response is an array and next token is the last element
  • Offset is a sum of Array Element Count starting page 0 - The bookmark for the next page is the total count of elements from all previously read arrays along with the count of elements from the current request. First call with page number 0
  • Offset is a sum of Array Element Count starting page 1 - The bookmark for the next page is the total count of elements from all previously read arrays along with the count of elements from the current request. First call with page number 1
  • Read Pages Count then sequental paging from 0 to Z - First reading pages count, then read pages in sequential order by starting from page 0 and ending at the total number of pages minus 1
  • Read Pages Count then sequental paging from 1 to Z - First reading pages count, then read pages in sequential order by starting from page 1 and ending at the total number of pages
  • Sequential Page Number starting page 1 - The request contains a page number starting from 1. The response may contain a field with the total number of pages

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 parameter 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 "Token is Array 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 "Next Page Full URL" 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" }

REST API Pagination if Next Page is Relative URL

If the RESP API triggered in a datasource is using the pagination where the next page URL is not full but relative to the host, then it is recommended to use this pagination: ?Relative Page URL?. This is the same as Full URL pagination, except that this is relative to the host.

For example, if host http://somerest.com and full URL output from pagination response was: http://somerest.com/some/end/point, now with relative URL output which is supported is "/some/end/point".

Read Pages Count then sequental paging

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 "Read Pages Count then sequential paging" 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 "Read Pages Count then sequential paging from 1 to Z", "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 (Offset is a sum of Array Element Count)

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 "Offset is a sum of Array Element Count starting page 0", leave the "Response Field Name" empty, and "URL parameter name" to "offset".

Pagination with Sequential Page Number starting page 1 - 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"
}