Seite
61 Tage, 10 Stunden und 42 Minuten - Sgoebel

BigData

1 Relationale Datenbanken

In der Praxis sind Relationale Datenbankmanagementsysteme immer noch von großer Bedeutung[1].

1.1 Datenbank-Entwurf

Nach Bearbeitung dieses Kapitels können Sie ...

  • ... ein konzeptionelles ER-Modell entwerfen
  • ... ein ER-Modell in ein Relationales Datenmodell überführen
  • ... ein Relationales Datenmodell optimieren
  • ... eine Tabellenstruktur in einer Datenbank anlegen

(Präsentation aus dem Unterricht)

1.1.1 Entwurfs-Phasen

Beim Entwurf von Datenbanken durchläuft man die folgenden Phasen:

Externe Phase
Meist ausgehend von den Attributen des Klassendiagramms werden in der Anforderungsanalyse die zu speichernden Informationen bezogen auf die Anwendung ermittelt.
Konzeptionelle Phase
Der Sachverhalt wird in einem sematischen Modell formalisiert beschrieben. Am Schluss dieser Phase steht ein Entity Relationship Modell[2]. Dieses ER-Modell stellt hauptsächlich die Entitätstypen und deren Beziehungen untereinander mit den entsprechenden Kardinalitäten übersichtlich dar.
Logische Phase
Das semantische Datenmodell wird in ein relationales Datenmodell[3] überführt. Dabei werden die Beziehungen zwischen den Tabellen konkret über Schlüsselfelder bzw. Verknüpfungstabellen realisiert. Anschließend wird das relationale Schema durch Normalisierung optimiert. Außerdem dürfen keine Prozessdaten gespeichert werden.
Physische Phase
Nach Festlegung der Datentypen wird das relationale Schema in einer Datenbank (z.B. mit SQL-Befehlen) angelegt.

1.1.2 Normalformen[4]

Zur Vermeidung von Redundanzen, die zu Anomalien führen können, sollten Datenbanken normalisiert werden.

1. Normalform
Atomare Werte.
2. Normalform
Jedes Nicht-Schlüsselattribut ist vom gesamten Schlüssel abhängig.
3. Normalform
Nicht-Schlüsselattribute sind voneinander unabhängig.

Links zum Nachschlagen

Arbeitsauftrag:

  • Datenbank für Rechnungserstellung:
  1. Erstellen Sie ein Datenbank-Modell in der Chen-Notation, die alle Informationen enthält, um eine Rechnung zu erstellen.
  2. Vergleichen Sie Ihr ER-Modell mit dem Modell an der Tafel, listen Sie die Unterschiede auf und beschreiben Sie die Auswirkungen in der Praxis.
  3. Überführen Sie das ER-Modell zur Rechnungserstellung in ein relationales Datenmodell.
  4. Überprüfen Sie das Datenmodell auf die drei Normalformen.
  • Betriebsabrechnungs-Datenbank:
  • Jeder Kunde hat die Attribute Name und Adresse.
  • Jeder Mechaniker hat die Attribute Name und Ausbildungsstand.
  • Jede Auftragsposition hat die Attribute Beschreibung und AnzahlArbeitseinheiten.
  • Kunden vergeben Aufträge; dabei erfolgt die Vergabe an bestimmtem Tag.
  • Mechaniker bearbeiten Aufträge; manche Aufträge werden von mehreren Mechanikern bearbeitet.
  • Jeder Auftrag besteht aus mindestens einer und maximal beliebig vielen Auftragspositionen.
    Auftragspositionen können zu mehreren Aufträgen gehören.
  • Mechaniker vergeben keine Aufträge.
  1. Definieren Sie ein entsprechendes, konzeptionelles Entity-Relationship Modell.
  2. Überführen Sie das ER-Modell in ein relationales Datenbankmodell. Vergessen Sie nicht alle Primär- und Fremdschlüssel sowie Attribute anzugeben.
  3. Überführen Sie das Modell in die dritte Normalform.
  • Datenbank für die Einkaufsabteilung:
  • ein Einkäufer führt mehrere Bestellungen aus
  • mit jeder Bestellung werden eine oder mehrere Waren zu unterschiedlichen Stückzahlen bestellt
  • eine Ware kann jeweils nur bei einem Hersteller bezogen werden
  • jede Ware wird einer Warengruppe zugeordnet
  • an einem Lagerort können mehrere Waren gelagert werden
  1. Erstellen Sie hierzu das entsprechende ER-Modell in der Chen-Notation.
  2. Überführen Sie das ER-Modell in ein relationales Datenbankmodell in der Dritten Normalform.
  • Datenbank für Online-Schichtplan:
  1. Entwickeln Sie aus dem Klassendiagramm, dem das WebTaxi Lastenheft zu Grunde liegt, für den Online-Schichtplan ein ER-Modell.
  2. Überführen Sie das ER-Modell für den Online-Schichtplan in ein relationales Datenmodell.
  3. Überprüfen Sie das Datenmodell auf die drei Normalformen.

