Privates Mini Data Warehouse mit Grafana und Synology

In jedem Haushalt fallen Daten an. Dazu gehören Temperaturen und Luftfeuchtigkeitswerte der Xiaomi Sensoren genau so wie die Außentemperatur am Haus. Wäre es nicht toll, wenn man alle seine Daten in einer Datenbank sammeln und auf Dashboards visualisieren könnte? Genau das habe ich mir mit der Hilfe von Docker mit Grafana, MySQL und Raspberry Pi Computern umgesetzt.

Grafana und MySQL

Grafana zeigt Daten aus Datenbanken über eine Klickibunti-Oberfläche an. Das Tool ist selber keine Datenbank und auch keine Plattform, in die man Daten speichert. Es muss irgendeine Datenbank existieren. Idealerweise verfügen die drin enthalten Daten über einen Zeitstempel um sie gleich zu sortieren. Grafana gibt es als fertiges Docker-Image im Repository.

MySQL kennt wahrscheinlich jeder. Ich hätte auch InfluxDB oder ElasticSearch nehmen können. MySQL war nur gerade für mich das Einfachste. In eine große Tabelle sollen alle Daten zusammengetragen werden. Egal woher sie kommen. Die Tabelle sieht dabei so aus:

TIMESTAMP	DEVICE	TYPE	VALUE	UNIT
2019-08-03 18:34:25	„Mi1“	„Xiaomi“	24.5	„Celsius“
2019-08-03 18:34:22	„YouTube“	„Social“	1943	„Subscriber“

Der SQL-Befehl zum Erzeugen der Datenbank findet man wie immer auf GitHub. Bei mir werden alle 5 Minuten alle Werte abgefragt und in die Datenbank geschrieben. Das erzeugt pro Jahr ca. 127 Megabyte an Daten. Auch MySQL gibt es als Docker-Image im Repository. Ich nutze MariaDB.

Die Docker-Container von Grafana und MariaDB verbrauchen wenig Ressourcen

Python und cron jobs

Die Datenbank muss nun gefüllt werden. Ich habe mich für Python entschieden. Mit php kam ich beim Ansprechen von hardware-nahen Funktionen nicht weit. Für das Einsammeln der Bluetooth-Daten der Xiaomi Sensoren hatte ich ohnehin Python verwendet.

Ein cron job führt nun alle 5 Minuten das Python-Skript auf dem Raspberry Pi aus und sammelt die Daten der Bluetooth-Sensoren in Sichtweite ein. Zusätzlich werden Metriken wie zum Beispiel Twitter-Follower oder das lokale Wetter in die Datenbank geschrieben.

def writeweather(args):

    with open(current_dir + 'weather-apikey.json') as json_data_file:
        data = json.load(json_data_file)
        apikey = data["weather"]["apikey"]

    city = args.city
    # Note f before first quote of string
    apiurl = f"http://api.openweathermap.org/data/2.5/weather?q={city}&APPID={apikey}&units=metric"

    req = urllib.request.Request(apiurl)
    r = urllib.request.urlopen(req).read()
    jsonreponse = json.loads(r.decode('utf-8'))

    temperature = jsonreponse['main']['temp']
    humidity = jsonreponse['main']['humidity']
    wind = jsonreponse['wind']['speed']

    writeMySQL(args, city , None, 'temperature', temperature, None , "Celsius" )
    writeMySQL(args, city , None, 'humidity', humidity, None , "Celsius" )
    writeMySQL(args, city , None, 'wind', wind, None , "Celsius"  )

Mein vollständiges Skript mit Anleitung befindet sich auf GitHub.

Grafana

Ich dachte immer, dass Grafana selber eine große Datenbank ist. Das ist falsch denn Grafana speichert nur die SQL-Statements auf die entsprechenden Felder in der Datenbank. Jede Linie in den Widgets ist ein SQL-Befehl der die lokale Datenbank abfragt.

Ein SQL-Statement in Grafana für die Temperaturen der Bluetooth-Daten

So lassen sich beliebige Widgets auf Dashboard nach persönlichem Geschmack zusammenstellen und vor allem kombinieren. Man kann die lokale Außentemperatur zusammen in einem Chart mit den Temperaturen der Sensoren darstellen. Oder nur die letzten Datensätze werden als Balkendiagramm angezeigt, um den aktuellen IST-Stand zu erhalten. Oder man legt maximalen und minimalen Schwellwerte fest und zeigt die Werte in einem Tacho an.

Freigabe von Grafana über das Internet

Die MySQL Datenbank und die Raspberry Pis müssen nicht von außen erreichbar sein. Für Grafana macht es allerdings Sinn. Dann lassen sich auf mobilen Browsern von unterwegs die Graphen und Dashboards anschauen. Auch anderen Personen können so über die Rechteverwaltung diese zugänglich gemacht werden. Um Grafana über das Internet erreichen zu können, muss im Router wie einer FritzBox 7590 (kaufen?) der Port 3000 über TCP freigegeben werden. Über DSM vom Synology NAS nutze ich die quickconnect-Funktion um über eine solche URL Zugriff zu haben: http://[meineid].diskstation.me:3000

„But why?“

Aber… warum das alles?

Wieso sammelt man so viele Daten über einen sehr langen Zeitraum? Weil man etwas daraus lernen kann. Nachdem der Velux Hitzeschutz installiert wurde, wollte ich wissen, wann die Außentemperatur deutlich niedriger ist als im Haus. Das sieht man nur, wenn man Zugriff die historischen Daten der letzten Tage hat.

Mein „Social Dashboard“ zeigt Entwicklungen auf Marc.TV, dem YouTube Kanal so wie dem Minecraft-Server. Natürlich kann ich über Matomo schon viele Einsichten gewinnen. Aber erst das Zusammenspiel dieser Daten ermöglicht Zusammenhänge zu erkennen, die nur alle Daten zulassen.

Fallbeispiel: Minecraft Server

Mein Minecraft-Server hat extreme Probleme. Die Ticks-per-Seconds (TPS) gingen seit dem Update auf Minecraft 1.14.4 stark in den Keller. Leider sieht man ohne Tools nur eine Momentaufnahme oder muss im Spiel einen Befehl eingeben, der die Daten für eine Stunde rückwirkend speichert und darstellt.

An dieser Stelle war es interessant zu schauen, wie sich RAM, CPU, Anzahl der Spieler und TPS zueinander im Laufe der Zeit verhalten. Dazu habe ich das Python-Script auf GitHub so erweitert, dass über docker stats und docker exec diese Befehle in die MySQL-Datenbank geschrieben werden.

dwh-proxy.py writemctps "docker exec -i mcserver rcon-cli --host localhost --port 25575 --password mypass tps | sed -e  's/[^0-9., ]*//g' -e  's/ \+/ /g'"
dwh-proxy.py writemccpu "docker stats mcserver --no-stream --format '{{.CPUPerc}}'"
dwh-proxy.py writemcmem "docker stats mcserver --no-stream --format '{{.MemPerc}}'"

Daten des Minecraft-Servers in Grafana

Einfach geht es mit Docker und Synology

Natürlich kann man eine Datenbank auf einem Webserver und Grafana auf einem Raspberry Pi 4 oder 3b+ nutzen. Einfacher geht es allerdings, wenn man ein Synology NAS mit Intel-CPU wie das DS218+ (günstig kaufen) dafür verwendet. Die einzelnen Komponenten sind innerhalb von wenigen Minuten als Docker-Container lauffähig. Nur die Temperaturdaten über Bluetooth abzufragen mache ich über die Raspberry Pis.

Abonnieren: