Write group of data to Influxdb

General
1

Is there a way to write an array of data set to Influxdb efficiently, but give each data set a different time stamp? For example, I have the following array of size 5, and I want to write them to influxdb and give them different time stamp, say 50 microsecond difference for each data set.

Thanks to Colin & Kuema, the following payload works. I am posting the working payload here, so that it is helpful to others too:

msg.payload=[
    {measurement:"acceleration",
    fields:{accelerometer:"sensor1",x_axis:0.1,y_axis:0.1,z_axis:1.02},
    timestamp: 1613675600773001
    },
    {measurement:"acceleration",
    fields:{accelerometer:"sensor1",x_axis:0.01,y_axis:0.02,z_axis:1.3},
    timestamp: 1613675600773002
    },
    {measurement:"acceleration",
    fields:{accelerometer:"sensor1",x_axis:0.02,y_axis:0.03,z_axis:1.1},
    timestamp: 1613675600773003
    },
    {measurement:"acceleration",
    fields:{accelerometer:"sensor1",x_axis:0.02,y_axis:0.15,z_axis:1.5},
    timestamp: 1613675600773004
    },
    {measurement:"acceleration",
    fields:{accelerometer:"sensor1",x_axis:0.03,y_axis:0.11,z_axis:1.2},
    timestamp: 1613675600773005
    },
    {measurement:"acceleration",
    fields:{accelerometer:"sensor1",x_axis:0.12,y_axis:0.05,z_axis:0.9},
    timestamp: 1613675600773006
     }
    ]
    
return msg;
2

The Influxdb Batch node will do that for you. Whether node-red will cope with that data throughput depends entirely on the hardware you are running on.

3

Yes, you can use the Influx batch node.

It expects an array of points, like

{
    timestamp: 1234567,
    measurement: 'my_measurement',
    tags: {
        tag1: 'tag1'
    },
    fields: {
        value: 123
    }
}
4

I tried the batch node but can only write one data set, instead of five data sets. The time stamp written is also not correct. Here is the message that I sent to the batch node:

msg.payload=[
    {measurement:"acceleration",
    fields:{accelerometer:"sensor1",x_axis:0.1,y_axis:0.1,z_axis:1.02},
    timestamp: 1603386709057360836  
    },
    {measurement:"acceleration",
    fields:{accelerometer:"sensor1",x_axis:0.01,y_axis:0.02,z_axis:1.3},
    timestamp: 1603386709057360846  
    },
    {measurement:"acceleration",
    fields:{accelerometer:"sensor1",x_axis:0.02,y_axis:0.03,z_axis:1.1},
    timestamp: 1603386709057360856  
    },
    {measurement:"acceleration",
    fields:{accelerometer:"sensor1",x_axis:0.02,y_axis:0.15,z_axis:1.5},
    timestamp: 1603386709057360866  
    },
    {measurement:"acceleration",
    fields:{accelerometer:"sensor1",x_axis:0.03,y_axis:0.11,z_axis:1.2},
    timestamp: 1603386709057360876  
    },
    {measurement:"acceleration",
    fields:{accelerometer:"sensor1",x_axis:0.12,y_axis:0.05,z_axis:0.9},
    timestamp: 1603386709057360886  
     }
    ]
    
return msg;

What could be the problem? :joy:

5

What do you mean by dataset? The batch expects a flat array of data points, you can just put all your data in there.

As for the timestamp... you need to use the correct precision. I think the default for the Influx nodes are milliseconds, but you can change that in the Batch node after ticking "Advanced Query Options". :face_with_monocle:

But I suspect you will run into precision issues using nanoseconds because JS numbers are always floats and only safe up to 2^53 for integers.

And a recommendation: I think the accelerometer value is better put in as a tag, rather than a field.

1 Like
6

For one dataset, I meant one object in the array:
{measurement:"acceleration",
fields:{accelerometer:"sensor1",x_axis:0.02,y_axis:0.15,z_axis:1.5},
timestamp: 1603386709057360866
}
If you look at my msg.payload, then you see that there are five objects. There is only the last one object that is accepted by the batch node. I don't think time resolution is the cause, since I tried different cases and the result is the same: only one data set is accepted.

7

I am posting the test flow here and it is easier to test.

