HA Recorder, SQLite, InfluxDB, Grafana

[scw2wi]

Eigentlich wollte ich anfangs nur die InfluxDB kennenlernen, dabei hab ich mich aber auch gleich noch mit dem HA-Recorder und ein paar anderen Dingen beschäftigt.
Da das alles irgendwie zusammen gehört, hab ich es hier auch entsprechend gemeinsam gepostet.
Wer sich für diese Einleitung nicht interessiert, der kann direkt zu Post 2 weiterspringen, wo die Installation der InfluxDB beschrieben ist.

Teil 1: HA-Recorder, SQLite und Alternativen
Teil 2: InfluxDB - Installation
Teil 3: InfluxDB - erste Queries
Teil 4: InfluxDB - Retention Policies (RPs) und Continuous Queries (CQs)
Teil 5: Grafana

Es gibt ein Community Add-On, um InfluxDB (v1) intern eingebettet in HA zu installieren, inkl. Chronograf & Kapacitor (Verwaltungstool & Verarbeitungs-Engine)
ein Influxdb V2 Repository Add-On von Daniel Oldberg, um InfluxDB (v2) intern zu installieren (da sind Chronograf & Kapacitor nicht mehr getrennte Tools),
und eine Integration, um eine externe InfluxDB (v1 oder v2) mit HA zu verbinden.

InfluxDB speichert die Daten.
Chronograf bietet eine Benutzeroberfläche für die Verwaltung und Visualisierung.
Kapacitor verarbeitet die Daten und löst z.B. Alarme aus.

Bevor ich die InfluxDB installiere, möchte ich mir noch einen Überblick verschaffen, wie HA mit einer DB umgeht.
Informationen dazu erhoffe ich mir vom Recorder, der History, der Statistics Integration, möglicherweise auch noch von der Logbook card.

Der Recorder zeichnet Daten auf und verwendet dafür die integrierte SQLite Datenbank, die aus einer einzigen Datei besteht.
Etwas verwirrend wird überall geschrieben, dass nach 10 Tagen die Daten gelöscht (purged) werden, das ist aber nur die halbe Wahrheit.
Es gibt 2 Datenbereiche, das Kurzzeitgedächtnis, wo jeder Statuswechsel 10 Tage erhalten bleibt,
und das Langzeitgedächtnis (long term statistics) wo die Daten im 1h Intervall unbegrenzt erhalten bleiben.
In der History sieht man daher die letzten 10 Tage fett und die älteren Daten dünn dargestellt.
Laut Doku landen aber nur Sensordaten mit SensorStateClass.MEASUREMENT, TOTAL oder TOTAL_INCREASING in den Langzeit-Statistiken.

Leider hatte ich alle meine Template-Sensoren im alten Format erstellt, und nur das neue unterstützt state_class.
Hier ist eine Anleitung, wie man das ändern kann.

Das 10 tägige Kurzzeitgedächtnis des Recorders kann man ebenfalls sehr leicht ändern, wobei nicht geraten wird, zu viele Tage aufzuheben, da die SQLite-Performance rasch an die Grenze kommt. Ich hab auch noch gleich ein paar Sensoren von der Aufzeichnung ausgenommen, für die ich keine Recorder-Daten benötige.

recorder:
  purge_keep_days: 15
  exclude:
    entity_globs:
      - sensor.flightradar*
      - sensor.spritpreis*

Neben der Default Datenbank SQLite kann der Recorder auch noch 3 andere Datenbanken verwenden.
MariaDB (deutlich leistungsfähiger als SQLite)
Dafür gibt es sogar ein Add-On, um diese direkt in HA zu installieren, die anderen beiden können nur extern installiert werden.
MySQL (sehr ähnlich zu MariaDB, hat für Privatanwender aber eher Nachteile, z.B. fehlende JSON-Funktionen)
PostgreSQL (mehr Möglichkeiten als MariaDB, z.B. TimescaleDB Erweiterung für Zeitreihen)

Es gibt auch noch ein Add-On für SQLite Web, damit kann man in der internen SQLite Datenbank etwas herumstöbern.
Achtung: Ich würde hier nur lesen und weder auf den Insert-Button und schon gar nicht auf den Drop Button klicken.

Hier ein paar interessante Queries, die man direkt im Query-Bereich von SQLite Web eingeben kann:

​SELECT s.state_id, s.state, sm.entity_id, datetime(s.last_reported_ts, 'unixepoch', 'localtime') AS timestamp
FROM states s
JOIN states_meta sm ON s.metadata_id = sm.metadata_id
ORDER BY s.last_reported_ts DESC;

