How can you get CSV instead of JSON from the HTTP API of InfluxDB?

Question

I want to use an influxdb within the context of business intelligence: ETL, joining data from other databases, creating live dashboards. Right now, we are using standard BI-Tools such as QLIK or Microsoft PowerBI.

According to the documentation the HTTP API should be used for querying (https://docs.influxdata.com/influxdb/v1.2/guides/querying_data/) My problem is that the output of the API seems to be JSON only. That means that each analyst first has to figure out how to transform the JSON into table-format before joining other data etc.

Is it possible to tell the API to produce a csv-like table output? Do you have recommendations which tools to use to produce good Dashboards? I tried grafana but that seemed to fall short when joining other data.

Pavo

Answer 1

You can use -H "Accept: application/csv" in your curl to have a response in CSV. For instance:

$ curl -G 'http://localhost:8086/query' --data-urlencode "db=my_db" --data-urlencode "q=SELECT * FROM \"cpu\"" -H "Accept: application/csv"
name,tags,time,host,region,value
cpu,,1493031640435991638,serverA,us_west,0.64

Towe

Is that somewhere in the documentation and I just missed it? – Pavo

I was following this issue in github: github.com/influxdata/influxdb/pull/7099, that's why I know. But I couldn't find this in the docs either :( – Towe

This feature is described in the manual here: docs.influxdata.com/influxdb/v1.7/tools/api/#request-body – Heptad

Answer 2

You can use jq to convert the JSON output to CSV as follows, which also allows you to get RFC3339 formatted timestamps:

jq -r "(.results[0].series[0].columns), (.results[0].series[0].values[]) | @csv"

which gives the output

"time","ppm","T"
"2019-01-17T19:45:00Z",864.5,18.54
"2019-01-17T19:50:00Z",861.4,18.545
"2019-01-17T19:55:00Z",866.2,18.5
"2019-01-17T20:00:00Z",863.9,18.47

and works because:

• (.results[0].series[0].columns) gets the column names as array
• , concatenates the output
• (.results[0].series[0].values[]) gets the data values as array
• | @csv uses the jq csv formatter
• -r is used to get raw output

Further resources:

• Use https://jqplay.org/ to build queries
• Other examples: Convert JSON array into CSV using jq
• https://unix.stackexchange.com/questions/429241/convert-json-to-csv
• How to convert arbirtrary simple JSON to CSV using jq?

Heptad