influxdb data/table be downloaded as csv file?

Question

Influxdb is a time series database which stores data and its attributes in the tables, commonly known as measurements.

Can the tables in databases of influxdb be fetched to local system in csv format?

Answer 1

In CLI following command can be used to download tables on the local system:

influx -database 'database_name' -execute 'SELECT * FROM table_name' -format csv > test.csv

Answer 2

Using CLI tool influx you can set csv output format for results:

influx -host your_host -port 8086 -database 'your_db' -execute 'select * from your_metric' -format 'csv'

-host and -port options can be omitted if command is run on local InfluxDB host. There is also useful -precision option to set format of timestamp.

Answer 3

The answer posted by surya rahul worked for me but with minor correction.

Original : influx -database 'database_name' -execute 'SELECT * FROM table_name' -format csv > test.csv.
This command returned me an error "error parsing query: invalid duration"

However . A simple modidfication of using double quotes " " instead of single ' ' in the query part solved this issue. Modified : influx -database 'database_name' -execute "SELECT * FROM table_name" -format csv > test.csv.

Being new, not sure how it worked but it works. Hope it may help.

Answer 4

the CLI-way is explained in the other answers. I used the HTTP-API-way:

curl -G 'http://localhost:8086/query' --data-urlencode "db=mydb" --data-urlencode "q=SELECT * FROM \"mymeasurement\" " -H "Accept: application/csv" >  mytargetcsv.csv
database = mydb
measurement/table = mymeasurement
CSV-File = mytargetcsv

You can also use this from a remote machine and cut the query in time slices (heading: memory usage):

"q=SELECT * FROM \"mymeasurement\" where time > now() - 130d"
"q=SELECT * FROM \"mymeasurement\" where (time < now() - 130d) and  (time > now() - 260d)"

https://docs.influxdata.com/influxdb/v1.8/tools/api/

Answer 5

Alternatively, 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?

Answer 6

If you are using InfluxDB Cloud, in Data Explorer tab, you can select bucket, measurement, and time range first, then you can click "Download CSV" button.

Answer 7

If you have problems with special chars in your measurement tablename you receive a error from influx at your terminal.

Example:

$ influx -execute 'SELECT * FROM sonoff.0.energy.value' 

error parsing query: found .0, expected: at line 1, char 21

You have to use the query like this with quotation marks:

Example:

$ influx -execute 'SELECT * FROM "sonoff.0.energy.value"' 

Answer 8

Easily just select the rows - > right click - > Export Selected - > select the desired format