Datenübernahme aus CCU-Historian
-
Datenübernahme aus CCU-Historian
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_numberwhere 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>
