CCU-Historian Grafana kann keine Werte auslesen

Das Langzeitarchiv für HomeMatic

Moderator: Co-Administratoren

Franz6405
Beiträge: 13
Registriert: 01.11.2020, 22:07
System: CCU
Wohnort: Schweiz
Hat sich bedankt: 2 Mal

CCU-Historian Grafana kann keine Werte auslesen

Beitrag von Franz6405 » 01.06.2022, 21:40

Hallo,
Ich habe soeben die neuste Version 3.02 vom CCU-Historian auf der RaspberryMatic (PI 4) als AddOn installiert.
Die alte Version 2.8 wurde deinstalliert und die neue installiert. Die alte Datenbank habe ich gelöscht, da zu viele unnötige Daten enthalten waren.
Nun läuft die CCU-Historien normal. Jedoch kann Grafana die Daten nicht mehr lesen.
Ich habe nun in der H2-Konsole einige Abfragen gemacht und festgestellt. dass keine Werte (Value) direkt abgefragt werden können.

Mit der Abfrage
SELECT * FROM D_HMIP_RF_00185BE9A575BE_1_ACTUAL_TEMPERATURE
werden alle drei Spalten TS, VALUE, STATE korrekt ausgegeben.

Mit der Abfrage
SELECT ts FROM D_HMIP_RF_00185BE9A575BE_1_ACTUAL_TEMPERATURE
wird die Spalten TS korrekt ausgegeben. Dito mit der Abfrage state.

Fehler mit der Abfrage
SELECT value FROM D_HMIP_RF_00185BE9A575BE_1_ACTUAL_TEMPERATURE

wird folgender Fehler angezeigt
"Syntax error in SQL statement "SELECT [*]value\000d\000a FROM D_HMIP_RF_00185BE9A575BE_1_ACTUAL_TEMPERATURE"; expected "DISTINCT, ALL, *, INTERSECTS, NOT, EXISTS, UNIQUE, INTERSECTS"; SQL statement: SELECT value FROM D_HMIP_RF_00185BE9A575BE_1_ACTUAL_TEMPERATURE [42001-212] 42001/42001"

Auch die Schreibweise "value" geht nicht.
Ist da noch ein Fehler in der SQL-Abfrage vorhanden oder wie muss die Abfrage von Value erfolgen?

Danke und Grüsse, Franz

Slice
Beiträge: 1192
Registriert: 03.02.2016, 14:44
System: Alternative CCU (auf Basis OCCU)
Wohnort: irgendwo aus Süd BaWü
Hat sich bedankt: 135 Mal
Danksagung erhalten: 82 Mal

Re: CCU-Historian Grafana kann keine Werte auslesen

Beitrag von Slice » 01.06.2022, 22:23

Hi Franz,
Franz6405 hat geschrieben:
01.06.2022, 21:40
Fehler mit der Abfrage
SELECT value FROM D_HMIP_RF_00185BE9A575BE_1_ACTUAL_TEMPERATURE
schreib mal "VALUE" in der Abfrage, also alles GROSS und ""!

Grüße,
Slice
----------------------------------------------------------------------------------------
Raspi3B+ Bullseye mit HB-RF-ETH und RPI-RF-MOD auf piVCCU-FW 3.73.9 / Addons: CuxD v2.11 - E-Mail v1.7.6 - Patcher v1.0.0 - Philips Hue v3.2.5 - Programme drucken v2.6 - Scriptparser v1.11 - XML-API v2.3
Geräte: 141 / Kanäle: 791 / Datenpunkte: 6080 / SysVars: 275 / Programme: 161 / Regadom IDs: 14010 / 48 CUxD-Kanäle in 3 CUxD-Geräten
Intel NUC i3-5010U @ 2,1 GHz mit 16 GB RAM & 512 GB SSD für Proxmox mit ioBroker VM und CCU-Historian/InfluxDB/Grafana VM
----------------------------------------------------------------------------------------
Projekte im Forum: HomeHub v4.1 / Fritzbox-Anruferliste für HomeHub
----------------------------------------------------------------------------------------

