Datenübernahme aus CCU-Historian

dw.dw.dw

Hallo zusammen,

dies ist als Erfahrungsbericht zu sehen, wie ich Daten aus einer DASHUI / CCU-Historian Installation in eine neue ioBroker Installation übernommen habe.

Als Ziel kommt der SQL Adapter mit einer MYSQL Datenbank (Maria-DB) auf einer Synology DS zum Einsatz.

Ein paar Grundlagen:

CCU-Historian speichert jeden Datenpunkt eines HM Gerätes in einer eigenen Tabelle auf einer H2 Datenbank ab.

z.B.: D_BIDCOS_RF_LEQ05XXXX5_1_TEMPERATURE

Struktur der Tabellen:

TS VALUE STATE

–------------------------------------------------------------------------

2015-02-15 10:35:42.589 10.799999997019768 1

2015-02-15 10:38:30.843 10.899999991059303 1

2015-02-15 10:41:04.595 11.0 1

2015-02-15 10:43:23.85 11.099999994039536 1

2015-02-15 10:45:28.603 11.199999988079071 1

2015-02-15 10:48:23.105 11.299999997019768 1

2015-02-15 10:51:03.111 11.399999991059303 1

Diese Tabelle lässt sich in der H2 Datenbank Konsole in eine CSV Datei Exportieren:

call CSVWRITE ('/temp/h2-outtemp.csv', 'SELECT * FROM D_BIDCOS_RF_LEQ05XXXX5_1_TEMPERATURE')

Den Pfad der Ausgabedatei muss individuell angepasst werden.

Inhalt dieser Datei:

"TS","VALUE","STATE"

"2015-02-15 10:35:42.589","10.799999997019768","1"

Der SQL Adapter speichert die Daten grundlegend anders ab:

Es gibt nur noch 5 Tabellen in einer Datenbank; bei mir heist die DB ioBroker.

1. Sources

bei mir mit diesem Inhalt:

id name

1 system.adapter.sql.0

2 system.adapter.rpi2.0

3 system.adapter.hm-rpc.0

4 system.adapter.hm-rega.0

2.Datapoints für jeden aktvierten Datenpunkt einen Eintrag

id name type

1 rpi2.0.temperature.soc_temp 1

2 hm-rpc.0.LEQ05XXXX5.1.TEMPERATURE 0

3 hm-rpc.0.LEQ05XXXX5.1.HUMIDITY 0

Type: 0 - number, 1 - string, 2 - boolean

Für jden der Datentypen gibt es eine Tabelle nach dieser Struktur in der alle Daten des gleichen Typs abgespeichert werden:

ts_bool

ts_number

ts_string

Field Type Description

id INTEGER ID of state from "Datapoints" table

ts BIGINT / INTEGER Time in ms till epoch. Can be converted to time with "new Date(ts)"

val REAL Value

ack BIT/BOOLEAN Is acknowledged: 0 - not ack, 1 - ack

_from INTEGER ID of source from "Sources" table

q INTEGER Quality as number. You can find description here

q kann diese Werte annehmen:

0x00 - 00000000 - good (can be undefined or null)

0x01 - 00000001 - general bad, general problem

0x02 - 00000010 - no connection problem

0x10 - 00010000 - substitute value from controller

0x40 - 00100000 - substitute value from device or instance

0x80 - 01000000 - substitute value from sensor

0x11 - 01000001 - general problem by instance

0x41 - 01000001 - general problem by device

0x81 - 10000001 - general problem by sensor

0x12 - 00010010 - instance not connected

0x42 - 01000010 - device not connected

0x82 - 10000010 - sensor not connected

0x44 - 01000100 - device reports error

0x84 - 10000100 - sensor reports error

Nach dem die Daten in eine CSV exportiert sind, muss mann den neuen Datenpunkt aktivieren und die ID des Datenpunktes in der Tabelle Datapoints herausfinden.

Bei mir hm-rpc.0.LEQ05XXXX5.1.TEMPERATURE also die ID 2.

Die Schwierigkeit ist auch, das das Feld ts im Unix Zeitformat abgespeichert wird.

Um die Export Datei in eine CSV der Zieldatenstruktur umzuwandeln habe ich ein kleines Windows Powershell Script geschrieben. Dieses wandelt die Ausgansdatei so um, dass sie mittels mysql routine als csv importiert werden kann. Im Script selber muss noch die Variable $FROM angepasst werden, dies kenzeichnet die Quelle des Adapters aus der Tabelle Sources.

Bei mir system.adapter.hm-rpc.0 also ID 3

Aufruf :

./convert-ccuio.ps1 Quelldatei Datenpunkt-ID

z.B.: ./convert-ccuio.ps1 h2-outtemp.csv 2

Die Ausgabedatei wird so aufgebaut: $outcsv="""$ID"",""$uts"",""$val"",""$ACK"",""$FROM"",""$Q"""

