Help needed with MQTT to influx

[onbekende]

Hi all,

After lurking a while in the forum, i've been trying to tackle an issue, but i'm just a hobbyist.
Hopefully someone can help me figure this out.
I have the following question:

I'm receiving all data from a sensor in my MQTT.
When i try to parse it to InfluxDB, somehow my splitting isn't going as i would like to . How can i split or parse it, to get in the Influx properly?

MQTT output:

{"topic":"sensors/networkStatus/mysensor","payload":"{\"event\":{\"eventId\":\"bqheousrto76o8dikp3g\",\"targetName\":\"projects/location/devices/mysensor\",\"eventType\":\"networkStatus\",\"data\":{\"networkStatus\":{\"signalStrength\":78,\"updateTime\":\"2020-04-24T13:41:15.670000Z\",\"connector\":[{\"id\":\"bjr27rkc0000n66nmetg\",\"signalStrength\":78}],\"transmissionMode\":\"LOW_POWER_STANDARD_MODE\"}},\"timestamp\":\"2020-04-24T13:41:15.670000Z\"},\"labels\":{}}","qos":0,"retain":false,"_topic":"sensors/networkStatus/mysensor","_msgid":"607a8f7d.3d0eb"}

After my attempt to split the msg, to make it ready for InfluxDB

{"topic":"mysensor","payload":[{"measurement":"mysensor","fields":{"event":{"eventId":"bqheousrto76o8dikp3g","targetName":"projects/location/devices/mysensor","eventType":"networkStatus","data":{"networkStatus":{"signalStrength":78,"updateTime":"2020-04-24T13:41:15.670000Z","connector":[{"id":"bjr27rkc0000n66nmetg","signalStrength":78}],"transmissionMode":"LOW_POWER_STANDARD_MODE"}},"timestamp":"2020-04-24T13:41:15.670000Z"},"labels":{}},"timestamp":"2020-04-24T13:41:16.062Z"}],"qos":0,"retain":false,"_topic":"sensors/networkStatus/mysensor","_msgid":"607a8f7d.3d0eb"}

The InfluixDB notices :
"No measurement specified"

Thanks in advance for any help provided.

[bakman2]

It looks like you are trying to insert it an array, instead try it an object. (ie. remove the [ ])

[Colin]

Also I think you should start by setting the MQTT node output to Parsed JSON, then you will get a javascript object which will get you much nearer in one go.
Are you using the Influx Out node or the Influx Batch node? If you want the values recorded with the current time as the timestamp then use the basic Out node, which is much simpler to arrange the data for.
However, the error is saying that you have not specified which Measurement in the database you want the data to go to. You have to either specify this in the influx node (if it is fixed) or in the message if it is variable.

[borpin]

I wrote 2 simple nodes to test writing data to InfluxDB.

• You can do time on both batch and Out nodes.
• Unless you change the default precision (via the node) it expects time in nano seconds.

These show the data structure required.

[{"id":"faf97557.84c5e8","type":"function","z":"e9fdb3ba.b608c","name":"For InfluxDB Batch","func":"var field1Data = 10;\nvar field2Data = 12;\nvar field3Data = 15;\nvar tag1Data = \"what is this?\";\nvar tag2Data = \"I'm a tag!\";\n\nvar payload= [{\n        measurement: \"testtable\",\n        fields: {\n            field1: field1Data,\n            field2: field2Data,\n            field3: field3Data\n        },\n        tags: {\n            tag1: tag1Data,\n            tag2: tag2Data\n        },\n        timestamp: 1585500000 * 1000 *1000 * 1000\n//        timestamp: new Date()\n        }];\nnode.warn(payload);\nmsg.payload = payload;\nreturn msg;","outputs":1,"noerr":0,"x":350,"y":140,"wires":[["2dd8c5a9.a16d5a","bbed54fe.821178"]]},{"id":"60486926.e8b088","type":"function","z":"e9fdb3ba.b608c","name":"For InfluxDB Out","func":"var field1Data = 10;\nvar field2Data = 12;\nvar field3Data = 15;\nvar tag1Data = \"what is this?\";\nvar tag2Data = \"Agile\";\n\nvar payload= \n    [\n        [\n            {\n            data: field1Data,\n            time: new Date(\"2020-03-31T20:00:01Z\").getTime() *1000 *1000\n            },\n            {\n            tag1: tag1Data\n            }\n        ],\n        [\n            {\n            data: field2Data,\n            time: new Date(\"2020-03-31T20:30:01Z\").getTime() *1000 *1000\n            },\n            {\n            tag1: tag1Data\n            }\n        ]\n    ];\n\nnode.warn(payload);\n\nmsg.measurement = \"testtable2\"\n\nmsg.payload = payload;\n\nreturn msg;","outputs":1,"noerr":0,"x":350,"y":240,"wires":[["b308d9f4.71bb08"]]}]

[Colin]

What happens if you put different timestamps on the fields and tags when using basic Out node?
If you don't need tags then there is the simpler format of course.

[borpin]

@Colin

What happens if you put different timestamps on the fields and tags

I don't think you can. I think this is the key difference between the 2. Batch is all done on a single timestamp, Out allows different timestamps.

@Colin

If you don't need tags then there is the simpler format of course.

It took me quite a while to work out the right format.

I remember now, I was looking at the input format, when it was the output format that you need when putting data into InfluxDB.

JSON output data format | Telegraf Documentation

[edit]

IIRC I eventually RTFM

node-red-contrib-influxdb

[Colin]

@borpin

@Colin

What happens if you put different timestamps on the fields and tags

I don't think you can

