Modul 93_DbRep - Reporting und Management von Datenbankinhalten (DbLog)

[supergrobi]

Ich hab die AT's alle angepassst und die Leerzeile entfernt.
Werde das aber demnächst mla ausprobieren, vielen Dank!

Gruß
Thomas

[tom2966]

Einen schönen guten Abend,

ich habe im Bereich "Sonstiges" eine Frage zum diesem Modul gestellt- "Sonstiges" weil dort ein ähnliches Thema schon mal ne Rolle gespielt hat. Ich würde den Link dazu gern auch hier noch mal einstellen. Vielleicht kann mir jemand helfen.

https://forum.fhem.de/index.php/topic,122791.0.html

Danke und viele Grüße

Tom

[Muenzi]

Hi, möglicherweise hätte ich länger suchen sollen, daher sorry wenn das Thema schon einmal beantwortet wurde.

Im letzten Jahr füllten sich meine LogDb und DbRep Instanzen recht stark mit Klima-Daten, die ich in recht hoher Auflösung an verschiedenen Stellen aufnahm. Die DB wuchs schnell auf einige Gigabyte an und wurde zunehmend langsam im Zusammenspiel mit Charts in FHEM.

Daher hatte ich überlegt ob man nicht zum Beispiel mit LogDb oder DbRep Partition Pruning nutzen könne um Queries gegen Zeitreihen performant zu halten. Wird sowas bereits intern genutzt oder wäre das hier unsinnig? Was sind eure Erfahrungen?

Außerdem vermisse ich eine Möglichkeit zur Normalisierung der DB-Tabellen, zum Beispiel per Star-Schema (der Performance wegen) um Speicher zu sparen. Gleiche Frage, gibt es da schon Lösungen oder ist das für den normalen FHEM-User overengineered?

[DS_Starter]

Hallo Muenzi,

grundsätzlich ist es so, dass das Standard Datenmodell sehr einfach ist und nur aus einer Tabelle besteht (history).
Dieses Modell besteht m.M. nach seit über 10 Jahren und müßte tatsächlich mal überarbeitet werden.

Es gibt dazu auch schon Ansätze:
https://forum.fhem.de/index.php/topic,111567.0.html

(Da kannst du gern etwas dazu schreiben)

Um den Bestand nicht zu gefährden, sollte es aber eine vollkommen neue Implementierung mit einem neuen DbLog-Modul geben, welches auf einer neuen DB Struktur arbeitet. Man muß natürlich dabei sicherstellen, dass alle DB Typen (MariaDB, SQLite, PostgreSQL) unterstützt werden.

Eigentlich hatte ich mir es mal vorgenommen, aber ich glaube nicht dass ich dazu noch kommen werde, andere Dinge sind meist wichtiger und meine Zeit mit den laufenden Projekten sowie Support aufgebraucht. 

Allerdings könnte man mit MariaDB m.M. nach die Partitionierung durchaus mit den bestehenden Modulen nutzen. Wenn ich es richtig überblicke würde es keine SQL Anpassungen notwendig werden lassen.
Das kannst du bei mir ja mal ausprobieren.

LG,
Heiko

[ch.eick]

Hallo zusammen,

ich denke für die Zwischenzeit wäre eine Auslagerung in eine Archiv Datenbank mit all den aktuellen Funktionalitäten sicher ein gangbarer Weg.
Auch das Ausdünnen der Daten ist immer wieder, gleich auch zu Anfang, ein wichtiger Punkt.
Bei den historischen Wetter- oder auch z.B. PV Daten ist es nach ein bis zwei Jahren sicherlich nicht mehr wichtig alles im Minuten Takt zu haben.

VG
   Christian

Hier mal meine DB Größe mit 2 Jahren PV Daten im Minuten Takt.
Das läuft auf einem RPI4 mit SSD im Docker Container. Die Auswertung erfolgt im Grafana auch im Docker.
Das PV Dashboard aktualisiert sich auch jede Minute. Hierbei war es jedoch wichtig nicht jedes SQL SELECT einzeln abzurufen, sondern lieber mit einem langen
SQL Statement mit vielen JOIN alle Daten innerhalb der Datenbank aufzubereiten.

