Handling NULLs policy

[blelump]

Hi!

Background: we’ve recently switched to Influx 2.0 from 1.8.

Problem: writing NULL point into measurement is not possible anymore in Influx 2.0? At least such insertion myMeasurement,tag1=value1,tag2=value2 fieldKey2=null 1620404467677 will fail.

Discussion: whether it makes sense to write NULL (or not) in time series database is somehow related to the problem I am reporting. Basically NULL means anything so it is not so useful from data analytics point of view. On the other hand a point in time with value NULL also means that an event has occurred at that point in time, which could be enough in some cases (ie. reporting heartbeat without any meaningful value).

Another interesting point is that the Flux QL has support for handling NULLs. That could be from the legacy reasons (to support legacy DBs), but in order to do so, they first would need to be migrated into the 2.0 version, using the 2.0 interface which disallow NULLs…?

[blelump]

To somehow continue the topic, it looks like the current approach is to prohibit explicit NULL insertion, but on the other hand also permit such insertion:

myMeasurement,tag1=value1,tag2=value2 fieldKey2=5 1620404467677
myMeasurement,tag1=value1,tag2=value2 fieldKey=1 1620404467677
myMeasurement,tag1=value1,tag2=value2 fieldKey2=3 1620404477677

where the 3rd row allows implicitly to set NULL on fieldKey inserted in 2nd row. Does not seem to be consistent…?

Spent some time to see how the Influx community deals with it and found this: Unable to write null values to db · Issue #7 · mblackstock/node-red-contrib-influxdb · GitHub . This one leads to https://github.com/influxdata/influxdb/pull/2429 , which suggests to omit NULL values if they are present and set a flag for given measurement, that a value is NULL. I’m not happy with such approach, but seems to be the only one viable solution.

[Anaisdg]

Hello @blelump,
How do you mean Flux has no support for handling NULLs? How are you writing data to influxdb?

Compare to previous column string value

Hello @R0bR, How are you writing empty strings? Wouldn’t it be easier to not write that value? Anywho this should work: import "experimental/json" null=json.parse(data: bytes(v: "null")) from(bucket: "test") |> range(start: v.timeRangeStart, stop: v.timeRangeStop) |> filter(fn: (r) => r["_measurement"] == "test") |> filter(fn: (r) => r["_field"] == "foo") |> filter(fn: (r) => r["test"] == "one") |> map(fn: (r) => ({ r with _value: if r._value == "" then null …

Fill null values in data | InfluxDB Cloud (TSM) Documentation

I agree that’s odd. And yes, I believe you’re correct the recommended approach is to add a tag for null values…

[blelump]

Hi @Anaisdg . Thanks for reply!

How do you mean Flux has no support for handling NULLs? How are you writing data to influxdb?

I might used not best wording here. I meant that the language itself allows to deal with NULLs while querying, but explicit data ingestion that would contain NULLs is prohibited.

I agree that’s odd. And yes, I believe you’re correct the recommended approach is to add a tag for null values…

Could you elaborate? What if any incoming value can be NULL?

I guess the overall problem appeared, because we don’t understand the data we gather and we dont do any transformation of this data. For example NULL may have meaning that a given sensor is in inactive state, but it’s the client problem to address that and convert into meaningful value (like 0 or false in this case).

[Cyrus_Jomo]

Hello. What would happen in a case of streaming data that null values are not predicable but also null values could be legitimate values.

[Anaisdg]

Hello @Cyrus_Jomo,
Field values in line protocol, the ingest format for influxdb, must not be a null. Here are the acceptable types:

InfluxDB glossary | InfluxDB OSS 1.8 Documentation

Nulls will not be written.
But if you’re streaming data and expecting to be writing data regularly, than you can use flux to group by that write duration and fill missing values where nulls would be.