Hier kann man recht gut erkennen, welche Sensordaten sich sehr häufig ändern.
Bei mir sind es die Up/Download-Geschwindigkeiten der Fritzbox, die alle 30s geschrieben werden.

Noch deutlicher sieht man die High-Score Sensoren bei dieser Query:

​SELECT sm.entity_id, COUNT(s.state_id) AS state_count
FROM states s
JOIN states_meta sm ON s.metadata_id = sm.metadata_id
GROUP BY sm.entity_id
ORDER BY state_count DESC
LIMIT 10;

Obwohl ich SQL recht gut beherrsche, hab ich mir diese Queries von ChatGPT erzeugen lassen, das geht einfach viel schneller.

Beim Umstieg von SQLite auf eine der 3 anderen DBs findet man eine Warnung in der Doku: die Daten werden dabei nämlich nicht migriert. Entweder entscheidet man sich also dafür, bevor man HA produktiv einsetzt, oder man muss sich selbst um die Migration der Daten kümmern.

Ich war anfangs etwas verwirrt, warum es zwar ein InfluxDB Add-On gibt, der Recorder aber mit InfluxDB gar nicht umgehen kann.
InfluxDB ist also nicht dafür geeignet, SQLite zu ersetzen, sondern wird parallel dazu eingesetzt - wozu genau, das wollen wir später erkunden.

Noch ein Hinweis zur InfluxDB Version.
Das offizielle Add-On installiert die V1, das ist die Variante, die ich hier beschreibe.
Ein nicht offizielles Add-On installiert die V2, das geht aber nur über ein eigenes Repository
Die Integration spricht mit einer externen V1 oder V2.
Die Abfragesprachen unterscheiden sich, bei V1 wird meist InfluxQL verwendet, bei V2 Flux.
Ein Umstieg von V1 auf V2 ist daher eine etwas größere Aufgabe.
Flux bietet zwar Vorteile bei Datenverarbeitungs-Pipelines, für SQL-kundige ist InfluxQL aber verständlicher.

Es gibt mit Prometheus noch eine weitere bekannte Zeitreihen-DB, diese verwendet PromQL als Abfragesprachen und ist ebenfalls mit Grafana kompatibel. Es gibt dafür auch eine HA-Integration, und es gab auch mal ein Add-On, das jedoch nicht mehr gepflegt wird.

Es gibt auch noch andere Lösungen, um (vor allem Energie-) Daten zu speichern und zu visualisieren, wie z.B. Emoncms mit dessen HA-Integration, aber ich möchte nicht mehr zu viel abschweifen, und wieder zur InfluxDB zurückkommen.

[scw2wi]

Im 2. Teil startet jetzt die Installation der InfluxDB V1 über das Add-On.

Ich lasse es mir auch in der Seitenleiste anzeigen, und wer oben 1s lang auf Home Assistant klickt, der kann die Seitenleiste auch individuell sortieren.
Bevor wir das Add-On starten ist es immer eine gute Idde, noch einen Blick in die Konfiguration (rechts oben) zu wagen.

Da ich mir dieses Mal erspart habe, Screenshots von der Installation zu erstellen, ein Tipp für all jene, denen Screenshots lieber sind als langer Text.
Daniel Strohbach hat eine Anleitung mit vielen Screenshots erstellt, damit geht es vielleicht noch etwas leichter.

Es gibt auch ein 1h-Video das zwar schon 1 Jahr alt ist, aber immer noch viele wertvolle Infos (vor allem zu Grafana) liefert.
Einen Hinweis darin konnte ich jedoch nicht verifizieren: Grafana benötigt nicht (wie behauptet) die Retention Policy autogen, das Tool kommt mit jeder beliebigen anderen RP genauso gut zurecht (später dazu noch mehr).

Weiter mit der Konfiguration der InfluxDB:
auth (also die user authentication) und reporting bleiben auf EIN,
ssl setze ich meist (wie auch bei Node-RED) auf AUS, da ich keine Zertifikate hinterlegt habe.
In diesem Fall könnte es sogar auf EIN bleiben, was mich etwas wundert. Warum funktioniert das hier und bei Node-RED nicht?
Wer sich mit envvars (Environment Variables) näher beschäftigen will, findet hier eine Doku dazu.
Es gibt hier noch den wichtigen Hinweis, dass alle Variablen (also auch z.B. "true") unter Hochkomma gestellt werden müssen, wie auch im Beispiel der Doku.
Der log_level steht per Default auf info und kann erst dann verstellt werden, wenn man "nicht verwendete optionale Konfigurationsoptionen" einblendet, wobei dieser Text eigentlich falsch ist, denn der Schalter blendet auch die verwendeten optionalen Konfigurationsoptionen wieder aus (leider unnötig verwirrend). Besser wäre es gewesen, den Schalter als "weitere optionale Parameter" zu bezeichnen, und diese dann auch darunter und nicht darüber einzublenden.

