REST API Integration - Connect Any API Data Source

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

For streaming live MQTT or server-sent event feeds, see Streaming.

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. Multiple headers must be separated one per line. Example:

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

Note: When the Authentication End Point is left blank, headers defined here will be passed automatically to all queries that use this datasource.

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}. Example:

          Authorization: Bearer {access token}

    • URL Params: Add any optional URL params (parameters) that you'd like to add. Parameters will be encoded automatically. 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 with:

           https://dummy.com/?startDate=2015-01-01&endDate=2016-01-01&blah=blah

      You can also format date fields using dateformat in the following sequence: {dateformat($c9_today,yyyy-MM-dd)} with a token and a date format. Example:

           https://dummy.com/?startDate={date_format($c9_yesterday,yyyy-MM-dd)}&endDate={date_format($c9_now,yyyy-MM-dd)}&blah=blah

      will be replaced with:

           https://dummy.com/?startDate=2016-01-01&endDate=2016-01-02&blah=blah

      For APIs that use Epoch times, use epoch or epoch_secs as the format. Example:

           https://dummy.com/?startDate={$c9_thisyear-1y:epoch}&endDate={$c9_thisyear:epoch_secs}&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

      • HTTP Link Header (Shopify, GitHub, GitLab): Automatically parses the HTTP Link response header to find the rel="next" URL. No Response Field Name or URL parameter needed. Pagination stops when the header no longer contains a next link. Ideal for APIs like Shopify, GitHub, and GitLab that use cursor-based pagination via Link headers.

    • 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

    • Rate Limit: The request rate limit to avoid blocking by the datasource during pagination, in request/seconds format. Example: 100/60 (means up to 100 requests per 60 seconds). If multiple queries or other processes hit the same API simultaneously, set this well below the API's limit, e.g. 1/1 (1 request per second). Leave empty to not use a rate limit. Maximum time limit is 120 seconds.

      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 sequential 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 sequential 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
  • HTTP Link Header - The next page URL is provided in the HTTP response Link header (RFC 5988), not in the JSON body. Used by Shopify, GitHub, GitLab, and other APIs. No response field name or URL parameter name is needed

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 below 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 sequential 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 APIs 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"
}

HTTP Link Header Pagination

Some APIs provide the next page URL in the HTTP response Link header instead of in the JSON body. This follows RFC 5988 (Web Linking) and is used by APIs like Shopify, GitHub, and GitLab.

The response headers will contain a Link header like this:

Link: <https://api.example.com/v1/items?page_info=eyJsYXN0X2lk&limit=250>; rel="next",
      <https://api.example.com/v1/items?page_info=dGhpcyBpcw&limit=250>; rel="previous"

To use this pagination type, select HTTP Link Header from the "Pagination Response type" selector. No "Response Field Name" or "URL parameter name" is needed - the next page URL is extracted directly from the Link header.

Pagination stops automatically when the response no longer contains a rel="next" link (i.e., the last page has been reached).

Widget-driven pagination (Paged Data Grids)

The pagination settings above apply when Knowi ingests data from your API: all pages are fetched and stored into the dataset. Alternatively, for Direct REST queries displayed in a Data Grid with Paged navigation mode, Knowi fetches one page per request as the user clicks through pages of the grid.

In this mode, reference the system pagination tokens wherever your endpoint expects pagination values, such as the endpoint URL, URL parameters, POST body, or Cloud9QL transformation:

/v1/getInventoryDetails?accountId=277152&pageSize=$c9_sys_page_size$&page=$c9_sys_page_number$

/v1/events?accountId=277152&limit=$c9_sys_page_size$&offset=$c9_sys_offset$

Each page click on the grid re-executes the request with the tokens resolved for the requested page. When these tokens are present in a query, the ingestion-time pagination loop above is disabled - exactly one request is made per page. See the Runtime Parameters documentation (System Pagination Tokens) for the full token list and how to return an exact total row count via $c9_sys_total$.

Notes:

  • Use $c9_sys_page_number$ for APIs that expect a page number and $c9_sys_offset$ for APIs that expect an offset/skip/start value.
  • $c9_sys_total$ must be the total row count before pagination, not the total page count.
  • The API must apply sorting and filtering consistently across pages. If users need sorted pages, include a stable sort in the API request; a Cloud9QL order by transformation after the page is fetched can only reorder the rows returned for the current page.
  • If the API does not return a total count, the grid pager shows Previous/Next controls; the end of data is detected when a page returns fewer rows than the page size.

