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

• CSV

• JSON

• HTML

• XLSX

• DataTables

Hierarchical data

• HDF5

• NetCDF

Timeseries plots

• matplotlib

• ggplot

• dygraphs

• Bokeh

• Vega, using Vincent

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