Als nächstes das Add-On STARTEN, und die Benutzeroberfläche öffnen.

Beim ersten Start kann es sein, dass die Oberfläche eine nichts sagende Fehlermeldung (502: Bad Gateway) anzeigt, in diesem Fall musste ich 2 bis 3x auf Wiederholen klicken. Es kann aber auch sein, dass man nur einen kreiselnden Ring sieht, dann muss man nochmals woanders hingehen und wieder zurückkommen, damit es funktioniert.

Laut Anleitung soll als nächstes eine Datenbank erstellt werden, der Name kann z.B. home_assistant sein.

Im InfluxDB Fenster links die Krone anklicken, damit landen wir im Bereich "InfluxDB Admin".
Es gibt hier bereits die Datenbank "_internal", wir erstellen mit "+ Create Database" eine weitere.
Die Retention Policy lassen wir noch auf unendlich.

Es gibt übrigens eine Möglichkeit, diese _internal Metrik-DB zu vermeiden. Falls jemand SSD-Platz spraren möchte, einfach in der Add-On Konfiguration diese Zeilen ergänzen.

​envvars:
  - name: INFLUXDB_MONITOR_STORE_ENABLED
    value: "false"

Der nächste Schritt ist die Erstellung eines Users, auch dieser Name kann wieder homeassistant (oder auch irgendwas anderes) sein.
Unter InfluxDB Admin auf den zweiten Tab "Users" wechseln, hier gibt es bereits die User chronograf und kapacitor, wir erstellen mit "+ Create User" einen weiteren.
Laut Anleitung (der Screenshot ist dort leider nicht mehr aktuell) benötigt dieser User alle Rechte, der Button "Grant Admin" macht genau das, oder eigentlich nur fast.
Der Button "Grant Admin" öffnet einen Tooltip "Grant ALL Privileges" und erst dieser Tooltip granted dann wirklich.
Und wenn man dann vom Tab "Users" zum Tab "Users" wechselt (nicht wundern, das geht!) dann sieht man auch die Liste aller User, unserer sollte jetzt Admin = yes haben.

Die InfluxDB-Seite ist jetzt geschafft, weiter geht's in HA in der Configuration.yaml

Wir müssen uns jetzt überlegen, was wir mit der InfluxDB eigentlich machen wollen.
Die Anleitung zur Anbindung findet man bei der Integration.
Ich verstehe das so, dass man mit dem Add-On die DB installiert und HA über die Integration damit dann kommuniziert.

Gleich ganz oben steht unter Note ein wichtiger Satz: InfluxDB läuft parallel zu SQLite und ersetzt diese nicht.
Das hab ich mir schon weiter oben gedacht, da der Recorder ja nicht von SQLite auf InfluxDB umgebogen werden kann, sondern nur auf die 3 anderen.

Es gibt nun mehrere Möglichkeiten.

Variante 1: Ich suche mir genau jene Sensoren heraus, deren Daten ich länger als 10 Tage aufheben möchte, und schreibe diese parallel zu SQLite auch in die InfluxDB.
Nach 10 Tagen (oder nach purge_keep_days, falls es geändert wurde), werden die Daten in SQLite vom Default 30s Intervall auf 1h-Intervall verdichtet, oder auch komplett gelöscht (siehe StateClass).
In der InfluxDB bleiben die Daten im vollen Intervall erhalten, ich kann sie aber über die Retention Policy jederzeit individuell verdichten.
Nachteil dieser Variante: Wenn ich später einen weiteren Sensor aufnehme, dann fehlt mir von diesem die History.

Variante 2: Ich nehme erstmal alle Daten auf und beobachte die Datenmenge. Wenn es zu viel wird, dann kann ich jederzeit gezielt einzelne Daten wieder entfernen, die mich nicht interessieren.
Ich hab mich für Variante 2 entschieden, da ich so keine Daten übersehen kann, die mir dann doch wichtig gewesen wären.

Folgenden Code habe ich in meiner configuration.yaml ergänzt.