Streaming

Streaming lets a Knowi query read updates as your provider sends them. By default, each received message becomes a new row in a bounded live window. You can optionally choose fields that identify a record to keep the latest reading for each device instead. Your provider does not need to add a version field or change its messages. Knowi does not collect a permanent event history.

Choose Server-sent events (SSE) for an HTTPS event-stream endpoint, or MQTT over WebSocket for MQTT 3.1.1 over WSS. An ordinary HTTP polling endpoint or a generic WebSocket feed is not a streaming source.

Before you start

Ask your provider for:

  • The HTTPS SSE URL, or the complete MQTT WSS broker URL including its port and path, such as wss://broker.example.com:443/mqtt.
  • Credentials and permission to receive the feed. MQTT also needs one exact topic, such as factory/line1/readings.
  • A sample message: each MQTT message or SSE event's data must be one JSON object.

You need permission to create or edit the Knowi datasource. Both protocols support public or private destinations reachable from the Knowi server and require a valid TLS certificate. Provider subscriptions and permission to use or redistribute their data are separate from the Knowi connection.

Set up the connection

  1. In Queries, choose New Datasource +, then REST API.
  2. Give the datasource a name and select Enable streaming.
  3. Choose Streaming type. Existing streaming datasources without a selected type continue to use MQTT.
  4. For SSE, enter the HTTPS provider host in REST Host. For MQTT, enter its complete WSS URL in Streaming broker URL.
  5. Choose the authentication method below, enter its details, and save the datasource.

Enabling streaming does not convert existing saved REST queries. Incompatible queries report an error when run. Create a separate REST datasource for streaming to keep existing REST queries and dashboards working. Streaming runs directly in Knowi; agent, private connector, HTTP proxy and SSH tunnel connections are not supported.

MQTT authentication
Username and password

Choose Basic, then enter the provider's MQTT username and password in the Basic authentication fields. These are MQTT credentials. Do not paste them into the broker URL. A username is required, even if the provider accepts a public test account.

A provider that issues an access token

Choose Other only if the provider supports the following token exchange:

  • Set REST Host to the HTTPS token service host and Authentication End Point to its relative path.
  • Enter the provider's URL-encoded form body in Auth POST Payload. It must contain username, for example username=YOUR_USERNAME&password=YOUR_PASSWORD. Encode special characters as required by the provider.
  • The endpoint must return HTTP 200 with a JSON access_token and an expires_in value of 60 to 86,400 seconds. The lifetime may be a number or a numeric string.

Knowi uses the form's username and returned token for the MQTT login, and renews the token 30 seconds before expiry. MQTT streaming does not support arbitrary OAuth flows, JSON token-request bodies, or static HTTP authentication headers. The datasource's OAuth 2.0 browser-authorization flow is not the streaming token flow.

SSE authentication

SSE uses the existing REST authentication fields:

  • Basic sends an HTTP Basic username and password. Leave both blank for a public feed.
  • Other with an empty Authentication End Point sends Auth Headers directly to the event stream. Use this for a static API key or bearer token, one header per line.
  • Other with an Authentication End Point makes an HTTPS form-token request. Enter a relative path on REST Host or a complete HTTPS token URL, and a URL-encoded Auth POST Payload. Auth Headers apply to this token request. Unlike MQTT, the form does not require a username field. The response must contain access_token and expires_in as described above. Knowi sends the returned token as Authorization: Bearer ... to the event stream and renews it before expiry.

The datasource's OAuth 2.0 browser-authorization flow and JSON token-request bodies are not supported for SSE. Query Headers can supply provider-specific headers or use Authorization: Bearer {access_token}. Do not put secrets in URLs. Headers controlling the connection, including Host and Last-Event-ID, are managed by Knowi.

SSE query settings

Use the normal End Point, Headers, URL Params and Cloud9QL Transformations fields. For a feed at https://api.example.com/events, set REST Host to https://api.example.com and End Point to /events. The endpoint must stay on the REST Host's HTTPS origin. Leave End Point blank if REST Host already contains the complete feed URL.

The request is GET, and the response must have content type text/event-stream. POST, HTTP source pagination, redirects and dynamic date or viewer-context macros in the request are not supported. URL Params are fixed request parameters, not pagination controls. An SSE source can still use the normal result pagination described below.

Each complete SSE event must contain one JSON object in its data fields. Named events are accepted; their names do not select rows or imply insert, update, delete or partial-update operations. With record keys selected, each accepted JSON object replaces the whole matching row. Multiple data: lines are joined before JSON parsing, and comment heartbeats do not create rows. For example, a provider can send:

event: reading
id: reading-42
data: {"device_id":"pump-7","temperature":22.1}

The provider ends each event with a blank line. Its SSE id is an opaque reconnect cursor, separate from Record version field. You do not need to convert SSE IDs into numeric versions. Knowi manages Last-Event-ID on reconnect; whether a provider can resume from that cursor depends on its own retention and replay behavior. This is not a guarantee that Knowi has received every event or a durable event history.

Choose the records to receive

Create a query on the streaming datasource. For MQTT, enter its MQTT topic; for SSE, use the HTTP settings above. The two record fields are optional in both modes; leave both blank to start receiving messages unchanged.

FieldWhat to enter
MQTT topicOne exact topic from your provider. Wildcards such as + and # are not supported.
Record key fields (optional)Leave blank to keep each message as a new row. To replace matching records, enter existing JSON field names separated by commas, such as device_id or session_id,device_id.
Record version field (optional)Leave blank to use arrival order. Set this only when you have chosen key fields and the provider already supplies an increasing whole-number field, such as version.

When configured, keys and versions refer to top-level fields, not nested paths. Key values must be strings or numbers, and every message must contain all fields you selected. You can use up to six key fields. Field names must start with a letter or underscore and contain only letters, digits or underscores. MQTT topic names can contain letters, digits, underscores, hyphens and slashes, up to 128 characters.

For example, these two provider messages describe one device:

{"device_id":"pump-7","temperature":21.5}
{"device_id":"pump-7","temperature":22.1}

With both optional fields blank, the query contains two rows in arrival order. If you set Record key fields to device_id, it contains one row for pump-7, with temperature 22.1. The last message received replaces the whole matching row, so it must include the fields you want to keep. A different device_id creates another row. Arrival order does not establish when the provider originally produced an event; a delayed message can replace an earlier arrival.

If your provider already supplies an increasing version, you can also set Record version field to that field. For a matching key, version 2 replaces version 1; a later arrival with version 1 or 2 is ignored. A configured version requires key fields and must be a non-negative JSON integer, not "2", an ISO date, or a fractional timestamp. If the provider resets versions for each session, include a session field in the key. Existing queries with key and version fields keep this behavior.

Without keys, repeated or redelivered messages each occupy a new row. With or without keys, the live-state limits below still apply; older records are removed when the window fills.

Preview and save

Click Preview to start receiving the feed. The first preview can be empty while Knowi connects or waits for a message. Send or wait for a valid provider message, then preview again. An empty result does not confirm that the provider has sent data.

Save the query to create its normal Direct dataset. Streaming queries always use Direct, with no result cache, schedule, trigger, or stored destination. Queries derived from a streaming dataset also bypass result caching. Joins involving streaming queries are not supported.

You can use Cloud9QL Transformations to select, filter, sort or aggregate the received rows. For the example above:

select device_id, temperature where temperature > 22

Transformations run on the current received state on each query read. They run before the preview row limit or page slice. Streaming receives messages continuously while active; a preview displays the result of that read. Apps can subscribe to the dataset's change notifications and read it again when it changes, or read it periodically.

Each page request reads the current state. Rows can move or change between pages, so paging through a live dataset is not a fixed snapshot or a reliable history export.

Show live updates on a dashboard

When you are signed in, a widget that reads a saved Direct REST streaming query subscribes to updates automatically.

  1. Save your streaming query, then open the dashboard where you want the chart.
  2. Open Widgets in the left menu and drag the query's widget onto the dashboard. To create another view of the same data, use Widgets > +, choose the saved streaming dataset, click Create, then Add to Dashboard.
  3. Open the widget's options menu and choose Settings. Choose the visualization type and fields you want to display. The Live Data area shows Streaming: updates automatically; you do not need to set a polling interval.
  4. Click Save, close the settings panel and leave the dashboard open. Wait for provider messages to see new rows arrive, or matching chart values change when key fields are selected.

