Persistence/Time series support

[Kai Kreuzer]

Hi all,

After having talked about it many times already, I now want to eventually start a discussion about the requirements and the design for a data persistence support in openHAB.

An issue for it exists since a long time already: http://code.google.com/p/openhab/issues/detail?id=21 and David was already so kind to share his implementation of such a support in a cloned repo (thanks again for it!). This implementation is already pretty neat: It listens to all events on the event bus, adds a timestamp and stores them in a db4o database. Data can then be retrieved again through an OSGi service or via HTTP through a servlet.

Nonetheless, I would like to fulfill some other requirements, which do not fit in this implementation. Most points were already mentioned in my comment http://code.google.com/p/openhab/issues/detail?id=21#c9 on the issue. As I see many requests for persistence support from others, I would like to openly discuss the main requirements and the general architecture for this feature.

Let me first summarize a few major points I would like to address:

1. Possibility to have the choice between different "persistence services". Implementations I would like to have here for the start: db4o, rrd4j, ThingSpeak

2. Different "data capture" strategies like: "persist every event", "persist a value every x seconds", "persist ONLY the very latest status" (just to be able to recover from crashes), some RRD way "only keep 1000 values taken every second, 1000 every hour, 1000 every day"

3. The strategy and the persistence implementation should be configurable for each item - possibly even multiple strategies for different implementations for the same item

4. There must be a powerful query mechanism to get data / timeseries out again. Especially for charting it should be possible to e.g. get equidistant values even though the strategy might have been "every event". For this, there must be a pluggable system to provide different implementations for data interpolation (or even extrapolation). This query API should then be usable for a REST service that can be used for charting engines as well as for http://code.google.com/p/openhab/issues/detail?id=48

5. Besides the persistence of item states, I would also like to have the possibility to persist other kinds of events/notifications. Commands are one of them, alarm messages might be others. We might have to introduce some notification API in openHAB first (result should be that one can realize something like http://goo.gl/GpZ4H)

Is there anything you would like to add?

Ok, enough of the talking, let's take some decisions. First thing I am not clear yet is how the user should best configure his persistence settings. I see quite some different options:

1. Using rules (similar to https://groups.google.com/d/msg/openhab/WZ_unJb43ag/pa2u3_8cFeQJ)

Pro: No additional implementation/concept required for the configuration

Con: Many rules to write, quite a lot of code and not very concise

2. We do it similarly to binding configurations, i.e. in the items file the user can add a { persist="event->db4o, perMinute->rrd4j, perHour->ThingSpeak" }

Pro: simple to implement

Con: it is no binding after all; no syntax validation and content assist in the designer; items file might get overloaded

3. Like option (2), but configuration is only done on groups and group members automatically inherit these settings (that's more or less the way David is using)

Pro: simple to implement, not much additional information necessary

Con: same as (2), additionally it feels a bit like "misusing" the item groups.

4. Like (3), but with newly introduced grammar elements, e.g. a "PersistenceGroup <name> strategy=daily service=rrd4j"

Pro: Brings syntax validation and content assist to the configuration

Con: Introduces something in the item files, which does not conceptually belong there

5. Adding configuration information in openhab.cfg. Different persistence configurations could be defined in there and then referenced by names (similar to (4), only that this is moved out from the item files) through a new syntax element, e.g. 'Switch Light "Entrance" @rrd1')

Pro: Configuration not inside the item files and a dedicated syntactical concept on the item definition; good for supporting "non-item" data, such as in requirement (5) above

Con: No IDE support on openhab.cfg, so it might not be easy to get the configuration right. Also not easy to do content assist for the @ element in the item files.

6. A completely new file type "*.persistence" with its own syntax and editor (same information as in (5). The link from items to persistence configs could then be either made by adding a reference in the item or in the persistence files.

Pro: Full editor support, dedicated file, so no concept mix with the items; good for supporting "non-item" data, such as in requirement (5) above

Con: A new DSL syntax to define, create and learn; files might not have a lot of information inside after all (and a single file will be in general enough)

So what do you think? Which road should we go down? Or do you have any other brilliant idea that I might have missed?

Looking forward to your feedback!

Kai

[Victor Belov]

Hi,

I think the big mistake here is mixing persistence and export. In
reality you will never use outside services like ThingSpeak, Pachube
and others for data to persist. You will use this services to export
this data to outer world without providing access to your system.
Someone would like to export certain graphs to their website/blog
(like me), someone just want to use it for research reasons and so on.
While persistence in general in openHAB is needed to recover from
crash, log events and commands, get historical reporting. From my
point of view there should be two separate mechanisms for that. I
think that we need to put all data going through the system to local
persistent storage for many reasons. Example with events history view
from your mail is just one of them. Crash recovery scenario is not
that good example, cause there could be some changes in real life
during the period of openHAB absence, so I would prefer to have a
mechanism of getting current state from the bus during openHAB
startup. You do not need to configure local persistence anyhow then,
probably we only need to create some logging-like configuration
mechanism, which will limit the amount of data stored locally,
filtering the data like debug/info/warning in terms of events.
Export services should be configured on a per-service basis in
openhab.cfg I think, cause I see no difference between a KNX bus
access configuration and ThingSpeak or Pachube account configuration.
It's just a pipe to push and pull some data (probably in future this
services will provide not only data storage but supply some events
too, I don't know, they could rise as data exchanges between different
systems for cloud based analysis and data exchange. For example if
certain amount of houses export high wind speed in the same area it
means some trouble coming...). You need to map items to this services
data storage then somehow in items configuration, but that is not big
deal, cause probably you will not be exporting 100 devices to the web
:-) At least I can't imaging a workable scenario for that. :-)
Pros: no or at least very small config for local persistence, small
config per export service, small config per item and not so many items
to configure
Cons: not that super flexible as your scenarios, but much closer to a
real user, not geek ;-)