influxdb:
  api_version: 1
  host: localhost
  port: 8086
  database: home_assistant
  username: homeassistant
  password: !secret influxdb_password # Passwort steht in secrets.yaml
  ssl: false # sollte genauso gesetzt sein wie in der Konfiguration des Add-On
  verify_ssl: false # Verify SSL certificate for HTTPS request
  max_retries: 3 # default: 0
  precision: ns # default: ns
  measurement_attr: unit_of_measurement # Possible values: unit_of_measurement (default), domain__device_class or entity_id.
  default_measurement: state # falls measurement_attr (also unit_of_measurement) nicht existiert
  retention_policy: rp_raw_data_1h
  exclude:
    entity_globs: # Exclude all entities matching a listed pattern.
      - sensor.flightradar*
      - sensor.spritpreis*
  tags: # Tags to mark the data.
    instance: prod
    source: hass
  tags_attributes: # The list of attribute names which should be reported as tags and not fields to InfluxDB.
    - friendly_name
  # ignore_attributes # The list of attribute names to ignore when reporting to InfluxDB.

InfluxDB unterscheidet zwischen tags und fields. Wenn man nach etwas suchen oder gruppieren möchte, dann sollte man es als tag erfassen, nicht als field. Der Parameter tags_attributes kann dafür verwendet werden.
Jetzt ist der richtige Zeitpunkt für einen Neustart, und mit etwas Glück haben wir danach dann Daten in der InfluxDB.
Sollte etwas nicht funktionieren, dann gibt es hoffentlich eine Benachrichtigung mit einer Fehlermeldung, oder einen Logbuch-Eintrag, oder einen Eintrag unter Einstellungen/Protokolle.

[scw2wi]

Im 3. Teil erkunden wir die neue InfluxDB

InfluxDB Daten finden wir im InfluxDB Fenster im Explorer (das ist links das 3. Symbol mit den Linien).
Links unten gibt es 2 Eintragungen: _internal.monitor und darunter home_assistant.autogen (das sind unsere Daten)
Man kann erkennen, dass alle Einträge nach Einheiten gruppiert sind (measurement_attr: unit_of_measurement),
und es auch die Dummy-Einheit "state" gibt (default_measurement: state), wo alle restlichen Werte gesammelt sind.
Um sich Daten anzeigen zu lassen können wir im mittleren Bereich eine Einheit aufklappen und darin eine entity_id auswählen, im rechten Bereich wählen wir "value", im oberen Bereich sehen wir die Grafik.

Wir können uns auch anzeigen lassen, wie die Datenmenge mit jeder Sekunde weiter ansteigt.
Dafür klicken wir links auf _internal.monitor, in der Mitte auf shard/database/home_assistant und rechts auf diskBytes.
Rechts oben können wir von Paused auf ein Refresh-Intervall wechseln und auch mehr als das "Past 1h"-Intervall anzeigen lassen.
Wenn man die Art der Visualisierung ändern möchte, dann klickt man oben in der Mitte auf Visualization und wählt links unten als Visualization Type z.B. Table, dann sieht man die Rohdaten.

Ich möchte mir noch die Größe der DB ansehen, was bei der InfluxDB scheinbar gar nicht so einfach ist.
Mit InfluxQL ist mir so eine Abfrage jedenfalls nicht auf Anhieb gelungen und selbst bei Flux sind alle von mir befragten KIs mehr oder weniger gescheitert, aber mit vereinten Kräften haben wir es dann doch geschafft.
Im Explore Fenster gibt es links oben einen Button, um von InfluxQL auf Flux umzuschalten und bei SCRIPT wird dann das Script eingefügt und mit "Run Script" ausgeführt.

Mit folgender Flux Abfrage sieht man die Größe der Daten entsprechend dem gewählten Range (also z.B. -1h, -1d, -7d, -30d, -1y), als Visualization wähle ich dazu Table.

from(bucket: "_internal/monitor")
  |> range(start: -1d)  // Zeitraum muss offensichtlich begrenzt werden, ohne hat's bei mir jedenfalls nicht funktioniert
  |> filter(fn: (r) => r._measurement == "shard")
  |> filter(fn: (r) => r._field == "diskBytes")
  |> filter(fn: (r) => r.database == "home_assistant")
  |> group()
  |> sum(column: "_value")
  |> map(fn: (r) => ({ r with _value: r._value / 1024 / 1024 }))  // Bytes in MB umrechnen

Mit InfluxQL sieht es im Vergleich dazu richtig übersichtlich aus. Dass man eine summe ohne "group by" berechnen kann, muss man als SQL-Programmierer erst mal lernen.

select sum(diskBytes)/1024/1024 as megabytes
from "_internal"."monitor"."shard"
where "database" = 'home_assistant'
AND time > now() - 1d

Interessant ist auch der Zuwachs, wobei man hier beachten muss, dass die Daten immer wieder komprimiert werden, und der Zuwachs dann negativ angezeigt wird, siehe: TSM (Time-Structured Merge Tree) Kompressionsprozess
Eine gute Visualization dafür ist z.B. die Balkenansicht.

from(bucket: "_internal/monitor")
  |> range(start: -30h)  // Zeitraum der letzten 30 Stunden
  |> filter(fn: (r) => r._measurement == "shard")
  |> filter(fn: (r) => r._field == "diskBytes")
  |> filter(fn: (r) => r.database == "home_assistant")
  |> aggregateWindow(every: 1h, fn: sum, createEmpty: false)
  |> difference()
  |> map(fn: (r) => ({ r with _value: r._value / 1024 / 1024 }))  // Bytes in MB umrechnen
  |> group(columns: ["_time"])
  |> sum()
  |> sort(columns: ["_time"])

Die interne SQLite ist mit 200 MB nach 3 Monaten vergleichsweise klein, da ja auch dort die Daten nach 10 Tagen verdichtet oder sogar gelöscht werden.

Ich möchte aber gerne die Größe der InfluxDB in HA anzeigen können. Dafür benötige ich einen Sensor, den diesmal ChatGPT besser erstellt hat als DeepSeek.

sensors.yaml

- platform: influxdb
  host: localhost
  port: 8086
  username: homeassistant
  password: !secret influxdb_password
  scan_interval: 600 # alle 10 min aktualisieren
  queries:
    - name: InfluxDB Size
      unit_of_measurement: "MB"
      measurement: '"_internal"."monitor"."shard"'
      field: diskBytes
      where: '"database" = ''home_assistant'' AND time > now() - 365d'
      group_function: sum
      value_template: '{{ (value | float (default=0) / 1024 / 1024) | round(3) }}'  # Bytes in MB umrechnen

Der Sensor wird erst nach einem HA-Neustart erstellt.

Nach all diesen Queries über die DB-Size bin ich mir immer noch nicht sicher, ob diese Angaben tatsächlich stimmen.
Ich beobachte in HA den sensor.system_monitor_belegter_massenspeicher, dort konnte ich z.B. keine Änderung durch die Installation der InfluxDB erkennen, vielleicht wird diese aber nicht mit erfasst.
Über das Samba Add-On konnte ich den Pfad zur InfluxDB leider nicht finden, sonst hätte ich mir direkt die Größe anzeigen lassen können.

Als nächstes folgt meine Lieblings-Query zur Anzeige der Top-10 Sensoren.

from(bucket: "home_assistant/autogen")
  |> range(start: -1d)  // Zeitbereich 1 Tag
  |> filter(fn: (r) => r._field == "value") // Nur Werte aus dem Feld "value" filtern
  |> group(columns: ["entity_id"]) // Nach entity_id gruppieren
  |> count() // Anzahl der Messwerte pro entity_id zählen
  |> group() // Gruppierung wieder aufheben, um alle Entity-IDs zu vergleichen
  |> sort(columns: ["_value"], desc: true) // absteigend nach Häufigkeit sortieren
  |> limit(n: 10) // Top-10 auswählen

Die Top-1 Datenquelle kann sich jetzt jeder selbst als Tabelle anzeigen lassen. Da die Query dazu bei jedem anders aussieht, muss sich das jeder selbst erarbeiten. Bei mir ist das ein Template-Sensor, den ich mal erstellt habe.

SELECT *
FROM "home_assistant"."autogen"."MiB"
WHERE "entity_id" = 'system_monitor_belegter_arbeitsspeicher'

[scw2wi]

Im 4. Teil geht es darum, sich gute Retention Policies (RPs) zu überlegen, damit die InfluxDB nicht unendlich groß wird.
Viele machen das erst dann, wenn die DB wirklich zu groß wird, und man kann das auch so machen.
Es steckt jedoch der RP-Name in jeder Query und wenn man etwas ändert, dann kann es leicht sein, dass man danach alle seine Queries anpassen muss.
Folglich hat es durchaus auch einen Vorteil, wenn man die RPs von Anfang an sinnvoll wählt und danach möglichst auch nicht mehr ändert.

Für Test-Zwecke will ich nicht zu lange warten, daher wähle ich fürs erste mal eine extrem kurze Zeit,
also z.B. nach 1 Stunde auf 1-min Intervall verdichten, nach 4 Stunden auf 5-min Intervall und nach 1 Tag dann auf 1h-Intervall.
Das muss aber jetzt nicht jeder hier so nachmachen, weiter unten beschreibe ich dann für mich sinnvoll gewählte Intervalle.

Zuerst legen wir 4 RPs an. In der ersten landen die Row-Daten mit voller Auflösung, alle weiteren werden über Continuous Queries (CQs) befüllt.
Man kann diese Create Befehle ebenfalls im Query-Bereich absetzen, bekommt dann aber eine verwirrende Meldung angezeigt: Your query is correct but returned no results.
Wer das nicht mag kann die Retention Policies auch im Admin Bereich erstellen, ansehen geht dort ohnehin am besten.

-- RP für Rohdaten, hier sollen alle Daten in voller Auflösung für 1 Stunde erhalten bleiben. Das ist also die Default-RP, wo die Daten zuerst landen.
CREATE RETENTION POLICY "rp_raw_data_1h" ON "home_assistant" DURATION 1h REPLICATION 1 DEFAULT;

-- RP für 1-Minuten-Durchschnittswerte, hier landen Daten mit 1-min Auflösung und bleiben für 4 Stunden erhalten
CREATE RETENTION POLICY "rp_1min_avg_4h" ON "home_assistant" DURATION 4h REPLICATION 1;

-- RP für 5-Minuten-Durchschnittswerte, hier landen Daten mit 5-min Auflösung und bleiben für 1 Tag erhalten
CREATE RETENTION POLICY "rp_5min_avg_1d" ON "home_assistant" DURATION 1d REPLICATION 1;

-- RP für 1-Stunden-Durchschnittswerte, hier landen Daten mit 1h Auflösung und bleiben hier ewig.
CREATE RETENTION POLICY "rp_1h_avg_inf" ON "home_assistant" DURATION INF REPLICATION 1;

Der Parameter REPLICATION ist eigentlich nur für Cluster interessant, nicht für eine Single-Node-Instanz wie wir sie hier haben.
Als nächstes erstellen wir die CQs, das hab ich im Explore Window gemacht.

-- CQ für 1-Minuten-Durchschnittswerte
CREATE CONTINUOUS QUERY "cq_1min_mean" ON "home_assistant"
BEGIN
  SELECT mean(*) INTO "home_assistant"."rp_1min_avg_4h".:MEASUREMENT
  FROM "home_assistant"."rp_raw_data_1h"./.*/
  GROUP BY time(1m), *
END;

-- CQ für 5-Minuten-Durchschnittswerte
CREATE CONTINUOUS QUERY "cq_5min_mean" ON "home_assistant"
BEGIN
  SELECT mean(*) INTO "home_assistant"."rp_5min_avg_1d".:MEASUREMENT
  FROM "home_assistant"."rp_raw_data_1h"./.*/
  GROUP BY time(5m), *
END;

-- CQ für 1-Stunden-Durchschnittswerte
CREATE CONTINUOUS QUERY "cq_1h_mean" ON "home_assistant"
BEGIN
  SELECT mean(*) INTO "home_assistant"."rp_1h_avg_inf".:MEASUREMENT
  FROM "home_assistant"."rp_raw_data_1h"./.*/
  GROUP BY time(1h), *
END;

Ansehen kann man sich diese Queries mit dem Befehlt: SHOW CONTINUOUS QUERIES
Löschen geht ebenso einfach mit: DROP CONTINUOUS QUERY cq_name ON home_assistant;
Es ist sinnvoll, nicht nur CQs für den mean value zu erstellen, sondern auch z.B. für min(value), max(value) und ev. auch stddev(value) für Standardabweichung.
Für den Medianwert gibt es in der v1 zwar keine Funktion, mit "SELECT percentile(value,50) AS median INTO ..." gelingt es dann aber doch, auch diesen Wert verdichtet vorhalten zu können.

Was mich jetzt noch interessiert hätte, ist die Größe der einzelnen RPs, aber ich konnte noch keine geeignete Query dafür finden.

Zuletzt noch die Überlegung, wie ich die Daten im Produktivsystem verdichten möchte.

Raw_Data möchte ich 15 Tage aufheben, den gleichen Wert hab ich auch in HA für SQLite gewählt.
1-min Intervall Daten werde ich für 2 Jahre (104w oder 730d) aufheben, das geht weit über die SQLite Daten hinaus.
1h und 1d Intervall Daten werden nicht gelöscht, das hat den Vorteil, dass ich je nach Auswertung bereits auf passend vorverdichtete Daten zugreifen kann.

[scw2wi]

Im 5. Teil wird nun auch noch Grafana installiert.

Da ich bisher immer von InfluxDB und Grafana zusammen gelesen habe, möchte auch ich gerne die Datenanzeige über Grafana kennenlernen. Es wäre zwar (wie oben bereits gezeit) auch möglich, über einen Sensor von HA aus auf Daten in der InfluxDB zuzugreifen, aber einfacher ist die Auswertung über Grafana.