Code Auswählen Erweitern


MySQL [fhem]> SELECT table_schema "DB Name",
    ->        Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
    ->   FROM  information_schema.tables
    ->   GROUP BY table_schema;
+--------------------+---------------+
| DB Name            | DB Size in MB |
+--------------------+---------------+
| fhem               |        4385.0 |
| information_schema |           0.0 |
+--------------------+---------------+


[Muenzi]

Sehr coole Übersicht ch.eick.

Und klar, da hast du Recht - ausdünnen der Daten ist sicher sinnvoller als Daten-Messi spielen. Habe mir bisher auch mit einer Archiv-Datenbank beholfen, ist nur etwas mehr Aufwand. Daher war auch die Frage ob solche Ansätze eventuell overengineered sind. Ich bin selbst Data Engineer mit Datentöpfen jenseits der 50 GB und max. Zugriffszeiten im Bereich < 10 Sekunden) und habe so vielleicht einen etwas zu ambitionierten Blick auf das Thema.

[Muenzi]

Danke für den Link DS_Starter. Ich schau mal rein und vielleicht kann ich beitragen.

Bin selbst aber eher wenig in SQL und Co unterwegs - dafür der Performance wegen mehr in Delta Tables. Aber let's see.

[ch.eick]

Hallo Heiko,
ich hatte ja schon länger kein SQL mehr gebaut

Das SELECT holt die Jahres Statistiken vom Vorjahr, allerdings musste ich beim reading etwas filtern, was ich gerne noch "sauberer" hätte.

Code Auswählen Erweitern


###################################################
## Jahres Statistik vom Wechselrichter für das Finanzamt Formular
##
SELECT * FROM
  (SELECT TIMESTAMP,READING,cast(VALUE/1000 AS decimal(6)) AS VALUE
    FROM history
    WHERE DEVICE = 'WR_1_API'
       AND READING          LIKE 'Statistic_%Year'
       AND READING NOT LIKE '%Autarky%'
       AND READING NOT LIKE '%Rate%'
       AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1,'-12-31'),'%Y-%m-%d')
       AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE())  ,'-01-01'),'%Y-%m-%d')
    ORDER BY TIMESTAMP DESC
  ) AS x1 GROUP BY x1.READING ;
+---------------------+---------------------------------+-------+
| TIMESTAMP           | READING                         | VALUE |
+---------------------+---------------------------------+-------+
| 2020-12-31 14:57:03 | Statistic_EnergyFeedInGrid_Year |  5200 |
| 2020-12-31 14:57:03 | Statistic_EnergyHomeBat_Year    |  1435 |
| 2020-12-31 14:57:03 | Statistic_EnergyHomeGrid_Year   |  2386 |
| 2020-12-31 14:57:03 | Statistic_EnergyHomePvSum_Year  |  4765 |
| 2020-12-31 14:57:03 | Statistic_EnergyHomePv_Year     |  3330 |
| 2020-12-31 14:57:03 | Statistic_EnergyHome_Year       |  7149 |
| 2020-12-31 14:57:03 | Statistic_TotalConsumption_Year |  7152 |
| 2020-12-31 14:57:03 | Statistic_Yield_NoBat_Year      |  8530 |
| 2020-12-31 14:57:03 | Statistic_Yield_Year            |  9965 |
+---------------------+---------------------------------+-------+


Die readings sehen dann jetzt so aus.
Den TIMESTAMP kann man ja noch weg lassen.

Code Auswählen Erweitern