Historical data access from local persistence is still a trick and I
fully agree about approximation and timeline stuff, it's needed in
real life scenario of users accessing their data.
Pros: it's needed badly for this type of system
Cons: Have to spend some efforts to implement

Best regards,
Victor Belov

> --
> You received this message because you are subscribed to the Google Groups
> "openhab" group.
> To post to this group, send email to ope...@googlegroups.com.
> To unsubscribe from this group, send email to
> openhab+u...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/openhab?hl=en.

[Kai Kreuzer]

Hi Victor,

Thanks for your quick feedback!

> I think the big mistake here is mixing persistence and export.

I see your point - you will actually never query data from IoT services. Still, some basic querying can be done on them as well as they offer data feeds as JSON or XML.

> In reality you will never use outside services like ThingSpeak, Pachube
> and others for data to persist.

But fact is, if you "export" your data to them, they will "persist" it. So like it or not - your data is persisted by this service.
I think the main reason for people wanting to persist their data is charting; and this is exactly what these services are offering. So imho they are a valid way to persist data. People store so much in the cloud these days and do not care about having a local copy of it, so why not in this case as well?

> Crash recovery scenario is not
> that good example, cause there could be some changes in real life
> during the period of openHAB absence, so I would prefer to have a
> mechanism of getting current state from the bus during openHAB
> startup.

Agreed, but for certain items it makes sense. In my case, my physical devices are connected through KNX and the openHAB KNX binding reads the status of almost all of them at startup. On other systems, you might not get feedback at all, so you have to trust what openHAB shows you - and if this is the same after a restart, it can be very helpful. The other types of items where it makes sense are the "virtual" ones, like "Presence", "Mood" etc. As there is no physical bus, they cannot be retrieved from anywhere else.

> cause probably you will not be exporting 100 devices to the web
> :-) At least I can't imaging a workable scenario for that. :-)

If I count my temperature, weather and other sensors, I end up at over 30 already… So the limiting fact will probably be the API restrictions of these IoT services that you had mentioned in your previous mail...

> Historical data access from local persistence is still a trick and I
> fully agree about approximation and timeline stuff, it's needed in

Ok, so what I summarize from your feedback is, that it might make sense to regard "export only persistence" and "queryable persistence" independently. Still, I think it makes sense to find a common way for the configuration as the export/persistence strategies should be fairly similar.