[{"id":"ca28714f.d27e2","type":"inject","z":"40c74d59.210d24","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":390,"y":180,"wires":[["6e8041da.61711"]]},{"id":"6e8041da.61711","type":"function","z":"40c74d59.210d24","name":"Accelerator","func":"/*\nmsg.payload=[\n    {measurement:\"acceleration\",\n    fields:{accelerometer:\"sensor1\",x_axis:0.1,y_axis:0.1,z_axis:1.02},\n    timestamp: 1613670599914915652\n    },\n    {measurement:\"acceleration\",\n    fields:{accelerometer:\"sensor1\",x_axis:0.01,y_axis:0.02,z_axis:1.3},\n    timestamp: 1613670599914915752  \n    },\n    {measurement:\"acceleration\",\n    fields:{accelerometer:\"sensor1\",x_axis:0.02,y_axis:0.03,z_axis:1.1},\n    timestamp: 1613670599914915852  \n    },\n    {measurement:\"acceleration\",\n    fields:{accelerometer:\"sensor1\",x_axis:0.02,y_axis:0.15,z_axis:1.5},\n    timestamp: 1613670599914915952  \n    },\n    {measurement:\"acceleration\",\n    fields:{accelerometer:\"sensor1\",x_axis:0.03,y_axis:0.11,z_axis:1.2},\n    timestamp: 1613670599914911052  \n    },\n    {measurement:\"acceleration\",\n    fields:{accelerometer:\"sensor1\",x_axis:0.12,y_axis:0.05,z_axis:0.9},\n    timestamp: 1613670599914915552 \n     }\n    ]\n*/\nmsg.payload=[\n    {measurement:\"acceleration\",\n    fields:{accelerometer:\"sensor1\",x_axis:0.1,y_axis:0.1,z_axis:1.02},\n    \n    },\n    {measurement:\"acceleration\",\n    fields:{accelerometer:\"sensor1\",x_axis:0.01,y_axis:0.02,z_axis:1.3},\n    \n    },\n    {measurement:\"acceleration\",\n    fields:{accelerometer:\"sensor1\",x_axis:0.02,y_axis:0.03,z_axis:1.1},\n    \n    },\n    {measurement:\"acceleration\",\n    fields:{accelerometer:\"sensor1\",x_axis:0.02,y_axis:0.15,z_axis:1.5},\n    \n    },\n    {measurement:\"acceleration\",\n    fields:{accelerometer:\"sensor1\",x_axis:0.03,y_axis:0.11,z_axis:1.2},\n    \n    },\n    {measurement:\"acceleration\",\n    fields:{accelerometer:\"sensor1\",x_axis:0.12,y_axis:0.05,z_axis:0.9},\n    \n     }\n    ]\n\n    \nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":570,"y":180,"wires":[["16c6a0ff.5b694f","f18dbc70.1a563"]]},{"id":"16c6a0ff.5b694f","type":"debug","z":"40c74d59.210d24","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":970,"y":120,"wires":[]},{"id":"f18dbc70.1a563","type":"influxdb batch","z":"40c74d59.210d24","influxdb":"51f418fb.625fd8","precision":"n","retentionPolicy":"","name":"","x":930,"y":180,"wires":[]},{"id":"51f418fb.625fd8","type":"influxdb","hostname":"127.0.0.1","port":"8086","protocol":"http","database":"test","name":"","usetls":false,"tls":"f7f39f4e.896ae","influxdbVersion":"1.x","url":"http://localhost:8086","rejectUnauthorized":true},{"id":"f7f39f4e.896ae","type":"tls-config","name":"local-tls","cert":"","key":"","ca":"","certname":"","keyname":"","caname":"","verifyservercert":false}]
8

It looks like the batch node only accepts different measurements as input.
If measurements are the same, then the node only accepts the last measurement.

So this batch node won't work for my case :joy:

9

Not correct.
What units do you think your timestamps are in and how far apart are they? I suspect they have all ended up at the same timestamp due to being too close together.

1 Like
10

Well, can you try the following payload:

msg.payload=[
    {measurement:"acceleration1",
    fields:{x_axis:0.1,y_axis:0.1,z_axis:1.02},
    tags:{accelerometer:"sensor1"},
    timestamp: new Date()
    },
    {measurement:"acceleration1",
    fields:{x_axis:0.01,y_axis:0.02,z_axis:1.3},
    tags:{accelerometer:"sensor1"},
    timestamp: new Date()
    }
    ]
    
return msg;

Still one measurement written to the database.

11

I need to correct myself. After trying the following payload, I think multiple data set were written:

msg.payload=[
    {measurement:"acceleration1",
    fields:{x_axis:0.2,y_axis:0.1,z_axis:1.02},
    tags:{accelerometer:"sensor1"},
    timestamp: 1613675600769
    },
    {measurement:"acceleration1",
    fields:{x_axis:0.21,y_axis:0.02,z_axis:1.3},
    tags:{accelerometer:"sensor1"},
    timestamp: 1613675600770
    }
    ]
    
return msg;

Will report back when I test more :sweat_smile:

12

OK problem solved.

It might be a good idea to change the fields and use tags for the accelerometer for better performance, as Kuema suggested.

13

As a side note, the hardware is raspberry pi 3 running Node Red, Influxdb and MQTT. I will report back about its performance soon. :grin:

14

So it's working now?

Yes, I didn't explicitly mention that the timestamps need to be numeric. :sweat_smile:

15

Yes it is working now. Thank you very much! :grin:

2 Likes
16

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