Organizing and preparing data for InfluxDB

I have what must be just about the most basic and common IOT objective and it is almost embarassing to have to ask for help, but I am new to this. I have been reading a lot, but have not found answers to my problem.
I want to collect data from a number of sensors in different locations in and around my home.
I send the data using mqtt to an mqtt broker.
I want to collect the data in an InfluxDB database for subsequent processing and display (e.g. using Grafana).
I am trying to use node-red to collect the data from the mqtt broker and to prepare it for submission to InfluxDB.


A lot works, but I am not sure about the best way to organize things.

At the moment I output the sensor readings with separate mqtt topic names which reflect the intended database (bucket?) name, location, sensor type and reading name:
e.g. Environment/livingroom/picowbme280/temperature
or: Environment/livingroom/picowbme280/#
I can read these without problem from the node-red mqtt input node.
If I pass them unchanged (as numbers) to the InfluxDB out node I can see (and display) them in the InfluxDB GUI, but I cannot filter them (e.g. by location or sensor type).
I can also split the data streams from each sensor by using a switch based on the mqtt topic content.

My understanding (am I correct?) is that I need to pass the bucket and tags to InfluxDB as the message payload in the following format:
"Environment, location = livingroom, devicetype = picowbme280"
but I don't know what to do with the sensor readings
I am trying to edit the message payload using a change node, but while I can create what look (in the debug output) like tag pairs InfuxDB does not seem to recognize them.
debug_8
and the sensor reading has disappeared.

My questions are:
What is the best overall architecture?
Do I need to process each topic separately?
Do I need a separate InfluxDB out node for each topic or just one for all "Environment" data?
How do I do the editing in a change node (or function?)?

Welcome to the forum!

A quick answer explaining InfluxDB structure:

Thank you. I had already read that, but it does not seem to answer my question about the best organization of my data nor how to process it in node-red.
What does the node-red "influxdb out" node need as input?
Can (should?) I put all the readings in one bucket?
As a new forum member I was not allowed more than two attachments in my previous post.
Here are my node-red flow (for one sensor with three readings) and the change node properties.
What should I change?

As described in the help text for the influxdb node, you need to costruct a payload with is an array of two elements, the first being the fields and the second being the tags. So the payload would look something like

[
  {
    temperature: 23,
  },
  {
    location: "livingroom",
    type: "picowbme280"
  }
]

If you have not already done so then I recommend watching this playlist: Node-RED Essentials. The videos are done by the developers of node-red. They're nice & short and to the point. You will understand a whole lot more in about 1 hour. A small investment for a lot of gain. In particular take note of the section Working with Messages, which should help with reorganising the data into the format required.

1 Like

Also, I would recommend you start with InfluxDB 1.x, not 2.x which has a steep learning curve for insert and query structures.

Thanks. I had already watched most of the "essentials" videos but there is a lot to take in and I doubtless missed a lot. I will watch them again and make some effort to learn JS basics.

  • I still don't understand how I introduce the measurement value (23 in your example). I obviously do not have it available as a fixed number. It is part of the incoming message payload.
  • I still have not understood how to handle the outputs of the different sensors.
  • Should I treat each measurement series separately (as a separate chain in the node-red flow)?
  • Can I simply then add the output of each chain to a single Influxdb node in node-red?
  • The debug node output shows the mqtt topic name. Is this dropped when data is passed to InfluxDB?

Ah! I just assumed that I should start with the most up-to-date version, so I have been working with 2.6.
I will have to think about that, but thanks for the suggestion.

I am in two minds about what to recommend new Influxdb users. Whilst those with any experience of SQL will find the old version easier, it does seem that V2 has reached some level of stability, so perhaps it would be better to go with that from the start.

When it comes to your data, if everything you need for the tags is in the topic and the value is in the payload, then it is possible to split the topic up and load the bits into the payload in the format I posted earlier. Personally I would do that in a Function node. I haven't got time to do that at the moment, but you need to look at the javascript split() function to split the topic at the / characters, which will give you an array. You can then move those into the relevant parts of the message for influx. It should not be necessary to divide the flow up into different paths for the different topics.

OK thanks. That sounds promising. I'll have a look at that tomorrow when I'm feeling fresher and can get my mind round JS. It's bedtime here now.
I do appreciate the quick responses I am getting.

Hallo Colin,
I need more than two images to reply to your last post, which helped me a lot. How can I best proceed?
Bob

You don't need any images to post code.

What do you need to show?

Flows, Code (from node editors) and debug node messages.

You can copy/paste code here. In order to make code readable and usable it is necessary to surround your code with three backticks (also known as a left quote or backquote ```)

``` 
   code goes here 
```

See this post for more details - How to share code or flow json

You can also paste debug node output by using the copy button in the debug pane.

There’s a great page in the docs (Working with messages : Node-RED) that will explain how to use the debug panel to find the right path to any data item.

Pay particular attention to the part about the buttons that appear under your mouse pointer when you over hover a debug message property in the sidebar.

BX00Cy7yHi

Hallo Colin,
I have been working hard to understand more about JS and Node-red functions and have made some progress.
I first tried a simple approach, with a single function to split the message on the basis of the topics.

var reading = msg.payload;  // read data value
var items = msg.topic.split("/");  // split message topic into list of strings
var newMsg = {};
newMsg.payload = [{items[3]: reading}, {location: items[1], sensor: items[2]}];
return newMsg;

This works well for the tags but not for the sensor readings. It gives an error. I checked that "items[3]" returns the string expected (e.g. "at").
If I replace it with a fixed placeholder text, the function is OK, and the payload structure looks ok, but all three sensor values then get the same name of course.
I also tried a flow in which the data streams are first split on the basis of their topics.

This allows a fixed string to identify each sensor type.

var reading = parseFloat(msg.payload);
var items = msg.topic.split("/");
var atMsg = {};
atMsg.payload = [{"at": reading}, {location: items[1], sensor: items[2]}];
return atMsg;

This seems to work OK, but I find the first approach more elegant.

I presume there is a problem with my syntax. Can you suggest how it should be altered?
Any help would be much appreciated.

When using variables as property keys you need to use [ ] notation
eg.

var reading = msg.payload;  // read data value
var items = msg.topic.split("/");  // split message topic into list of strings
var newMsg = {};
newMsg.payload = [{[items[3]]: reading}, {location: items[1], sensor: items[2]}];
return newMsg;

Thank you. That solved it for me. I can now do everything in a single function which feeds to a single InfluxDB node.

var reading = msg.payload;  // read data value
var items = msg.topic.split("/");  // split message topic into list of strings
msg.payload = [{[items[3]]: reading}, {location: items[1], sensor: items[2]}];
return msg;

I can now select each measurement as a graph in InfluxDB, selected by location and sensor type. Now I need to immerse myself in the details of InfluxDB (CLI, Flux etc).

1 Like

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.