# OH3: how to persist data to an external relational database

**URL:** <https://community.openhab.org/t/oh3-how-to-persist-data-to-an-external-relational-database/110732>\
**Category:** Solutions\
**Tags:** mysql, oh3\
**Created:** [December 21, 2020, 2:14pm UTC](https://community.openhab.org/t/oh3-how-to-persist-data-to-an-external-relational-database/110732 "2020-12-21T14:14:19Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![edolis](https://community.openhab.org/user_avatar/community.openhab.org/edolis/32/62294_2.png) [@edolis](https://community.openhab.org/u/edolis)\
**Post date:** [December 21, 2020, 2:14pm UTC](https://community.openhab.org/t/oh3-how-to-persist-data-to-an-external-relational-database/110732/1 "2020-12-21T14:14:19Z")

</div>

Hello everyone.  
Just wanted to share the results of the investigation and my current approach in persisting data to an external database (mySQL in specific) from OH3. Absolute beginner so please take what I say about OH3 with critical spirit.

**What is all this for?**  
Once your data are stored in a relational database, you can - if you are handy at SQL - define the relationships between data to be able to reaggregate and query them more quickly.  
In any case, you can extract and work on the data using common spreadsheets like MS Excel

**The problem**  
In short, I need to persist data from a sensor I made (ESP8266) which is broadcasting data using a MQTT broker. OpenHAB connects the broker and displays data. Nice.

- _I wish I could_  
I wish I could store the data, after charting, to a mySQL database -as I need them stored accurately to further develop my project (_in the future I think of using different readings of luminosity,humidity,temperature,soil humidity to find the ideal condition to let different plants grow in my orchard, by controlling shading, watering and the like_)
- _What I tried_  
I tried setting up a mySQL persistence in OH3. I did not know exactly what I would end up to, to I tried setting up. Various issues, but i fixed them
- _What was not satisfying_  
I think natively there is just a partial coverage of my “wish I could”. Limitations being:
  1. When you’ve got a sensor board broadcasting different data at the same time, _data seem to be always treated individually by openHAB_ -I still have to investigate how I can attach metadata and if this can help to fix the issue, but to make it short the practical effect is that you’ll end up having the JDBC mySQL persistence create different tables for each of the data items.  
Below, for instance, the three tables created when persisting the three readings of my sensor board  
 ![image](https://community-openhab-org.s3.dualstack.eu-central-1.amazonaws.com/original/3X/4/8/4802ba71d849c258f4f7646fd6fe9712995d66de.jpeg)  
This is not OK for me for some reasons:  
a) you _miss the link_ between the data. Three readings for the same original timestamp are being treated as (potentially - following item on this) three unrelated reading. This is messy when you want to analyze data, using the timestamp to identify a set of data read “at the same time”  
b) _openHAB optimization_, to compress data I bet, posts data to these tables only on value changes. That means that if you have a equipment sending 3 sensor data every second and of the three sensor data one is constant while the other is everchanging, you’ll get in one table 1 line (0 potentially) and 10 in the other.  
c) _memory use in the database_. In mySQL every datatime uses 5bytes. a DECIMAL(3,1) uses 2bytes. So to store the original package set of 1 timestmp and three readings in a proper form, I need 5+2+2+2=11 bytes. With OH persistence -which creates a table with a DATETIME and DOUBLE fields - (neglecting the optimization mentioned) (5+4)\*3=27 bytes. Things get obviuosly worse if your thing has multiple sensors and broadcasts even more data readings at the same time. Moreover double is an approximate value, while DECIMAL is exact value (it will make analysis easier to read, I’m aware that obviously the sensor readings are approx 🙂 ).  
d) OH optimization is good but brings data loss. Two examples: if a reading is not being broadcasted when it stays constant, you cannot simply compute an average with a database function, as a 10 minutes value gets the same relevance of a one time glitch. Second one is that you don’t know if the silence from OH means that the data is constant or simply your sensor is dead and is not broadcasting anymore. Not good.

OK, enough for the foreword.

**My current setup to fix this**  
a) have the sensor broadcast a JSON packet through MQTT and create an Item in OH to get it  
`{ "time": "2020-12-21 14:53:37.", "temperature": 13.6, "humidity": 66.2, "senstemp": 12.7 } `  
b) potentially use JSONPATH to extract the single data and use them to feed Items showing the three data in the OH UI  
c) OH will persist the data in a table similar to this

 ![image](https://community-openhab-org.s3.dualstack.eu-central-1.amazonaws.com/original/3X/f/f/ff8c97673da1378aa81e8b06b1c09c2421ac10a7.png)  
don’t get confued by the time value -more on that in item (e). It should be an actual timestamp from OH

d) set up a new table with optimized structure, something like

 ![image](https://community-openhab-org.s3.dualstack.eu-central-1.amazonaws.com/original/3X/8/2/822713819131363a4d3c63014ce81329651151dd.png)  
e) add a trigger to the table created by the JSONDB mySQL persistence, that is:  
CREATE DEFINER=`root`@`localhost` TRIGGER `bundle_tsdc_0007_BEFORE_INSERT` BEFORE INSERT ON `bundle_tsdc_0007` FOR EACH ROW BEGIN  
INSERT INTO data\_tsdc SET date=NEW.value-\>“$.time”,temp=NEW.value-\>“$.temperature”,tempsens=NEW.value-\>“$.senstemp”,  
umidita=NEW.value-\>“$.humidity”;  
SET NEW.time=“2000-01-01 00:00:00”;  
END