Franz6405
Beiträge: 13
Registriert: 01.11.2020, 22:07
System: CCU
Wohnort: Schweiz
Hat sich bedankt: 2 Mal

Re: CCU-Historian Grafana kann keine Werte auslesen

Beitrag von Franz6405 » 02.06.2022, 21:11

Hallo Slice

Danke, Super funktioniert.

Wieso dies beim State sowie bei ts kleingeschrieben und ohne "" geht ist sehr speziell. In der Version 2.8 war dies kein Problem.
Nun funktionieren die meisten Abfragen aus Grafana wieder, aber leider noch nicht alle.

Wurde die TOP Funktion nicht mehr implementiert, oder ist eine andere Schreibweise erforderlich?
Folgende Funktion zum Auslesen der maximalen Geschwindigkeit aus einem Bereich geht seit V3.02 nicht mehr:

SELECT TOP 1
ts AS "time",
"VALUE" AS "MaxWind"
FROM D_HMIP_RF_00185BE9A575BE_1_WIND_SPEED
WHERE
ts BETWEEN '2022-05-31T18:37:56.56Z' AND '2022-06-02T18:37:56.56Z'
ORDER BY "VALUE" DESC

Wenn TOP 1 weggelassen wird, werden alle Werte korrekt ausgegeben.
Mit TOP 1 müsste der oberste Wert aus der neu sortierten Tabelle ausgegeben werden. Es wird aber eine Fehlermeldung angezeigt.

Hast du da auch eine Lösung?

Mathias
Beiträge: 1767
Registriert: 03.11.2010, 10:25
System: CCU
Wohnort: Aachen
Hat sich bedankt: 58 Mal
Danksagung erhalten: 253 Mal
Kontaktdaten:

Re: CCU-Historian Grafana kann keine Werte auslesen

Beitrag von Mathias » 02.06.2022, 21:27

Franz6405 hat geschrieben:
02.06.2022, 21:11
Wieso dies beim State sowie bei ts kleingeschrieben und ohne "" geht ist sehr speziell. In der Version 2.8 war dies kein Problem.
Der Bezeichner VALUE ist in der neuen Version der eingebetteten Datenbank ein reservierter Bezeichner. Deshab muss dieser Spaltenname jetzt in " gesetzt werden.
Franz6405 hat geschrieben:
02.06.2022, 21:11

Code: Alles auswählen

SELECT TOP 1
  ts AS "time",
  "VALUE" AS "MaxWind"
FROM D_HMIP_RF_00185BE9A575BE_1_WIND_SPEED
WHERE
  ts BETWEEN '2022-05-31T18:37:56.56Z' AND '2022-06-02T18:37:56.56Z'
  ORDER BY "VALUE" DESC 
Wenn TOP 1 weggelassen wird, werden alle Werte korrekt ausgegeben.
Mit TOP 1 müsste der oberste Wert aus der neu sortierten Tabelle ausgegeben werden. Es wird aber eine Fehlermeldung angezeigt.

Hast du da auch eine Lösung?
Vielleicht hat sich hier auch etwas geändert. Entferne mal bitte TOP 1 und hänge ans Ende FETCH FIRST ROW ONLY an.

Du kannst den Ausdruck aber schneller machen, wenn Du ihn folgendermaßen umschreibst:

Code: Alles auswählen

SELECT MAX("VALUE") AS "MaxWind"
FROM D_HMIP_RF_00185BE9A575BE_1_WIND_SPEED
WHERE
  ts BETWEEN '2022-05-31T18:37:56.56Z' AND '2022-06-02T18:37:56.56Z'

Franz6405
Beiträge: 13
Registriert: 01.11.2020, 22:07
System: CCU
Wohnort: Schweiz
Hat sich bedankt: 2 Mal

Re: CCU-Historian Grafana kann keine Werte auslesen

Beitrag von Franz6405 » 02.06.2022, 22:55

Danke

Ja, mit deinem Vorschlag FETCH FIRST ROW ONLY am Schluss erhalte ich das gewünschte Ergebnis.
Dein zweiter optimierter Vorschlag mit SELECT MAX("VALUE") AS "MaxWind" funktioniert in der H2 Konsole.
Da ich den MaxWind bereits als Gauge darstelle lese ich in Grafana mit meiner Variante nur noch das Entsprechende Datum zum maximalen Wind aus.