Das Grafana Add-on wird (wenig überraschend) im Add-on Store installiert, bei mir hat das mehrere Minuten gedauert.

In Seitenleiste anzeigen kann sicher nicht schaden.

Grafana Plug-In Konfiguration:
SSL kann man hier sogar (wie bei der InfluxDB) auf EIN lassen, nur bei Node-RED war bei SSL=EIN keine Verbindung zu bekommen.
Später werden wir hier sicher noch einige Grafana-PlugIns auswählen, es gibt da ganz schön viele.
Bei den Environment-Variablen hab ich die Datumsanzeige umgestellt von Monat/Tag auf Tag.Monat

env_vars:
  - name: GF_DATE_FORMATS_INTERVAL_DAY
    value: "D.MMM"
​

Nach dem Start des PlugIns können wir auf die Grafana Oberfläche wechseln.

Jetzt geht es darum, eine Verbindung mit der InfluxDB herzustellen. Das geht über
Connections
Add new connection
Hier wählen wir (welch eine Überraschung) InfluxDB aus und klicken rechts oben auf "Add new data source"

Obwohl die nächste Seite recht kompliziert aussieht, ist hier eigentlich schon fast alles sinnvoll vorbestückt, bis auf die URL.
Wenn man dieses Feld leer lässt, dann wird http://localhost:8086 verwendet, und so gut das auch aussieht, es funktioniert in unserem Fall leider nicht.
Der korrekte Wert laut Doku lautet: http://a0d7b954-influxdb:8086
Wer wissen möchte, wo das herkommt, der kann sich mal beim InfluxDB Plug-In unter Informationen das Feld Hostname genauer ansehen, genau das steht dort.

Wer will könnte hier noch die Query Language von InfluxQL auf Flux umstellen, aber ich sag's gleich, ich mach' das sicher nicht.

Im unteren Bereich finden wir dann folgende Felder, die wir noch ausfüllen müssen:

Database: home_assistant
User: homeassistant
Password: geheimesPasswort
HTTP Method: GET

"Save & Test" nicht vergessen!

Wenn alles funktioniert hat, lautet die Meldung dann: datasource is working: n measurements found

Im Fenster Grafana/Explore kann man sich eine Abfrage zusammenklicken und auch direkt ins Dashboard übernehmen, wobei man das Aussehen erst im Dashboard konfiguriert. Es gibt auch rechts einen Stift der vom Editor-Mode in den Raw-Query-Mode umschaltet, aber beim Zurückschalten verliert man leider die gemachten Änderungen wieder.

Im Grafana Dashboard kann man (über das Hamburger-Menü in der rechten oberen Ecke des Panels unter Share / Share Link) einen Link erstellen, den man dann in HA in einer iFrame-Card anzeigen kann.
Vorher muss man aber diesen Link noch modifizieren.

von http://localhost:3000/api/hassio_ingress/
auf http://homeassistant.local:8123/api/hassio_ingress/

Diesen Hinweis konnte ich in der Add-On Doku leider nirgends finden, die Info stammt aus einem eigenen Thread zu diesem Problem.
Die ebenfalls in diesem Thread beschriebenen Environment-Variablen musste ich bei mir übrigens nicht setzen.

Den Schalter "Lock time range" habe ich deaktiviert, da ich ja eine dynamisch nachgeführte Zeitachse anzeigen möchte.

Wen es so wie mich stört, dass beim eingebetteten Diagram das gesamte Bearbeitungsmenü von Grafana angezeigt wird, der hängt an die URL einfach noch ein &kiosk an, dann bleiben nur die Einstellungen für die Zeitachse und das Refresh-Intervall sichtbar.

Wer auch diese beiden Buttons noch ausblenden möchte, der erweitert die URL um &_dash.hideTimePicker=true

Ein Refresh-Intervall kann über einen weiteren Schalter an die URL angehängt werden, z.B.: &refresh=1h

Grafana bietet in Kombination mit der InfluxDB vermutlich wesentlich mehr Möglichkeiten als die Standard SQLite Datenbank in Kombination mit z.B. apexcharts-card oder der plotly-graph Card, wobei sich jeder die Frage stellen muss, ob er dieses Mehr an Möglichkeiten wirklich benötigt, oder ob die Standard-Features nicht ohnehin bereits ausreichend sind.

Eine weitere Überlegung wäre auch, InfluxDB und Grafana nicht als HA Add-On sondern außerhalb von HA zu installieren.
Die Verbindung mit HA sollte in diesem Fall kaum aufwändiger sein und der Vorteil wäre jedenfalls eine größere Unabhängigkeit.

