SQLite to Influxdb migration of historical data

ASL07 · August 7, 2021, 10:15am

Hi @ linisgre
Thanks for sharing your script. I hope you could answer to a couple of questions I have:

Does the script respect the include: entities: property from the yaml file? Or will it migrate all entities data to influxdb? By having a look at the code, i think it does only migrate the entities included in the yaml file.
In my case, I am already running the influxdb integration. Am I supposed to disable the integration and delete data from influxdb before running the script. Or can I run it anyway, and it will take care of “merging” historical data with the data already present on influxdb?

Regards

linisgre · August 21, 2021, 7:36pm

The script itself does nothing with the influx.yaml, it does not even access InfluxDB itself, that’s purely handled via the code in HA. I don’t recall whether it is so, but I believe that this means it will honor the include lines, i.e. only port the stuff given there.
In my case I did it with a clean conversion. The script itself will not handle any merging. I don’t know whether the code in HA will check duplicates, but I doubt it. What you could do is check your InfluxDB for the date of the earliest element, and adjust the query in the script to only include data before that timestamp.

jotie · November 9, 2021, 8:50pm

I tried the script for SQLite and core_2021.11.2.
I used debian and python 3.9.
I have errors, I assume they are related to elements in the home-assistant_v2.db which are not accepted (anymore?).
These are the errors:

python homeassistant2influxdb.py
Traceback (most recent call last):
  File "/home/jo/Downloads/migrate2influxdb/migrate2influxdb/homeassistant2influxdb.py", line 190, in <module>
    main()
  File "/home/jo/Downloads/migrate2influxdb/migrate2influxdb/homeassistant2influxdb.py", line 91, in main
    influx_config = schema(influx_config)
  File "/home/jo/Downloads/migrate2influxdb/migrate2influxdb/.venv/lib/python3.9/site-packages/voluptuous/schema_builder.py", line 272, in __call__
    return self._compiled([], data)
  File "/home/jo/Downloads/migrate2influxdb/migrate2influxdb/.venv/lib/python3.9/site-packages/voluptuous/validators.py", line 215, in _run
    return self._exec(self._compiled, value, path)
  File "/home/jo/Downloads/migrate2influxdb/migrate2influxdb/.venv/lib/python3.9/site-packages/voluptuous/validators.py", line 341, in _exec
    raise e if self.msg is None else AllInvalid(self.msg, path=path)
  File "/home/jo/Downloads/migrate2influxdb/migrate2influxdb/.venv/lib/python3.9/site-packages/voluptuous/validators.py", line 339, in _exec
    v = func(path, v)
  File "/home/jo/Downloads/migrate2influxdb/migrate2influxdb/.venv/lib/python3.9/site-packages/voluptuous/schema_builder.py", line 817, in validate_callable
    return schema(data)
  File "/home/jo/Downloads/migrate2influxdb/migrate2influxdb/.venv/lib/python3.9/site-packages/voluptuous/schema_builder.py", line 272, in __call__
    return self._compiled([], data)
  File "/home/jo/Downloads/migrate2influxdb/migrate2influxdb/.venv/lib/python3.9/site-packages/voluptuous/schema_builder.py", line 594, in validate_dict
    return base_validate(path, iteritems(data), out)
  File "/home/jo/Downloads/migrate2influxdb/migrate2influxdb/.venv/lib/python3.9/site-packages/voluptuous/schema_builder.py", line 432, in validate_mapping
    raise er.MultipleInvalid(errors)
voluptuous.error.MultipleInvalid: extra keys not allowed @ data['influxdb']

Any suggestions on what is wrong or even better what can be done to solve the errors?

jpeletier · November 12, 2021, 11:36pm

Simply awesome work. I was able to rescue all my data perfectly

phithor · December 14, 2021, 11:01am

I have the same issue, noe sure how to fix. Any ideas?

Using MariaDB.

EDIT: Did som investigation and it seems like there is a bug somewhere between the current home-assistant-core and voluptous versions. This is using python 3.9. Same without anything in the influxdb.yaml

gigatexel · January 18, 2022, 2:10pm

Unfortunately, errors here too.

(.venv) root@homeassistant:/migrate2influxdb# python homeassistant2influxdb.py -h
Traceback (most recent call last):
  File "/migrate2influxdb/homeassistant2influxdb.py", line 25, in <module>
    from homeassistant.components.influxdb import get_influx_connection, _generate_event_to_json, INFLUX_SCHEMA
  File "/migrate2influxdb/home-assistant-core/homeassistant/components/influxdb/__init__.py", line 34, in <module>
    from homeassistant.helpers import event as event_helper, state as state_helper
  File "/migrate2influxdb/home-assistant-core/homeassistant/helpers/state.py", line 12, in <module>
    from homeassistant.components.sun import STATE_ABOVE_HORIZON, STATE_BELOW_HORIZON
  File "/migrate2influxdb/home-assistant-core/homeassistant/components/sun/__init__.py", line 13, in <module>
    from homeassistant.helpers.entity import Entity
  File "/migrate2influxdb/home-assistant-core/homeassistant/helpers/entity.py", line 45, in <module>
    from .entity_platform import EntityPlatform
  File "/migrate2influxdb/home-assistant-core/homeassistant/helpers/entity_platform.py", line 15, in <module>
    from homeassistant import config_entries
  File "/migrate2influxdb/home-assistant-core/homeassistant/config_entries.py", line 17, in <module>
    from .components import persistent_notification
  File "/migrate2influxdb/home-assistant-core/homeassistant/components/persistent_notification/__init__.py", line 15, in <module>
    from homeassistant.helpers.entity import async_generate_entity_id
