Feedback on my first integration - backup to BigQuery

Third party integrations
1

I’m fairly new to Home Assistant and this is my first shot at putting an integration together. I felt I wanted to save the data from my state and event tables and be able to analyze some of the data from them in Google BigQuery. This integration adds three services: bigquery_backup.backup_all, bigquery_backup.backup_states and bigquery_backup.backup_events that pull the latest data from your local event/state tables and pushes it up to BigQuery. In my local, I setup an automation to call the “all” one every few hours so I could keep my information updated.

Right now, it uses the recorder integration directly and so one nice thing is that it’ll work even if you use an in-memory sqlite db, or any other type of db really (as far as I know).

Since I’m new to this, I’m not sure what the normal approaches are for these things, but if anybody has any feedback or anything, I’d love to hear it. The repo is here: https://github.com/mryckman/home_assistant_bigquery_backup

1 Like
2

This is exactly what I didn’t know I needed. I am going to play around with it tonight. I was thinking about what to do with the historical data… I’d rather not lose it, but I don’t want to keep it all within HA (slowing things down any). Thank you for sharing.

3

Do you have some example of what can be done with this?

4

This is mostly for if you want to do analytics on your state and event data. The state table holds a record of every time one of your entities reports a new state or checks in. So if a light turns on, it will add a new record in the state table. The events table is a log of every event that is fired. This creates a fair amount of data and so you usually can’t store all that much of it and might not even want to directly on your HA device anyway.

But I was interested in analyzing some of the data from those tables. It’s mostly stupid stuff, but it could be for whatever is interesting to you. For example, maybe you just want to know when your bedtime routine fires every night and plot it over the course of months or the year - you could use those tables to find that answer.

BigQuery is a cloud database that has a decent free tier (though it can end up costing you money if you put a lot in there or use it a ton - so heads up there). BQ is great at taking in a bunch of data like this and then helping you analyze it.

This tool just copies the records from your state and event tables into BigQuery so you don’t lose them.

5

Can I make a scatter plot of inside vs outside temperature to evaluate my house insulation?

This is something very difficult to with home assistant or Grafana as they plot sensor data against time only.

6

Sure - I actually made one of those! But note that this is just the data. You’d still have to know how to write the SQL to get what you want out of BigQuery, and/or how to use a BI tool like Power BI/Tableau/Data Studio/etc. to generate that graph. The key is really just that this only gets you the data itself.

1 Like
7

Just thought I’d check - anybody get a chance to try this? Would there be interest in submitting it to the main codebase?