Your function for the Out node shows a timestamp in the fields object and another in the tags object, I don't think those are documented in the readme. So it is possible to put separate timestamps for each.

@borpin

Batch is all done on a single timestamp, Out allows different timestamps.

Wrong way round, Batch allows a whole series of points to be written with different timestamps, so you can collect a batch of data and submit them all at once. The Out node only allows a single record, not multiple.

[borpin]

Actually I think we are both wrong

(I've rewritten this multiple times)
Depending on how you structure the data, both nodes allow multiple data sets (data + time) passed at once. One is an array of objects and the other an array of arrays.

Now I remember why I got so confused (and wrote the nodes to test)!

@Colin

Your function for the Out node shows a timestamp in the fields object and another in the tags object

No, it is an array of arrays.

[Colin]

@borpin

Actually I think we are both wrong

That is correct

@borpin

Depending on how you structure the data, both nodes allow multiple data sets (data + time) passed at once. One is an array of objects and the other an array of arrays

Are you sure that you can provide a timestamp when you pass an array of points to the Out node? The readme makes no mention of that, though I have not tried. But if you can then there is virtually no difference in capability between it and the Batch node. The only remaining difference that I can see is that the batch node allows different points to go to different measurements, and if you are correct in saying that even with the non-batch node you can supply a timestamp then I would not be surprised if you can, in fact, provide a measurement with each point too.

I writing the above I notice that in your flow you are passing across the time by writing to a field called time, though in the readme for the batch node it says " To set the time for the point, supply a timestamp property". Is your understanding that those two techniques achieve the same thing?

[borpin]

@Colin

Are you sure that you can provide a timestamp when you pass an array of points to the Out node?

Yes as per the docs. node-red-contrib-influxdb (node) - Node-RED

@Colin

But if you can then there is virtually no difference in capability between it and the Batch node.

Yes

@Colin

The only remaining difference that I can see is that the batch node allows different points to go to different measurements

This was why I settled on that method

@Colin

I would not be surprised if you can, in fact, provide a measurement with each point too.

Not tried it, but didn't find anything saying you could. [edit] yes you can - msg.measurement

@Colin

I writing the above I notice that in your flow you are passing across the time by writing to a field called time , though in the readme for the batch node it says " To set the time for the point, supply a timestamp property"

Interesting. You are correct; I wonder if that is a change that has been made. time does work as I use it in a node I have created for Octopus but is obviously not correct. I'll update and test - thanks for that.

[Colin]

@borpin

Yes as per the docs. node-red-contrib-influxdb (node) - Node-RED

Oh yes, I was relying on the info tab, which does not mention that. I should have looked at the full readme. I think maybe if the info tab does not supply full details it should say so and refer to the full docs. Thanks for pointing that out.

@borpin

@Colin

I would not be surprised if you can, in fact, provide a measurement with each point too.

Not tried it, but didn't find anything saying you could. [edit] yes you can - msg.measurement

That allows specification of measurement for all points, the batch node requires the measurement to be specified for each point, so they need not all go to the same measurement. As far as I can see that is the only fundamental difference between the two.

[onbekende]

Thanks a lot, everyone. I'm gonna work my way through all ideas, test it and report back in.

[borpin]

@Colin following up on your observation re time v timestamp, if I change my octopus node so the data sent has timestamp, it doesn't work!

The author mike has not been seen on the forum for a while.

I'll start a new conversation as we are way OT here.

[edit]

The readme on GitHub (and so also the NR docs) says that for an Out node, the format should be time and for a Batch node it should be timestamp.

[onbekende]

@borpin Am i correct to understand the "msg.measurement" as per your example is the table of InfluxDB ?

[borpin]

@onbekende

the "msg.measurement" as per your example is the table of InfluxDB

Yes - InfluxDB speak.

[Colin]

In fact I suspect that most use cases are satisfied by the simpler formats that can be specified with the basic Out node. In particular if the requirement is to add a record with the current time taken as the timestamp then, if not using tags, use the most simple format with the payload in the form

{
  field1: value1,
  field2: value2,
  field3: value3
}

which writes the values to the fields specified with the current time as the timestamp.
If tags are used then use the format

[
  {
    field1: value1,
    field2: value2,
    field3: value3
  },
  {    
    tag1: tag1_value,
    tag2: tag2_value
  }
]

where the first element of the array has the values for the fields and the second has the values for the tags. Again it is written using the current time.
I suspect those two use formats cater for 90% of cases.
In both cases the measurement can be specified in the node config or in msg.measurement if it is not fixed.

[Colin]

@onbekende if you don't know about measurements then ideally before you start you need to read up a bit more on influx. It is worth while taking some time to understand measurements, fields, tags, retention policies and continuous queries before starting to store real data that you want to keep for some time. When I started with great enthusiasm I rushed ahead without fully understanding the concepts and the result was that after a while I realised I was not storing the data in the most useful way so I had to spend some time restructuring the database.

[onbekende]

@Colin Yes, i think i'm at this point. I can find my way in regular DB's, like MySQL/MariaDB, MSSQL, but this is yet another form of DB. I haven't quite figured out the correct way to store the data.
The million dollar question is. Where should i start to learn a bit more. I learn by doing, so i usually build the examples in order to understand.

Any tips on a good reference?

[Colin]

@onbekende

Any tips on a good reference?

I haven't found anything outstanding unfortunately. The influxdb docs themselves are not bad, make sure you look at the 1.8 docs not the version 2 ones. Version 2 is a bit different I gather but is not here yet, and 1.8 will still be available for a good time I think.
Then google for tutorials.

[onbekende]

Thanks. Your input is much appreciated!