Migrate your existing persistence data to InfluxDB

christoph_wempe · October 11, 2020, 3:31pm

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.

brononius · October 17, 2020, 12:49pm

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…

christoph_wempe · October 17, 2020, 10:07pm

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

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

github.com

CWempe/rest2influxdb/blob/master/rest2influxdb.sh#L42-L47


curl -X GET --header "Accept: application/json" "$resturl&starttime=${tenyearsago}&endtime=${oneyearago}"  > ${itemname}_10y.xml
curl -X GET --header "Accept: application/json" "$resturl&starttime=${oneyearago}&endtime=${onemonthago}"  > ${itemname}_1y.xml
curl -X GET --header "Accept: application/json" "$resturl&starttime=${onemonthago}&endtime=${oneweekago}"  > ${itemname}_1m.xml
curl -X GET --header "Accept: application/json" "$resturl&starttime=${oneweekago}&endtime=${onedayago}"    > ${itemname}_1w.xml
curl -X GET --header "Accept: application/json" "$resturl&starttime=${onedayago}&endtime=${eighthoursago}" > ${itemname}_1d.xml
curl -X GET --header "Accept: application/json" "$resturl&starttime=${eighthoursago}"                      > ${itemname}_8h.xml

Do these files look ok?

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

github.com

CWempe/rest2influxdb/blob/master/rest2influxdb.sh#L50


# get values and write to different files
# curl -X GET --header "Accept: application/json" "$resturl&starttime=${tenyearsago}&endtime=${oneyearago}"  > ${itemname}_10y.xml
curl -X GET --header "Accept: application/json" "$resturl&starttime=${tenyearsago}&endtime=${oneyearago}"  > ${itemname}_10y.xml
curl -X GET --header "Accept: application/json" "$resturl&starttime=${oneyearago}&endtime=${onemonthago}"  > ${itemname}_1y.xml
curl -X GET --header "Accept: application/json" "$resturl&starttime=${onemonthago}&endtime=${oneweekago}"  > ${itemname}_1m.xml
curl -X GET --header "Accept: application/json" "$resturl&starttime=${oneweekago}&endtime=${onedayago}"    > ${itemname}_1w.xml
curl -X GET --header "Accept: application/json" "$resturl&starttime=${onedayago}&endtime=${eighthoursago}" > ${itemname}_1d.xml
curl -X GET --header "Accept: application/json" "$resturl&starttime=${eighthoursago}"                      > ${itemname}_8h.xml

# combine files
cat ${itemname}_10y.xml ${itemname}_1y.xml ${itemname}_1m.xml ${itemname}_1w.xml ${itemname}_1d.xml ${itemname}_8h.xml > ${itemname}.xml

# convert data to line protocol file
cat ${itemname}.xml \
     | sed 's/}/\n/g' \
     | sed 's/data/\n/g' \
     | grep -e "time.*state"\
     | tr -d ',:[{"' \
     | sed 's/time/ /g;s/state/ /g' \
     | awk -v item="$itemname" '{print item " value=" $2 " " $1 "000000"}' \
     | sed 's/value=ON/value=1/g;s/value=OFF/value=0/g' \

The same for ${itemname}.txt

github.com

CWempe/rest2influxdb/blob/master/rest2influxdb.sh#L53-L61


cat ${itemname}.xml \
     | sed 's/}/\n/g' \
     | sed 's/data/\n/g' \
     | grep -e "time.*state"\
     | tr -d ',:[{"' \
     | sed 's/time/ /g;s/state/ /g' \
     | awk -v item="$itemname" '{print item " value=" $2 " " $1 "000000"}' \
     | sed 's/value=ON/value=1/g;s/value=OFF/value=0/g' \
> ${itemname}.txt

I hope this will help you find the issue.

wars · October 19, 2020, 3:11pm

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

christoph_wempe · October 19, 2020, 3:15pm

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

brononius · October 20, 2020, 9:06am

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?

stefaanbolle · January 6, 2022, 12:02pm

Does the script work with OH3.2?

BlackAlpha · September 23, 2022, 12:26pm

…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

BlackAlpha · September 25, 2022, 5:58pm

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 )

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

Boby · September 26, 2022, 7:52pm

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?

Boby · September 26, 2022, 8:23pm

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

Boby · September 27, 2022, 8:35pm

I was just impatient, everything works fine, thanks!

obones · April 11, 2023, 7:26pm

For what it’s worth, it works fine with OH3.4 provided you create a config24.cfg file that indicates rrd4j as serviceid

And for those who want to import all items, here is a linux command:

curl  http://localhost:8080/rest/items | jq '.[].name' | xargs -L1 ./rest2influxdb24.sh

Better make tries with a single item to make sure everything is ok before using it though.