Hello again! I am converting some sensor data from Ecowitt2MQTT to QuestDB in Node-Red using the Node.js ingestion client. I am wondering if the method I am using is best practice, as it seems restrictive when trying to send only valid data. I am using the function node in Node-Red.
First I load the client in the ‘Setup’ tab of the function node:
Module name: @questdb/node.js-client Import as: qdbClient
Then in the ‘On Message’ tab:
const Sender = qdbClient.Sender;
// create a sender using HTTP protocol
const sender = Sender.fromConfig(“http::addr=192.168.1.20:9000;username=;password=”);
// Get the last item in the topic
var parts = msg.topic.split(“/”);
var count = parts.length;
var location = “myPlace”;
var itm = parts[count - 1];
var pl = msg.payload;
var temp1 = pl[“temp1”];
var humidity1 = pl[“humidity1”];
if (temp1 == null || humidity1 == null) {
temp1 = NaN; // null for double
humidity1 = -2147483648; // null for long integer?
node.warn(“Area temp sensor is down.”);
}
// add rows to the buffer of the sender
await sender
.table(itm)
.symbol(“location”, location)
.intColumn(“runtime”, pl[“runtime”])
.floatColumn(“tempin”, pl[“tempin”])
.intColumn(“humidityin”, pl[“humidityin”])
.floatColumn(“baromrel”, pl[“baromrel”])
// Around 30 more key/values removed here for clarity
.floatColumn(“temp1”, temp1)
.intColumn(“humidity1”, humidity1)
.at(Date.now(), “ms”)
// flush the buffer of the sender, sending the data to QuestDB
await sender.flush();
// close the connection after all rows ingested. Unflushed data will be lost
await sender.close();
return null;
I think I would prefer not to send a column if the value is missing, rather than sending null. Above is my work-around, which isn’t working yet as the long column humidity1 is being set to -2147483648 (when empty) rather than null. I got that value from one of the other questions on the forum.
How can I fix this and is there a better way overall?
Cheers and thanks - Andrew
Hi @ajay100 ,
Simply omit the column from the dataaset. There is no need to populate the nulls explicitly.
For example:
await sender
.table(itm)
.symbol(“location”, location)
.intColumn(“runtime”, pl[“runtime”])
.floatColumn(“tempin”, pl[“tempin”])
.intColumn(“humidityin”, pl[“humidityin”])
.floatColumn(“baromrel”, pl[“baromrel”])
// Around 30 more key/values removed here for clarity
.floatColumn(“temp1”, temp1)
.intColumn(“humidity1”, humidity1)
.at(Date.now(), “ms”)
Pretend tempin, humidityin, baromrel are null, then you just do this:
await sender
.table(itm)
.symbol(“location”, location)
.intColumn(“runtime”, pl[“runtime”])
// Around 30 more key/values removed here for clarity
.floatColumn(“temp1”, temp1)
.intColumn(“humidity1”, humidity1)
.at(Date.now(), “ms”)
For what its worth, the null value for long is: 0x8000000000000000L, whereas for int, it is 0x80000000 as you showed.
But there is no point sending them, it just takes more bandwidth up sending the key and value in text format!
So just move your if checks where you are setting the null values to instead control whether you call floatColumn, intColumn etc. at all.
Thank you, I’m glad my approach is heading in the right direction. I considered the method you mentioned and that’s what I meant about being messy. If I had to test for all thirty values for example, wouldn’t I have to repeat the same code thirty times (with one line missing in each)? And what about different combinations of ‘no values’?
The missing values don’t happen often, but I’d prefer the code was solid and efficient too - sending as many values at once as possible.
I have to repeat the same code thirty times (with one line missing in each)? And what about different combinations of ‘no values’?
Or the code just once with an if block on any row you think is nullable?
sender
.table(itm);
if (location) {
sender.location("location", location);
}
if (runtime) {
sender.intColumn("runtime", pl["runtime"]);
}
// etc.
await sender.at(Date.now(), “ms”)
It would be easier to have a nullable API. Unfortunately, if you aren’t careful, you slow down the cases where the values are always not-null. The API is a bit clunky, but hopefully write once, leave for a long time.
Great, thanks! I tried that method initially but was getting errors so I didn’t think it was possible to embed the logic amongst the column statements. My lack of understanding.
I have now removed all of the pre-checking for null and it is looking much cleaner and the following is now working. Nulls are automatically added to the QuestDB table for missing columns.
// add rows to the buffer of the sender
await sender
.table(itm)
.symbol(“location”, location)
.intColumn(“runtime”, pl[“runtime”])
.floatColumn(“tempin”, pl[“tempin”])
.intColumn(“humidityin”, pl[“humidityin”])
.floatColumn(“baromrel”, pl[“baromrel”])
// Around 30 more key/values removed here for clarity
// Prepend sender from here
if (pl["temp1"]) {
sender.floatColumn("temp1", pl["temp1"])
}
if (pl["humidity1"]) {
sender.intColumn("humidity1", pl["humidity1"])
}
if (pl["temp2"]) {
sender.floatColumn("temp2", pl["temp2"])
}
if (pl["humidity2"]) {
sender.intColumn("humidity2", pl["humidity2"])
}
sender.at(Date.now(), "ms")
The trick it seems is that any statements around and after the logic need ‘sender’ prepended.
This would appear to be a solid and straight-forward way to get any MQTT data into QuestDB. Thanks again for your ideas and support.
Cheers - Andrew
Sorry, I will edit the code with blockquotes when the post has been approved.
Sorry, I will edit the code with blockquotes when the post has been approved.
All good, did it for ya!
Indeed, the fluent interface only works for direct chaining. If you don’t keep the returned object (which is just self/this/the original sender object), then you will need to reference it again from the original variable.
I tried that method initially but was getting errors so I didn’t think it was possible to embed the logic amongst the column statements. My lack of understanding.
No worries, that’s what we are here for 
Maybe we can add some better advice to the docs about null handling!