Wer jetzt mehr zum Thema Grafana lernen möchte, dem empfehle ich das oben bereits verlinkte 1h-Video, da werden viele interessante Beispiele zu Grafana gezeigt.


Weitere Beiträge dieser Serie:

HACS Wetterkarte
HACS Sonne & Mond (inkl. Tipps zur configuration.yaml)
HACS Schieberegler (inkl. Möglichkeiten der Icon Farbanpassung & erste Vorstellung Farbschema)
HACS Gauges (Tachoanzeigen, inkl. senkrechte Balken-Cards)
HACS stack-in-card für Raum-Card
HACS Graph-Cards
HACS Thermostat-Cards
HACS Entity Cards (inkl. stacking Beispiele mit der custom:button-card)
HACS Reminder (trash-card, atomic-calendar-revive)
HACS Person- & Öffi-Card (inkl. Geschichte zu ChatGPT)
HA-Behaglichkeits-Diagramm
HA Kurzeinführung
HACS Sidebar & Dashboard-Entwurf
HACS Bewässerung
HA & Node-Red
HA Notifications

HA Python Scripts

[henfri]

Hi,

vielen Dank für die Beiträge.
Warum hast du dich für V1 entschieden? Wegen der Query-Sprache?
Wäre es nicht eine Alternative, die SQLite zu ersetzen, und die Retention-Policy anzupassen?

Gruß,
Hendrik

[scw2wi]

Es kann durchaus sein, dass ich mir die V2 auch noch ansehe, wenn ich Zeit dazu hab, für den Anfang aber reicht mir das, was die V1 bietet völlig. Das Fehlen von InfluxQL ist aus meiner Sicht eigentlich der größte Nachteil der V2.

Die SQLite möchte ich derzeit nicht ersetzen, und wenn, dann höchstens durch die MariaDB, da man diese als Add-On intern installieren kann. Ich finde jedoch nur sehr wenige Beiträge zu diesem Thema, entsprechend selten wird das vermutlich auch gemacht, und wenn das nur sehr wenige machen, dann ist die Standard-Lösung entsprechend mehr erprobt und damit auch robuster. Solange ich keine Performance-Probleme mit der SQLite erkennen kann, behalte ich sie jedenfalls.

Deine zweite Frage hab ich nicht ganz verstanden. Man kann die SQLite nicht durch die InfluxDB ersetzen, auch wenn das im HA-Buch von Udo Brandes behauptet wird. Eigentlich hab ich das im Teil 2 oben auch so beschrieben, ich werde das auch noch fett markieren, damit es nicht so leicht übersehen wird.

[henfri]

Zweite Frage:
Wenn man SQLite durch eine performante DB ersetzt und die Retention-Policy hoch setzt: Wofür braucht man noch Grafana?

[scw2wi]

Ich hab SQLite nicht durch InfluxDB ersetzt, da das gar nicht möglich ist.

Du kannst SQLite durch MariaDB ersetzen, oder durch PostgreSQL, dann können alle HA- und HACS-Cards wie gewohnt weiter auf die Daten zugreifen und merken gar nicht, dass da jetzt eine andere DB dahinter steckt.

InfluxDB kann SQLite nicht ersetzen, das läuft immer parallel, aber du kannst die Daten aller oder einer Auswahl von Sensoren parallel auch in die InfluxDB schreiben und dann die Auswertung über Grafana erstellen.

Die HA-Cards benötigen also zur Anzeige SQLite, MariaDB, MySQL oder PostgreSQL, andere DBs werden dafür nicht unterstützt.
Parallel dazu kannst du InfluxDB und Grafana verwenden, aber das ist eine Parallel-Schiene und kein Ersatz.

[henfri]

Ich hab SQLite nicht durch InfluxDB ersetzt, da das gar nicht möglich ist.

Ja genau. Hatte ich ja auch nicht vorgeschlagen.
Ich kenne Grafana und Influx noch von smarthome-ng. Fänd aber eigentlich besser, wenn alles in HA wäre.
Daher die Idee.

[scw2wi]

Auch wenn mir die Diagramm Erstellung mit Grafana echt gut gefällt (mit schönen Auswahl-Möglichkeiten statt mit YAML-Code) werde ich trotzdem versuchen, weiterhin das meiste direkt in HA zu lösen. Wenn das aber aus irgendeinem Grund nicht so richtig funktioniert, dann kann ich jederzeit auf die Kombi InfluxDB&Grafana ausweichen.

Wer's noch nicht gesehen hat, das erste Beispiel hab ich hier bereits gepostet.
Es ging dabei um fehlende Werte, die sich mit Grafana sehr einfach interpolieren lassen.