REST API connector

This connector lets you connect a REST API to Serenytics.

Default format accepted by the API connector

The API must return a valid JSON.

The simplest accepted format is a list of rows. Here is an example with 4 rows:

1
2
3
4
5
6
[
    { "col1": 1, "col2": 2},
    { "col1": 3, "col2": 4},
    { "col1": 5, "col2": 6},
    { "col1": 7, "col2": 8}
]

test tip

If you want to test the data accepted by our connector. You can mock your API with an online tool (e.g. https://www.mocky.io/).

using quotes

Use doubles quotes in your JSON to create a valid JSON object, not single quotes.

The JSON can also contain a single main object containing your data (it won't work if you have several keys at the top level of your JSON):

1
2
3
4
5
6
7
8
9
{
"items":
    [
        { "col1": 1, "col2": 2},
        { "col1": 3, "col2": 4},
        { "col1": 5, "col2": 6},
        { "col1": 7, "col2": 9}
    ]
}

JSON Path Option

If the list of rows is deep inside your JSON, you can provide the path to access this list.

For example, if your JSON is:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
{
"data": {
    "items":
        [
            { "col1": 1, "col2": 2},
            { "col1": 3, "col2": 4},
            { "col1": 5, "col2": 6},
            { "col1": 7, "col2": 10}
        ],
    "nb_rows": 4
    },
"code": 28,
"date": "2019-08-29"
}

In the option JSON Path, you must provide "data;items".

Pagination

If you provide a parameter in the Pagination query parameter, our engine will query all the available pages from your API and concatenate the results in a single table.

For example, let's say your API URL is https://api.myhome.com/objects and you provide the parameter my_page_code for this parameter. Our engine will first query the URL: https://api.myhome.com/objects&my_page_code=1.

If it gets an answer, it will also query the URL https://api.myhome.com/objects&my_page_code=2.

If it gets an answer, it will query the URL https://api.myhome.com/objects&my_page_code=3.

And so on.

Pagination and performance

A dashboard built on a paginated API might be very slow as each data query will require several API calls. For such cases, we strongly advise to sync your data into the Serenytics datawarehouse. See Performance section at the bottom of this page.

Login / password

If you provide login/password options, it will be used as HTTP Basic Auth when querying your API.

Login URL

When this URL is provided, a first GET call will be made to this URL before querying the data URL. If the returned code obtained from this first call is not 200, the query will be rejected.

HTTP Headers

You can use this field to provide any extra HTTP headers. Do not enter the curly brackets around the headers, use single quotes and separate your headers with semi-colons.

Here is an example: 'user-agent': 'my-app/0.0.1'; 'test-header':'value'

Method

Choose the method (GET or POST) that will be used to call your API.

HTTP Payload

If you enter a valid JSON here, it will be passed as a payload to the API call.

Here is an example: {"key1":"value1", "key2": "value2"}

Format

Use this option to specify the format of data returned by the API. Most options above are only compatible with JSON format.

Flatten sub objects

Let's say your JSON contains sub-objects (such as city in the below example):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
[
    {
        "firstname": "John",
        "city": {"name": "Paris", "country":"FR"}
    },
    {
        "firstname": "David",
        "city": {"name": "New-York", "country":"US"}
    }
]

By default, the table obtained in Serenytics will have only two columns: "firstname" and "city":

firstname city
John {"name": "Paris", "country":"FR"}
David {"name": "New-York", "country":"US"}

If you active the Flatten sub objects option, you will get three columns: "firstname", "city.name" and "city.country":

firstname city.name city.country
John Paris FR
David New-York US

Flatten sub arrays

Let's say your JSON contains sub-arrays (such as visited_countries in the below example):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
[
    {
        "name": "John",
        "visited_countries": ["FR","US","CHINA"]
    },
    {
        "name": "David",
        "visited_countries": ["UK","DE"]
    }
]

By default, you will get only two rows in the Serenytics table.

If you enable the Flatten sub arrays option, you will obtain 5 rows:

name visited_countries
John FR
John US
John CHINA
David UK
David DE

Flatten sub-arrays of name/value objects (advanced)

This option is helpful when your API returns generic meta-info for each object. In the below example, the part {"name":"City","value":"Paris"} for John means there is a meta info named "City" and its values is "Paris".

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
[
    {
        "user": "John",
        "meta_info": [{"name":"City","value":"Paris"}, {"name":"Age","value":35}]
    },
    {
        "user": "David",
        "meta_info": [{"name":"City","value":"Paris"}, {"name":"Gender","value":"Male"}]
    }
]

By default, the Serenytics table will have two columns and four rows:

user meta_info
John [{"name": "City", "value": "Paris"}, {"name": "Age", "value": 35}]
David [{"name": "City", "value": "London"}, {"name": "Gender", "value": "Male"}]

If you enable the Flatten sub-arrays of name/value objects option, meta-infos will be interpreted and you will get:

user meta_info.City meta_info.Gender meta_info.Age
John Paris 35
David London Male

Each meta-info will generate a column. If one meta-info is not available for a given row, it will stay empty.

Performance hints

When your data is available through a REST API, you have two options to use it in Serenytics:

  • you use the REST API connector and then you build your dashboard widgets directly on this datasource.
  • you synchronize the data coming from this API into the internal Serenytics datawarehouse (i.e. in a Storage) and then you build your dashboard widgets against this datawarehouse.

Option1 can be very easy to setup and the data is not stored on the Serenytics side. The counterpart is that your dashboard will probably be slow as each loaded widget or applied filter will query the API. You can configure the Serenytics cache on the datasource, but if the queries results are not in cache, it won't solve the performance issue.

With option2, dashboards are not 'live' anymore (data is updated at each sync) but they will be a lot faster. To sync the data from your REST API into Serenytics, you can either run an ETL step using your API connector as input and a storage as output (read the dedicated tutorial on our website). Or you can skip the REST API connector and directly connect to your REST API in a Python script (using the requests package) and store the result in a Storage (and schedule this script once a day to update the data).

In the end, we encourage you to use the second option using an ETL step based on our API REST connector.

Extracting a field from a JSON object

When your data is in a Serenyics storage, you can use the json_extract_path_text function. This function extracts a given field from a json string. If your api returns complex objects, you can store them in the storage, and then extract the fields you need in new columns using this function. Note that this function is not available directly on the REST connector, it's only available on storages.