Knowi supports connectivity to internal and external REST API's, with the ability to manipulate, store and join with other datasources.
Highlights
- Connect to any REST API.
- Query and transform the data.
- Perform advanced analytics and queries on top of it using Cloud9QL, including multi-structured data returned.
- Join and blend it against other datasources
- Store and incrementally track query results where needed
Connecting
- From the New Datasource menu (Datasources icon on the left-hand menu --> 'New Datasource'), click on the REST icon.
- Enter a name and the REST API the base URL that your requests/end points will be driven off from.
-
After creation, click on the 'Configure Queries here' link.
`
Querying
Note that the data returned must be in JSON form.
- Enter any HTTP headers, if applicable. Multiple headers must be separated one per line.
- Specify the REST end point for the request
- Add any optional URL parameters that you'd like to add. Parameters will be encoded automatically.
- Cloud9QL: 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.
- Query strategy: You can either have this query execute real-time when the direct Query checkbox is selected, or, store and incrementally track the results into the datastore we provide (default option).
Example
The integration is pre-configured to a live Parse API end-point with dummy data to easily follow along.
- Create the REST API datasource with the default URL.
- Enter user id and password, if required (If present, the base64 encoded hash will be automatically passed into all requests)
- In the Headers box, copy and paste the sample headers from the help icon, use the default End Point.
-
Click on Preview to see the results. The result in this cases is a nested object, which can be unwound using the following Cloud9QL
select expand(results);
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.
NOTE: If the REST API does not use a next_page token but a url instead, then configure the setting as follows:
"Pagination - Response Field Name" use "next_page" value
"Pagination Response type" use "Response contains full url to next page"
Automating date-based requests
https://dummy.com?startDate={$c9_thisyear-1y:yyyy-MM-dd}&endDate={$c9_thisyear:yyyy-MM-dd}&blah=blah will be replaced to:
https://dummy.com?startDate=2015-01-01&endDate=2016-01-01&blah=blah
EPOCH_SECS
Used in conjunction with the URL parameter option, this allows for the differentiation between the epoch with millis vs without millis
Example:
date={$c9_today,epoch_secs} will format today into epoch seconds format
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: { }
}