basically what I am doing here is - when a new data point is pushed to the table by OH, I

1. pick the values and push them to the optimized table
2. alter the timestamp to a standard time point, here “2000-01-01 00:00:00”. This will bring to overwrite the previous entry and have - actually - OH store just one row in this table.

Result is what I expected, that is:  
optimized table

 ![image](https://community-openhab-org.s3.dualstack.eu-central-1.amazonaws.com/original/3X/e/4/e429986a1e15ada23ba23d1e294774aecd133246.jpeg)  
OH transit table  
 ![image](https://community-openhab-org.s3.dualstack.eu-central-1.amazonaws.com/original/3X/b/4/b4371ae3a085fff6d09425f3f9109166df082f17.png)  
perk of this are

- I can read directly in the DB when was the last trasmission from the equipment
- if any error occurs during the data push to the optimized table, the unimported value will sit there and won’t get lost. It can be examined at anytime and recovered by the db admin.

Hope this helps some other newbie.

**Improvements**

- looking into this [Design Pattern: Group Based Persistence](https://community.openhab.org/t/design-pattern-group-based-persistence/15098) to restrict mySQL persistence to this bundle only, as the rest is not useful anymore
- Trying to figure out if I can add a field in the OH table to identify the equipment, so that I can control easily at database level (extracting the last timestamp from the data) when every equipment transmitting sensor data was seen. Can be done adding an equipment ID to the JSON string, though.
- Would be nice to improve the JDBC persistence with some of this. Not sure where to start tough. Not even found the code of the persistence in the GIT repo.

---

<div class="post-metadata">

**Author:** ![rlkoshak](https://community.openhab.org/user_avatar/community.openhab.org/rlkoshak/32/75251_2.png) [@rlkoshak](https://community.openhab.org/u/rlkoshak)\
**Post date:** [December 21, 2020, 6:32pm UTC](https://community.openhab.org/t/oh3-how-to-persist-data-to-an-external-relational-database/110732/2 "2020-12-21T18:32:56Z")

</div>

> [@edolis](#):
>
> I wish I could store the data, after charting, to a mySQL database -as I need them stored accurately to further develop my project

Maybe just a typo but you have it backwards. It’s stored to a database first and the charts are generated from there.

> [@edolis](#):
>
> When you’ve got a sensor board broadcasting different data at the same time, data seem to be always treated individually by openHAB -I still have to investigate how I can attach metadata and if this can help to fix the issue, but to make it short the practical effect is that you’ll end up having the JDBC mySQL persistence create different tables for each of the data items.

openHAB is very self centered when it comes to persistence. It saves data for it’s own purposes (analysis is not one of it’s purposes) in it’s own ways and that usually means _per Item_. If you want something else, you need to do that outside of openHAB. openHAB is a home automation platform, not a data collection and analysis platform.

> [@edolis](#):
>
> posts data to these tables only on value changes.

Perhaps by default. But you can define one an Item, by Item basis if you want, exactly when and how openHAB stores data. See the docs ([Persistence | openHAB](https://www.openhab.org/docs/configuration/persistence.html)). You can save periodically (e.g. everyMinute), on change, on update, or on command.

> [@edolis](#):
>
> Moreover double is an approximate value, while DECIMAL is exact value

DOUBLE more closely represents the value as it’s stored in memory in openHAB before it gets stored anyway. And again, persistence is designed for openHAB’s use.

> [@edolis](#):
>
> OH optimization is good but brings data loss

As I said, you have full control over this.

> [@edolis](#):
>
> when every equipment transmitting sensor data was seen. Can be done adding an equipment ID to the JSON string, though.

Or you can do this with an Item and the timestamp profile.

> [@edolis](#):
>
> Would be nice to improve the JDBC persistence with some of this. Not sure where to start tough. Not even found the code of the persistence in the GIT repo.

One important thing to understand is openHAB’s interface to databases is, by necessity, the lowest common denominator between SQL (JSBD), NoSQL (e.g. MongoDB), Time Series (e.g InfluxDB), and round robin databases (e.g. rrd4j). And openHAB’s purpose with Persistence is to save the states of _Items_ to:

- generate charts of _Items_ for display
- restore the state of Items to their most recently saved value on startup
- use the historic state of an _Item_ (or aggregation thereof) in Rules.

Combining Items into a table with multiple columns isn’t part of that because the concept of tables and columns doesn’t even exists in all the supported databases. Adding metadata and extra timestamps and such is supported because openHAB doesn’t need it. So anything like what you’ve done, (thank you for posting it by the way), will necessarily have to be done outside of openHAB. If you want something more friendly for analysis outside of openHAB, you should either bypass openHAB and save it to a database directly or do something like you have here and create something outside of openHAB to restructure the data as desired.

The rest of the issues are not actually issues as you have full ability to define which Items are saved under what conditions.

---

<div class="post-metadata">

**Author:** ![edolis](https://community.openhab.org/user_avatar/community.openhab.org/edolis/32/62294_2.png) [@edolis](https://community.openhab.org/u/edolis)\
**Post date:** [December 21, 2020, 6:40pm UTC](https://community.openhab.org/t/oh3-how-to-persist-data-to-an-external-relational-database/110732/3 "2020-12-21T18:40:25Z")

</div>

> [@rlkoshak](#):
>
> Maybe just a typo but you have it backwards. It’s stored to a database first and the charts are generated from there.

well, after, before -not really important for me - I need just data storage and charting in OH

> [@rlkoshak](#):
>
> If you want something else, you need to do that outside of openHAB.

Well, I expected that using a JDBC mySQL persistence was finalized at having the data persisted in usable form in a standard DB. If data can be persisted only as OH desires, I don’t get it -what’s the point of bothering using a specific persistence?

---

<div class="post-metadata">

**Author:** ![rlkoshak](https://community.openhab.org/user_avatar/community.openhab.org/rlkoshak/32/75251_2.png) [@rlkoshak](https://community.openhab.org/u/rlkoshak)\
**Post date:** [December 21, 2020, 7:05pm UTC](https://community.openhab.org/t/oh3-how-to-persist-data-to-an-external-relational-database/110732/4 "2020-12-21T19:05:59Z")

</div>

> [@edolis](#):
>
> having the data persisted in usable form in a standard DB

That’s quite a subjective term; “usable”. It persists the data in a form that’s usable to itself. It persists it in a form that is usable for a large number of external tools to analyze. It may not be “usable” for your specific use case but that doesn’t negate all the rest.

> [@edolis](#):
>
> what’s the point of bothering using a specific persistence?

Because different people have different requirements and needs. Some are able to use the data just as openHAB stores it. Some may have tools and/or preferences for different types of databases.

Ultimately, it’s because at some point a developer decided they needed to support database X and they spent their time coding up that support and submitted it to the project.

---

<div class="post-metadata">

**Author:** ![edolis](https://community.openhab.org/user_avatar/community.openhab.org/edolis/32/62294_2.png) [@edolis](https://community.openhab.org/u/edolis)\
**Post date:** [December 21, 2020, 7:11pm UTC](https://community.openhab.org/t/oh3-how-to-persist-data-to-an-external-relational-database/110732/5 "2020-12-21T19:11:01Z")

</div>

> [@rlkoshak](#):
>
> That’s quite a subjective term; “usable”.

OK I hope readers understand what I mean.  
Usable the same way relational databases are usable. You can also dump the individual data points in a folder as text files, that’s a database in broad terms, not a database usable for data analysis and processing. Lacking the relations between data, in the logical and time frames.  
Not here for arguments, though. If you have suggestions to change the title of the topic, please share.

---

<div class="post-metadata">

**Author:** ![rlkoshak](https://community.openhab.org/user_avatar/community.openhab.org/rlkoshak/32/75251_2.png) [@rlkoshak](https://community.openhab.org/u/rlkoshak)\
**Post date:** [December 21, 2020, 7:22pm UTC](https://community.openhab.org/t/oh3-how-to-persist-data-to-an-external-relational-database/110732/6 "2020-12-21T19:22:42Z")

</div>

No, it’s a good tutorial. If I’d change anything I’d include MySQL/MariaDB in the title since it’s only applicable to those databases. Even other SQL type databases have different ways to set up triggers and not all databases that OH 3 supports does this tutorial even make sense for.

I’ve never argued against the tutorial itself. I’m just trying to explain why OH persistence works the say it does and correct the parts that impliy that you can’t configure how often the data is stored which covers a lot of your concerns.

---

<div class="post-metadata">

**Author:** ![edolis](https://community.openhab.org/user_avatar/community.openhab.org/edolis/32/62294_2.png) [@edolis](https://community.openhab.org/u/edolis)\
**Post date:** [December 21, 2020, 9:11pm UTC](https://community.openhab.org/t/oh3-how-to-persist-data-to-an-external-relational-database/110732/7 "2020-12-21T21:11:06Z")

</div>

changed the title reference to relational databases. mySQL is not really important (it was the only relational I knew in the list when I had to pick one) - SSRV, mySQL, etc they have their own dialect things, but it takes nothing to translate using internet resources.