Regards,
Kai

[Mihail Panayotov]

> I think the big mistake here is mixing persistence and export. In

> reality you will never use outside services like ThingSpeak, Pachube
> and others for data to persist.

Personally I agree on this one. Instead of using different
"persistence" media of different types (local, cloud), it's more
logical to use just ONE and definitely local. Then if a user wants he
could export the data to cloud services like Pachube, ThingSpeak etc.
via other bindings providing APIs. I'm not familiar in close with
these services, but I guess they have their limits. They are better in
one thing and worse in other things. But they all have one big
disadvantage for sure - they are all dependent on Internet connection.
I think using our own local database and implementing our own
"service" is a way more flexible solution for the openHAB needs.

The regular user (and system integrator) just wants to get charts and
graphics. In general he doesn't care where and how this data is
stored. So a decision like "db4o", "rrd4j", etc. would be hard and
not understandable for someone outside the Java world (99% of users
and system integrators). For me persistence must work out of the box
with minimum (or no) initial configuration and not be dependent on
outside services.


For item configuration, I would go again with a solution requiring
little configuration and possibly no new config files and new syntax.
Personally I think the solution (2) is good enough and well balanced.
If we get out of question what storage method to use, then the
additional configuration needed would look nothing more than that:
"persist="value_change"" (on value change event) or
"persist="1m" (every minute) or "persist="3h" (every 3 hours) or
"persist="=22.5" (every time the value is "22.5") or
"persist="1m>22.5" (every 1 minute when the value is above 22.5).

Regards,
Mihail

[Kai Kreuzer]

> I think using our own local database and implementing our own
> "service" is a way more flexible solution for the openHAB needs.

I think you are missing an important point here. openHAB is meant to
be an open platform, easily extensible to integrate with other systems
- and not an off-the-shelf product with a "buy it as is or leave it"
approach.
People might have their own databases already set up (MySQL, MongoDB,
whatever) and would like to use these for the persistence. Hence
adding a new implementation of a persistence service should be made
easy. And yes, some services might be cloud-based (with all their
disadvantages), others might not. Conceptually, I do not see a reason
to draw a line between them.

> The regular user (and system integrator) just wants to get charts and
> graphics. In general he doesn't care where and how this data is
> stored.

I agree that there should be a decent default setup, so that the
uninterested user does not have to know about things like rrd4j db4o
etc. But the advanced user should have the possibility to configure
all of these things to his needs.

> "persist="1m>22.5" (every 1 minute when the value is above 22.5).

This already involves the evaluation of expressions - I think with
such requirements, option (1), i.e. dedicated rules is probably the
way to go.

Regards,
Kai

[Mihail Panayotov]

> I think you are missing an important point here. openHAB is meant to
> be an open platform, easily extensible to integrate with other systems
> - and not an off-the-shelf product with a "buy it as is or leave it"
> approach.
> People might have their own databases already set up (MySQL, MongoDB,
> whatever) and would like to use these for the persistence. Hence
> adding a new implementation of a persistence service should be made
> easy. And yes, some services might be cloud-based (with all their
> disadvantages), others might not. Conceptually, I do not see a reason
> to draw a line between them.

I am all to the open approach, but honestly I doubt someone would ever
want to use MySQL or MongoDB in the real world when there is a
perfectly working multi-platform database out of the box. I really
can't imagine a real use case requiring this. But for the sake of
openes I agree, there would be option for different databases. If we
go to that road, maybe option (5) or (6) would be the way. But there
definitely must be a preconfigured out of the box persistence support.
I know openHAB is not a "buy it as is or leave it" product, but lets
not get users scared. Many good software projects failed because of
their geeky approach and never reached the real user base.

[Victor Belov]

Hi,

You guys are talking about different databases, not persistent storage
schemes. Using one database or another is just a question of DAO or
object-rdbms or object-nosql mapping... There are plenty apps in the
world which can run on top of different databases.
I have another clue - we need to stay away from sql, cause the task
openHAB is working on is closer to object storage and non strict data
structure. Again mongo or mysql will limit usage of embedded platforms
very much...
And at the end of the day you can't make everybody happy. Any openness
must have limitation or it will sink in the process without result :-)