1.2 Vererbungen realisieren

Relationale Datenbanken können keine Vererbungsstrukturen darstellen. Es gibt verschiedene Wege, Vererbungshierarchien objektorientierter Modelle auf Datenbanktabellen abzubilden[5].

1.3 SQL

SQL am Beispiel der verbreiteten MySQL-Datenbank (Präsentation aus dem Unterricht).

Nordwind.png

Für Übungszwecke eignet sich die Nordwind-Datenbank.

Vorbereitung:

  • Installation (wählen Sie eine der Optionen):
    • als Docker-Container starten:
      • laden Sie die Datei docker-compose-lampp.yml herunter
      • starten Sie mit docker compose -f docker-compose-lampp.yml up -d die enthaltenen Container.
      • öffnen Sie den phpMyAdmin
      • melden Sie sich mit 'root' und 'geheim' an
    • Lokale Installation auf dem PC:
      • installieren Sie xampp im Hauptverzeichnis auf Ihrem PC.
      • rufen Sie C:\xampp\xampp-control.exe auf
      • starten den Apache-Webserver und die MySQL-Datenbank. Falls der Apache nicht startet, kontrollieren Sie mit netstat -an ob der Port 80 belegt ist. In diesem Fall können Sie in der c:\xampp\apache\conf\httpd.conf über den Eintrag 'Listen' den Port umstellen, z.B. auf 8080.
      • öffnen Sie den phpMyAdmin
    • Virtuellen Desktop nutzen:
      • öffnen Sie die Seite https://edu-vdi.gs-ldk.de/
      • folgen Sie dem Link und installieren Sie den VMware Horizon Client
      • melden Sie sich mit Ihrem Benutzernamen (S00xxxx) an.
      • auf dem Desktop ist der xampp bereits installiert
    • Nutzen Sie die Demo-Datenbank von phpMyAdmin: https://demo.phpmyadmin.net/master-config/
  • Importieren Sie die Nordwind-Datenbank:
    • Speichern Sie das SQL-Skript lokal auf Ihrem PC.
    • Importieren Sie das Skript mit dem phpMyAdmin, alle erforderlichen Datenbanken und Tabellen mit Daten werden angelegt.

Arbeitsauftrag:

  1. Legen Sie eine leere Datenbank an und überführen Sie das folgende ER-Modell
    er-modell.png
    mit Hilfe von SQL-Befehlen in Tabellen. Legen Sie die entsprechenden Primär- und Fremdschlüssel und alle Verknüpfungen an.
    Dokumentieren Sie alle hierfür notwendigen SQL-Befehle.
  2. Ermitteln Sie die SQL-Befehle für diese Abfragen[1].
    Dokumentieren Sie Ihre Lösungen, hierfür können Sie diese Vorlage verwenden.

