SQLite to Influxdb migration of historical data

Configuration
1

I have successfully installed Influxdb and Grafana, configured it to display HA data from now.
Is it possible in the current version of HA/Influxdb to populate influxdb with the old data stored in default HA database (SQLite)?

I found that there was a script but the corresponding text was delited from the help page related to Influxdb installation. Do we have any options now?

By the way, all instructions about Influxdb and Grafana installation are outdated! I managed to install separate Docker images and configure Influx via http requests.

1 Like
2

For historical data, look at history_stats
https://www.home-assistant.io/components/history_stats/

3

Hi, thank you for attention to this sensor… but I am keen to populate Influxdb with historical data to work with them in Grafana.

Is it possible?

4

I don’t know. I will watch this space and if someone is able to answer , I will be glad to learn as well. :slightly_smiling_face:

5

I’d like to know if this is feasible too !
let’s bookmark this thread !

6

Same here, I set it all up a few days ago, made a typo with the sensor names to send to influx. I knew I was going to do this a while ago so I set my recorder retention to 28days. Then I find that only new vaules under the influxdb: component get exported

7

Yes, this!

How do I get the old data into InfluxDB and then into Grafana.

I don’t have the skills to do this, but maybe this can help?

8

Did anyone find a solution to this?

9

I found this:

There is described how to export sqlite to a csv. It should also be possible to import csv to influxdb but it have to reformated somehow so influxdb understands it…

10

I am trying to do the same … well, with MySQL (MariaDB) to InfluxDB, but for all sense and purposes that’s the same.

Theoretically this is possible. Getting data from SQLite/MySQL and writing data to InfluxDB are doable. The main challenge seems to be to convert the data from the SQLite / MySQL structure to the InfluxDB structure. Whether that’s done live (querying and uploading) or indirectly by exporting to CSV first and then converting does not really matter imho.

The code from influxdb component should give enough information on how to convert the contents in the states table (plus time_fired from the corresponding entry in the events table if you want to be precise). Before I start going down that rabbit hole (and getting familiar with a lot of new stuff), I wanted to check whether anybody else has already done such a conversion script. Maybe tied all together nicely ready-for-use?

11

If anybody needs it, I’ve uploaded my script to GitHub. I used it to migrate a database with ~70 million entries (applying some filters and doing some clean-up along the way). It is tested with MariaDB, but I added some lines of code that should (theoretically) get it to run with SQLite.

6 Likes
12

Have you tried dumping the sqlite data base https://www.sqlitetutorial.net/sqlite-dump/. You will need to know the name of the sqlite file, look at the recorder section of configuration.yaml for db_url.

This should give the database as a set of SQL create and insert command. Run that as a script from the application for you new database server. I have done this with mysql, and I realize that and an SQL script may not work with influxdb.

13

@john1949, dumping the SQLite or MySQL database, either to a CSV file or as a set of SQL statements, is only the first (trivial) step. You can not run the statements against InfluxDB (different concept of a database!) or “just” import the CSV. You need to convert it explicitly. The link I posted is to a script that uses the existing Home Assistant component to do exactly that, ensuring that the format is exactly as it would have been if you put it into InfluxDB from the start.

1 Like
14

Thanks! Just a few small questions; as I’m just installing influxdb and have soms 180K records in my MariaDB installation I want do do it right in one go.

I do not (yet) have any security on my new InfluxDB docker image, so I was going to enter just this in the HA configuration.yaml. Should i just add an empty influxdb.yaml?

influxdb:

I did not yet do this step; so should I first restart HA with this configuration so it creates some tables in the home_assistant db in influxdb or can I just start using influxdb and merge later; will the script detect duplicate entries?

Nevermind all this. Just went ahead and tried it; needed to make some changes though because I’m using MariaDB that’s listening on port 3307. Added that and refined the readme a bit, the requirement installation was out of order; wheel is defined in the requirements.txt, but is already needed in the requirements.txt of Home Assistant. :wink:

Pull request: https://github.com/dseifert/homeassistant2influxdb/pull/2
Repo: https://github.com/rowdynl/homeassistant2influxdb

Thanks for the script, worked like a charm apart from the little hickups :partying_face:

15

I was very lucky to find this thread after I realized this one https://community.openhab.org/t/migrate-your-existing-persistence-data-to-influxdb/30693 was for openHAB instead of Home Assistant.

Thanks for that @linisgre !!! - I was looking for something exactly for that purpose - migrating data from SQLite to InfluxDB before the InfluxDB has been integrated into HA.

