Memory db with cloud backup for analytics

I use a rpi4 with an sd card and so I’ve been worried about wearing it out. But I also wanted to keep a lot of data for analysis/data science projects.

I put together a custom component:

This lets me copy my recorder data to Google BigQuery, which has a decent free tier and is cheap even after that, and then switched to using a memory db for sqlite. Now, nothing hits the sd card, but I get to keep the data in a good analytics platform.

The “bigquery_backup” component just looks at the last record it copied to BQ and then grabs any records from the recorder that are after that and pushes them to BQ. You actually call the backup with a service call, so it’s easy to do through an automation. This way, I can run the backup every 15 minutes (and have it trigger on any of the home assistant events) and it just copies in whatever changed recently. I’m hopeful this will make sure I rarely, if ever, end up losing any of the data from the memory db.

I’ve just been getting started on actually using the data: