Write many values for the same time with nodered in influxdb

General
1

With the standard influx node, if I pass

        for (let i=0; i<sensors.length; i++)
            timea.push(new Date().getTime()+i); // microsecsec from 00:00 1/1/1970
        
        influxa.push({time: timea[0], field: sensors[7].value, tag: sensors[7].name });
        influxa.push({time: timea[1], field: sensors[5].value, tag: sensors[5].name });

Where timea and influxa are empty arrays when the node starts.

The influx node complains that

error
"Error: A 400 Bad Request error occurred: {"error":"partial write: invalid tag key: input tag \"time\" on measurement \"mainlog\" is invalid dropped=1"}↵"

If I remove the first part:

        influxa.push({field: sensors[7].value, tag: sensors[7].name });
        influxa.push({field: sensors[5].value, tag: sensors[5].name });

In the measurements the same time has two columns for the values and two columns for tags. 2 is for trial because there would 16! I would end up with a row with 33 entries where just 3 has information. I got that and it is the reason, as workaround, that I simulate that every entry is a microsecond apart from the precedent.

But I can't write in time column: how can I store the values in one DB call?

Thanks

2

If you read the help for the influxdb out node you will see that if you give it an array it expects the first element to be a set of fields and the second to be a set of tags, so it has written a record with two fields and two tags, rather than writing two records. If you want to write multiple records in one go you need the influxdb batch node, which is intended for exactly that purpose.

3

Looking back at your first post, why are you writing them with different times?

The simplest way to solve the problem might be something like

for (let i=0; i<sensors.length; i++) {
  node.send({payload: [{field: sensors[i].value}, {tag: sensors[i].name}]})
}

and pass that to the influxdb out node which will record them with the current time as timestamp.
It would be more conventional to call your field value and the tag sensor or something similar. Then a query to fetch the values would be something like
select value from measurement where sensor = 'inlet_temperature'
or whatever the sensor names are.

However, if you usually have all the sensor values available at the same time then it would be more efficient to not have the tag at all and use the sensor name as the field name, and write all the sensor values in one record, then the query would be
select inlet_temperature from measurement

4

That's the reason I haven't replied to your previous post, yet: I end up to the same conclusion and I was modifying it to achieve the result the name of the columns are the sensor name.

(Upon the value of the sensors I open and close some heaters or valves: is it more wise use tags or have a different measurement?)

5

Tags are for identifying the source of the data, as you originally were planning. Don't use tags for data values. The reason is that tags are indexed in the database so that you can search on them, but indexing adds overhead in other ways, so don't use tags for data. Your original idea to use the sensor name as a tag was perfectly valid, and if the sensor values were not all available at the same time that would be the way to do it, but any time you have a set of data values all available at the same time it is more efficient to put them in multiple fields in one record.

6

This is the final code that I share:

        for (let i=0; i<sensors.length; i++)
            timea.push(nanoNow +i); // microsec from 00:00 1/1/1970
        
        influxa.push( { measurement: "mainlog", fields: { [sensors[4].name]: Number(sensors[4].value) }, tags: { pos: sensors[4].pos }, timestamp: timea[0] });
        influxa.push( { measurement: "mainlog", fields: { [sensors[5].name]: Number(sensors[5].value) }, tags: { pos: sensors[5].pos }, timestamp: timea[0] });
        influxa.push( { measurement: "mainlog", fields: { [sensors[6].name]: Number(sensors[6].value) }, tags: { pos: sensors[6].pos }, timestamp: timea[0] });
        influxa.push( { measurement: "mainlog", fields: { [sensors[7].name]: Number(sensors[7].value) }, tags: { pos: sensors[7].pos }, timestamp: timea[0] });
        influxa.push( { measurement: "mainlog", fields: { [sensors[9].name]: Number(sensors[9].value) }, tags: { pos: sensors[9].pos }, timestamp: timea[0] });
        influxa.push( { measurement: "mainlog", fields: { [sensors[10].name]: Number(sensors[10].value) }, tags: { pos: sensors[10].pos }, timestamp: timea[0] });
        influxa.push( { measurement: "mainlog", fields: { [sensors[11].name]: Number(sensors[11].value) }, tags: { pos: sensors[11].pos }, timestamp: timea[0] });
        influxa.push( { measurement: "mainlog", fields: { [sensors[12].name]: Number(sensors[12].value) }, tags: { pos: sensors[12].pos }, timestamp: timea[0] });
        influxa.push( { measurement: "mainlog", fields: { [sensors[16].name]: Number(sensors[16].value) }, tags: { pos: sensors[16].pos }, timestamp: timea[0] });

The array influxa is passed to the batch influxdb node that writes in influxdb in the measurement mainlog rows like:

influx  -precision rfc3339 -database 'vrvspm' -host 'localhost'
Connected to http://localhost:8086 version 1.6.4
InfluxDB shell version: 1.6.4
> select * from mainlog limit 10
name: mainlog
time                    pos sens10 sens11 sens12 sens16 sens4  sens5  sens6 sens7 sens9
----                    --- ------ ------ ------ ------ -----  -----  ----- ----- -----
2021-04-27T22:24:16.07Z 10  21.875                                                
2021-04-27T22:24:16.07Z 11         22.015                                         
2021-04-27T22:24:16.07Z 12                21.846                                  
2021-04-27T22:24:16.07Z 16                       21.937                           
2021-04-27T22:24:16.07Z 4                               21.937                    
2021-04-27T22:24:16.07Z 5                                      21.937             
2021-04-27T22:24:16.07Z 6                                             22.15       
2021-04-27T22:24:16.07Z 7                                                   22.15 
2021-04-27T22:24:16.07Z 9                                                         21.812
2021-04-27T22:24:20.07Z 10  21.875                                                
>

(I kept the tag "pos", because some pos correspond to "heater" or "valve", so it would be easy to write a statement like SELECT * FROM mainlog WHERE tag="heater" .)

Thanks

7

That isn't what you want, you want them all in one record with one timestamp. As you have it the database is mostly full of null values. All that data should be in one row. Do you need the pos tag when you already have the sensor name? What are you going to use it for? If you don't need it then All you need is something like

const sensorsToUse = [4,5,6,7,9,10,11,12,16]
msg.payload = {}
for (let i=0; i<sensorsToUse.length; i++) {
  const sensorIndex = sensorsToUse[i]
  msg.payload[sensors[sensorIndex].name] = sensors[sensorIndex].value
}
msg.measurement = "mainlog"
return msg;

Then feed that direct to an influx out node (not batch). I haven't tested that, feed it into a debug node first to make sure it looks right.

8

Now I have a problem: my RB decided to don't let me login anymore. I'll solve.
In the meantime I can't past the code, so you have to trust :slight_smile:

Without anything the new records was always in 1970.

I wrote nownsec = <something_in_JS_which_yields_secs_from_1970> /1000 /1000 to get nanoseconds instead of seconds, and ther i add msg.timestamp = nownsec between msg.measurement and the return.

9

Why? You want influx to use the current time, which it will do automatically. You don't need to provide a timestamp

10

The reason your previous code didn't work was because you have divided seconds by 10^6 instead of multiplying by 10^9 to get nanoseconds from seconds, so your figure will be 10^15 out, which is quite a lot. But in fact the best way to do it would be to use
msg.timestamp = new Date().getTime()
which is in milliseconds and then go into the advanced settings in the node and set the precision to milliseconds.

I see that the help text for the node doesn't even mention msg.timestamp so I don't know whether you can specify a timestamp with the influx out node. I use the batch node if I want to specify a timestamp.

11

Ah that's the reason influx was creating points in the 1970! Thanks!

OT
What a terrible day: as I said I lost the contacts with the RB so I started removing a component, then two, then another, then ...finally I finished to have a naked Raspberry's board and it still wasn't booting: I declared it death/kaputt/morta/bricked

12

Sorry for the long absence, I'm back. I had received a Adafruit MAX31865 broken (to get temp from a pt100 probe) so I waited for it, when it finally was here I did something that burned my Raspberry, so I waited for a new one, finally I assembled everything again, and now all is working.

Thanks to all and to Colin

1 Like
13

Glad you got it working after the trials and tribulations.

14

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