SqlResultRow_1 TIMESTAMP|READING|VALUE
SqlResultRow_2  2020-12-31 14:57:03|Statistic_EnergyFeedInGrid_Year|5200
SqlResultRow_3  2020-12-31 14:57:03|Statistic_EnergyHomeBat_Year|1435
SqlResultRow_4  2020-12-31 14:57:03|Statistic_EnergyHomeGrid_Year|2386
SqlResultRow_5  2020-12-31 14:57:03|Statistic_EnergyHomePvSum_Year|4765
SqlResultRow_6  2020-12-31 14:57:03|Statistic_EnergyHomePv_Year|3330
SqlResultRow_7  2020-12-31 14:57:03|Statistic_EnergyHome_Year|7149
SqlResultRow_8  2020-12-31 14:57:03|Statistic_TotalConsumption_Year|7152
SqlResultRow_9  2020-12-31 14:57:03|Statistic_Yield_NoBat_Year|8530
SqlResultRow_10 2020-12-31 14:57:03|Statistic_Yield_Year|9965


1) Ich glaube ich hatte auch schon mal gefragt, ob man bei zweispaltigem Output nicht direkt die erste Spalte als reading Namen und die Zweite als Wert im Device setzen kann.
2) Hättest Du eventuell Code, mit dem ich das hin bekomme?

3) Kann ich aus dem sqlCmd direkt auf die interne Variablen Steuerung verweisen?
   Als die Attribute, die man für Zeitsteuerung oder sogar "device" und "reading" zugreifen?

Wenn es eine andere Lösung gibt, wäre ich natürlich auch interessiert.

VG
   Christian

[DS_Starter]

Hallo Christian,

Ich glaube ich hatte auch schon mal gefragt, ob man bei zweispaltigem Output nicht direkt die erste Spalte als reading Namen und die Zweite als Wert im Device setzen kann.

Das habe ich nicht vorgesehen. Grund ist vor allem dass mann mit SQL allerhand "Spielchen" treiben kann, so zum Beispiel im Ergebnis das Reading umbennenn. Dadurch wäre es nicht mehr FHEM konform und würde wieder eine Fehlerbehandlung nach sich ziehen. Denk du weißt was ich meine.

Hättest Du eventuell Code, mit dem ich das hin bekomme?

Du könnest mit dem Attr userExitFn die Ergebnisse in eine eigene Funktion umleiten, aufspalten und eigene Readings im DbRep Device oder Dummy etc. setzen wie du es willst. Beispiel für userExitFn habe ich m.W. im Wiki.

Kann ich aus dem sqlCmd direkt auf die interne Variablen Steuerung verweisen?

Auf die Zeitsteuerung ja (steht in ComRef).
Auf reading/device nicht weil die Attribute zB. auch so gesetzt werden können:

Code Auswählen Erweitern


attr <name> reading eto%,Einspeisung EXCLUDE=etoday
attr <name> reading etotal,etoday,Ein% EXCLUDE=%Wirkleistung
attr <name> device TYPE=SSCam EXCLUDE=SDS1_SVS
attr <name> device TYPE=SSCam,TYPE=ESPEasy EXCLUDE=SDS1_SVS
attr <name> device EXCLUDE=SDS1_SVS
attr <name> device EXCLUDE=TYPE=SSCam


Um diese Methoden in ein beliebig aussehendes SQL zu übernehmen muß man wieder viel prüfen bzw. einschränken.
Oder einfach den User auf Fehler laufen lassen, dann könnte man es einfach zulassen. 

Grüße,
Heiko

[ch.eick]

Du könnest mit dem Attr userExitFn die Ergebnisse in eine eigene Funktion umleiten, aufspalten und eigene Readings im DbRep Device oder Dummy etc. setzen wie du es willst. Beispiel für userExitFn habe ich m.W. im Wiki.
Auf die Zeitsteuerung ja (steht in ComRef).
Auf reading/device nicht weil die Attribute zB. auch so gesetzt werden können:

Code Auswählen Erweitern


attr <name> reading eto%,Einspeisung EXCLUDE=etoday
attr <name> reading etotal,etoday,Ein% EXCLUDE=%Wirkleistung
attr <name> device TYPE=SSCam EXCLUDE=SDS1_SVS
attr <name> device TYPE=SSCam,TYPE=ESPEasy EXCLUDE=SDS1_SVS
attr <name> device EXCLUDE=SDS1_SVS
attr <name> device EXCLUDE=TYPE=SSCam