As far as I can see according to https://github.com/dseifert/homeassistant2influxdb#setup this script needs to have HA running on a full Linux machine in order to do the setup, install dependencies etc., right?

Or how would I point to the SQLite database?

I have SQLite database and InfluxDB (as add-on/container managed by Supervisor) running in HASS OS on the same machine and trying to figure out how to run that script… :face_with_monocle: :confused: :persevere: Linux would be no problem, current problems are:

  1. Understand how to setup the necessary steps to run the script (any linux machine okay?)
  2. Adjust the script to work with SQLite instead of MySQL

As this topic initially was about SQLite as source database I´m very interested in a solution for exactly this. The script provided by @linisgre is probably the best start.

I spent some time (few hours already) but ended up in being just frustrated. Because: I know what I want to achieve, I have the tool for that right in front of me, but I don´t know how to (adjust and) use it. Hopefully anyone can help.

Update: After investing/wasting a few hours in total on this, I tend to just forget about that history data, unfortunately. If someone coming over this will find a solution to adjust the script for SQLite as source DB I´ll happy to hear (I kept a copy before pruning and repacking it :wink: ).

16

  1. You do not need HomeAssistant running at all! On Linux, you only need to download the source and install the necessary dependencies (technically only the dependencies required by the influxdb component). No need to actually start HomeAssistant. For SQLite, you need to copy the SQLite database to the Linux machine, however.

  2. Any Linux machine should be okay as long as you have Python 3.7 / 3.8 and the dependencies are available. It has only been tested on Ubuntu 18.04 and 20.04 (everything in between should work as well). If in doubt, install VirtualBox and create a virtual Ubuntu 20.04 machine for the migration. Ditto if you are on Windows … it may work in WSL, but no idea. Probably easiest to do it virtual.

  3. The script contains example code (untested) for SQLite. As I did not have a SQLite database, I did not test it. Hopefully it just works, possibly it requires minor modifications.

17

Thank you, good to know. Anyway I assume it would still cost me few hours with likely ending in a “Aaaaaah I skip” mood because it won´t work. Problem is 2. (untested for SQLite), rest would be achievable for me I think.

18

Hey, I’m having some trouble trying to make it work, do you know what I’m doing wrong?

  1. I followed all instructions and it looks like all dependencies are installed (I’m doing this on arch linux).
  2. I placed file “influxdb.yaml” into my working directory, file contents:
host: a0d7b954-influxdb
port: 8086
database: homeassistant
username: homeassistant
password: my_password_here
max_retries: 3
default_measurement: state
  1. I coppied my database into working directory (home-assistant_v2.db) and executed:

python homeassistant2influxdb.py -t sqlite -d home-assistant_v2.db

  1. I’m getting following error