Links zum Nachschlagen

Tutorials und Lernplattformen

  1. ↑ Die Seite wird mit folgenden php-Skript erzeugt: SQL_Aufgaben-Funktion

2 Datenbankanbindung mit ESP8266

2.1 Anbindung an MySQL

Starten Sie auf Ihrem PC den MySQL-Datenbankserver, dieser ist dann auf dem Standardport 3306 erreichbar. Die IPv4-Adresse Ihres PCs ermitteln Sie am einfachsten in der Eingabeaufforderung mit ipconfig

Damit sich andere Rechner, also auch der ESP, an der Datenbank anmelden können, muss noch ein Benutzer mit einem Passwort, eine Datenbank und darin eine Tabelle angelegt werden. Gehen Sie dazu wie folgt vor:

  • http://localhost/phpmyadmin/ öffnen
  • Benutzerkonten - Benutzerkonto hinzufügen
  • Tragen Sie einen Benutzernamen und ein Passwort ein, achten Sie darauf, dass bei Hostname 'Jeder Host' eingestellt ist.
  • Wählen Sie bei 'Authentifizierungs-Plugin': 'Native MySQL-Authentifizierung'
  • Haken setzten bei: 'Erstelle eine Datenbank mit gleichem Namen und gewähre alle Rechte.'
  • mit OK abschließen
  • Wählen Sie die neue Datenbank aus und legen Sie darin mindestens eine Tabelle an.
    z.B.: CREATE TABLE IF NOT EXISTS daten ( zeit timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, temp float NOT NULL )

Installieren Sie in der Arduino-IDE über 'Werkzeuge - Bibliotheken verwalten...' den Mysql Connector. Nachdem die beiden Header-Dateien #include <MySQL_Connection.h> und #include <MySQL_Cursor.h> inkludiert wurden, steht die Datenbankfunktionalität zur Verfügung. Ein ausführliche Dokumentation und Erläuterung der Beispiele finden Sie hier.

Zuerst muss im Deklarationsteil mit MySQL_Connection conn((Client *)&wifiClient); eine Datenbankverbindung instanziert werden. Dabei ist &wifiClient der Zeiger auf den zuvor instanzierten WiFi-Client.

Im Initialisierungsteil wird dann mit conn.connect(server_addr, 3306, DBuser, DBpassword); die Verbindung zum Datenbank-Server hergestellt. Hierbei sollte gewartet werden, bis die Verbindung erfolgreich ist.

In der Ausführungsschleife können mit MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); cur_mem->execute(SQL-query); beliebige SQL-Abfragen an die Datenbank gesendet werden.
Nutzen Sie zum Schreiben Ihrer Messwerte INSERT INTO ....

Öffnen Sie das Beispiel 'Datei - Beispiele - MySQLConnector Arduino - basic_insert_esp8266' in der Arduino-IDE.

Arbeitsauftrag:

  1. Messen Sie mit dem TMP36-Sensor die Raumtemperatur. Diese Temperaturwerte sollen in regelmäßigen Abständen in eine MySQL-Datenbank eingetragen werden. Speichern Sie zu jedem Wert auch den Zeitstempel.
  2. Sie sollen den Verbrauch eines Pelletofens mit Hilfe eines ESPs protokollieren. Jedesmal, wenn ein neuer Sack (15kg) Pellets in den Vorratsbehälter des Ofens gefüllt wird, soll auf einen Taster gedrückt werden.
    Schreiben Sie ein Programm, das nach Druck auf den Taster aus einer Datenbanktabelle den größten Wert ausliest, 15 addiert und einen neuen Datensatz in die Tabelle schreibt. Dabei soll der aktuelle Zeitstempel ebenfalls erfasst werden.
    Nutzen Sie ebenfalls den DeepSleep-Modus.


3 Grafana

