And while i have a listening ear on this | Home Assistant | Page 1

uncut solstice Jun 15, 2024, 9:52 PM

#

It is possible, I did it too. But... there is no standard way to do it and it is hard. Since you say you're a dba, I assume you know your way around in sql. If you don't, you should really not even try this, as you will very likely corrupt your new HA installation....
If you still want to go ahead, I suggest you install the Terminal & SSH Add-on first, that will give you a terminal window from which you can run sqlite3 to execute your statements.

elfin galleon Jun 15, 2024, 9:54 PM

#

if i recall, spook can do that
https://github.com/frenck/spook

#

at your own risk*

torn blade Jun 15, 2024, 9:54 PM

#

🙂 after almost 3 decades working with SQL / data, i think i have a fairt chance 🙂

uncut solstice Jun 15, 2024, 9:55 PM

#

In the configuration page for the Terminal & SSH add-on add sqlite to the Packages list so that you do not have to install the sqlite package every time.

torn blade Jun 15, 2024, 9:57 PM

#

SQLite web looks like it uses standard T-SQL 92 compatible code, nice !

uncut solstice Jun 15, 2024, 9:57 PM

#

It's over a year ago for me now, but I do have some of my notes and scripts I used still lying around. You can have those to get you started, but I will not be able to assist you a lot.

torn blade Jun 15, 2024, 9:58 PM

#

i would really appreciate any and all docs you can share

elfin galleon Jun 15, 2024, 9:58 PM

#

dont forget to create backups before modifying things ^^
(also keep in mind that you can use more powerful software to edit the db as you can copy it on and off system)

torn blade Jun 15, 2024, 9:59 PM

#

Half the fun is tinekering around and figuring out stuff

uncut solstice Jun 15, 2024, 10:00 PM

#

What I did was to attach a 2nd database in my domoticz machine, then select and insert all data I wanted to transfer into that transfer database. Copy the transfer database over into the home assistant machine, and from there do the inverse, copy the data from the transfer db into the HA database.

torn blade Jun 15, 2024, 10:01 PM

#

You did not export the data in CSV format ?

elfin galleon Jun 15, 2024, 10:03 PM

#

oh another thing, make sure ha doesnt actually run while modifying / changing out the db

uncut solstice Jun 15, 2024, 10:03 PM

#

Correct, I did not. I used a sqlite db to transfer the data. This 1st script contains the statements I used in the domoticz instance. As I said, use them as an example. DO NOT RUN THEM blindly, and do NOT RUN THE ENITRE SCRIPT IN ONE GO...

📎 Domoticz_to_Home_Assistant_sql_scripts_Dz.sql

uncut solstice Jun 15, 2024, 10:06 PM

#

elfin galleon oh another thing, make sure ha doesnt actually run while modifying / changing ou...

We're not going to change out HA's database or modify HA's database structures, we're only transferring data into and out of the database, so no worries about having HA running while you're doing this. It took me more than a few weeks to discover everything, so I would not have been able to it without having HA running during that time.

#

Making proper backups is very sound advice though 😉

torn blade Jun 15, 2024, 10:07 PM

#

Making backups is THE advice if you ask a DBA 😉

elfin galleon Jun 15, 2024, 10:08 PM

#

sqlite doesnt really work like a dedicated sql server. its simply a file thats being read and written to with possibly some in memory caching.
adding a second process to access it may be enough of a disturbance to kill it. at best ha notices and dumps it as corrupt and you can simply copy it back and its fine. at worst you may leave the file in a corrupt enough state for the data to be lost

torn blade Jun 15, 2024, 10:08 PM

#

Ahh... your just mounting a temp 2nd DB in HA for the transfer of the data

#

Nice.... looks like there is even code to correction of the datetime datatype

uncut solstice Jun 15, 2024, 10:09 PM

#

That 1st script contains statements I used in the domoticz machine to create the transfer database. You can try that multiple times.

torn blade Jun 15, 2024, 10:09 PM

#

Check !

torn blade Jun 15, 2024, 10:12 PM

#

elfin galleon sqlite doesnt really work like a dedicated sql server. its simply a file thats b...

Thanks for the warning, i'll be sure to make backups. And in the case the second DB file is problematic, i can allways convert the data to a (rather large) insert script

elfin galleon Jun 15, 2024, 10:12 PM

#

moving stuff around like that will be fine, id just turn off the ha core container for a moment while you do the moving

torn blade Jun 15, 2024, 10:13 PM

#

Use the domotics sqllite enginge to generate insert scripts you can run by using sqlite web on HA

torn blade Jun 15, 2024, 10:13 PM

#

elfin galleon moving stuff around like that will be fine, id just turn off the ha core contain...

ah.... ofcourse... (noob here)

#

HA assumes its the only process working in the DB

elfin galleon Jun 15, 2024, 10:14 PM

#

pretty much yes

uncut solstice Jun 15, 2024, 10:15 PM

#

What I'm doing is to insert the data from DZ before the data set already created in HA. Since HA stores the energy data in a cumulative format (Long Term Statistics or LTS), there will be a point between those two sets where the sequence is broken. I did not try to adjust this break myself. Instead I used the developer tool 'STATISTICS' to find the break and adjust only the 1st data point of the HA data to restore the sequence. You'll see what I mean once you've used the STATISTICS tool once.

torn blade Jun 15, 2024, 10:16 PM

#

so, its not just raw data that gets compressed to large timeslots when it gets older ?

uncut solstice Jun 15, 2024, 10:18 PM

#

Correct, HA doesn't play very nice when you run long running statements, but it still applies basic locking mechanisms, so as long as you keep the batches small, and possibly avoid running larger DDL around the :00 times, you'll have no issues at all.

