2

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
  • 17,275
  • 9
  • 58
  • 83
RubenStrenzke
  • 55
  • 2
  • 5

2 Answers2

4

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
Pigueiras
  • 17,275
  • 9
  • 58
  • 83
  • Is that somewhere in the documentation and I just missed it? – RubenStrenzke Apr 25 '17 at 14:16
  • I was following this issue in github: https://github.com/influxdata/influxdb/pull/7099, that's why I know. But I couldn't find this in the docs either :( – Pigueiras Apr 25 '17 at 20:35
  • This feature is described in the manual here: https://docs.influxdata.com/influxdb/v1.7/tools/api/#request-body – Tim Jan 18 '19 at 19:52
0

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:

Tim
  • 1,221
  • 13
  • 23