Updated InfluxDB binding with tags

Add-ons Persistence Services
,
1

Hi folks,

I am using the InfluxDB binding now for quiet a while. Unfortunately, there is no way to add additional tags to my saved data. As I understand the concept of InfluxDB, data points from different, but similar sources are written in the same measurement, and separated by tags. So for example the data from all my temperature sensors gets stored in one measurement. Later, I can query them by room, by manufacturer of the device, by item name, by charge number, or by whatever tag I have added. This also simplifies the creation of dashboard in Grafana, as I can say “Show me all data from measurement x, grouped by room / item name / whatever”. When I add new temperature sensors, I do not have to modify my graphs, alerting rules, etc.

So I have updated the InfluxDB binding from addons1 to the OpenHAB 2 ecosystem. These are the changes:

  • Add ability to add custom tags through item metadata
  • Add ability to add predefined tags through config
  • Fix bug that when using an alias name for the item, querying the database for that item was broken
  • Configurable through UI (and file, of course)
  • Update to the newest InfluxDB-java library with all of its dependencies
  • Add option to replace underscores “_” in item names / alias names with points “.”
  • Some minor bug fixes

The current work can be found here: https://github.com/dominikkv/openhab2-addons/tree/influxdb-neo

What we need to discuss is the following:

  • Name & namespace. I have used org.openhab.persistence.influxdb like the old one, is that OK?
  • How to add this to addons2? Just a simple PR?
  • Are there other feature wishes?
  • Are there other points you’d like to discuss?

I’m happy to hear your opinions :slight_smile:

Thanks
Dominik

9 Likes
2

I think i know what you are doing
 i was thinking its a shame the data is written as separate measurements rather than using tags to determine what room the sensor is in etc
 so this is great!

One thing though, InfluxDB server 2.x is in alpha https://www.influxdata.com/products/influxdb-overview/influxdb-2-0/
So the title of your binding version also confused me initially as i was thinking it was only for InfluxDB2.x


Ill try to test this out in a day or so and give any feedback!

3

I was missing tags, too.

Great to see someone working on this. :+1:

I only took a glance at the README, but I think it would be nice to have some examples with temperature and humidity instead (or in addition to) your power example.
Because measuring temperature is properly the first thing anybody does in the smart home world.
And it would show how to work with different measurements.

Another note: Every user that will start using this approach will want to migrate the existing measurements to the new ones.
So a little info how to do this would be helpfull.

4

Fantastic work @dominikkv! Tags were dearly missed in openHab InfluxDB persistence.
I’ll have a good look and test the coming days.

5

Sure :smirk:
Changed that, thanks!

To be honest, I have written half of the example with temperature sensors, but then switched over to the power example. The reason is that for temperature, I could think of only one tag that would fit: room. Yeah one could add tags like manufacturer or device model, but that would only make sense if you use different sensors. Do you have ideas for other tags I could add?

Yup! I have done this with a small self-written Java program, but I don’t see any user friendly way of migrating your old data. InfluxDB s*cks at manipulating existing data :woozy_face:
Does someone have an idea?

@all: When cloning from Github, don’t forget that these changes are in the branch ‘influxdb-neo’ :slight_smile:

6

Beside room there could be a tag for floor.
And maybe one to differentiate between inside and outside.

And there could be temperature measurements that are not for room tempreatures.
Like temperature of heating or cooling equipment.

But I get your point.

7

Create a new database for the new binding

Could your java program ask for the new tags for each series it finds in the old database?
Ie: Prompt for the tags to assign for each series ?
Also, check if the series has had data written into it in the last few days, and skip import into the new database if no data has been there for that period.

As for tags, i wonder if this type of metadata is being considered for OH3 - where each item/thing has a “room” or “floor” tag field


8

As a newbie who started only two weeks ago with logging openhab data into influxdb I would sacrifice my existing data in a heartbeat to get tag support. Is there anywhere a .jar flying around that I could “borrow”?

2 Likes
9

Any updates on this?

10

Great work @dominikkv !

Have you thought adding a way to specify the retention policy to the item ?
I think I’m not the only one having items varying very frequently that are kept a few days before being downsampled.
I currently use a rule and a direct http post to influx but, as for tags, your update would make it a lot cleaner !

11

I was really happy to see there’s finally a way to work with tags directly from the persistence binding. I am using tick-scripts to add them as data comes into the influx DB, but that’s quite unhandy.

I would like to test this, but I am not experienced enough to compile the addon myself.
Is there a .jar file I could download somewhere to test?

Also: will this become part of v2.5 final?

12

Hey all, sorry for not responding :-/ I am now father of a lovely boy, so there is not that much time left for smart home anymore.

@christoph_wempe : I’ll improve the readme with your suggestions.
@greg: I understand your wish, but I think I will not implement an automated migration mechanism. This is complicated, error prone and in the end the user wanted to have it in a different way. Sorry :frowning:
@StephN: You can already specify a retention policy in the configs for all items. For a per-item-config, this is a little bit more difficult. Saving the states in another RP would be possible, but when querying the database, there is no information about the item. This means OpenHAB charts and functions to query a value like <item>.deltaSince(AbstractInstant) would not work anymore for measurements not in the global RP. Maybe a solution for you is saving the data in the global RP in the first place, and then later on transfering them to another RP with a continous query?
@bastian, @klaernie: here you go.

Dominik

1 Like
13

