Hello,
I'M totally new to this stuff but I have set up a functional environement on a raspberry pi 4 containing mosquitto, grafana, node-red, influxdb and telegraf.
I am able to send messages via mqtt (from, for example, for testing purpose from MQTTbox or from a ESP32/MCU) to node-red. I also can forward these data to an influxdb. But unfortunately the data is written as one big string to a colomn called value.
This is the mqtt message: "room=bath, temp=25, hum=65"
I want to send all environment messages to one topic (sensors/env) and use the message to split the content to different colomns. similar to this:
can you give me an example, which I could use to reach this goal? just to split a mqtt message to different parts to write as seperate colomns to the influxdb.
Are you using node-red-contrib-influxdb to write to influx? If so have you worked out how you need to structure the data going to that node?
Do you have control of the data format being written to mqtt? If so then you should format the data as a JSON string, then it will be much easier to handle in node-red. I believe esp32 development environment libraries should be able to do that for you easily.
Hi,
yes, I can create my mqtt messages like I want to. Shall I first make to get the messages as json? I think I can do that. Takes some time maybe. So I can do that and give notice as far as it works that way.
I am using node-red-contrib-influxdb.
Yes, send it in json, then you can select Parsed Json output in the mqtt in node and get it immediately into a javascript object. So the json going to mqtt wants to be something like {"room": "bath", "temp": 25, "hum": 65}
or maybe include the room in the mqtt topic rather than in the data, so a topic like /home/bath with temp and hum in the data.
Then when you send it to influx you will, presumably, want the tag room to be set to the room name and fields temperature and humidity to be set to the values. The help for the influx node tells you how to do that by passing a two dimensional array, if I remember correctly.
It works! thank you very much. I'm not sure why I need telegraf. If I stop the container it also works.
I have just send the mqtt-message in the format you told me. I also added a json-node without any further edit between my mqtt-in node and my influxdb-out node. Now I get a nice table in the influxdb.
A little bit mysterious: I can do
select * from temp
but not
select * from hum
ok, I found out that it must have somthing to do with the field "Measurement" if I do open the edit window of the influxdb-out node. If I set it to "temp" these values appear if I do a select * from temp. If I set it to hum, I have to set it to hum.
Can I do both? what does that option mean?
As I mentioned, if you set the output of the mqtt node to parsed json it will parse the json string in the message and convert it to a javascript object, so you don't need the JSON node.
Note though that you should not be writing the room as a field, you should be writing it as a tag, because tags are indexed and fields are not. That matters when you start querying for all the data in a particular room.
Measurement isn't a field it is similar to a table in a conventional sql database. So if you have called the measurement temp then when you say select * from temp it gives you all the fields that have been written to the temp measurement. I think you need to do a bit of reading of the influx docs in order to get your head round fields, tags, and measurements. Make sure that you read the influx 1.8 docs as we aren't using 2.0 yet and quite a lot changes there.
Man, I think I have to give up. my knowledge is insufficient and with my kids I have to care for, I do not have the time and the mood to figure it out
I wonder: I couldn't find any complete example in the net where someone uses a nodemcu to send the date like location, temperature and humidity using node-red to an influxdb doing this by splitting and using the topic for a tag and the other two values as fields.
I would just need one flow example which portrays just this to apply it to my environemt.
Sorry, I am frustrated.
Are you trying to use the mqtt topic to set the room or are you going to leave it with the room in the mqtt payload?
When making major changes to the db structure like making the room a tag then you will need to delete the measurement (temp) and start again. Why are you calling the measurement temp when it contains temperature and humidity.
You don't seem to have published the most important bit, which is what the message going to the influx node looks like.
Oh, I always deleted the database "sensors" to do another try. Is that also ok or not? I think it is.
MAN!!! I got it!!! IT works. I think something went wrong because of a second alternatve flow which I was set up for testing purpose and which wasn'T disabled yet.
Now I get this:
Thank you so much!
is it rude to ask how I can make it work using the topic-to-tag method?
You are great!
Not rude at all. First decide on a topic, possibly something like home/rooms/<room> where <room> is the room obviously. So for for the bathroom you would publish to home/rooms/bath, and the data you publish would just be temp and hum.
Then in node red you could subscribe to home/rooms/# so that you would get the data for all the rooms. In the function node the code would extract the room name from the topic, something like let thisRoom = msg.topic.split("/")[2]
and then build the payload to go to the influx node msg.payload = [{temp: msg.payload.temp, hum: msg.payload.hum},{room: thisRoom}]
The conceptual reason for doing it like this is that the topic tells you what the data is (data for the bathroom) and the payload contains the actual values. This is effectively the same as your influx structure where the tag tells you what the data is and the fields contain the values.
[Edit] Edited the text putting in the backticks to stop the forum from thinking the topic names are markdown sequences.
It works! thank you so much!
Your explanations were great. Now I have a working flow which uses the topic as a tag and the two numbers as fields.
One last question: wherefor is that measurement input box? Is it necessary for grafana selects? I just change the name to somethin universal like sensormeasure.
where default is the retention policy (which we haven't even mentioned yet, look in the docs for that), home is the measurement, conservatory is the room, field(temperature) is the field to use and Alias By is what you want the line to be identified as.
Ok, I think I can handle grafana.
I know what retention policy is. I am not sure which time span I should set. At the moment, I have less than 10 sensors. every sensor will write one dataset (hum and temp) every 15 minutes.
So there will be around 1000 datasets per day :-/
I have no clue how fast the database will grow. Can I set the policy to unlimited (think that is the default value) and have a look in a few weeks and eventually lower it to a few months? One year would be nice.
That is a tiny amount of data for Influx. It will run for a long time before the database gets large. Running influx on an SD card is not ideal though, I have a USB disc plugged into my Pi 3 and put the database on the disc. In practice though with only a years worth of data at that data rate the card will probably be fine.
I don't think you can change a retention policy for existing data though, you would have to export it and import it again if you want to keep it, so if you think a year is enough then you might as well set it to that to start with.
There is also the concept of using Continuous Queries to downsample the data to a different retention policy. I run mine with a 1 year policy and then downsample the data to 1 per hour into a forever policy. I have about 50 values going into the db, many of which are every 5 seconds.
If the data is important (so if the card was corrupted and you lost it all would that be serious) then I have developed a node that you can use for backing up from influx, node-red-contrib-influxd-backup that you might like to look at.