elfin galleon Jun 15, 2024, 10:18 PM

#

ha stores both the change and an absolute value in the history

#

depending on what is being displayed that helps to keep processing times down

uncut solstice Jun 15, 2024, 10:19 PM

#

Indeed. statistics.state has the absolute value at the time of measurement, statistics.sum has a running total of the change between this and the last row.

elfin galleon Jun 15, 2024, 10:19 PM

#

im not quite sure how the technical side of the modify statistic works in regards to either field, but it effectively adds a static offset to all of the following sets of data

#

the adjustment will be dropped once it hits the long term side of things if i recall, not sure on that anymore

uncut solstice Jun 15, 2024, 10:20 PM

#

But only for those rows where the statistics_meta table has a has_sum = 1.

torn blade Jun 15, 2024, 10:21 PM

#

elfin galleon ha stores both the change and an absolute value in the history

Thaty figures... i use techniques like that to keep the measurement DB's down to 60 TB'ish, if i would not compress older data to large timeslots i'd end up with some behemoth that would be a PB by now 😛

uncut solstice Jun 15, 2024, 10:21 PM

#

The Enegry dashboard (and other things using the LTS) only look at the statistics.sum column

torn blade Jun 15, 2024, 10:22 PM

#

so rows with has_sum = 1, describe a datetime range, and has_sum = 0 is absolute data?

#

ah, there is a page describing the DB https://www.home-assistant.io/docs/backend/database/

#

and https://data.home-assistant.io/

uncut solstice Jun 15, 2024, 10:27 PM

#

Almost. has_sum = 1 means state and sum columns are filled; these are created from entities that have a state_class = total_increasing or state_class = total attribute.
entities that have a state_class = measurement will result in a statistic_meta row that has has_mean = 1. The rows in statistics for these statistics will get their min, max and mean columns filled.
As every entity can have only 1 attribute called state_class, the has_sum = 1 and has_mean = 1 are mutually exclusive. i.e. rows in statistics will either get their state + sum columns populated or their min, max and mean columns.

torn blade Jun 15, 2024, 10:30 PM

#

hmmm... i think i'm beginning to understand he <says optimistically>

uncut solstice Jun 15, 2024, 10:30 PM

#

II see I did not keep a lot of the statements used in the HA database.

📎 Domoticz_to_Home_Assistant_sql_scripts_HA.sql

torn blade Jun 15, 2024, 10:31 PM

#

it takes some time to understand a new datamodel.

#

ow? its that simple to attach .db files to sqlite ?

uncut solstice Jun 15, 2024, 10:33 PM

#

The HA database model isn't overly complicated. It's just that it is not very well normalized and not all data is available in the database sadly. It's been designed apparently mostly by developers, not a dba 😉

elfin galleon Jun 15, 2024, 10:33 PM

#

the file is effectively the database. attach is just the command to open a "connection" to it (read the file)

torn blade Jun 15, 2024, 10:34 PM

#

uncut solstice The HA database model isn't overly complicated. It's just that it is not very we...

Bwahahahaha.... thats my life in a nutshell

uncut solstice Jun 15, 2024, 10:34 PM

#

elfin galleon the file is effectively the database. attach is just the command to open a "conn...

correct

#

are you familiar with sqlite or do you use another sql dialect normally?

torn blade Jun 15, 2024, 10:35 PM

#

its been far too long ago that i've played around with stuff like this 🙂

I thank you guys for giving me a flying start, truly much apprecianted!

uncut solstice Jun 15, 2024, 10:35 PM

#

i.e. do you need hints on how to read and write dates/datetimes?

torn blade Jun 15, 2024, 10:36 PM

#

nah, i'm famliar with unix epoch to UTC conversions in my own projects

uncut solstice Jun 15, 2024, 10:37 PM

#

excellent. I normally work in T-SQL (MS SQL), so this was another big hurdle for me 🙂

torn blade Jun 15, 2024, 10:37 PM

#

Same here, MSSQL DBA since version 6.5

uncut solstice Jun 15, 2024, 10:38 PM

#

same

torn blade Jun 15, 2024, 10:39 PM

#

wow... just read that HA even has support for Jupyter

uncut solstice Jun 15, 2024, 10:39 PM

#

I'm looking to see if I can find some more queries I used, because that last one falls a bit short...

torn blade Jun 15, 2024, 10:40 PM

#

i will report back if i can make it work, andwe can exchange notes ! 🙂

#

Queries are pretty fast, considering it's only running on a RPI 5

uncut solstice Jun 15, 2024, 10:44 PM

#

One side note: HA keeps one row in the LTS for each hour. Domoticz however has one row per day. i.e. you best insert that at 00:00 each day, so that the energy dashboard has at least one row for each day. It will look at bit strange when you look on a per day view, but that's all the data we have, so nothing to do about that. I made an error somewhere when inserting the data, probably due to Daylight saving time, that makes mine show up as 02:00, but this still works.. 🙂

#

For example

torn blade Jun 15, 2024, 10:46 PM

#

ey, thats a good tip....

#

And the reason why i always use UTC (because i have data coming in from all timezones)

uncut solstice Jun 15, 2024, 10:48 PM

#

Right, I love UTC too. But sadly I don't always get to have a say in this...

torn blade Jun 15, 2024, 10:49 PM

#

well... sometimes you must make space for dev's to fubar, and then swoop in to save the day.

#

😉

#

again, thanks for the tips.

I'm off to bed, need to work @ 10 tomorrow.

uncut solstice Jun 15, 2024, 10:50 PM

#

good night

torn blade Jun 15, 2024, 10:51 PM

#

https://tenor.com/view/hi-gif-26886134

#And while i have a listening ear on this