Um diese Methoden in ein beliebig aussehendes SQL zu übernehmen muß man wieder viel prüfen bzw. einschränken.
Oder einfach den User auf Fehler laufen lassen, dann könnte man es einfach zulassen. 

Das mit reading und device hatte ich gesehen und gehofft dort einfach meine Filter rein zu bekommen.

Die userExitFn wollte ich vermeiden, Du weißt ja, dass ich gerne die Möglichkeiten der Module bis zum Bersten ausschöpfe, damit ich nicht soviele Funktionen schreiben muss und mehr Unterstützung in Anspruch nehmen kann.

VG
   Christian

[DS_Starter]

Naja wie gesagt, ich könnte einfach reading bzw. device als Platzhalter im SQL vorsehen und blind 1:1 übernehmen was im Attr steht. Wenn der User das Attr nicht passend zu seinen SQL definiert läuft das Ding eben auf Error.
Eigentlich auch nicht sooo schlimm wenn ich darüber nachdenke.

Könnte ich eigentlich recht schnell einbauen mal so leichtsinnig dahin gesagt 

[ch.eick]

Naja wie gesagt, ich könnte einfach reading bzw. device als Platzhalter im SQL vorsehen und blind 1:1 übernehmen was im Attr steht. Wenn der User das Attr nicht passend zu seinen SQL definiert läuft das Ding eben auf Error.
Eigentlich auch nicht sooo schlimm wenn ich darüber nachdenke.

Könnte ich eigentlich recht schnell einbauen mal so leichtsinnig dahin gesagt 

Man sollte als user ja auch sein SQL durchaus mal testen. Ich bekomme ja auch so SQL Fehler, wenn ich im sqlCmd was falsches rein schreibe.
Ich finde, das würde es etwas lesbarer machen, wenn man variable Teile so auslagern kann. Geh mal in Dich und wäge es ab.

[DS_Starter]

Ich habe eine Testversion in mein contrib gestellt.
Wenn im sqlCmd die Platzhalter §device§ bzw. §reading§ vorhanden sind, werden an dieser Stelle die Attributwerte von device, reading eingesetzt.
Probiers mal aus.

Grüße,
Heiko

[ch.eick]

Ich habe eine Testversion in mein contrib gestellt.
Wenn im sqlCmd die Platzhalter §device§ bzw. §reading§ vorhanden sind, werden an dieser Stelle die Attributwerte von device, reading eingesetzt.

Moin Heiko,
ich habe es ausprobiert und es hat bei der ersten Ausführung funktioniert.

Danach wurde jedoch im sqlCmd das zuletzt ausgeführte SQL angezeigt, also das mit der Ersetzung und das Original mit den Variablen wurde überschrieben.
Damit würde jedoch beim nächsten Aufruf ein geändertes §reading§ nicht wieder ersetzt werden.

Hier das List nach der Ausführung und die original Zeile vorher:

Code Auswählen Erweitern


SELECT * FROM (SELECT TIMESTAMP,READING,cast(VALUE/1000 AS decimal(6)) AS VALUE FROM history WHERE DEVICE=§device§ AND §reading§ AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1,'-12-31'),'%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()) ,'-01-01'),'%Y-%m-%d') ORDER BY TIMESTAMP DESC ) AS x1 GROUP BY x1.READING ;