Grafana ist ein sehr mächtiges Tool zur Visualisierung von (fast) beliebigen Daten auf Dashboards. Die Daten können u. a. von unterschiedlichen Datenbankmanagementsystemen bezogen werden, z.B. auch von MySQL und InfluxDB.

3.1 Installation

3.1.1 Docker-Container

Starten Sie einen Grafana-Container: https://hub.docker.com/r/grafana/grafana
docker run -d --name=grafana -p 3000:3000 grafana/grafana.
Alternativ können Sie den Container auch über einen Stack starten. Der Vorteil hierbei ist, dass sich Grafana mit anderen Containern - z.B. Datenbanken - automatisch im selben Netzwerk befindet.

Damit Datenbankverbindungen und Abfragen in Grafana gespeichert werden können, muss ein Volume angebunden werden.

3.1.2 lokale Installation

  1. Laden Sie das Programm für Ihren Computer herunter: https://grafana.com/grafana/download
  2. Öffnen Sie die Eigenschaften der zip-Datei und setzen Sie den Haken bei 'Zulassen' unter 'Sicherheit'
  3. Entpacken Sie die zip-Datei
  4. Öffnen Sie den Ordner C:\grafana-n.n.n\conf und erstellen Sie eine Kopie der Datei sample.ini, die Sie anschließend in custom.ini umbenennen. Änderungen dürfen nur in dieser Datei vorgenommen werden.
  5. Starten Sie c:\grafana-n.n.n\bin\grafana-server.exe

3.2 Daten visualisieren

WICHTIG: in der Praxis muss für den Zugriff auf die Datenbank durch Grafana ein Benutzer angelegt werden, der nur SELECT auf einer bestimmten Tabelle ausführen darf.

  1. Öffnen Sie in Ihrem Browser die Seite http://localhost:3000 Benutzer und Passwort lauten 'admin'
  2. Im nächsten Schritt werden Sie aufgefordert ein neues Passort einzugeben.
    (Falls Sie das neue Passwort vergessen sollten, führen Sie als Administrator über die Kommandozeile im Pfad cd "C:\grafana-n.n.n\bin" den Befehl ./grafana-cli.exe admin reset-admin-password passwdneu aus.)
  3. Wählen Sie anschließend 'Add data source'. Sollte die Verbindung mit 'localhost' fehlschlagen, geben Sie stattdessen die IP-Adresse Ihres Rechners ein.
  1. Um Daten visualisieren zu können, müssen zunächst welche zur Verfügung stehen. Falls Sie keine Temperaturdaten in Ihrer MySQL-Datenbank gespeichert haben, können Sie dieses SQL-Skript importieren.
  2. Wählen Sie 'Add data source', um Grafana mit einer Datenbank zu verbinden.
    Wählen Sie Ihre lokale MySQL-Datenbank, der Benutzer ist 'root' ohne Passwort.
  3. Stellen Sie die Daten in einem Graphen dar. (Beachten Sie, dass die Temperaturdaten in dem Skript im Dezember 2020 erstellt wurden.)
  4. Richten Sie in Grafana eine Ansicht ein, mit der die Daten ohne Eingabe eines Passworts angezeigt werden können.
    Dokumentieren Sie die hierzu notwendigen Schritte.


4 Time-Series-Datenbanken[6]

Am Beispiel von InfluxDB[7]. Diese Datenbank zählt zu den NoSQL-Datenbanken und speichert die Daten im Gegensatz zu einer relationalen Datenbank schemalos. Das bedeutet, dass keine Tabellen angelegt werden sondern sich die Struktur aus den Daten selbst ergibt. Daher eignet sie sich für die Speicherung von Messwerten, da sie mit einer zeitlichen Auflösung im Nanosekundenbereich schneller ist als eine SQL-Datenbank und Daten mit einer SQL-ähnlichen Abfragesprache auch automatisch einfacher zusammengefasst werden können. Wie bei relationalen Datenbanken ist eine genaue Planung der Speicherung für die spätere Auswertbarkeit zwingend erforderlich. Zudem gibt es zahlreiche Konnektoren, z.B. für NodeRed, ESP8266 und Grafana.

