Data export#

Synopsis#

Exporting data to CSV, HTML or XLSX (Excel) is just a matter of accessing the appropriate HTTP endpoint:

http://localhost:24642/api/mqttkit-1/testdrive/area-42/node-1/data.csv
http://localhost:24642/api/mqttkit-1/testdrive/area-42/node-1/data.html
http://localhost:24642/api/mqttkit-1/testdrive/area-42/node-1/data.xlsx

Note

The suffix (.txt, .html, .xlsx) controls the output format.

See also

Gallery of data exports

Introduction#

Kotori offers a HTTP interface for exporting timeseries data to various tabular and hierarchical data formats and different timeseries plots. After querying the timeseries database, data is channeled through a pandas DataFrame object and routed to a flexible rendering machinery, which supports a number of output formats out of the box:

Tabular data

Hierarchical data

Timeseries plots

Details#

Setup#

To make Kotori listen to HTTP requests for raw data export and data plot rendering, please have a look at HTTP export API configuration about how to configure a HTTP endpoint for accessing and querying timeseries data.

List of output formats#

Possible suffixes are (.csv, .txt), .json, .html, .xlsx, (.hdf, .hdf5, .h5), (.nc, .cdf), (.dy, .dygraphs), (.dt, .datatables), (.bk, .bokeh), (.vega, .vega.json), .png

Todo

Make table which maps extension to description/web link, maybe combine with table from Introduction

Todo

Add gallery of exports.

Todo

Mention .png?renderer=ggplot&theme=… and .png?style=…

Download#

Download data using HTTPie:

export HTTP_URI=http://localhost:24642
export MQTT_TOPIC=mqttkit-1/testdrive/area-42/node-1

http GET $HTTP_URI/api/$MQTT_TOPIC/data.csv  --download
http GET $HTTP_URI/api/$MQTT_TOPIC/data.xlsx --download

Date ranges#

Date ranges can be specified using the HTTP URI query parameters from and to. Both accept absolute and relative datetime expressions. If omitted, the defaults are from=now-10d and to=now.

Absolute timestamps:

  • ?from=2016-06-25T22:00:00.000Z

  • ?from=2016-06-26T00:00:00.000%2B02:00 (%2B is “+” urlencoded)

  • ?from=2016-06-25

  • ?from=20160625

Relative datetime expressions:

  • ?from=now-4h&to=now-2h

  • ?from=now-8d5h3m&to=now-6d

Attention

Please recognize absolute datetimes are expected to be in ISO 8601 format. Default is UTC, optionally specify an appropriate timezone offset. There’s another, ISO 8601-like convenience format without separators. The accepted datetime formats are:

YYYY-MM-DDTHH:mm:ss.%sZ
YYYY-MM-DD
YYYYMMDDTHHmmssZ
YYYYMMDD

When a straight date without time component is supplied as value to the to parameter (e.g. YYYY-MM-DD or YYYYMMDD), the date is ceiled to YYYY-MM-DDT23:59:59.999, which is most probably what you want anyway (DWIM).

Examples using HTTPie:

http GET $HTTP_URI/api/$MQTT_TOPIC/data.csv from=now-30m
http GET $HTTP_URI/api/$MQTT_TOPIC/data.csv from=now-2h
http GET $HTTP_URI/api/$MQTT_TOPIC/data.csv from=now-1h to=now-20m
http GET $HTTP_URI/api/$MQTT_TOPIC/data.csv from=2016-06-26T12:42:59.000+02:00
http GET $HTTP_URI/api/$MQTT_TOPIC/data.csv from=20160626T124259+0200
http GET $HTTP_URI/api/$MQTT_TOPIC/data.csv from=20160626T124259Z to=20160628T124259Z

Exclude fields#

Excluding fields is a handy option for outlier mitigation when plotting data and more. Let’s have look at this feature by an example.

Measure some values of different units:

http POST $HTTP_URI/api/$MQTT_TOPIC/data temperature:=48.48 humidity:=64.64 weight:=10000

As this would not be plotted well due to different scaling on the y-axis, let’s drop the “weight” field when exporting data:

http GET $HTTP_URI/api/$MQTT_TOPIC/data.csv exclude=weight

This would yield the result with “weight” field omitted:

time,humidity,temperature
2016-07-01 16:58:34.788767764,64.64,48.48
2016-07-01 16:58:37.645754806,64.64,48.48

Include fields#

There is also the url parameter include which does things the other way round: It will only export the named fields:

http GET $HTTP_URI/api/$MQTT_TOPIC/data.csv include=weight

Both parameters take a comma-separated list of field names.

Sort records#

The URL parameter sort specifies a field name you would like to sort by, e.g. sort=temperature. The parameter direction can be used to control the sort oder, e.g. direction=desc for sorting in descending order. If you omit the direction parameter, the sort order is ascending.

Limit result size#

The limit parameter controls the number of records to be returned.

Return scalar values#

In order to return a single scalar value, use the scalar parameter. It will omit any headers. For example, sort=time&direction=desc&scalar=humidity will return the most recent humidity value in the database.

Hierarchical data#

Kotori supports exporting data in HDF5 and NetCDF formats. See Hierarchical data export.

Todo#

Todo

Describe parameters:

pad, interpolate=true