@dominikkv - thanks a lot, will try it out on the weekende. And all the best for your boy - congratulations to the three of you!

14

Was this code finally merged or in process to a PR?
I want to do some personal experimentation with influxdb 2.0 and I’ve been looking for current code and it’s in openhab 1.0 addons?
The only work in 2.0 is this? or I’m missing something?

15

Hey Joan @lujop, I have not yet created a PR, but that’s the next step. Today I have added the other example in the documentation.

You are right, the current InfluxDB binding is an OpenHAB 1.x binding (which works perfectly fine with OpenHAB 2.x). I have ported it to OpenHAB 2.x, adding other features like additional tags.

PR is coming soon, stay tuned :slight_smile:

Dominik

16

Is it feasible to deliver the update with OpenHAB 2.5? Having tags would add a lot of value to the InfluxDB persistence binding. I am happy to help by testing things with my dockerized OpenHAB/InfluxDB environment.

17

Thanks @dominikkv for you work! Finally got to installing it right now and it works really nicely.

For anybody searching for a migration strategy for the “replace _ with .” problem: I just grabbed a simple bash script to do this:

 foreach olditem in $( influx -database openhab_db -execute 'show measurements' | grep -E '[_]' ); do echo "select * into \"${olditem//_/.}\" from ${olditem}"; done

The output I pasted back into influx, double checked the new measurements were there, then did the same with

foreach olditem in $( influx -database openhab_db -execute 'show measurements' | grep -E '[_]' ); do echo "drop measurement ${olditem}"; done
1 Like
18

Okay, after running a few days I noticed one problem. Somehow .averageSince() doesn’t work anymore.

This debug rule:

rule "debug output"
when
        Time cron "0 * * * * ?"
then
        logInfo("debug",PresenceCountdown.averageSince(now.minusMinutes(15)))
end

produces only this log output:

 2019-12-02 18:13:00.002 [DEBUG] [ersistence.influxdb.internal.InfluxDBPersistenceService] - Got a query for historic points!
 2019-12-02 18:13:00.002 [DEBUG] [ersistence.influxdb.internal.InfluxDBPersistenceService] - Query: SELECT * FROM "openhab_db"."autogen"./.*/ WHERE item = 'PresenceCountdown' AND time > 1575305880s  LIMIT 2147483647
 2019-12-02 18:13:00.028 [DEBUG] [ersistence.influxdb.internal.InfluxDBPersistenceService] - Returning query() with 985 items
 2019-12-02 18:13:00.030 [ERROR] [thome.model.rule.runtime.internal.engine.ExecuteRuleJob] - Error during the execution of rule 'debug output': An error occurred during the script execution: Could not invoke method: org.eclipse.smarthome.model.script.actions.LogAction.logInfo(java.lang.String,java.lang.String,java.lang.Object[]) on instance: null

but when ask the persistence over HTTP I get meaningful data:

kandre@mainframe(pts/7) ~ % curl 'http://openhab.ak-online.be/rest/persistence/items/PresenceCountdown?starttime=2019-12-02T18:18:00'
{
  "name":"PresenceCountdown",
  "datapoints":"16",
  "data":[
     {"time":1575307080004,"state":"1798.0"},
     {"time":1575307080060,"state":"1798.0"},
     {"time":1575307081003,"state":"1797.0"},
     {"time":1575307082058,"state":"1796.0"},
     {"time":1575307083003,"state":"1795.0"},
     {"time":1575307084002,"state":"1794.0"},
     {"time":1575307085004,"state":"1793.0"},
     {"time":1575307086003,"state":"1792.0"},
     {"time":1575307087002,"state":"1791.0"},
     {"time":1575307088003,"state":"1790.0"},
     {"time":1575307089003,"state":"1789.0"},
     {"time":1575307090003,"state":"1788.0"},
     {"time":1575307091003,"state":"1787.0"},
     {"time":1575307092077,"state":"1786.0"},
     {"time":1575307093003,"state":"1785.0"},
     {"time":1575307094003,"state":"1784.0"}
  ]
}

Does anyone have an idea if this is caused by the InfluxDB binding or where I could find more debugging information?

19

Hey @klaernie,

this is an exception thrown in your rule. Your log says that the InfluxDB binding has provided 985 data points, and that you have an error in your logInfo() call. Try something like this:

logInfo("debug", "Average: {}", PresenceCountdown.averageSince(now.minusMinutes(15)));

Thanks for your rename script!

@martingruening, I don’t think it’l made it into 2.5, as M6 has already arrived, wich will be the last milestone. But you can use the jar I have provided.

@all: Good news - PR is ready: https://github.com/openhab/openhab2-addons/pull/6507

Thanks for your feedback, and sorry again that it took so long :slight_smile:

Dominik

2 Likes
20

@dominikkv Could you change the way Color items are written? I just tried to graph an RGB lamp’s brightness, and it took me a while to find out why Grafana didn’t want to deliver data for it :wink:

From my user perspective I would expect that the binding writes three or six values into InfluxDB:

  • either HSB values into (H,S,B)
  • or the RGB into fields (R,G,B)
  • or possibly both together.

Maybe to keep backwards-compatibility the field value should be still written like it currently is.

From my personal view I’d like the HSB approach best, as openHAB uses it internally, so conversion is lossless and it supports the use case: “Show me a graph of the lamp’s brightness”.

Else I can report everything still working fine, once I discovered that during the 2.5 upgrade the 1.14 bundle got activated and was messing with my setup.