Adding temp and humidity to influxdb

General
1

I've searched a lot and have not hit on what I need yet. I have a Raspberry Pi reading a sensor and sending temperature and humidity readings using MQTT to another RPI which is the Mosqitto Broker and node-red server.
The data is coming in the format of a JSON object. I've created a MQTT input on nodered that get the data and sends it on to the debug msg object. So every 10 seconds I get a message in the debug window like this:
1/6/2021, 2:12:12 PMnode: ab69ee34.8aa8d
testTopic : msg.payload : Object

object

Temperature: 58.2

Humidity: 40.8

I've tried to create a influxdb out object to add the 2 items to the database but at this point I really don't know what I'm doing. When I connect the MQTT input to the influxdb out, I get stuff in the database but it's wrong. There is only one measurement, Temperature and when I select * from Temperature I get a lot of data like:
1609960616822963797 40.8 58.4
1609960626985944420 40.8 58.4
1609960637143475475 40.8 58.4
Where the 40.8 is the humidity and the 58.4 is the temerature.

I feel like I'm just stumbling around. So any advice on how I should be doing this would be appreciated

2

I did try to change this up, maybe for the better. I put in 2 influxdb outputs and made one Temperature and one Humidity. I now seem to have 2 Measurements in the influxdb and I can select either measurement and see a list of temperatures and humidity correctly labeled. The flow is:

[{"id":"ab69ee34.8aa8d","type":"debug","z":"3b73e5ef.a7ba8a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":490,"y":200,"wires":[]}]
3

I think you had it right the first time. A Measurement in Influx is similar to a table in a conventional database. If you are acquiring two values at the same instant (temp and hum in this case) then the two should go together into one Measurement. I suspect this is what you did the first time, so you had a Measurement called Temperature, which you probably defined in the influx node, and in there you had a number of rows each with a timestamp, temp and hum in it. If you had done
select * from Temperature limit 2
you would likely have seen the headings at the top showing the column names. So probably the only mistake was to call the Measurement Temperature instead of something more meaningful.

4

Thanks for answering. I wonder if both are right. The data before I adding the Humidity variable to the influxdb, displays the same as when only temperature was a variable. The title at the top of the columns is the same. I would have thought that the method of having 2 influxdb outputs would not have had the same time code, but it's the same. I'm going to play with the first method some and use better measurement names . Seems to be a better DB way of doing things.

5

Sorry, I can't understand most of that statement.
There is nothing positively wrong with sending the two values to separate Measurements (in which case the default field name is value as you discovered), but if you are acquiring them at the same time the database operation is more efficient if you put them both in the same measurement.