The provider pushes MQTT messages or HTTPS SSE events to Knowi. This upstream connection is separate from the dashboard notification connection: Knowi notifies the dashboard when received state changes, and the dashboard requests the current chart result through the normal permission and filter checks. It does not use a two-second polling timer for these widgets. A page shares one notification connection for up to 16 streaming widgets. Provider, network and query-processing delays still affect how quickly a change appears.

Compatible column, bar, line and area charts update their existing chart. Other chart types or configurations may redraw. Both paths show the current bounded live window. Unkeyed messages add rows within that window; they do not create a permanent history. SSE providers may replay events when reconnecting, but completeness depends on the provider. Use a separate historical dataset when you need that history.

The chart's status shows whether it is streaming, reconnecting, missing recent updates, or stopped. A gap warning means updates may be missing, even if values are still visible. Dashboard streaming pauses while Settings or Analyze is open and resumes when it closes. For a stopped dashboard subscription or failed chart read, reload the dashboard after checking your session and access. A provider-side stop needs the separate recovery steps below.

If a chart read is rate limited, times out, loses its network connection or encounters a server error, the chart keeps its displayed rows and shows Streaming waiting to retry. It retries automatically after the server's requested wait; new notifications do not bypass that wait. Without a valid requested wait, repeated failures use increasing delays, up to 30 seconds. Displayed rows may be out of date while the chart waits. Sign-in and permission failures stop reads instead; reload the dashboard after restoring access.

To stop watching, remove the widget or leave the dashboard. This does not disable the datasource or stop other readers. To stop the provider connection for everyone using that datasource, disable streaming on the datasource as described below.

Other views and derived datasets

A query derived from a streaming dataset uses the usual refresh behavior. On a dashboard, enable Live Data in its widget settings and choose an interval, for example 2 seconds; the minimum is one second. This checks Knowi repeatedly rather than using the direct-stream notification connection.

Public dashboards and shared-link dashboard views, API access and exports also retain their ordinary refresh behavior. They do not receive the signed-in dashboard's automatic push subscription. Preview remains a manual read: click it again to see the latest received state.

Show live updates in Knowi Apps

Bind the saved Direct streaming dataset to your App and declare the data.watch capability. The App can then subscribe to changes for up to 16 bound datasets through one connection:

var events = new EventSource('/__knowi/data/stream?binding=timing');

Here, timing is the App's dataset binding name. Each change event identifies the binding and includes its current revision, last update time, connection status and stale flag. The notification contains no data rows. When the revision changes, the App reads the updated rows through its normal data.query call, where Cloud9QL, filters and permissions still apply. Combine frequent notifications into one pending refresh and avoid overlapping queries.

Knowi pushes these notifications to the Apps gateway, which forwards them to the browser. The gateway does not poll Knowi for changes. The connection periodically renews, and each connection sends current state so the App can recover after a disconnect. Reconnection does not replay every missed event or restore historical rows.

This works for signed-in and public Apps when their current access settings, capabilities and dataset bindings permit it. Provider credentials remain in Knowi. Revoked access stops the subscription; a stopped or stale feed must not be presented as current. Close the EventSource when the view is removed. Subscribing to notifications does not enable streaming on an ordinary dataset; for a derived dataset, subscribe to its underlying Direct streaming binding and refresh the derived result when it changes.

Understand freshness and gaps

Untransformed rows include knowi_stream_status and knowi_stream_stale. Keep these fields in your output when users need to assess freshness.

StateMeaning and next step
pending or reconnectingKnowi is starting or restoring the connection. Wait, then read again.
connectedThe subscription is connected. Check the stale flag as well.
disconnected or stoppedDo not treat retained values as current. Check the provider and datasource settings.
rate_limitedThe provider has limited requests. Knowi waits before retrying; retained values may be out of date.
authentication_failed or configuration_errorCheck the datasource credentials or token-request settings, correct them and save.
gap_detectedSome updates may be missing. Reconnection does not prove completeness; SSE replay depends on the provider's retention. Knowi keeps no durable event log.

The stale flag becomes true after 15 seconds without an accepted update, and during a disconnected, stopped or gap state. A quiet feed can therefore be stale even while connected. This indicates freshness, not necessarily a provider fault. An empty dataset has no rows on which to show these fields; Apps can also receive connection status through their dataset subscription.

