Migrate your existing persistence data to InfluxDB

The REST API hasn’t changed as far as I know so it should still work.

I’m using the script with openHab 2.5.5.
I try to migrate from jdbc:sqlite to Influx.
This is the output of the script:

./rest2influxdb.sh LoungeSxDoorSensorTemperature

### timestamps
item: LoungeSxDoorSensorTemperature
10y:  2010-05-18T16:47:57+02:00
1y:   2019-04-20T16:47:57+02:00
1m:   2020-04-19T16:47:57+02:00
1w:   2020-05-11T16:48:57+02:00
1d:   2020-05-17T16:48:57+02:00
8h:   2020-05-18T08:48:57+02:00
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   338  100   338    0     0     56      0  0:00:06  0:00:05  0:00:01    89
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   338  100   338    0     0    169      0  0:00:02  0:00:01  0:00:01   169
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   338  100   338    0     0   3520      0 --:--:-- --:--:-- --:--:--  3520
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   338  100   338    0     0  14083      0 --:--:-- --:--:-- --:--:-- 14083
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   338  100   338    0     0  37555      0 --:--:-- --:--:-- --:--:-- 37555
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   338  100   338    0     0  21125      0 --:--:-- --:--:-- --:--:-- 21125

### found values: 0
Warning: Couldn't read data from file "LoungeSxDoorSensorTemperature-*", this 
Warning: makes an empty POST.
HTTP/1.1 204 No Content
Content-Type: application/json
Request-Id: 9593c329-9916-11ea-8c88-000000000000
X-Influxdb-Build: OSS
X-Influxdb-Version: 1.6.4
X-Request-Id: 9593c329-9916-11ea-8c88-000000000000
Date: Mon, 18 May 2020 14:48:06 GMT

Sleep for 5 seconds to let InfluxDB process the data...

### delete temporary files

What I’m doing wrong ?

Thanks

Hi,

I try to run the latest version of the script on openhabian openHAB 2.5.5-1 (Release Build)

My config.cfg is:

#!/bin/bash
# openHAB server
openhabserver="rpi.local"
openhabport="8080"
serviceid="rrd4j"

# InfluxDB server
influxserver="rpi.local"
influxport="8086"
influxdatbase="openhab_db"
influxuser="openhab"
influxpw="somepass"
importsize=100
sleeptime=5

I get this:
$ ./rest2influxdb.sh RPi_Temp_CPU

