Export InfluxDB to .CSV

General
1

Although my question is a bit off topic for this forum, I hope someone can help me anyway. I have asked this question on the official InfluxDB community, but that is or seems little active.

My intention is to write data from the database "lucht" to a .CSV file and I tried the following command:

influx database lucht format csv -execute "select n from tab1 limit 100"> temp.csv

This creates the .CSV file but it is empty. You can see the database structure in the graphic. Can anyone help me with the correct command line?

8acdd221cc2261c6d84e600b023373dc79a84ee5
8acdd221cc2261c6d84e600b023373dc79a84ee5760Ă—190 5.4 KB

2

What happens if you run the command without the redirect on the end?

3

What do you get if you open an influx session and run your query?

4

Thanks zenofmud.

What I did: from root to Influx and after 'using database lucht' the query shows nothing :frowning:

5

Thanks Colin.

Running:

influx database lucht format csv -execute "select n from tab1 limit 100"

shows an error "ERR: error parsing query: found influx, expected SELECT, DELETE, ....."

No matter it the query has the > at the end.

6

You have to run it at the command prompt, not in the influx application.

7

Hi Colin. When I execute:

influx database lucht format csv -execute "select n from tab1 limit 100"

from the command prompt an error is displayed as shown in the image:

error
error1439Ă—1264 49.6 KB

8

It is better to copy/paste here rather than screenshot if possible, as it is easier to read and allows us to quote from it.

What does it say there about how to tell the command what database to use?

Another problem is that, as you saw when you followed @zenofmud 's suggestion to run the query in the influx app that it did not find anything. Can you explain in words what you think the query select n from tab1 limit 100 should do?

9

Working with Influx is completely new to me, which is why I depend on the information on the internet. I found the query on the page https://www.quora.com/How-can-I-extract-data-from-influxDB-to-external-files but it doesn't really mean much to me. I also don't know if Namita Rayangoudar is correct.

The error message that the query generates ("unknown argument") does not say much, with that message we don't get any wiser I think.

With a query I want to save the last 100 measurements from the database as a .CSV file for further processing. Any other query that does the same is fine too, of course.

10

In that case you need to do some learning. You cannot use a tool like influxdb without knowing the basics of what it is about. You will have to decide whether to go with influx 1.8 or 2.0. 2.0 is the future but it is still very new and more difficult for anyone who knows SQL to pick up. However since you obviously don't know SQL then perhaps you would be better going straight to v2.0.

This is a good place to start understanding the key concepts for 1.8. There is a link there to the 2.0 equivalent. InfluxDB key concepts | InfluxDB OSS v1 Documentation

1 Like
11

Thanks for your advice Colin. Still, I hope someone can help me with this, as a very occasional user (presumably just this simple database) I'd rather not put so much energy into learning InfluxDB. That is why I am already going to see if I can achieve my goal in a different way.

But thank you anyway!

12

What Measurement name or names have you put the data in? Not the field names, the Measurement. Is it tab1? If you don't know then how did you get the image you posted earlier showing the data?

13

The TTN data consists of humi, temp and volt. In NodeRED this TTN data is retrieved with a TTN Uplink flow and then written to the database "lucht" with an InfluxDB Out flow.

Because I also need this data in a .CSV file, I am looking for a correct query.

The tab1 you mention is from the example I found on the web ( 'How to extract data from influxDB to external files - Quora).

14

That doesn't tell us the Measurement name. Feed the message you are sending to the Influx node into a debug node set to show Complete Message (that is important) and show us what it shows. Also show us how you have configured the influx out node.

15

Your help is appreciated!

First of all, I reinstalled everything to make sure it's okay. I think the best way to show the influx out node configuration is this way:

[{"id":"1de25c26.2776b4","type":"mqtt out","z":"cc7221f7.8586c","name":"MQTT Topic Lucht","topic":"Lucht","qos":"","retain":"","broker":"b7121f19.a0272","x":490,"y":160,"wires":[]},{"id":"b7121f19.a0272","type":"mqtt-broker","z":"","name":"IP160","broker":"192.168.2.160","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""}]

The debug output looks like this:

object
  humiachtertuin: 61.3
  tempachtertuin: 7.6199999997
  voltachtertuin: 4.222

Hopefully I understood you correctly and is this information what you asked for.

16

That was a waste of time.

You have posted an mqtt node instead of the influx out node.

Did you do that?

17

Sorry, this is the complete flow including influx out:

[{"id":"cc7221f7.8586c","type":"tab","label":"Lucht","disabled":false,"info":""},{"id":"a8e73786.bcdee8","type":"ttn uplink","z":"cc7221f7.8586c","name":"Vocht en temperatuur","app":"320ca0e6.622ee","dev_id":"humitemp-achtertuin","field":"","x":120,"y":220,"wires":[["1de25c26.2776b4","b1245fee.a95da","c382368f.517b28"]]},{"id":"1de25c26.2776b4","type":"mqtt out","z":"cc7221f7.8586c","name":"MQTT Topic Lucht","topic":"Lucht","qos":"","retain":"","broker":"b7121f19.a0272","x":490,"y":160,"wires":[]},{"id":"b1245fee.a95da","type":"influxdb out","z":"cc7221f7.8586c","influxdb":"30c360.5b7f3ca","name":"InfluxDB Lucht","measurement":"lucht","precision":"","retentionPolicy":"","x":480,"y":280,"wires":[]},{"id":"c382368f.517b28","type":"debug","z":"cc7221f7.8586c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":710,"y":220,"wires":[]},{"id":"320ca0e6.622ee","type":"ttn app","z":"","appId":"humtitemp-achtertuin","accessKey":"ttn-account-v2.Lfk6s7Xabcde7GjCCCC2DkviRu_gF3_DNPjNx9BUtv0","discovery":"discovery.thethingsnetwork.org:1900"},{"id":"b7121f19.a0292","type":"mqtt-broker","z":"","name":"IP160","broker":"192.168.2.160","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"30c360.5b7f3ca","type":"influxdb","z":"","hostname":"192.168.2.185","port":"8086","protocol":"http","database":"lucht","name":"DB Lucht","usetls":false,"tls":""}]

The debug node output (most right frame in Nodered) is what I posted.

18

So when I asked you what the Measurement name was all you had to do was to look in the Influx node to see what measurement you had specified. You are saving it in a Measurement called lucht in a database also called lucht. The query to get all the data from that measurement is
select * from lucht
If you are trying to send it to a csv file why are you trying to do that in a command rather than in node-red?
I have to say again that I think you should spend a couple of hours learning about the basics of influx. I think you would have saved yourself, and me, some time already.

19

Thanks Colin and it is my intention to write the output of select * from lucht to the file. I prefer doing it in a command with a CRON task, not in NodeRED.

20

OK, so is everything ok now?