New DBQuery addon submitted

Tags: #<Tag:0x00007f616d1e11c8>

Introduction

I submitted a new database query addon named dbquery that allows creating items from native database queries.
The initial submission only supports InfluxDB 2.0 but I expect to publish another version in the near future with support for JDBC databases that are currently supported as persistence services.
The addon targets OH 3.0 and it’s only usable on that version.

I open that thread for further discussion and comments about the addon.

I know it’s difficult by now, because you need InfluxDB 2.0 and OH 3.0, that both are not released, but both also started to work in a release.
However, if you are able to install both and test the addon it will be heavily appreciated. If not, but you are interested in the addon any suggestion or comment will be well received.
The addon is in submission state and until is approved if you want to try you can download from here. Remember you need OH 3.0 to install it.

The initial submission is made in this PR:

Documentation

Detailed documentation about features and usage of the addon can be found here:

You can also find previously discussion about the addon in that message.

Some use cases

  • Integrate a device that stores it’s data in a database
  • Query derived data from you OpenHAB persistence, for example with Influx2 tasks you can process your data to create a new one
  • Bypass limitations of current OpenHAb persistence queries if you need to do complex filters or aggregations or you are more comfortable using native queries

A complex use case

One sample use case, that is the origin of this addon and a contribution I did in the InfluxDb persistence service to support InfluxDB 2.0, is the following that I like to explain because I think that there are some very nice synergies between this addon and InfluxDB 2.0:

I have an Iotawatt device that I use to get readings of my energy consumption, with the device I have different readings for instant consumption of all major home appliances: light, plugs, heating, fridge, washing machine, …

Those readings are continuously integrated to InfluxDB 1 and actually using the HTTP binding addon I launch some queries to InfluxDB to integrate the power consumption and obtain instant power usage or daily consumptions for each device.

But it has several limitations:

  • Due to escaping rules of HTTP binding and query syntax the queries are very difficult to read and maintain, for example one looks like this:

Number AerotermiaPower "Aerotermia [%d W]" <"energy"> { http="<[http://localhost:8086/query?db=openhab_db&q=SELECT%%20mean(%%22value%%22)%%20AS%%20%%22mean_value%%22%%20FROM%%20%%22openhab_db%%22.%%22origin%%22.%%22IOT_Aerotermia%%22%%20where%%20time%%20%%3E%%20now()%%20-1m:10000:JSONPATH($.results[0].series[0].values[0].[1])]"

  • The Influx 1 query language misses several features like obtaining the hour of the day of a given time, and that way, for example you are not able to do calculations of electricity cost if you have a per hour tariff.
  • Influx 1 has not as good features as 2 to process data and create derived measures.

With the combinations of the addon, I expect to have a lot more readable and maintainable queries, and using InfluxDB 2.0 and it’s new tasks feature I will be able to create derived measures like electricity cost for each device applying hourly tariff.
And that new measures will be easily queried and integrated into items with the query addon.

I think that the synergy with Influx2 tasks and dbquery addon it’s a good tool to do complex processing of your data or a new way to integrate newer devices that use supported databases.

Thanks

Finally, I like to give thanks to the community, specially @rossko57 and @hilbrand that help me answering some questions during addon development and improving it’s design and @5iver that helped me to get rules working for OH 3.0 and to be able to test the addon actions.

3 Likes