Franz6405
Beiträge: 13
Registriert: 01.11.2020, 22:07
System: CCU
Wohnort: Schweiz
Hat sich bedankt: 2 Mal

Re: CCU-Historian Grafana kann keine Werte auslesen

Beitrag von Franz6405 » 05.06.2022, 17:03

Ein weitere Befehl der mit Historian 2.8 funktionierte und in der neuen Version 3.02 nicht mehr möglich ist:
extract(epoch from ts AS "time"
Dieser Befahl habe ich in Grafana wie folgt häufig verwendet um z.B. Stundenmittelwerte zu bilden.
SELECT
floor(extract(epoch from ts)/3600)*3600 AS "time",
avg(value) as "Mittlere Windgeschwindigkeit [km/h]"
...

Gibt es dazu auch eine einfache Alternative?

Wurden bei der neuen Version Historian nicht mehr alle Befehle der Postrgee-SQL abfragen implementiert?
Werden diese fehlenden Funktionen in den nächsten Historian-Versionen wieder implementiert?
Danke für eure Rückmeldungen

Mathias
Beiträge: 1767
Registriert: 03.11.2010, 10:25
System: CCU
Wohnort: Aachen
Hat sich bedankt: 58 Mal
Danksagung erhalten: 253 Mal
Kontaktdaten:

Re: CCU-Historian Grafana kann keine Werte auslesen

Beitrag von Mathias » 06.06.2022, 11:59

In der V3 wurde die interne Datenbankmaschine H2Database aktualisiert. Die Befehle/Funktionen können nicht in einer kommenden Version ergänzt werden, da dies komplett von der eingebetteten Datenbank abhängt. Es werden nicht alle PostgreSQL-Befehle unterstützt, da es sich nur um eine Emulation handelt. Die Änderungen verbessern generell die Kompatibilität zum SQL-Standard.

Die vollständige Beschreibung der unterstützten Funktionen und Kommandos sind auf der H2Database-Seite zu finden.

EXTRACT(EPOCH FROM TS) müsste aber noch gehen. Du kannst aber auch folgenden Ausdruck verwenden:

Code: Alles auswählen

SELECT DATEDIFF(HOUR, TIMESTAMP'2000-01-01', TS), AVG("VALUE") FROM D_HMIP_RF_xxxx_1_CURRENT_ILLUMINATION GROUP BY  DATEDIFF(HOUR, TIMESTAMP'2000-01-01', TS) ORDER BY DATEDIFF(HOUR, TIMESTAMP'2000-01-01', TS)

Franz6405
Beiträge: 13
Registriert: 01.11.2020, 22:07
System: CCU
Wohnort: Schweiz
Hat sich bedankt: 2 Mal

Re: CCU-Historian Grafana kann keine Werte auslesen

Beitrag von Franz6405 » 09.06.2022, 22:07

Danke für eure Unterstützung. Mit den oben genannten Anpassungen funktionieren nun die meisten Grafana-Abfragen.

Leider sind noch wenige etwas längere Abfragen die nicht funktionieren, obwohl wenn die gleiche Abfrage in der H2 Console ausgeführt wird, die richtigen Werte ohne Fehlermeldung ausgegeben werden. Bei Grafana wird bei der gleichen Abfrage, welche ich unten eingefügt habe, dieser Fehler angezeigt:
db query error: pq: General error: "java.lang.NullPointerException" [50000-212]

Abfrage zur Darstellung der Temperatur, Feuchtigkeit und Taupunkt als zeitlicher Verlauf mit Fehlerausgabe in Grafana:

Code: Alles auswählen

WITH Temperatur AS (
SELECT
   floor(extract(epoch from ts)/3600)*3600 AS Time,
   AVG("VALUE") AS Ta
FROM D_HMIP_RF_0010DA499479DD_1_ACTUAL_TEMPERATURE
WHERE
 ts BETWEEN '2022-06-07T19:19:20.076Z' AND '2022-06-09T19:19:20.076Z'
GROUP BY  floor(extract(epoch from ts)/3600)*3600
)
, Feuchte AS (
SELECT
   floor(extract(epoch from ts)/3600)*3600 AS Time1,
   AVG("VALUE") AS Fe
FROM D_HMIP_RF_0010DA499479DD_1_HUMIDITY
WHERE
   ts BETWEEN '2022-06-07T19:19:20.076Z' AND '2022-06-09T19:19:20.076Z'
GROUP BY floor(extract(epoch from ts)/3600)*3600
)
SELECT
 Time, 
  Ta,
 Fe,
240.7*LOG(6.1078 * POWER(10, ((7.6*Ta)/(240.7+Ta)))*Fe/100/6.1078)/(7.6-LOG(6.1078 * POWER(10, ((7.6*Ta)/(240.7+Ta)))*Fe/100/6.1078)) AS Taupunkt
FROM Temperatur JOIN Feuchte ON Time = Time1
GROUP by Time
In Grafana wird anstelle WHERE ts BETWEEN '2022-06-07T19:19:20.076Z' AND '2022-06-09T19:19:20.076Z der flexible Ausdruck $__timeFilter(ts) verwendet.
Irgendwo ist da vermutlich im Historian noch ein Fehler vorhanden, denn mit der alten Version werden die korrekten Daten zurückgegeben.

Mathias
Beiträge: 1767
Registriert: 03.11.2010, 10:25
System: CCU
Wohnort: Aachen
Hat sich bedankt: 58 Mal
Danksagung erhalten: 253 Mal
Kontaktdaten:

Re: CCU-Historian Grafana kann keine Werte auslesen

Beitrag von Mathias » 20.06.2022, 22:51

Wenn es in der H2Database-Konsole funktioniert und in Grafana nicht, dann muss der Fehler in Grafana liegen. Der CCU-Historian ist in diesem Fall nicht zwischen den beiden.

Franz6405
Beiträge: 13
Registriert: 01.11.2020, 22:07
System: CCU
Wohnort: Schweiz
Hat sich bedankt: 2 Mal

Re: CCU-Historian Grafana kann keine Werte auslesen

Beitrag von Franz6405 » 21.06.2022, 22:17

Deine Antwort, dass es bei Grafana liegen muss, kann ich mir noch schwer vorstellen, denn mit dem Historian 2.8 funktionierte dies fehlerfrei.
Bei Grafana wurde nichts geändert ausser der Anpassung bei der Abfrage "VALUE". Es wurde kein Update oder andere Änderungen in Grafana vorgenommen.
Nur Historian wurde auf Version 3 angepasst.

Es könnte auch ein Unterschied in der Datenrückgabe zu diesem Problem in Grafana führen.
So ist mir aufgefallen, dass in der H2-Konsole bei einer einfachen Abfrage eines einzelnen Datenpunkts mit SELECT die Zeit unterschiedlich zurückgegeben wird als bei der komplexen Abfrage.

Bei einer einfachen SELECT Abfrage in der H2-Konsole wird Folgendes zurückgegeben:

TIME TA
2022-06-20 21:35:30.304 26.2
2022-06-20 22:03:59.797 25.7
2022-06-20 22:31:08.29 25.2

Bei der komplexeren Abfrage, wie ich Sie oben bei der Abfrage aus mehreren Datenpunkten und der Berechnung vom Taupunkt geschrieben habe, wird in der H2-Konsole Folgendes zurückgegeben.

TIME TA FE TAUPUNKT
1654635600 17.1333333333333333333333333 54 7.76191249729
1654639200 17.1615384615384615384615385 53.7692307692307692307692308 7.72534055214
1654642800 17.1307692307692307692307692 53.7692307692307692307692308 7.69678193435

Die zurückgegeben Zeit ist unterschiedlich formatiert.
Könnte dies die Ursache sein?
Kann die Abfrage angepasste werden, dass auch bei der komplexen Abfrage das gleiche Datenformat der Zeit zurückgegeben wird?

Antworten

Zurück zu „CCU-Historian“