Sent from my iPhone

[Kai Kreuzer]

Ok, clear that it won't be able to define an interface that will fit
for all possible databases out there - different implementations have
different limitations.

My main point is that I want to have a service/interface defined that
should allow different implementations. And I deliberately include
"export" implementations here as well (they would simply not implement
the query part of the interface).

In general, I would like to note that openHAB is already very useful
without ANY persistence. Take version 0.9.0 and convince yourself ;-)
So to me, ANY persistence implementation is optional and not per-se
required. This might change, once we have a notification API as these
would need to be stored somewhere. But my first use case that I want
to realize is storing time series in rrd files. I am using an external
rrdtool installation at the moment for this and would like to replace
it. Charting would be done directly through rrd4j, so I wouldn't need
querying of this persistence store through openHAB. This is therefore
quite similar to ThingSpeak. So is this now persistence or export? I
really do not want to make a strict difference here.
For some events like alarm items I would prefer a durable and
query-able persistence, such as db4o. So you see, I will have
different implementations in use right from the start...

And the question of configuration is the same for all persistence
services. You want to be able to define the same kind of
persistence/export strategies for all of them. That's another reason,
why I would like to find a common concept and syntax for the
configuration. It imho does not make sense to invent different ways of
configuration for them.

Regards,
Kai

[Thomas Eichstädt-Engelen]

Hi,

Let me first summarize a few major points I would like to address:

first of all: thank you Kai for this very comprehensive summary! See my thoughts below.

1. Possibility to have the choice between different "persistence services". Implementations I would like to have here for the start: db4o, rrd4j, ThingSpeak

2. Different "data capture" strategies like: "persist every event", "persist a value every x seconds", "persist ONLY the very latest status" (just to be able to recover from crashes), some RRD way "only keep 1000 values taken every second, 1000 every hour, 1000 every day"

3. The strategy and the persistence implementation should be configurable for each item - possibly even multiple strategies for different implementations for the same item

4. There must be a powerful query mechanism to get data / timeseries out again. Especially for charting it should be possible to e.g. get equidistant values even though the strategy might have been "every event". For this, there must be a pluggable system to provide different implementations for data interpolation (or even extrapolation). This query API should then be usable for a REST service that can be used for charting engines as well as for http://code.google.com/p/openhab/issues/detail?id=48

