Giter VIP home page Giter VIP logo

homeassistant2influxdb's Introduction

Home Assistant - History to InfluxDB

Adapted from dseifert now archived version.

Working with home assistant core-2022.7.6 and influx v2.3.0.

Important

Quality of the script is also disputable given that it is a one-off. Use of MySQL/MariaDB is hard-coded, but (untested) lines of code to work with the SQLite dtabase are included (search for SQLite).

Use at your own risk. (Backups recommended)

Introduction

Home Assistant's recorder component allows to store historical data in a database. Database access is handled by SQLAlchemy, with the default database in SQLite. MySQL/MariaDB is also quite popular and so is PostgreSQL.

However, if one wants to store a lot of data over a long period of time, neither of these options gives the best performance. Instead, a dedicated time-series keeping database format like InfluxDB allows best retrieval and storage of the data.

However, if you only figure this out after already having assembled a huge amount of historical data, there is no option to migrate your data.

This is an attempt to do exactly that. It is a one-off migration of data to InfluxDB. Afterwards you should setup the InfluxDB integration to directly store data to InfluxDB (and only keep a couple of days to few weeks at most in the traditional database for the logbook and history components).

This version contains adaptations on the original. By setting the table argument to both, statistics will be used to build out historical data for entities.

References:

Caveats

This script is rather simple and limited to my use-case. As it is a one-off and I do not have other setups readily available, I limited it to the specific task at hand. However, it should be easily adaptable.

Namely, this handles MySQL / MariaDB only. Adding SQLite, PostgreSQL etc could be done trivially, I believe.

Setup

In order to not duplicate logic, the script uses the InfluxDB component of Home Assistant directly.

I've tested this on Ubuntu 18.04 with Python 3.7.

Setup:

  1. sudo apt install python3 python3.7-dev
  2. git clone <this repository> migrate2influxdb
  3. cd migrate2influxdb
  4. git clone --depth=1 [email protected]:home-assistant/core.git home-assistant-core
  5. python3 -m venv .venv

Dependency installation:

  1. . .venv/bin/activate
  2. pip install -r home-assistant-core/requirements.txt
  3. pip install -r requirements.txt

Run script:

  1. Copy your InfluxDB configuration from Home Assistant to influxdb.yaml. This should be the file that you include via influxdb: !include influx.yaml in your installation (i.e. it does not start with influxdb:\n!). It must not include any !secret statements but rather the token (for v2) or user/password (for v1) explicitly
  2. . .venv/bin/activate
  3. python homeassistant2influxdb.py ... (see -h for options to specify your MariaDB/MySQL credentials)

homeassistant2influxdb's People

Contributors

bahuma20 avatar dseifert avatar fabyte avatar ladlap45 avatar maaxion avatar

Stargazers

avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar

Forkers

bahuma20 fabm3n blaet jfurtner sschw56 valsr cobryan05 dsteinkopf

homeassistant2influxdb's Issues

sqlite3.OperationalError: near "SELECT": syntax error

Hi, I'm trying to use the script to recover a hole of missing data in InfluxDB.
As I was using the SQLite DB created by HA 2022.8.1, I thought I should install the dependencies like so:

git clone https://github.com/Maaxion/homeassistant2influxdb migrate2influxdb
cd migrate2influxdb/
git clone --depth 1 --branch 2022.8.1 https://github.com/home-assistant/core.git home-assistant-core
python3 -m venv .venv
. .venv/bin/activate
pip install -r home-assistant-core/requirements.txt
pip install -r requirements.txt
python homeassistant2influxdb.py --type SQLite --database /mnt/hgfs/influxdb/image/home-assistant_v2.db --dry-run

When typing the last command I get this error:

(.venv) user@debian:~/mig/migrate2influxdb$ python homeassistant2influxdb.py --type SQLite --database /mnt/hgfs/influxdb/image/home-assistant_v2.db --dry-run

option --dry-run was given, nothing will be writen on InfluxDB
Migrating home assistant database statistics, states to Influx database /mnt/hgfs/influxdb/image/home-assistant_v2.db and bucket homeassistant_missing
Traceback (most recent call last):
  File "/home/user/mig/migrate2influxdb/homeassistant2influxdb.py", line 358, in <module>
    main()
  File "/home/user/mig/migrate2influxdb/homeassistant2influxdb.py", line 151, in main
    cursor.execute(tmp_table_query)
sqlite3.OperationalError: near "SELECT": syntax error

Can you please suggest what I'm doing wrong?
I also tried downloading the same HA version listed in the README, but I get the same error.

Looking at the sources, it seems to fail on:

homeassistant2influxdb/homeassistant2influxdb.py

Lines 345 to 350 in c25dc11

return """CREATE TEMPORARY TABLE IF NOT EXISTS state_tmp
SELECT max(states.attributes_id) as attributes_id, states.entity_id
FROM states
WHERE states.attributes_id IS NOT NULL
GROUP BY states.entity_id;
"""

but if I run the SELECT statement using SQLite on the same database, it seems to be working...

(.venv) user@debian:~/mig/migrate2influxdb$ sqlite3 /mnt/hgfs/influxdb/image/home-assistant_v2.db \
"SELECT max(states.attributes_id) as attributes_id, states.entity_id \
FROM states \
WHERE states.attributes_id IS NOT NULL \
GROUP BY states.entity_id;"

8984|switch.presa_mss310h_main_channel_2
66891|update.fritz1_fritz_os
66880|update.fritz2_fritz_os
66089|var.total_car_fuel_liters
181|var.zero_emissions_km
... CUT ...
71732|weather.casa
66854|zone.home

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.