### timestamps
item: RPi_Temp_CPU
10y:  2010-06-11T00:09:28%
1y:   2019-05-14T00:09:28%
1m:   2020-05-13T00:09:28%
1w:   2020-06-04T00:10:28%
1d:   2020-06-10T00:10:28%
8h:   2020-06-10T16:10:28%
./rest2influxdb.sh: line 60: unexpected EOF while looking for matching `''
./rest2influxdb.sh: line 83: syntax error: unexpected end of file

I tested the link manually through the REST API:
curl -X GET http://rpi.local:8080/rest/persistence/items/RPi_Temp_CPU?serviceId=rrd4j

It works fine:

{“name”:“RPi_Temp_CPU”,“datapoints”:“657”,“data”:[{“time”:1591784640000,“state”:“53.069”}

{
  "name": "RPi_Temp_CPU",
  "datapoints": "657",
  "data": [
    {
      "time": 1591784640000,
      "state": "53.069"
    },

Can you spot the problem? I run out of ideas!

sigmoidal

spotted this:
L:38 echo “resturl: $resturl”

There is a problem with the latest update. Check latest pull req.

You are right, fixed.

Thanks

I’m also trying to move into influxdb from rrd4j.
When I try your script, it seems that my item data is empty?
Is it possible that it has something to do with the timezone? I guess I’ve got the +2 in the timeframe wrong?

I’m running openHAB 2.5.9 on Ubuntu 20

The config file:

#!/bin/bash

# openHAB server
openhabserver="192.168.0.10"
openhabport="8080"
serviceid="rrd4j"

# InfluxDB server
influxserver="192.168.0.18"
influxport="8086"
influxdatbase="openhab"
influxuser="admin"
influxpw="admin"
importsize=100
sleeptime=5

When I run it with an existing item:

./rest2influxdb.sh fluksoGasA

### timestamps
item: fluksoGasA
10y:  2010-10-11T12:48:00+02:00
1y:   2019-09-13T12:48:00+02:00
1m:   2020-09-12T12:48:00+02:00
1w:   2020-10-04T12:49:00+02:00
1d:   2020-10-10T12:49:00+02:00
8h:   2020-10-11T04:49:00+02:00
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   338  100   338    0     0  56333      0 --:--:-- --:--:-- --:--:-- 67600
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   338  100   338    0     0  67600      0 --:--:-- --:--:-- --:--:-- 67600
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   338  100   338    0     0  67600      0 --:--:-- --:--:-- --:--:-- 67600
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   338  100   338    0     0  84500      0 --:--:-- --:--:-- --:--:--  110k
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   338  100   338    0     0  84500      0 --:--:-- --:--:-- --:--:-- 84500
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   338  100   338    0     0  84500      0 --:--:-- --:--:-- --:--:-- 84500

### found values: 0

### delete temporary files

When I open the rest api in a browser:

http://192.168.0.10:8080/rest/persistence/items/fluksoGasA

{"name":"fluksoGasA","datapoints":"349","data":[{"time":1602326880000,"state":"301.0"},{"time":1602327120000,"state":"301.0"},{"time":1602327360000,"state":"301.0"},{"time":1602327600000,"state":"301.0"},{"time":1602327840000,"state":"301.0"},{"time":1602328080000,"state":"301.0"},{"time":1602328320000,"state":"301.0"},{"time":1602328560000,"state":"301.0"},{"time":1602328800000,"state":"301.0"},{"time":1602329040000,"state":"301.0"},{"time":1602329280000,"state":"301.0"},{"time":1602329520000,"state":"301.73333333333335"},{"time":1602329760000,"state":"25907.507738095235"},{"time":1602330000000,"state":"18144.0"},{"time":1602330240000,"state":"18144.0"},{"time":1602330480000,"state":"18144.0"},{"time":1602330720000,"state":"18144.0"},{"time":1602330960000,"state":"9573.266666666666"},{"time":1602331200000,"state":"712.0"},{"time":1602331440000,"state":"712.0"},{"time":1602331680000,"state":"712.0"},{"time":1602331920000,"state":"712.0"},{"time":1602332160000,"state":"712.0"},{"time":1602332400000,"state":"712.0"},{"time":1602332640000,"state":"712.0"},{"time":1602332880000,"state":"712.0"},{"time":1602333120000,"state":"712.0"},{"time":1602333360000,"state":"35622.183333333334"},{"time":1602333600000,"state":"35769.229166666664"},{"time":1602334320000,"state":"18923.0"},{"time":1602334560000,"state":"29591.33863636364"},{"time":1602334800000,"state":"19000.0"},{"time":1602335040000,"state":"19000.0"},{"time":1602335280000,"state":"19000.0"},{"time":1602335520000,"state":"28950.55"},{"time":1602335760000,"state":"29409.0"},{"time":1602336000000,"state":"29409.0"},{"time":1602336240000,"state":"29409.0"},{"time":1602336480000,"state":"35824.183333333334"},{"time":1602336720000,"state":"47679.0"},{"time":1602336960000,"state":"47679.0"},{"time":1602337200000,"state":"47679.0"},{"time":1602337440000,"state":"29666.344444444443"},{"time":1602337680000,"state":"64492.0"},{"time":1602337920000,"state":"64492.0"},{"time":1602338160000,"state":"64492.0"},{"time":1602338400000,"state":"56264.17283950617"},{"time":1602338640000,"state":"47654.458333333336"},{"time":1602338880000,"state":"46705.0"},{"time":1602339120000,"state":"46705.0"},{"time":1602339360000,"state":"35259.75"},{"time":1602339600000,"state":"43576.17222222222"},{"time":1602339840000,"state":"37975.0"},{"time":160234





I guess it’s linked with the timezone format.

With the +2 (timezone)

http://192.168.0.10:8080/rest/persistence/items/fluksoGasA?serviceId=rrd4j&starttime=2020-10-11T05:18:53+2

{"error":{"message":"2020-10-11T05:18:53 2 is not in a valid format.","http-code":500,"exception":{"class":"java.lang.IllegalArgumentException","message":"2020-10-11T05:18:53 2 is not in a valid format.","localized-message":"2020-10-11T05:18:53 2 is not in a valid format.","cause":"java.time.format.DateTimeParseException"}}}

without the +2

http://192.168.0.10:8080/rest/persistence/items/fluksoGasA?serviceId=rrd4j&starttime=2020-10-11T05:18:53

{"name":"fluksoGasA","datapoints":"111","data":[{"time":1602386400000,"state":"17634.0"},{"time":1602386640000,"state":"17634.0"},{"time":1602386880000,"state":"22724.243537414965"},{"time":1602387120000,"state":"18306.74166666667"},{"time":1602387360000,"state":"17995.504166666666"},{"time":1602387600000,"state":"17904.44166666667"},{"time":1602387840000,"state":"17802.279166666667"},{"time":1602388080000,"state":"17732.433333333334"},{"time":1602388320000,"state":"17670.487906504066"},{"time":1602388560000,"state":"17610.80416666667"},{"time":1602388800000,"state":"17589.1"},{"time":1602389040000,"state":"17590.0"},{"time":1602389280000,"state":"17590.0"},{"time":1602389520000,"state":"17590.0"},{"time":1602389760000,"state":"2995.0"},{"time":1602390000000,"state":"910.0"},{"time":1602390240000,"state":"910.0"},{"time":1602390480000,"state":"910.0"},{"time":1602390720000,"state":"910.0"},{"time":1602390960000,"state":"21612.05576923077"},{"time":1602391200000,"state":"19635.0"},{"time":1602391440000,"state":"6663.0"},{"time":1602391680000,"state":"2339.0"},{"time":1602391920000,"state":"2339.0"},{"time":160

When I remove the %:z in all time lines, it seems to go on?

fe from

tenyearsago=`date +"%Y-%m-%dT%H:%M:%S%:z" --date="10 years ago"`

to

tenyearsago=`date +"%Y-%m-%dT%H:%M:%S" --date="10 years ago"`

But at first sight, data isn’t complete/correct. :blush:

I think this was already fixed by this PR in the master branch…

At least the %:z was removed.
I think you might have downloaded the outdated branch for v2.1.

I will update the first post, so new users will not use the outdated v2.1 branch.
Instead the master brach ist supposed to work with v2.5.5.

1 Like

Is it correct that only the last 30 days are being migrated?
I’m seeing the correct dates:

Command:

./rest2influxdb.sh fluksoElecB

Output:

### timestamps
item: fluksoElecB
10y:  2010-10-17T15:30:23
1y:   2019-09-19T15:30:23
1m:   2020-09-18T15:30:23
1w:   2020-10-10T15:31:23
1d:   2020-10-16T15:31:23
8h:   2020-10-17T07:31:23
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   202  100   202    0     0  22444      0 --:--:-- --:--:-- --:--:-- 25250
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  2704  100  2704    0     0   330k      0 --:--:-- --:--:-- --:--:--  330k
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 26912    0 26912    0     0  2021k      0 --:--:-- --:--:-- --:--:-- 2021k
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   229  100   229    0     0  32714      0 --:--:-- --:--:-- --:--:-- 38166
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 12067    0 12067    0     0  1309k      0 --:--:-- --:--:-- --:--:-- 1309k
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 23390    0 23390    0     0  2076k      0 --:--:-- --:--:-- --:--:-- 2076k

### found values: 1298

### Line from 1 to 1000
HTTP/1.1 100 Continue

HTTP/1.1 204 No Content
Content-Type: application/json
Request-Id: e8f5c243-107c-11eb-b196-000000000000
X-Influxdb-Build: OSS
X-Influxdb-Version: 1.6.4
X-Request-Id: e8f5c243-107c-11eb-b196-000000000000
Date: Sat, 17 Oct 2020 13:30:23 GMT

Sleep for 10 seconds to let InfluxDB process the data...

### Line from 1001 to 2000
HTTP/1.1 204 No Content
Content-Type: application/json
Request-Id: eef0bba3-107c-11eb-b1a7-000000000000
X-Influxdb-Build: OSS
X-Influxdb-Version: 1.6.4
X-Request-Id: eef0bba3-107c-11eb-b1a7-000000000000
Date: Sat, 17 Oct 2020 13:30:33 GMT

Sleep for 10 seconds to let InfluxDB process the data...

### delete temporary files

RRD4 Graph:
rrd
Grafana/influxdb graph:

Any idea what I’m doing wrong?
Or does his only work on ‘empty’ databasefields? My current grafana installation is running for about 1-2 weeks…

try to check step by step where the data ist lost.

The script first creates XML-files fo speparated time periods.

Do these files look ok?

Then check if the ${itemname}.xml does contain all data you would expect.

The same for ${itemname}.txt

I hope this will help you find the issue.

1 Like

Does this script also works when the data is in mySQL ?

I guess so, because the script gets the data via the REST API and does not care what database is used in the background.

Thank you very much.
When I followed your analyses, I noticed that my date had some extra 00’s at the end of the date. With removing everything, clone it again, it works !!!

Next step, checking if I can export/import ‘all’ exisitng data now. Guess it’ll be a bit much, but that’s why we’ve computers, no? :wink:

Does the script work with OH3.2?

…or with OH3.3 and InfluxDB 2.4?

I’ve tried it and it seems the values were read from my MySQL database, but could not be written to InfluxDB 2.4.

 ./rest2influxdb.sh MQTT_Wetterstation_Temperature

### timestamps
item: MQTT_Wetterstation_Temperature
10y:  2012-09-23T14:23:41
1y:   2021-08-26T14:23:41
1m:   2022-08-25T14:23:41
1w:   2022-09-16T14:24:41
1d:   2022-09-22T14:24:41
8h:   2022-09-23T06:24:41
resturl:   http://openhab.fritz.box:8080/rest/persistence/items/MQTT_Wetterstation_Temperature?serviceId=jdbc&api_key=MQTT_Wetterstation_Temperature
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 24.5M    0 24.5M    0     0  1221k      0 --:--:--  0:00:20 --:--:-- 5745k
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 10.3M    0 10.3M    0     0  1242k      0 --:--:--  0:00:08 --:--:-- 2463k
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  681k    0  681k    0     0  1414k      0 --:--:-- --:--:-- --:--:-- 1414k
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  173k    0  173k    0     0  1443k      0 --:--:-- --:--:-- --:--:-- 1431k
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 24268    0 24268    0     0  1077k      0 --:--:-- --:--:-- --:--:-- 1077k
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 11343    0 11343    0     0   615k      0 --:--:-- --:--:-- --:--:--  615k

### found values: 988158
HTTP/1.1 401 Unauthorized
Content-Type: application/json; charset=utf-8
X-Influxdb-Build: OSS
X-Influxdb-Version: v2.4.0
X-Platform-Error-Code: unauthorized
Date: Fri, 23 Sep 2022 12:24:13 GMT
Content-Length: 48
Connection: close

Today I spent some time with the script and added a version which seems to work with openhab 3.3 and InfluxDB 2.4

I’ve forked the repository from @christoph_wempe and added a new script and cfg for use with InfluxDB 2.4. You can find it here:

(Sorry, if I’ve made something wrong with GitHub, it’s my first fork/repo :see_no_evil:)

For me it works fine, maybe my version helps someone else… :slight_smile:

1 Like

Hi, I’m running OH3.4 (latest version) and installalled InfluxDB via openHABian-config, which seems to be version 1.8.

When starting the script I get:

openhabian@openhabianpi:/etc/openhab/migrate_rrd4j $ sudo ./rest2influxdb.sh ShellyTVWohnzimmer_Watt

### timestamps
item: ShellyTVWohnzimmer_Watt
10y:  2012-09-26T21:36:17
1y:   2021-08-29T21:36:17
1m:   2022-08-28T21:36:17
1w:   2022-09-19T21:37:17
1d:   2022-09-25T21:37:17
8h:   2022-09-26T13:37:17
resturl:   http://192.168.10.50:8080/rest/persistence/items/ShellyTVWohnzimmer_Watt?serviceId=rrd4j&api_key=ShellyTVWohnzimmer_Watt
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100    61    0    61    0     0   5083      0 --:--:-- --:--:-- --:--:--  5545
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  216k    0  216k    0     0  3604k      0 --:--:-- --:--:-- --:--:-- 3604k
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 78728    0 78728    0     0  3075k      0 --:--:-- --:--:-- --:--:-- 3075k
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  319k    0  319k    0     0  4697k      0 --:--:-- --:--:-- --:--:-- 4767k
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 35747    0 35747    0     0  1837k      0 --:--:-- --:--:-- --:--:-- 1837k
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 18126    0 18126    0     0   737k      0 --:--:-- --:--:-- --:--:--  737k

### found values: 18105
curl: (7) Failed to connect to 192.168.10.50 port 8086: Connection refused
Sleep for 5 seconds to let InfluxDB process the data...
curl: (7) Failed to connect to 192.168.10.50 port 8086: Connection refused
Sleep for 5 seconds to let InfluxDB process the data...

So, the script can read the RRD4J data - but can’t connect to the database.
The IP address, the port, the user, the password and the name of the database are correct.

What else could be the reason for the error message - and how can I find out what’s the real problem?

I’m now a step further - the reason was the binding address was “localhost” while I was using the real ipaddress in the script. However - how I get “no content”:

### found values: 18106
HTTP/1.1 100 Continue

HTTP/1.1 204 No Content
Content-Type: application/json
Request-Id: a90948b6-3dd8-11ed-8002-dca6323873fc
X-Influxdb-Build: OSS
X-Influxdb-Version: 1.8.10
X-Request-Id: a90948b6-3dd8-11ed-8002-dca6323873fc
Date: Mon, 26 Sep 2022 20:20:27 GMT

I was just impatient, everything works fine, thanks!