5. Besides the persistence of item states, I would also like to have the possibility to persist other kinds of events/notifications. Commands are one of them, alarm messages might be others. We might have to introduce some notification API in openHAB first (result should be that one can realize something like http://goo.gl/GpZ4H)

These requirements looks really comprehensive to me.

1. Using rules (similar to https://groups.google.com/d/msg/openhab/WZ_unJb43ag/pa2u3_8cFeQJ)

Pro: No additional implementation/concept required for the configuration

Con: Many rules to write, quite a lot of code and not very concise

Using Rules should definitely be an option in order to provide really tricky storing conditions. We should be able to support requirements like those raised by Mihail (1m>22.5) but i wouldn't expect to find many implementations here. So from my point of view we shouldn't include them into the "normal" configuration just to keep it as simple as possible

6. A completely new file type "*.persistence" with its own syntax and editor (same information as in (5). The link from items to persistence configs could then be either made by adding a reference in the item or in the persistence files.

Pro: Full editor support, dedicated file, so no concept mix with the items; good for supporting "non-item" data, such as in requirement (5) above

Con: A new DSL syntax to define, create and learn; files might not have a lot of information inside after all (and a single file will be in general enough)

The existing items-file is just one option to provide items to openHAB. Conceptually there are many other ways to provide items as well (see BindingProvider). We could think of an item provider which reads all available sensor's from the OneWire-Bus and provides them as items automatically. Or think about other (possibly ready only) Item repositories which are not under openHABs control. There would be no chance to configure persistence attributes if we go with the items file. Therefore i would prefer to configure persistence by a new file. I am not sure if this has to be a new DSL or if a plain properties file will do the job as well (like option 5 - with the given Pros and Cons).

Regards,

Thomas E.-E.

[Kai Kreuzer]

> Using Rules should definitely be an option in order to provide really tricky storing conditions. We should be able to support requirements like those raised by Mihail (1m>22.5) but i wouldn't expect to find many implementations here. So from my point of view we shouldn't include them into the "normal" configuration just to keep it as simple as possible

Absolutely agreed - it's an option for advanced requirements :-)

> The existing items-file is just one option to provide items to openHAB. Conceptually there are many other ways to provide items as well (see BindingProvider).

Very good point, I forgot about this. Indeed, this cancels all options about putting the information in the item files.

> Therefore i would prefer to configure persistence by a new file. I am not sure if this has to be a new DSL or if a plain properties file will do the job as well (like option 5 - with the given Pros and Cons).

If a new file, I would go for a DSL to have the full IDE support, which I think is very helpful if one is not 100% clear about the correct syntax.

[Pablo Romeu]

Hello everyone,

I have a question, and I am not sure if it is a non-sense one, but, if
so, please forgive me.

My colleague and I tried to make an easy persistence method inside
openHAB and we found that it could be REALLY easy to make a logger
bind to a jdbc connection modifying a bit the logger code. What is the
problem with that? We could simply log to a JDBC and that is all we
need!

For example: http://wiki.apache.org/logging-log4j/JDBCAppenderConfiguration

Regards,

Pablo Romeu

[Kai Kreuzer]

Hi Pablo,

Yes, this is indeed a simple solution which does not even require any implementation. But this solution has quite some restrictions:
- You can only use storages with JDBC drivers (i.e. in general only RDBMS)
- You can only use "every event" strategy, i.e. you could not send a value every minute.
- You cannot activate it for only some items or items of certain groups.

Regards,
Kai

[Pablo Romeu]

I think a JDBC driver is a really easy and straightforward way to go.

In our openHAB installation we are actually working with a query
system using a ETL tool to extract data from the REST interface and we
insert it in a table once a minute. In our experience, it is more
desireable to record EACH event and filter after that, but we cannot.
Polling intervals do have some problems indeed. In our installation,
for example, we miss if a presence detector is ON just for some
seconds.

So answering your concerns:

- Which other persistence systems not compatible with RDBMS do you
plan to use? There is no way to migrate data from a database to other
"persistence" systems? Or to use a trigger? Or send notifications?
- The every event strategy is the only accurate strategy in a real
time system. And also you can actively poll the system every minute if
you want to do so. We already do it!
- You can filter your database for that items. You may trigger a rule
inside the database to do all the work. Also, we could ship openHAB
with a MySQL/Postgresql database with all of this already prepared.

[Juanker Atina]

Hi Pablo, could you please give us some details about this ETL engine?

[Pablo Romeu]

It is called "Kettle". Is an opensource tool intended to be used on
Data Warehouses, but it is useful for any kind of data movement and
transformation. It is programmed in java and is part of the pentaho
suite of tools:

http://kettle.pentaho.com/

It has a user interface to program the transformations and you may
schedule them using a tool called "pan". We schedule a REST request
every minute and get the data from all the sensors, then we insert
them in two databases (one local and a remote backup).

[ope...@lb-automation.de]

Hi,

i have seen you started already with implementing persistant support. can you tell us how it will be (something about the design) and how the progess is

what do i expect ?

- i would like to have some storage in openhab
- i would like to do some calculations with the data from storage, could be on the datastorage site but also outside (scripts in openhab ??) the database/storage (avg, sum for certain datsets)
- i would like to "export" data to external storage (postgres) to do some calculations there and give the result back to openhab (ist for heating, if teh sun is shining a lot, some calculations will calculate how much is ist and how much to heat, after that the heating is regulated)
- use the exported data for other purposes
- would like to have an list of alarns/events inside openhab (list of how called me)

[h...@harleyman.lu]

 

I would like to contribute some reflections to this subject:

• Oodb versus RDBMS: The point is do we want to store objects or just one or two attributes of any object at a given time.
• Are we talking about crash recovery or just plain logging: While logging is quite easy, crash recovery can be a lot more tricky, the first question being if we are recovering from a crash of the HAB runtime or from a 5 hour power-outage. For shure the logfile (or database) can givel you the last known state of an object but it's not always a good idea to simply "replay" the last known state, except if you find out that you had a very short power outage.
• Logging is a condition for crash recovery, but if you want persistence from an operational point of view you need more than just logging, probably a dedicated recovery module would be appropriate, maybe similar to the KNX settings (behaviour after bus failure and behaviour after AC failure)
• Different cases could be preconfigured:
1. Long power outage -> No action - If you have breakfast at 7 in the morning with lights on and listening news on the radio and then the power goes away for 15 minutes you will probably have left for work by the time power is restored.
2. Short power outage -> Replay last known state for light and multimedia - You are watching a Bluray and power goes away for 2 secs
3. HAB server crashed -> Learn current situation - Maybe you switched some light ore moved a blind before you realized that your server hung (and you have not implemented a watchdog)
• I believe that it would be an advantage if OpenHAB had some sensible logging "out of the box".
• A tab delimited textfile would do the job as these are really easy to exploit and can even be imported into a RDBMS, at least if the headers are not too cryptic . If the user wants to use any other target the config file is the right place to do it. A JDBC driver might be included and other connectors can be added by anybody.
• Just like the other Demo files there should be a Demo logging file with some default values, temperature is always nice to log, but maybe also the switches.
• Even if the Rules section is appropriate for some advanced logging like caculating temperature averages, I believe that a dedicated logging section is easier to use and more transparent.

[Kai Kreuzer]

Hi,

I have already implemented a "restoreAtStartup" feature while working
on the persistence support - it is not as flexible as your suggestion,
but should do for most cases.

I will integrate the work I have done in the default branch next week,
document how it works on the wiki and will post some information about
it here in the group - so I suggest to continue any discussion after
that next week :-)

Stay tuned,
Kai

Quoting h...@harleyman.lu:

>
>>
>>
>>
> I would like to contribute some reflections to this subject:
>

> - Oodb versus RDBMS: The point is do we want to store objects or just

> one or two attributes of any object at a given time.

> - Are we talking about crash recovery or just plain logging: While

> logging is quite easy, crash recovery can be a lot more tricky, the first
> question being if we are recovering from a crash of the HAB runtime or
> from a 5 hour power-outage. For shure the logfile (or database) can givel
> you the last known state of an object but it's not always a good idea to
> simply "replay" the last known state, except if you find out that
> you had a
> very short power outage.

> - Logging is a condition for crash recovery, but if you want persistence

> from an operational point of view you need more than just
> logging, probably
> a dedicated recovery module would be appropriate, maybe similar to the KNX
> settings (behaviour after bus failure and behaviour after AC failure)

> - Different cases could be preconfigured:
> 1. Long power outage -> No action - If you have breakfast at 7 in

> the morning with lights on and listening news on the radio
> and then the
> power goes away for 15 minutes you will probably have left
> for work by the
> time power is restored.

> 2. Short power outage -> Replay last known state for light and

> multimedia - You are watching a Bluray and power goes away
> for 2 secs

> 3. HAB server crashed -> Learn current situation - Maybe you

> switched some light ore moved a blind before you realized
> that your server
> hung (and you have not implemented a watchdog)

> - I believe that it would be an advantage if OpenHAB had some

> sensible logging "out of the box".

> - A tab delimited textfile would do the job as these are really easy

> to exploit and can even be imported into a RDBMS, at least if
> the headers
> are not too cryptic . If the user wants to use any other
> target the config
> file is the right place to do it. A JDBC driver might be
> included and other
> connectors can be added by anybody.

> - Just like the other Demo files there should be a Demo logging file

> with some default values, temperature is always nice to log,
> but maybe also
> the switches.

> - Even if the Rules section is appropriate for some advanced logging

> like caculating temperature averages, I believe that a dedicated logging
> section is easier to use and more transparent.
>

> --
> You received this message because you are subscribed to the Google
> Groups "openhab" group.

> To view this discussion on the web visit
> https://groups.google.com/d/msg/openhab/-/KrFWk_TdnMsJ.