[influxdb] Use semantic model for structuring InfluxDB storage

[DerOetzi]

To make easier to build powerful graphic respresentations of measurements for huge amount of sensors the kind of storing measurement points into influxdb should be changed.

Making it easy to query for example all temperature measurements of groundfloor. Or all battery curves of first floor.

In my opinion this can be achieved very easily by using the "new" OH3 semantic model.

First there should be no measurement per item but by property type. Second there should be a tag with item name and one with item label. Third there should be tags representing the location tree of this item.

This would give in influxdb series like this:

Using database openhab
> show series from temperature
key
---
temperature,building=Schuppen,item=tempSchuppenAqara,label=Schuppen\ Aqara\ Sensor
temperature,floor=Aussen,item=tempAussen,label=Aussen,room=Terrasse
temperature,floor=Aussen,item=tempAussenGefuehlt,label=Gefühlte\ Temperatur,room=Terrasse
temperature,floor=Dachgeschoss,item=tempGaestezimmer,label=Danis\ Zimmer,room=Danis\ Zimmer
temperature,floor=Dachgeschoss,item=tempMusikzimmer,label=Musikzimmer,room=Musikzimmer
temperature,floor=Erdgeschoss,item=tempEsszimmer,label=Esszimmer,room=Esszimmer
temperature,floor=Erdgeschoss,item=tempFlurEG,label=Flur,room=Flur
temperature,floor=Erdgeschoss,item=tempHauptbadAqara,label=Hauptbad\ Aqara\ Sensor,room=Hauptbad
temperature,floor=Erdgeschoss,item=tempHauptbadGross,label=Hauptbad\ groß,room=Hauptbad
temperature,floor=Erdgeschoss,item=tempHauptbadKlein,label=Hauptbad\ klein,room=Hauptbad
temperature,floor=Erdgeschoss,item=tempWohnzimmerGross,label=Wohnzimmer\ groß,room=Wohnzimmer
temperature,floor=Erdgeschoss,item=tempWohnzimmerKlein,label=Wohnzimmer\ klein,room=Wohnzimmer
temperature,floor=Erdgeschoss,item=tempWohnzimmerNetatmo,label=Wohnzimmer\ Thermometer,room=Wohnzimmer
temperature,floor=Obergeschoss,item=tempArbeitszimmer,label=Arbeitszimmer,room=Arbeitszimmer
temperature,floor=Obergeschoss,item=tempDuschbadAqara,label=Duschbad\ Aqara\ Sensor,room=Duschbad
temperature,floor=Obergeschoss,item=tempDuschbadHeizung,label=Duschbad\ Heizung,room=Duschbad
temperature,floor=Obergeschoss,item=tempKinderzimmerAqara,label=Kinderzimmer\ Aqara\ Sensor,room=Kinderzimmer
temperature,floor=Obergeschoss,item=tempKinderzimmerHeizung,label=Kinderzimmer\ Heizung,room=Kinderzimmer
temperature,floor=Obergeschoss,item=tempSchlafzimmerGross,label=Schlafzimmer\ groß,room=Schlafzimmer
temperature,floor=Obergeschoss,item=tempSchlafzimmerKlein,label=Schlafzimmer\ klein,room=Schlafzimmer
temperature,floor=Obergeschoss,item=tempSchlafzimmerNetatmo,label=Schlafzimmer\ Thermometer,room=Schlafzimmer

And an single select to get all temperatures of a room or floor for example all of groundfloor:

SELECT moving_average(mean("value"), 5) FROM "temperature" WHERE ("floor" = 'Erdgeschoss') AND $timeFilter GROUP BY time($__interval), "label" fill(none)

Alias by $tag_label

[dlaplexurenet]

You can achieve this using the metadata for InfluxDB. Nonetheless, I do agree with you; why should we add the same information once again in the metadata. Currently I use the following metadata structure and most information is just duplicated:

influxdb=" " [binding="KNX",device="Elsner KNX TH-UP basic",area="Indoor",zone="First Floor",location="Bathroom",equipment="Sensor",measurementProperty="Humidity",measurementType="Dimensionless",measurementUnit="Percent",measurementSymbol="%"]

It allows me, as you mentioned, to quickly group measurements by location/area/zone and to use other information such as the measurementProperty in the axis descriptions.

--

[DerOetzi]

You can achieve this using the metadata for InfluxDB. Nonetheless, I do agree with you; why should we add the same information once again in the metadata. Currently I use the following metadata structure and most information is just duplicated:

influxdb=" " [binding="KNX",device="Elsner KNX TH-UP basic",area="Indoor",zone="First Floor",location="Bathroom",equipment="Sensor",measurementProperty="Humidity",measurementType="Dimensionless",measurementUnit="Percent",measurementSymbol="%"]

It allows me, as you mentioned, to quickly group measurements by location/area/zone and to use other information such as the measurementProperty in the axis descriptions.

--
Is this working by default with the influxdb binding or are you using a extra rule based writing to influxdb like I do at the moment?

[dlaplexurenet]

This is working since the Influx Persistence addon as of OH3 (there used to be an unofficial version for OH2.5 but it was decided to officially merge the feature only into the addons for OH3).
It working out of the box, no config needed on Influx, nor OpenHAB (except the metadata). Using MainUI the metadata influx is not listed but you can choose to add a custom one. Or just add it to your items file in case you are using the textual definitions. Just make sure that you also configure well your persistence file in order to write all items of a specific type into the same measurement table, else it will not really make any sense as per default it creates one measurement table per Item.

It’s working flawlessly with InfluxDB 1.x as well as 2.0. I recently switched to 2.0 but I’m still struggling with FluxQL in Grafana and currently it’s slow in Grafana (but this is in no report to OH or the persistence addon).

[DerOetzi]

@dlaplexurenet thanks for this information.

As I'm writing an openhab configuration creator at the moment, before switching completly to OH3, it should be no problem to add this to the generation of items files. I will give it a try.

The only thing not working if I see correctly is to define a seperate measurement, correct?

[DerOetzi]

Using metadata at items definition works fine for me. So will close this issue.

[openhab-bot]

This issue has been mentioned on openHAB Community. There might be relevant details there:

https://community.openhab.org/t/using-tags-for-providing-floor-to-influxdb/130943/1