Internals:
   CFGFN     
   DATABASE   fhem
   DEF        LogDB
   FUUID      614b0a08-f33f-61a8-f1e8-10ef59dfe90ba545
   FVERSION   93_DbRep.pm:v8.42.9-s24929/2021-09-06
   LASTCMD    sqlCmd SELECT * FROM (SELECT TIMESTAMP,READING,cast(VALUE/1000 AS decimal(6)) AS VALUE FROM history WHERE DEVICE='WR_1_API' AND READING LIKE 'Statistic_%Year' AND READING NOT LIKE '%Autarky%' AND READING NOT LIKE '%Rate%' AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1,'-12-31'),'%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()) ,'-01-01'),'%Y-%m-%d') ORDER BY TIMESTAMP DESC ) AS x1 GROUP BY x1.READING ;
   MODEL      Client
   NAME       LogDBRep_Statistic_previous_Year
   NOTIFYDEV  global,LogDBRep_Statistic_previous_Year
   NR         77178
   NTFY_ORDER 50-LogDBRep_Statistic_previous_Year
   ROLE       Client
   STATE      done
   TYPE       DbRep
   UTF8       1
   HELPER:
     DBLOGDEVICE LogDB
     GRANTS     USAGE,ALL PRIVILEGES
     IDRETRIES  2
     MINTS      2019-04-03 00:23:42
     PACKAGE    main
     SQLHIST   
     VERSION    8.42.9
     CV:
       aggregation no
       aggsec     1
       destr      2020-12-31
       dsstr      2019-04-03
       epoch_seconds_end 1609455599
       mestr      12
       msstr      04
       testr      23:59:59
       tsstr      00:23:42
       wdadd      432000
       yestr      2020
       ysstr      2019
     DBREPCOL:
       COLSET     1
       DEVICE     64
       EVENT      0
       READING    64
       TYPE       64
       UNIT       32
       VALUE      128
   Helper:
     DBLOG:
       state:
         LogDB:
           TIME       1632307720.54312
           VALUE      initialized
   OLDREADINGS:
   READINGS:
     2021-09-23 07:49:23   SqlResultRow_1  TIMESTAMP|READING|VALUE
     2021-09-23 07:49:23   SqlResultRow_10 2020-12-31 14:57:03|Statistic_Yield_Year|9965
     2021-09-23 07:49:23   SqlResultRow_2  2020-12-31 14:57:03|Statistic_EnergyFeedInGrid_Year|5200
     2021-09-23 07:49:23   SqlResultRow_3  2020-12-31 14:57:03|Statistic_EnergyHomeBat_Year|1435
     2021-09-23 07:49:23   SqlResultRow_4  2020-12-31 14:57:03|Statistic_EnergyHomeGrid_Year|2386
     2021-09-23 07:49:23   SqlResultRow_5  2020-12-31 14:57:03|Statistic_EnergyHomePvSum_Year|4765
     2021-09-23 07:49:23   SqlResultRow_6  2020-12-31 14:57:03|Statistic_EnergyHomePv_Year|3330
     2021-09-23 07:49:23   SqlResultRow_7  2020-12-31 14:57:03|Statistic_EnergyHome_Year|7149
     2021-09-23 07:49:23   SqlResultRow_8  2020-12-31 14:57:03|Statistic_TotalConsumption_Year|7152
     2021-09-23 07:49:23   SqlResultRow_9  2020-12-31 14:57:03|Statistic_Yield_NoBat_Year|8530
     2021-09-23 07:49:23   sqlCmd          SELECT * FROM (SELECT TIMESTAMP,READING,cast(VALUE/1000 AS decimal(6)) AS VALUE FROM history WHERE DEVICE='WR_1_API' AND READING LIKE 'Statistic_%Year' AND READING NOT LIKE '%Autarky%' AND READING NOT LIKE '%Rate%' AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1,'-12-31'),'%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()) ,'-01-01'),'%Y-%m-%d') ORDER BY TIMESTAMP DESC ) AS x1 GROUP BY x1.READING ;
     2021-09-23 07:49:23   sqlResultNumRows 9
     2021-09-23 07:49:23   state           done
Attributes:
   DbLogExclude .*
   allowDeletion 0
   comment    Version 2021.09.23 07:48
   device     WR_1_API
   reading    Statistic_%Year  EXCLUDE=%Autarky%,%Rate%
   room       Strom->Energie,System


[DS_Starter]

Moin,

Works as designed.
Die variablen werden natürlich ersetzt. Das sqlCmd muss man einfach neu reinkopieren. Bei Skripten ist das eh kein Problem.