InfluxDB query addon proposal

About a year ago I asked about creating an InfluxDB query addon.
In the end, I used the HTTP binding with heavy pain in scaping rules and very complex and not maintainable items.

Now that I’ve more experience with OpenHab code after creating a InfluxDB 2.0 persistence add-on and that InfluxDB 2.0 it’s closer to the final version I will found very useful that addon.

In my case, I have a lot of data in Influx; from OpenHab, from other devices like iotawatt and I plan to create derivate data using flux tasks.

And the use case of the addon will be to be able to define Items from arbitrary flux queries.

Can be this addon accepted in the codebase?

I think it makes a good addition. But it might be even better to add support for such features to the openhab-core persistence APIs since I think it may also be useful functionality for other peristence add-ons. WDYT @Kai?

The restoreOnStartup persistence strategy is a special instance of such functionality where the items are restored on startup only. Whereas it might be useful to periodically restore the item state when the state is persisted by other applications/users using the database.

Then it could also be possible to transform data to item state within the database by using a database view or some database triggers.

1 Like

I would agree

But we can already do that within rules.

A better use case for a kind of “general query”? (I’m imagining the mechanism here would be a command/argument string where we could set a database specific query e.g. SQL)

A metering or monitoring application, where we want to get the data for some past calendar month. Not possible in current persistence, where you can get only from past point X to now.

Yes, it will be nice to have it globally for all addons.
Because it will allow to query derived data in the persistence addon or use it as an integration point for external systems.
For me, a very important point to go for InfluxDB 2.0 was the flux task to create derivative data that will be very useful for me.

For me a possible implementation could be something simple and flexible like:
nativeQuery(String query, Map<String,Object> params)

Because it provides flexibility to query data that is not tied to OpenHab persistence rules.

1 Like

I am not fully clear on what the suggestion is here, I have the impression that different things are discussed.

If we are talking about introducing a binding that is able to interact with databases in general (because there might be data that is stored by devices like iotawatt or calculated/derived in some other way), then I could imagine having a “Database Binding”, which is generic enough to make use of any queryable persistence service that is available.

If we want to add querying features to rules, we could easily extend PersistenceExtensions by a query method, which integrates with the existing QueryablePersistenceService.query method, which is generic and not specific for any database.

Adding a nativeQuery function as suggested by @lujop looks to me as the least attractive option from an architectural point of view.

This is exactly my use case. But it will need to have some native query support from the persistence services to do the queries.
Because each persistence service use its clients and very different queries, from SQL for MySQL to flux for InfluxDB 2.0

As mentioned above, I would hope that such a binding can be implemented to work with any
QueryablePersistenceService. The db-specific queries are then already implemented within the persistence service and there should be no need to have anything specific in the binding.

For me will be a combination.
Adding something similar to nativeQuery to QueryablePersistenceService

And then a Database binding that makes use of it to be able to provide items from an arbitrary native query for any QueryablePersistenceService.

Because current FilterCriteria doesn’t seem enough for me, and will be very difficult to extend it accomplish queries from different domains like SQL o Flux.

Any update on the possibility to implement a generic Database query binding using QueryablePersistenceService but adding the notion of native query to the interface?

I will be interested in developing it if it can be accepted.

1 Like

@Kai, any update on my proposal?
Or at least will be accepted to do the addon only for InfluxDB.
Adding a NativeQuery interface to the InfluxDB persistence service and develop a new addon with a binding that uses it?

To meet my needs I’ve to be able to do flux queries in the binding, using just FilterCriteria is not a solution.

I am still not very fond of this idea as it somewhat breaks the abstraction layer of the persistence service. But if you think it makes a lot of sense, feel free to create an issue at openhab-core, where it can be discussed. Decision is not taken by me, but by the core maintainers, so those are the ones you need to convince :slight_smile: .

I finally got some time to create an issue about what is discused in this topic:

In the end, to be able to advance I’ve started creating a new binding named DbQuery.

My initial plans for the binding are to provide an Influx2Bridge an MySQLBridge, and if someday the linked Github issue is a reality an OpenHabQueryablePersistenceServiceBridge will be added.

Thinking it after time, the possibility to query an external database even not related to any persistence service can be a plus for some requirements.

3 Likes

Finally I submited a first version of DbQuery addon in that PR.
Initially it only supports InfluxDB 2.0 but will be added support for other JDBC database in a near future.