exporting data from Openhab and mysql

[michael c]

perhaps it's the scientist in me but i am amazed to not find any data exporting features built into OH. 

i'm generating data that needs offline analysis to determine what online analysis i'd like to do using OH rules etc.

 

just like i can highlight and zoom in on data on a chart (using habmin, actually) i'd like to be able to export that data to a CSV file.

i've heard about using the logging features but the data is already in a DB and just needs to be pulled out. seems like this is openhab's thing to do. i don't want to maintain some inferior DB by logging....

really searched everywhere for this for over a year now. surprised there's not more interest in this.

thanks!

michael

[Rich Koshak]

The problem is OpenHAB supports a bunch of widely different databases with widely different capabilities. For example, rrd4j can only store numbers, db4o may not translate to a flat table and therefore CSV very easily, MongoDB stores everything as a document, etc. Given this inconsistent set of data and capabilities and the limited demand for the export feature I'm not surprised that the feature doesn't exist. It is not trivial to implement and there doesn't seem to be much demand.

I believe that your particular use case (i.e. getting csv data for use external to openHAB) is what the Logging Persistence Binding is intended for, despite your reluctance to use it. I'm not sure how this approach is inferior. Whether openHAB writes the stuff to CSV directly or you export whatever DB(s) openHAB's persistence is configured to use to CSV, you still have a second "inferior" copy of your database as CSV either way. 

But if you would rather go through the extra steps of exporting CSV from a database, you could use the MySQL persistence binding and then dump the table as CSV using instructions like these. Or just use MySQL queries directly.

You can have multiple persistence bindings active at any given time so you can only save to MySQL or Logging the stuff you want to analyze and use rrd4j or db4o for everything else openHAB needs (e.g. restore on restart and historical data in rules).

I think there are lots of ways for you to achieve what you desire with what openHAB already supports.

Rich

[michael c]

excellent response, thanks.

i would like to reserve the option to go back in time and look at any section of data for whatever reason. i would have to log everything in case i ever want to see it in the future. this means i would have to run two db's, mysql and logging. that seems like setting up for problems.also have to remember to update the logging manually when adding new items etc...

it's true that all these db's are different, but the fact is that OH makes charts just fine so all the info i want is already pulled together right there, with the UI to let you do it. i want to be able to simply export that data that's on the screen as, say, CSV ascii data.

heck, if the Table tab in OH/habmin would allow me to highlight and grab the table that made the graph i would be a happy camper, but that doesn't seem to be allowed. maybe i'm missing something with this Table tab, could solve the whole issue.

yes, an opportunity to expand my mysql skills. i appreciate the link. i was hoping to do this from inside OH/habmin so that i can explicitly asks for only the records i'm interested in.

i mean, if i have a graph i want to publish i have to screen capture from OH? seems rather obtuse. (publication could = Hackaday, for instance)

i don't really consider generating a single graph/table as creating a second db, but duplicating mysql db as a logging persistence file definitely is.

thanks!

[Rich Koshak]

I suspect you would be happier going with MySQL directly no matter what as neigther HABMin nor openHAB can chart anything but numbers and it sounds like you want something more universal.

That being said, there is nothing about saving the data to two databases that I can see that would cause problems in and of itself. However, if you are on an SD card or have limited space having two copies of the data could be a concern. Also, I agree that the would grow unruly as time passes and you have to manage years worth of data.

Indeed, openHAB can make charts but don't confuse openHAB charts, rrd4j charts, and HABMin charts. They are three different things. 

• openHAB: I just looked at the code and the way that openHAB generates its charts through the charting servlet only ever sees an image of the chart as well. OpenHAB never actually sees the data. The only things that sees the underlying data are the persistence bindings themselves. Adding the ability to export the data as CSV would require changes that would impact the openHAB core and all of the persistence bindings, not to mention the creation of a UI that does not exist (remember, HABmin does not equal openHAB). Its possible but would probably only be considered for development on the openHAB2 baseline.
• rrd4j: We can ignore rrd4j charts at the moment because all the openHAB UI ever sees is the image of the chart. OpenHAB never has access to the underlying data there. 
  
• HABmin: HABMin is a third party plugin that is being developed by Chris Jackson. HABmin is incomplete and development has stopped on it so he can focus on HABmin 2, which is also incomplete but undergoing continued development. The ability to query for and export data as CSV may be something on his roadmap or something he might consider as an enhancement. I have not looked at his code to see whether he is actually getting the charts from but I suspect he might have access to the underlying data. But if you want to pursue this that is the place to look. 

Also, don't underestimate the complexity and amount of work that it would take to build the UI that you are asking for. Just because the data exists does not mean it is super easy to just build a table in the UI with the data for you to select from. I have experience with this sort of thing and it is WAY more complicated than it looks unless you built your software with that in mind from the start. I suspect it won't be much of a priority for some time unless HABmin2 is structured where it is easy to implement. I can mainly speak for myself but I think that your desires (i.e. the ability to study, manipulate, and publish charts) are shared by a minority of users (one piece of evidence is the choice of rrd4j as the default persistence database) so building in support to CSV export has not been a priority.

Rich

[michael c]

ok, very interesting. that's interesting how the graphs are generated. 

i'm mostly using habmin OG and not familiar with anything else. (i'll check on Habmin 2 again, love being beta)

when i'm looking at a graph there are two tabs for at the bottom of it for Graph or Table. when i click for the table i see data for perhaps only one column, and, amazingly, it is not copy-able. if all the data i'm plotting in the graph that's being displayed was listed in this tab, in spreadsheet format, and i could just mouse it and copy it, i would be super-thrilled. i'm not even sure who might be able to do anything about that, openhab or habmin.

i keep looking into rrd4j because it's quite popular and i can't see the attraction.  i'd like to try some smaller mysql-like db's, though.  
you're super helpful, thanks

Mike

[Rich Koshak]

This sounds like a Habmin request. I'd recommend posting something to the openHAB community forum tagged Habmin2, or sending a feature request to Chris at the Habmin2 github.

When you use openHAB's charting capability all you get is an image, no table so I'm pretty sure you are looking at Habmin. openHAB can't do anything to help.

I can say for me the attraction of rrd4j is that I know my database will remain the same size no matter what. I never have to worry about it running out of space over the years. Since I pretty much don't care about any data older than a week (and even then it is just minor curiosity) and my primary use of persistence is so have my values restored on restart and I can check when the last time an item was changed in my rules. And even then, rrd4j's limitation of only saving numerical data has not been a hindrance. It is small, fast, does exactly what I need it to do and doesn't require any long term maintenance. Its the perfect fit for my needs.

If I had any "data scientist" inclinations with my home automation I would almost certainly choose something else but for the most part, I want my home automation to work in the background without needing my attention. In fact, anytime I have to resort to using any user interface (Habmin, Habdroid, etc) I consider it a home automation design failure (except for when I'm developing and configuring something new in which case the UIs can be invaluable). With that perspective, rrd4j is perfect.

Rich