Weitere Informationen:

4.1 Installation

Achten Sie bei produktivem Einsatz einer InfluxDB auf eine Absicherung und ein User-Management!

4.1.1 Docker-Container

  1. Starten Sie einen InfluxDB-Container: https://hub.docker.com/_/influxdb in der Version 1.8.6.
    Erstellen Sie dabei für die Datenbank ein Volume.
    docker run -d --name influxdb -p 8086:8086 -v influxdbdata:/var/lib/influxdb influxdb:1.8.6
    Alternativ: Einbindung in Stack.
  2. Öffnen Sie die Kommandozeile in diesem Container. docker exec -it influxdb bash
  3. Starten Sie dort das CLI der Datenbank influx -precision rfc3339

4.1.2 Lokale Installation

InfluxDB ist für alle gängigen Server-Betriebssystem erhältlich - die aktuelle Version leider nicht mehr für Windows.

  1. Installieren Sie InfluxDB, indem Sie diese zip-Datei entpacken[8]. Weitere Versionen und andere Programme finden Sie hier.
  2. Starten Sie die Datenbank durch Ausführen des Dienstes: C:\influx-1.8.6-n>influxd.exe. Das Fenster darf nicht geschlossen werden. Der Dienst kann mit Strg+C beendet werden.
  3. Öffnen Sie eine weitere Eingabeaufforderung und starten Sie mit C:\influx-1.8.6-n>influx.exe -precision rfc3339 das CLI (Command Line Interface) der Datenbank. Durch den Parameter werden die Timestamps in einem verständlichen Format ausgegeben.
    Verlassen Sie später das CLI mit exit.

4.2 Daten erfassen

Zur Speicherung muss zunächst eine Datenbank angelegt werden create database datenbankname. Danach muss diese Datenbank ausgewählt werden use datenbankname.

Jetzt können beliebige Datenpunkte eingefügt werden. Hierbei muss der Name der Messungen angeben werden (z.B. 'temp'), was in etwa der Tabelle im relationalen Datenmodell entspricht. Ein Datenpunkt besteht hierbei aus:

  • tag value - z.B. 'Bad' (entspricht dem Spaltennamen)
  • field value - die Messwerte, Standard ist der Datentyp float, andere sind möglich, nach der ersten Speicherung muss der Typ beibehalten werden
  • timestamp - optional, wenn er fehlt, wird er automatisch vom Server übernommen

Hier einige Beispiele:
insert temp,ort=Bad,sensor=1 value=22.8
insert temp,ort=Bad,sensor=1 value=23.2 1613415973169237754 - hier wurde ein Timestamp angegeben
insert pres,ort=Bad,sensor=1 value=825

Datenpunkt können nach ihrer Speicherung nicht mehr verändert oder gelöscht werden.

  1. Legen Sie über das CLI eine neue Datenbank an und fügen Sie ca. zwanzig beliebige Datenpunkte gemäß obigem Beispiel mit unterschiedlichen Tag Values, Field Values und Werten ein.
  2. Schreiben Sie mit dem ESP8266 Daten direkt in die InfluxDB
    1. Installieren Sie in der Arduino-IDE über die Bibliotheksverwaltung die Erweiterung 'ESP8266 influxdb' von Tobias Schürg[9]
    2. Öffnen Sie 'Datei - Beispiele - ESP8266 - BasicWrite'
    3. Schreiben Sie in regelmäßigen Zeitabständen Werte in die InfluxDB. Nutzen Sie dazu entweder einen Poti, Zufallszahlen oder inkrementierte Werte.

4.3 Daten auswerten

