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.

Pigueiras · Accepted Answer · 2017-04-24 12:05:27Z

7

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

answered Apr 24, 2017 at 12:05

Pigueiras

19.5k10 gold badges67 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RubenStrenzke Over a year ago

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

Pigueiras Over a year ago

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 :(

Tim Over a year ago

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

Tim · Accepted Answer · 2019-01-18 19:58:27Z

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?

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related