ImportError: cannot import name 'async_generate_entity_id' from partially initialized module 'homeassistant.helpers.entity' (most likely due to a circular import) (/migrate2influxdb/home-assistant-core/homeassistant/helpers/entity.py)

Maaxion · July 25, 2022, 2:36pm

Hi everyone!

The original repo for this script by dseifert has been archived.

I created a fork of it and updated it to match the current HA DB schema.

If you use option both for the argument tables it’ll populate data to entities using statistics for days you do not have event data for.

You can find my fork of it here. If you’re reading this far in the future, please add an issue to the repo or make a pull request if the script is broken again.

manustar · August 3, 2022, 2:53pm

i read the post but honestly i’m not very familiar with db, i have an installation of HA OS on a vm and i would like to copy all the data i have on the standard db of HA on influx and then clean HA, the script in question works on this type of installation?

manustar · August 4, 2022, 8:55pm

now testing but when i lunch pip install -r requirements.txt homeassiant version not found the last aviable is 2021.12.10

rodrigodf · December 11, 2022, 8:02pm

Hi! I tried to use your script but got the following error:

(.venv) root@debian:~/migrate2influxdb# python homeassistant2influxdb.py -t sqlite -d home-assistant_v2.db
Warning: Could not load Mysql driver, might not be a problem if you intend to use sqlite
Migrating home assistant database statistics, states to Influx database home-assistant_v2.db and bucket Home Assistant
Traceback (most recent call last):
  File "/root/migrate2influxdb/homeassistant2influxdb.py", line 358, in <module>
    main()
  File "/root/migrate2influxdb/homeassistant2influxdb.py", line 151, in main
    cursor.execute(tmp_table_query)
sqlite3.OperationalError: near "SELECT": syntax error

How can I solve? Thanks!

maxime1992 · February 1, 2023, 5:43pm

Hey guys, I’ve tried to build a docker container to run that script but I always end up with a pip error with a dependency issue. I’m not used to Python. I’ve got an easy repro here: fix sqlite migration by fabm3n · Pull Request #3 · Maaxion/homeassistant2influxdb · GitHub Any help would be appreciated! Thanks

virtualdj · July 8, 2023, 12:59pm

Try with the patch posted by fabm3n and use the home assistant branch that your database is using:

sudo apt-get update
sudo apt-get install gcc libmariadb-dev
git clone --branch fix-sqlite-migration https://github.com/fabm3n/homeassistant2influxdb.git
cd homeassistant2influxdb/
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
cat influxdb.yaml
python homeassistant2influxdb.py --type SQLite --database home-assistant_v2.db

bazthewaz · July 24, 2023, 3:41pm

Guys I getting some incompatability issues and thoughts (I have tried down grading conextfiler")

(.venv) pi@PiB:~/homeassistant2influxdb $ python homeassistant2influxdb.py --type SQLite --database home-assistant_v2.db
Traceback (most recent call last):
File “homeassistant2influxdb.py”, line 6, in
from homeassistant.components.influxdb import get_influx_connection, _generate_event_to_json, INFLUX_SCHEMA
File “/home/pi/homeassistant2influxdb/.venv/lib/python3.7/site-packages/homeassistant/components/influxdb/init.py”, line 30, in
from homeassistant.helpers import event as event_helper, state as state_helper
File “/home/pi/homeassistant2influxdb/.venv/lib/python3.7/site-packages/homeassistant/helpers/event.py”, line 47, in
from homeassistant.helpers.template import RenderInfo, Template, result_as_boolean
File “/home/pi/homeassistant2influxdb/.venv/lib/python3.7/site-packages/homeassistant/helpers/template.py”, line 19, in
from jinja2 import contextfilter, contextfunction
ImportError: cannot import name ‘contextfilter’ from ‘jinja2’ (/home/pi/homeassistant2influxdb/.venv/lib/python3.7/site-packages/jinja2/init.py)

Are these related to python version issue that we reported earlier ?

homeassistant 2021.1.5 has requirement importlib-metadata==1.6.0; python_version < “3.8”, but you’ll have importlib-metadata 6.7.0 which is incompatible.
homeassistant 2021.1.5 has requirement pyyaml==5.3.1, but you’ll have pyyaml 6.0.1 which is incompatible.
homeassistant 2021.1.5 has requirement requests==2.25.0, but you’ll have requests 2.31.0 which is incompatible.
homeassistant 2021.1.5 has requirement voluptuous==0.12.1, but you’ll have voluptuous 0.13.1 which is incompatible.

What version of HA and Python worked ?

Thanks

nanderson · September 5, 2023, 5:16pm

I’m getting the same error mentioned in this post which seems to fail some state or valid_entity check. Anyone else encounter something similar?

MPS · February 2, 2024, 9:00pm

Have you found a solution? Having the same problem.

nabeelr · February 16, 2024, 12:26am

Yeah, all these forks seem to be broken right now. They all fail to resolve dependencies successfully when you run “pip install -r home-assistant-core/requirements.txt”

Pip2 · March 21, 2024, 4:18pm

I’ve finally succeeded to make Maaxion/homeassistant2influxdb run for me.

You find my updates in my fork:

and in the PR: Fixes for sqlite on current version (24.03) by dsteinkopf · Pull Request #9 · Maaxion/homeassistant2influxdb · GitHub