Zur Auswertung der Daten steht die Abfragesprache InfluxQL zur Verfügung, die stark an SQL angelehnt ist. Mit dem Befehl show measurements können die Messungen aus der Datenbank ausgegeben werden. Genauere Informationen zu den Inhalten erhält man mit den Befehlen show series, show tag keys und show field keys. Die Daten lassen sich dann mit SELECT-Abfragen ausgeben - z.B. select * from temp - und mit where und group by einschränken und gruppieren.

  1. Erstellen Sie verschiedene Abfragen auf die Daten, die folgendes ausgeben:
    1. alle Daten in der 'temp'-Messung
    2. alle Daten in der 'temp'-Messung, die Werte enthalten
    3. alle Daten des Sensors 1 in der 'temp'-Messung
    4. alle Daten in der 'temp'-Messung aus dem Bad
    5. alle Daten in der 'temp'-Messung, bei denen der Wert im Bad unter 21°C liegt
    6. alle Daten in der 'temp'-Messung gruppiert nach Ort
    7. alle Daten in der 'temp'-Messung, die in den letzten 30 Minuten geschrieben wurden
  2. Daten importieren
    1. Laden Sie die Datei https://s3.amazonaws.com/noaa.water-database/NOAA_data.txt herunter und speichern Sie diese im Verzeichnis der Influx-Datenbank. Diese Datenbank enthält 15.258 Datenpunkte zur Wasserqualität an zwei Orten, die zwischen dem 17.08.2019 und 17.09.2019 erhoben wurden.
    2. Importieren Sie die Datei mit dem Befehl influx -import -path=NOAA_data.txt -precision=s
  3. Dokumentieren Sie die Struktur der beiden Datenbanken mit den oben genannten Befehlen.

Links:

4.4 Daten automatisiert weiterverarbeiten

Mit Continous Queries können Daten von mehreren Messung zu einem neuen Messwert zusammengefasst werden und anschließend mit einer Retention Policy automatisiert gelöscht werden.

Links:

Arbeitsauftrag:

  1. Zeigen Sie zunächst die Datenpunkte Ihrer Datenbank an. Halten Sie fest, wie diese zusammengefasst werden sollen. Erstellen Sie anschließend eine Abfrage, die die Datenpunkte sinnvoll zusammenfasst und kontrollieren Sie das Ergebnis.

4.5 Daten visualisieren

Influxdata stellt ein eigenes Visualisierungs-Tool names Chronograf zur Verfügung, allerdings lassen sich ebenso andere andere Tools nutzen.

  1. Visualisieren Sie die erfassten Daten mit Grafana.


5 Hausautomation mit Home Assistant[10]

Arbeitsauftrag:

  1. Installieren Sie Home Assistant (https://www.home-assistant.io/installation/) auf einer beliebigen Plattform.
    Anschließend rufen Sie die Startseite mit http://homeassistant.local:8123 auf
  2. Fügen Sie einen OpenWeatherMap-Sensor hinzu und zeigen Sie die Wetterdaten z.B. von Dillenburg auf dem Dashboard an. Hierzu müssen Sie sich registrieren und einen API-Key erstellen.
  3. Wechseln Sie im Supervisor-Menü in den Add-on-Store und fügen Sie den File-Editor hinzu.
  4. Installieren Sie die Home Assistant-App auf Ihrem Smartphone und geben Sie alle Sensoren frei.
  5. Zeigen Sie auf der Übersicht den Füllstand des Akkus an und geben Sie eine Warnung aus, wenn dieser 80% überschreitet.
  6. Erkennen Sie, ob jemand zu Hause ist und zeigen Sie diesen Status auf der Übersichtsseite an.
    Hierfür gibt zwei Möglichkeiten:
    1. greifen Sie auf den Sensor der Home Assistant-App zu
    2. über Ihren Router, ob Ihr Smartphone im heimischen WLAN eingebucht ist


6 Interessante Links

Abgerufen von „https://sgoebel.de/bluespice/index.php?title=BigData&oldid=951“