Node-RED node for InfluxDB control - i need a button on NodeRED Dashboard to perform database content delete

[bogi]

Hi Folks!

i know by this thread:

Node-RED should empty my influxDB "test database" every 7 days

Hello all, Can anyone tell me if it is possible to specify in Node-RED that all records in my database are deleted every 7 days? kind regards nilsbaurx

That retention policy feature in InfluxDB itself can control data retention. However my need is specific - i need ability to clean the data base by \one click\ action from either Node-RED Dashboard or Home assistant or using MQTT message.

Imagine I'm starting my machine and want to see its parameters during the run - i don't need to save it or keep in data base (I'm using InfluxDB). My machine is equipped with basic temperature sensors and ESP8266 which is sending data only when i use machine. I can then see on the trend it's behavior. When i finish my work i need to destroy the data base by one click - i don't want to go to influxDB CLI and search for database, deleting etc... I just need simple option to click a button on the Node-RED Dash or even better: a button on ESP8266 so when i press it ESP8266 will send MQTT message to Node-RED and the Flow will remove the content from specific database.
Do we have a Node that will do?

Thank you.

[Colin]

Which bit do you need a node for? The usual influx node used is node-red-contrib-influxdb.

[bogi]

The node that will allow me to delete the content of a specified database using i.e. MQTT message.
The plan is: when i click button on ESP8266 module -> it will publish a message i.e. clean "temporary_database" -> Node-RED Flow will get it and will tell InfluxDB to go and clear all content from database called "temporary_database".
By doing this i can start datalogging of the temperature of my device and see the trend on the tablet. When i finish and stop logging i can destroy the data because i'll not need it anymore. And i want to destroy that data by means of the pushbutton on my machine (meaning ESP8266 publishing MQTT message).

[Colin]

Have you tried with the node I suggested?

[bogi]

i'll try to try tomorrow and will update

[bogi]

So i'm already sing influxdb db node as i'm collecting data from mqtt using node-red, formatting them and sending to influxdb database. Actually in my palette i have 3 inlfuxdb related nodes: influxdb in, influxdb out and influxdb batch.

I assume i have take a closer look to "influxdb out" node as i want to do something with influxdb...

From here:

Manage your database using InfluxQL | InfluxDB OSS 1.8 Documentation

i found two interesting things (queries):
CREATE DATABASE < database neme >
DROP DATABASE < database name >
which looks promising. But how to connect the dots? How to send CREATE DATABASE < database name > to influxdb? Inspecting "influxdb out" node i can only see the place to put database name/path which must be already created in influxdb. I hope you get my point: database must me created first, before i use Node-Red node "influxdb out" to query it.
To create or drop databse i would need to use influxdb CLI somehow (at least this is how i understand it...). Is it possible from node-red?

[bakman2]

Please read the documentation for the influx db nodes.

Queries one or more measurements in an influxdb database. The query is specified in the node configuration or in the msg.query property. Setting it in the node will override the msg.query . The result is returned in msg.payload .

Click on the node once and click the documentation tab in the node-red ui, it gives you details about the usage.

[Colin]

I would use DROP MEASUREMENT or DELETE * FROM ...

If that is the right syntax for delete, I would have to check. I am not sure if you can do DROP via the influx node.

[bogi]

Please take into account language barriers. I've read documentation. This is what i do first as it is the fastest way and the right way. Everywhere i checked it is said "query" which can be translated to "request for information from database", right? I don't have data base, i need to create one using mqtt message and then destroy it the same way. So far i can see the only way to create database is using LinfluxDB CLI via Poratiner or directly in terminal of my RB Pi.

DROP MEASUREMENT will drop measurement only if i understand it correctly.
DELETE * FROM is interesting. Will "DELETE * FROM * " meaning "delete everything from particular database", work? Maybe i'll create one GPDB - General Purpose Data Base for ad hoc use.

[bakman2]

Look, it is simple:

inject node:

Connect it to an influxdb-in node
Deploy
Click inject button

Inject node to drop:

Connect it to the same influxdb-in node
Deploy
Click inject button

Query databases

Output in sequence:

[Colin]

If you drop and recreate the database does an Influx In or out node configured for that database reconnect correctly?

[Colin]

Answering my own question, yes, the influx nodes do reconnect after dropping and recreating the database.

[{"id":"a882f9e675263bdb","type":"inject","z":"bdd7be38.d3b55","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":200,"y":1320,"wires":[["94c15495bcc3c1d4"]]},{"id":"94c15495bcc3c1d4","type":"influxdb out","z":"bdd7be38.d3b55","influxdb":"b0493664.35a528","name":"Add data","measurement":"testing_m","precision":"","retentionPolicy":"","database":"database","precisionV18FluxV20":"ms","retentionPolicyV18Flux":"","org":"organisation","bucket":"bucket","x":380,"y":1320,"wires":[]},{"id":"85dfcda50757cbb4","type":"catch","z":"bdd7be38.d3b55","name":"","scope":["94c15495bcc3c1d4","0742437cf425bed1","98583360a468f016"],"uncaught":false,"x":460,"y":1140,"wires":[["cc3a811aef5ea457"]]},{"id":"cc3a811aef5ea457","type":"debug","z":"bdd7be38.d3b55","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":660,"y":1140,"wires":[]},{"id":"628d0026307a8b23","type":"inject","z":"bdd7be38.d3b55","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":200,"y":1060,"wires":[["0742437cf425bed1"]]},{"id":"0742437cf425bed1","type":"influxdb in","z":"bdd7be38.d3b55","influxdb":"b0493664.35a528","name":"Create Testing","query":"CREATE DATABASE Testing","rawOutput":false,"precision":"","retentionPolicy":"","org":"organisation","x":380,"y":1060,"wires":[["152258a8ecbf50e0"]]},{"id":"152258a8ecbf50e0","type":"debug","z":"bdd7be38.d3b55","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":650,"y":1060,"wires":[]},{"id":"5ae05673e05dd2d5","type":"inject","z":"bdd7be38.d3b55","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":200,"y":1200,"wires":[["98583360a468f016"]]},{"id":"98583360a468f016","type":"influxdb in","z":"bdd7be38.d3b55","influxdb":"b0493664.35a528","name":"Drop Testing","query":"DROP DATABASE Testing","rawOutput":false,"precision":"","retentionPolicy":"","org":"organisation","x":370,"y":1200,"wires":[["33ba20e47e3bfb06"]]},{"id":"33ba20e47e3bfb06","type":"debug","z":"bdd7be38.d3b55","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":650,"y":1200,"wires":[]},{"id":"8be457793ed98e84","type":"inject","z":"bdd7be38.d3b55","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":180,"y":1380,"wires":[["1cde79013f6ac193"]]},{"id":"1cde79013f6ac193","type":"influxdb in","z":"bdd7be38.d3b55","influxdb":"b0493664.35a528","name":"read","query":"select * from testing_m","rawOutput":false,"precision":"","retentionPolicy":"","org":"organisation","x":330,"y":1380,"wires":[["b549639bf57a4a04"]]},{"id":"b549639bf57a4a04","type":"debug","z":"bdd7be38.d3b55","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":630,"y":1380,"wires":[]},{"id":"b0493664.35a528","type":"influxdb","hostname":"localhost","port":"8086","protocol":"http","database":"Testing","name":"","usetls":false,"tls":"ced18875.cf10b8","influxdbVersion":"1.x","url":"http://localhost:8086","rejectUnauthorized":true},{"id":"ced18875.cf10b8","type":"tls-config","name":"","cert":"","key":"","ca":"","certname":"","keyname":"","caname":"","servername":"","verifyservercert":true}]