Beispiel: "2","1423996542589","10.8","1","3","0"

Dem Dateinamen wird ein new- vorrangestellt also: new-h2-outtemp.csv

Script kopieren und als convert-ccuio.ps1 abspeichern.(unter Windows 10 getestet)

! #https://www.epochconverter.com/ zum prüfen der Timestamps
! #Export H2 DB
! #call CSVWRITE ('/nfs/syno/pi.2/h2csv/h2-outtemp.csv', 'SELECT * FROM D_BIDCOS_RF_LEQ056XXX5_1_TEMPERATURE')
! #call CSVWRITE ('/nfs/syno/pi.2/h2csv/h2-outhumi.csv', 'SELECT * FROM D_BIDCOS_RF_LEQ056XXX5_1_HUMIDITY')
! $ErrorActionPreference = "stop"
! #Eingabeparameter
! if($args[0] -eq $null)
! {
! #Default Parameter
! $InputFile = "h2-outtemp.csv";
! $OutputFile = "C:\t\ccu\new-h2-outtemp.csv";
! $ID=2
! }
! else
! {
! $InputFile = $args[0];
! $OutputFile = "$pwd\new"+"-"+$args[0];
! $ID = $args[1]
! }
! Write-Host "Lese Datei $InputFile für den Datenpunk ID: $ID Output: $OutputFile"
! # Define Var's for Output Table
! $ACK=1
! $FROM=3
! $Q=0
! # Open Outfile
! $stream = [System.IO.StreamWriter] $OutputFile
! #Import Input CSV
! $values = import-csv $InputFile -Delimiter ","
! Write-Host "Quell Datei gelesen: $InputFile"
! #Konvertiere Zeit nach Unix Zeit ; runde Wert mit 1 Kommastelle
! ForEach ($value in $values){
! #Timestamp ohne Hunderstel
! $tsa=$($value.TS).split(".")
! $ts = $tsa[0]
! $tsh = [double]$tsa[1]
! #Zeit Einlesen und Konvertierung nach Daten Typ Zeit/ Datum
! $timestamp = [datetime]::parseexact($ts,"yyyy-MM-dd HH:mm:ss",$null)
! # Einlesen Wert als Daten Typ Double
! $val = [double]$($value.VALUE)
! # Wert runden
! $val= [math]::Round($val,2)
! # write-host $val
! #Sommerzeit Ja nein
! $summertime=(Get-Date -date $timestamp).IsDayLightSavingTime()
! #Umrechnung Zeit nach Unix Format
! $uts = [double](Get-Date (Get-Date -date $timestamp) -UFormat %s)
! $uts = $uts * 1000
! $uts = $uts + $tsh
! $output="Summertime:$summertime–$flag--- $ts ---- ""$uts"" ---- !$val! -"
! # CSV Format der Ziel Tabelle
! $outcsv="""$ID"",""$uts"",""$val"",""$ACK"",""$FROM"",""$Q"""
! #Schreibe In Datei
! $stream.WriteLine("$outcsv")
! }
! #Datei schliesen
! $stream.close()

Nun kann die Datei, bei mir auf einen Raspi, kopiert werden (SCP/SFTP) auf dem ein MYSQL Client installiert ist und von wo aus ein Zugriff auf die SQL Datenbank möglich ist.

mysql -u <user>-h 192.168.XXX.XX -p -P <portnummer mysql="">mysql -u ioadmin -h 192.168.xxx.xx -p -P 3307

SHOW DATABASES;

SHOW TABLES from iobroker;

use iobroker;

LOAD DATA LOCAL INFILE '/temp/new-h2-outtemp.csv' INTO TABLE ts_number FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

Load DATA hat noch ein paar mehr Schalter um Daten zu überschreiben usw. Bei Interesse einfach in der MYSQL Befehlsreferenz nachlesen.

Bei Erfolg sind nun alle Daten in die Tabelle importiert. Abhängig von den Werten muss die Zieltabelle entsprechend ausgwählt werden.

Ich habe bisher nur Temperatur Werte vom Type 0 Number importiert. Andere Werte und Tabellen muss ich noch ausprobieren.

Da ich in eine Neuinstallation Daten übernommen habe, weiss ich nicht wie es sich mit Installationen verhält, die vorher länger im Betrieb sind.

Also vor dem Import unbedingt eine Datenbanksicherung der Ziel Datenbank erstellen. Für alle Fälle.

Für den Import habe ich ioBroker angehalten. Um zu sehen wieviele Daten in der Tabelle für einen Datenpunkt enthalten sind,

kann mann per SQL die Anzahl abfragen: SELECT count(*) FROM ts_number where ID=2;

Einmal vor dem Import und Anschliesend nachher.

Ich hoffe einigen damit helfen zu können, aber nachmachen auf eigene Gefahr.

Grüße</portnummer></user>

Community Forum

NEWS

Datenübernahme aus CCU-Historian

Support us

579

32.7k

82.5k

1.3m