Connections and received state are shared by active readers of the same datasource on one Knowi server. If a feed has no query reads or watches for 60 seconds, its state expires. When all feeds are idle, the connection closes. Closing your own view may not stop a feed that someone else is using.

Restarting Knowi, changing the connection, or reopening after idle expiry starts a new live-state window. Reconnection and token renewal can lose updates. State also differs between Knowi servers, so moving to another server can initially return an empty result.

Each feed/key configuration holds at most 512 KiB of payload and 50,000 records, whichever limit is reached first. A single message must be no larger than 60,000 bytes. When the state fills, the least recently updated records are removed and a gap is reported. Knowi does not backfill or keep a durable event log, and cannot restore an earlier query's history. SSE providers may replay retained events in response to Last-Event-ID; this does not guarantee completeness. Use a separate historical dataset when you need that history.

Change or stop a connection

Reopen the datasource to update its streaming type, provider address or credentials, then save. This replaces the active connection and its received state. Edit the query to change its endpoint, headers, parameters, topic, key fields or version field; the new combination receives a new live-state window.

To disable streaming for the datasource, clear Enable streaming and save. Existing streaming queries return empty results while it is disabled; they do not become HTTP requests. To use ordinary REST again, edit and save those queries with their HTTP settings.

An SSE provider can return HTTP 204 to stop reconnection. Authentication or configuration failures also stop automatic retries. Correct the provider or datasource settings first, then save the corrected datasource; if its settings have not changed, disable streaming and save, wait for the connection to stop, then enable it and save again. Alternatively, close all readers for more than 60 seconds so the stopped connection expires, then reopen the query or dashboard. Reloading the dashboard alone does not restart a retained provider-side connection that has stopped.

Troubleshooting

What you seeWhat to check
Invalid streaming configurationCheck the streaming type and its HTTPS endpoint or WSS broker URL and topic. If you chose key or version fields, check their names and values; a version requires keys. Ensure each message is a JSON object, not an array.
Empty previewWait for the first message and preview again. Check the endpoint or topic, provider access, credentials and any Cloud9QL filter.
Rows do not changeMake another query read; a preview is not an automatically updating display. If a version field is configured, check that incoming versions increase for the same key.
Unexpectedly few rowsCheck whether different entities share your key. Include the device, session or partition fields that distinguish them. Also check filters and capacity limits.
Connection repeatedly dropsVerify the provider's protocol, port/path, TLS certificate and access permissions. SSE requires an HTTPS event-stream response; MQTT requires MQTT 3.1.1 over WSS. For Other authentication, verify the token response and expiry format.
gap_detectedCheck for disconnects, credential renewal or evicted records. A later connection does not prove completeness. Consult a separate historical source if missing events matter.
Chart says streaming stopped or read failedReload the dashboard and check sign-in and dataset access for a failed browser subscription or chart read. For provider HTTP 204, authentication or configuration failures, correct and restart the datasource connection as described above.
Chart says waiting to retryLeave it open while the rate-limit or retry wait expires. Check your network if it persists. Reads resume automatically; repeated notifications do not trigger extra requests during the wait.
Streaming limit: 16 widgets per pageRemove a streaming widget or split the views across separate dashboards.
Streaming capacity reachedClose unused readers and wait for idle expiry. Ask your administrator to check shared datasource and topic limits.
Access deniedAsk the owner to share the dataset for viewing, or the datasource for query creation and preview.

For administrators

MQTT broker URLs must use WSS and cannot contain query parameters, embedded credentials or fragments. SSE uses HTTPS endpoints and fixed query parameters. Public and private network destinations are supported for MQTT, SSE and token authentication when reachable from the Knowi server. URL credentials and fragments are not supported. Do not disable TLS verification or network protections to make a test broker work.

Limits apply per Knowi server: up to four active streaming datasources per customer, 32 across the server, and 16 feed/key configurations per datasource. SSE also has a separate limit of four active HTTP stream requests per customer and 32 per server. Queries on the same datasource share one SSE request when their endpoint, parameters and headers match, even when their record keys differ. Multiple servers can each connect to the same provider; account for its connection limits. Apps that use dataset change notifications require the matching streaming gateway support and an explicit dataset change subscription.