Traceback (most recent call last):
  File "/tmp/influx/homeassistant2influxdb/.venv/lib/python3.9/site-packages/urllib3/connection.py", line 169, in _new_conn
    conn = connection.create_connection(
  File "/tmp/influx/homeassistant2influxdb/.venv/lib/python3.9/site-packages/urllib3/util/connection.py", line 73, in create_connection
    for res in socket.getaddrinfo(host, port, family, socket.SOCK_STREAM):
  File "/usr/lib/python3.9/socket.py", line 953, in getaddrinfo
    for res in _socket.getaddrinfo(host, port, family, type, proto, flags):
socket.gaierror: [Errno -2] Name or service not known

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/tmp/influx/homeassistant2influxdb/.venv/lib/python3.9/site-packages/urllib3/connectionpool.py", line 699, in urlopen
    httplib_response = self._make_request(
  File "/tmp/influx/homeassistant2influxdb/.venv/lib/python3.9/site-packages/urllib3/connectionpool.py", line 394, in _make_request
    conn.request(method, url, **httplib_request_kw)
  File "/tmp/influx/homeassistant2influxdb/.venv/lib/python3.9/site-packages/urllib3/connection.py", line 234, in request
    super(HTTPConnection, self).request(method, url, body=body, headers=headers)
  File "/usr/lib/python3.9/http/client.py", line 1255, in request
    self._send_request(method, url, body, headers, encode_chunked)
  File "/usr/lib/python3.9/http/client.py", line 1301, in _send_request
    self.endheaders(body, encode_chunked=encode_chunked)
  File "/usr/lib/python3.9/http/client.py", line 1250, in endheaders
    self._send_output(message_body, encode_chunked=encode_chunked)
  File "/usr/lib/python3.9/http/client.py", line 1010, in _send_output
    self.send(msg)
  File "/usr/lib/python3.9/http/client.py", line 950, in send
    self.connect()
  File "/tmp/influx/homeassistant2influxdb/.venv/lib/python3.9/site-packages/urllib3/connection.py", line 200, in connect
    conn = self._new_conn()
  File "/tmp/influx/homeassistant2influxdb/.venv/lib/python3.9/site-packages/urllib3/connection.py", line 181, in _new_conn
    raise NewConnectionError(
urllib3.exceptions.NewConnectionError: <urllib3.connection.HTTPConnection object at 0x7fe73b1821c0>: Failed to establish a new connection: [Errno -2] Name or service not known

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/tmp/influx/homeassistant2influxdb/.venv/lib/python3.9/site-packages/requests/adapters.py", line 439, in send
    resp = conn.urlopen(
  File "/tmp/influx/homeassistant2influxdb/.venv/lib/python3.9/site-packages/urllib3/connectionpool.py", line 755, in urlopen
    retries = retries.increment(
  File "/tmp/influx/homeassistant2influxdb/.venv/lib/python3.9/site-packages/urllib3/util/retry.py", line 574, in increment
    raise MaxRetryError(_pool, url, error or ResponseError(cause))
urllib3.exceptions.MaxRetryError: HTTPConnectionPool(host='a0d7b954-influxdb', port=8086): Max retries exceeded with url: /write?db=homeassistant (Caused by NewConnectionError('<urllib3.connection.HTTPConnection object at 0x7fe73b1821c0>: Failed to establish a new connection: [Errno -2] Name or service not known'))

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "home-assistant-core/homeassistant/components/influxdb/__init__.py", line 432, in write_v1
    influx.write_points(json, time_precision=precision)
  File "/tmp/influx/homeassistant2influxdb/.venv/lib/python3.9/site-packages/influxdb/client.py", line 603, in write_points
    return self._write_points(points=points,
  File "/tmp/influx/homeassistant2influxdb/.venv/lib/python3.9/site-packages/influxdb/client.py", line 681, in _write_points
    self.write(
  File "/tmp/influx/homeassistant2influxdb/.venv/lib/python3.9/site-packages/influxdb/client.py", line 413, in write
    self.request(
  File "/tmp/influx/homeassistant2influxdb/.venv/lib/python3.9/site-packages/influxdb/client.py", line 332, in request
    response = self._session.request(
  File "/tmp/influx/homeassistant2influxdb/.venv/lib/python3.9/site-packages/requests/sessions.py", line 542, in request
    resp = self.send(prep, **send_kwargs)
  File "/tmp/influx/homeassistant2influxdb/.venv/lib/python3.9/site-packages/requests/sessions.py", line 655, in send
    r = adapter.send(request, **kwargs)
  File "/tmp/influx/homeassistant2influxdb/.venv/lib/python3.9/site-packages/requests/adapters.py", line 516, in send
    raise ConnectionError(e, request=request)
requests.exceptions.ConnectionError: HTTPConnectionPool(host='a0d7b954-influxdb', port=8086): Max retries exceeded with url: /write?db=homeassistant (Caused by NewConnectionError('<urllib3.connection.HTTPConnection object at 0x7fe73b1821c0>: Failed to establish a new connection: [Errno -2] Name or service not known'))

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/tmp/influx/homeassistant2influxdb/homeassistant2influxdb.py", line 195, in <module>
    main()
  File "/tmp/influx/homeassistant2influxdb/homeassistant2influxdb.py", line 97, in main
    influx = get_influx_connection(influx_config, test_write=True, test_read=True)
  File "home-assistant-core/homeassistant/components/influxdb/__init__.py", line 465, in get_influx_connection
    write_v1([])
  File "home-assistant-core/homeassistant/components/influxdb/__init__.py", line 438, in write_v1
    raise ConnectionError(CONNECTION_ERROR % exc) from exc
ConnectionError: Cannot connect to InfluxDB due to 'HTTPConnectionPool(host='a0d7b954-influxdb', port=8086): Max retries exceeded with url: /write?db=homeassistant (Caused by NewConnectionError('<urllib3.connection.HTTPConnection object at 0x7fe73b1821c0>: Failed to establish a new connection: [Errno -2] Name or service not known'))'. Please check that the provided connection details (host, port, etc.) are correct and that your InfluxDB server is running and accessible.

Any help would be greatly appreciated :neutral_face:

19

nvm I had to replace host from “a0d7b954-influxdb” to “192.168.1.45” (my home assistant server IP). It worked!

20

I can absolutely confirm that it works with SQLite. Just follow the directions in the script what to comment/uncomment.
I could save around 90 days of historical data into Influxdb this way (1.2 M rows, around 900 MB db size).

1 Like