As a slight aside, if you are going to have temperature and humidity values from other locations (other rooms for example) then you should put them all in one Measurement, all with field names temperature and humidity, with a tag (possibly called room to identify the location. Then the select statements would read something like
select * from environment where room = "bedroom"

6

thanks, good advice

7

I do have an additional question. I made the changes and now I see:

> select * from environmential limit 3
name: environmential
time                Humidity Location Temperature
----                -------- -------- -----------
1609974245581971052 45.4     RV       59
1609974305728266776 45.6     RV       59
1609974365868077181 45.6     RV       59
>

But I tried a select by location like you suggested and I can't get it to work.
This select yields nothing:

select * from environmential where Location = "RV"
>
8

It was the double quotes

select * from environmential where Location = 'RV' limit 3
name: environmential
time                Humidity Location Temperature
----                -------- -------- -----------
1609974245581971052 45.4     RV       59
1609974305728266776 45.6     RV       59
1609974365868077181 45.6     RV       59
9

Can I just check that you have specified that Location is a tag, not a field. It is important as Influx handles them differently. Tags are indexed so that they can be used in a where clause efficiently. In the help text for the influx out node it tells you how to format the data for fields and tags by passing an array of two objects in msg.payload. The first is the set of fields and the second the tags. So for your situation msg.payload should contain something like

[
  {Temperature: 25, Humidity: 50},
  {Location: 'RV'}
]
10

I see what you mean. Here is the raw data:

 msg.payload : Object

{ Location: "RV", Temperature: 52.3, Humidity: 53.2 }

Let me work with that. This is just at test run and once I get it all working I'll fix the formatting as you suggest.

Thanks.

11

With the data like that Location is a field which is not indexed, so when you use it in a WHERE clause it will have to read every record in the db and test that value in code. With an indexed field it just has to look up 'RV' in the index and it will know that it needs records 1,2,3,5,35, etc.
You won't notice the difference with a small set of data but once you get up to a few thousands or even millions of records then you certainly will.

12

I'm going to need some help with the formatting. I now have the sender publish this mesage:

[{"Temperature": 52.1, "Humidity": 53.0},{Location: 'RV'}]

The MQTT in can't deal with that format gives a parsing error. If I change it from a parses JSON to string, then influxdb out can't seem to handle it.

1610017802383052824 [{"Temperature": 52.1, "Humidity": 52.9},{Location: 'RV'}]
1610017862534485962 [{"Temperature": 52.1, "Humidity": 53.0},{Location: 'RV'}]
1610017922630854092 [{"Temperature": 52.1, "Humidity": 52.9},{Location: 'RV'}]

13

What has it got to do with MQTT? that is what you need to send to the influx node.

14

MQTT is how I'm getting the data from the remote raspberry pi 3 over wifi to the mosquitto broker and node-red servers, and the influxdb

If I have MQTT just receive it as a string unmodified it gets passed on to the influxdb but it doesn't seem to get added to the database correctly. When I just send over the string to influxdb the select statement produces this output:

1610018624333263482                               {"Temperature": 52.1, "Humidity": 52.9},{Location: 'RV'}
1610018744636223334                               {"Temperature": 52.0, "Humidity": 53.0},{Location: 'RV'}
15

It would be more usual to do the message adjustment for influx in the pi and send it over mqtt as you were before. However if you want to do that show us in a debug node what you are sending to mqtt and what you get out at the other end.

16

I'd tried an experiment. I create a node-red inject object to sent a string when clicked. This is the string:

[{"Temperature": 99.1, "Humidity": 89.0},{Location: 'RV'}]

If I look at the database from console with with > select * from environmental I see this:

1610021787158126446                               [{"Temperature": 99.1, "Humidity": 89.0},{Location: 'RV'}]
1610021810351416884                               [{"Temperature": 99.1, "Humidity": 89.0},{Location: 'RV'}]
>
17

That is because you are passing a string to the influx node not an array. So it just adds the string as the field called value to the database.

18

As to your other question. I have the MQTT in node set with topic environmentialTopic with output "a parsed JSON Object. That is connected to both the influxdb out node and the debug node. The current debug from where the data gets to the DB shows this.

1/7/2021, 7:24:17 AMnode: ab69ee34.8aa8d
environmentialTopic : msg.payload : Object
object
Location: "RV"
Temperature: 51.7
Humidity: 53
1/7/2021, 7:25:18 AMnode: ab69ee34.8aa8d
environmentialTopic : msg.payload : Object
{ Location: "RV", Temperature: 51.7, Humidity: 53
19

If you want to pass the array across via mqtt do it like this

[{"id":"6271c81c.bb635","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":100,"y":3980,"wires":[["b4f090e3.744f88"]]},{"id":"b4f090e3.744f88","type":"function","z":"bdd7be38.d3b55","name":"","func":"msg.payload = [{\"Temperature\": 52.1, \"Humidity\": 53.0},{Location: 'RV'}]\nmsg.topic = \"test\"\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":270,"y":3980,"wires":[["424fea8b.e2252c"]]},{"id":"424fea8b.e2252c","type":"mqtt out","z":"bdd7be38.d3b55","name":"","topic":"","qos":"0","retain":"false","broker":"10e78a89.5b4fd5","x":430,"y":3980,"wires":[]},{"id":"396bccbc.0f042c","type":"mqtt in","z":"bdd7be38.d3b55","name":"","topic":"test","qos":"0","datatype":"json","broker":"10e78a89.5b4fd5","x":80,"y":4060,"wires":[["22c41098.a1b7e"]]},{"id":"22c41098.a1b7e","type":"debug","z":"bdd7be38.d3b55","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":270,"y":4060,"wires":[]},{"id":"10e78a89.5b4fd5","type":"mqtt-broker","name":"","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""}]
20

Sorry, come to this rather late. However, I provided a detailed view on how to record home environment data in InfluxDB in this previous thread:

Need more detailed information on influxdb - General - Node-RED Forum

Including an explanation of the terminology and examples of measurements, tags and fields. Also examples of continuous queries and retention policies in order to keep the data sizes under control.

1 Like