SQL Datenbank Design (Wetterstation)

  • Guten Abend die Damen, guten Abend die Herren,
    ich möchte mich hier mal mit meiner Frage an diejenigen wenden die mit SQL bereits Erfahrungen gemacht haben und mir bei meinem Gedankenknoten helfen können.

    Die Grundidee:
    Auf einem RPi läuft eine SQL Datenbank welche von einer erweiterbaren (Ab hier X genannt) Anzahl von Einzelstationen mit Werten gefüttert wird. Die Datenlage soll dann verwendet werden um Diagramme und Karten zu erstellen was aber irrelevant ist da nicht teil der Frage.

    Die Frage:
    Ich habe gerade einen Knoten im Kopf wie die Tabellen Designt werden sollen:
    Im Moment schwebt mir vor für jede dieser X Stationen eine Tabelle anzulegen welche die Spalten ID, Wert1, Wert2 [...] WertX, Timestamp enthält. Meine Frage richtet sich nun darum, erreiche ich so einen performanten Aufbau? Ist der Timestamp in der späteren Verarbeitung auch auf Minuten normiert nutzbar, sprich kann ich die Sekunden wegkürzen? Wie bekomme ich die Timestamps syncron über alle X Tabellen? Wäre es hier nicht besser eine Timestamp Tabelle einzuführen und lediglich Spalten für die ID's der einzelnen Sensorstationsdaten zu machen? Ich habe ein (rudimentär und mit Paint gezeichnetes xD) Bild angehangen was illustrieren soll was ich meine.
    Erstere Lösung hätte den Charm zu jedem Wert die reale Zeit aufzunehmen, zweitere könnte da Probleme bekommen wenn die Datenübertragung länger braucht oder auf sich warten lässt (Funk)?

    Fakt ist ich bin in SQL zu unerfahren und denke ich übersehe gerade was...

  • Weder - noch!
    Du machst nur eine Tabelle. Die Felder sind dann:
    ID, SensorNr, Time, Wert

    ID solltet du besser sogar weglassen, und SensorNr und Time als kombiniertes Schlüsselfeld nehmen.

    Jeder Wert wird als eigene Datenzeile gespeichert. Alles andere machst du dann mit Auswertungen über SQL.
    So bist du auch mit der Anzahl der Stationen variabel und die Auswertung ist in der Form "Wert1, Wert2, Wert3, ..." nur schlecht machbar. Und in der Praxis werden die öfter mal nicht in der selben Minute Daten senden. Gelegentlich fällt eine aus... usw.
    Wie viele Messwerte erfasst du pro Zeiteinheit? Evtl ist es auch sinnvoll, die Zeit in zwei Felder für Datum, und Uhrzeit zu teilen - evtl auch noch die Zeit in Stunde und Minute... Dann kannst du geeignete Indexe auf die Felder legen, was die Auswertungen beschleunigt.

    Oh, man kann hier unliebsame Nutzer blockieren. Wie praktisch!

  • Hallo,

    wenn es für die wichtig ist, dass der Timestamp synchron ist, dann müsstest du den außerhalb der Datenbank setzen und dann mit den Daten schreiben. Wenn der Zeitstempel von der DB geschrieben wird, hast du zwangsläufig verschiedene Timestamps, weil die Datensätze ja sequentiell geschrieben werden.

    Bei Wettersensoren halte ich da aber für Quatsch, dass man synchrone Timestamps haben muss. Das Wetter ändern sich ja nicht innerhalb einer Sekunde. Den Timestamp kannst du ruhig genau schreiben - du kannst später trotzdem "nur" auf Minuten oder Stunden genau abfragen.

    Ansonsten sehe ich das vom Tabellenaufbau wir Gnom: eine Tabelle passt.

    Alternativ so einer SQL-Datenbank könnstest du auch ein DB wie InfluxDB nehmen, welche speziell für "time based data" gemacht ist.

    Gruß, noisefloor

  • Ich würde timestamp nehmen da das weniger Platz in der Tabelle verbraucht und sich damit auch einfacher rechnen lässt ;)

    Ich hab die Erfahrung gemacht dass eine reine INT Tabelle schneller ist als jene wo auch noch VAR drin steht, oder allgemein unterschiedliche Typen.
    Sucht man in Tabellen mit unterschiedlichen Typen dauert eine Abfrage länger als wenn man LEFT JOIN auf weitere Tabellen anwendet in denen anhand der IDs dann dazu gehörige statische Textwerte stehen.
    Wenn also auch noch gespeichert werden soll wo sich der jeweilige Sensor befindet (location) dann würde ich dafür eine separate Tabelle nehmen bestehend aus "id_sensor, location" und den id_sensor Wert aus der ersten Tabelle einpflegen. Diese weitere Tabelle wird dann eigentlich auch nicht ständig beschrieben.

  • Wie immer: Kommt drauf an, was gemacht werden soll.
    (Wie meigraf schon ausführte)

    Am besten ist eine Tabelle, welche die 'Grunddaten' der Sensoren enthält. Also auch den Namen des Sensors, wenn du diesen z.B. bei der Ausgabe verwenden willst. Auch, wenn gewünscht, die Geo-Position und so weiter.
    Diese Daten sind für den normalen Betrieb nicht wirklich wichtig, wenn dort nicht z.B. die ID des Sensors mit drinsteht, über welche der Sensor im System gefunden wird.
    So z.B. die ID eines OneWire-Sensors.

    Dann eine Tabelle, in der die ID des Sensors (passend zur ersten Tabelle), die Uhrzeit (mit Datum, damit man auch sauber in die Vergangenheit sehen kann) und Messwert geschrieben wird.
    In dieser Tabelle können die Werte aller Sensoren stecken.
    Oder man kann für jeden Sensor seine eigene Tabelle bauen. Dann sollte man die Sensor-ID im Tabellennamen haben.

    Wenn man für jeden Sensor seine eigene Datentabelle hat, spart man sich das Filtern nach der Sensor-ID, wenn man die Daten verarbeiten will. Man hat dann aber mehr Tabellen, die man beachten muss.

    Computer ..... grrrrrr

  • Zitat

    Welche Wert für die Zeit wäre in der Datenbank am besten? Timestamp oder Datetime oder etwas ganz anders?
    Oder welchen Wert solle man dafür verwenden, damit diese später auch auf Minuten oder Stunden oder vielleicht auch nur auf den Tag auswertbar wäre?


    Dazu hab ich oben schon was geschrieben. Einen Timestamp kannst du nur komplett auswerten - wenn du dann einen bestimmten Tag oder eine bestimmte Stunde haben willst, muss immer der gesamte Timestamp erst analysiert werden. Für einzelne Zeitpunkte/Zeiträume mag das noch gehen (weil der Timestamp letztlich nur eine lange Zahl ist, die man eingrenzen kann), aber für komplexere Abfragen wird das aufwändig (stell dir vor, du willst für einen Monat täglich Werte für 10, 14 und 18 Uhr haben haben oder für ein Jahr täglich den Max- und Min-Wert...). Dann ist es einfacher, wenn du einzelne Werte für Datum, Stunde (und Minute) hast - da kannst du Indexe drauflegen, was die Sache enorm beschleunigt. Beim Timestamp muss das System ALLE Datensätze lesen und in jedem Timestamp nachsehen, ob der passende Tag, die passende Stunde usw. gegeben ist.

    Nochmal die Frage: Wie viele Messwerte sollen da pro Station und Tag erfasst werden? Um wie viele Messstationen geht es? Gibt es nur einen Wertart (Temperatur) oder noch weitere (Druck, Luftfeuchte, ...)?

    PS. Wer ist hier eigentlich der TO? DerSchlangen oder wusa? Doppelidentität?

    Oh, man kann hier unliebsame Nutzer blockieren. Wie praktisch!

  • Hallo zusammen,
    Ich versuche mal auf alle Fragen einzugehen die ich gesehen habe :)
    Ich möchte gerne pro Minute einen Messwert, wenn geht evtl. 2 Werte pro Station. Die Anzahl der Stationen ist X. Also soll nach oben offen sein wobei ich denke das ist nicht so relevant hätte es nur gerne flexibel.
    Die Idee mit der Zeittabelle bzw. des zerlegten Zeitstempels in der Tabelle ist finde ich auch eine interessante Idee gerade für das Auswerten später.

    Ich muss mir heute nach der Arbeit da nochmal Gedanken drüber machen.
    Noch eine kurze Frage:
    Timestamp ausserhalb generieren wie hier angedprochen: Wie das? Zeitmodul auslesen und in die Spalte schreiben?

    Gesendet von meinem SM-G935F mit Tapatalk

  • Hallo,

    Zitat

    Timestamp ausserhalb generieren wie hier angedprochen: Wie das? Zeitmodul auslesen und in die Spalte schreiben?


    Genau. Jede Programmiersprache hat ja eine Klasse/Funktion/was_auch_immer, um die aktuelle Zeit auszulesen.

    Macht aber wie gesagt nur Sinn, wenn es für dich wichtig ist, dass mehrere Datensätze exakt den gleichen Zeitstempel haben. Ansonsten laß' es einfach die DB machen.

    Gruß, noisefloor

  • Das Anlegen eigener Tabellen für jeden Sensor ist völlig jenseits jeglicher Datenmodellierungspraxis und widerspricht allem, was in Datenmodellierung seit Chen gelehrt wird!
    Speicherplatz ist das geringste Problem. Bei großen Datenmengen ist Performance das entscheidende Thema. Timestamp braucht 4 Bytes, Date (3) + 3 mal Int (1) für Stunde, Minute, Sekunde sind 6 Byte. Also 2 Byte mehr. Daran wirds nicht scheitern.

    Einfache Datentypen sind performanter als Fließkommazahlen oder komplexe Datentypen, da hat meigrafd Recht.

    Rasps Ausführungen zu der verknüpften Tabelle mit den Sensordaten sind richtig. Du solltest allerdings in der Tabelle nicht die (Hardware-)ID des Sensors in dem Sinne verwenden, sondern eine ID, die den Messort repräsentiert. Sonst hast du bei Austausch oder Wechsel der Sensoren das Problem, dass Sensor 0658F4A mal in der Küche und mal im Wohnzimmer gemessen hat. Das bring dich dann völlig durcheinander.
    Die Grunddatentabelle sollte dann eher so aussehen:
    (Ort-)ID, Ortsbezeichnung
    Das wärs schon.
    Evtl. kannst du dazu speichern, welcher physische Sensor dort im Einsatz ist/war... das kannst du direkt in der Tabelle machen (dann immer nur die aktuelle Situation) oder über eine oder zwei weitere Tabelle machen, z. B:
    Ort-ID, Sensor-Hardware-ID, Zeitpunkt Inbetriebnahme, Zeitpunkt Außerbetriebnahme
    oder mit zwei Tabellen
    Tabelle 1: Sensor-ID, Sensor Hardwareadresse, Bezugsquelle, Einkaufspreis, Farbe, Datum der letzten Kalibrierung...
    Tabelle 2: Ort-ID, Sensor-ID, Zeitpunkt Inbetriebnahme, Zeitpunkt Außerbetriebnahme
    Automatisch zusammengefügt:
    Den Timestamp der Datenbank zu verwenden ist hier nicht sinnvoll (wie bereits ausgeführt). DB-Timestamps sind interessant als Log-Information, aber nicht, wenn komplexe Auswertungen darauf laufen sollen.
    Die Frage nach dem exakt gleichen Zeitstempel musst du an deinen Anforderungen messen. Wenn dien Programm alle Sensoren abfragt und dann die x Werte in die DB schreibt, kannst du ja alle mit dem gleichen Zeitpunkt schreiben. Was machst du, wenn ein Sensor nicht antwortet? Alle Messungen verwerfen? Eine nicht schreiben? Statt des Wertes eine null schreiben?
    Wenn alle Sensoren synchron abgefragt und Geschreiben werden, kann man eine gesonderte Zeittabelle machen:
    Messzeitpunkt-ID, Datum, Stunde, Min, Sek
    in der Wertetabelle dann:
    Orts-ID, Messzeitpunkt-ID, Wert
    wenn du verschiedene Werte erfasst:
    Orts-ID, Messzeitpunkt-ID, Temperatur, Feuchte, Druck

    Das hat aber das Problem, wenn du für einen bestimmten Zeitpunkt Daten brauchst und der Messwert fehlt (weil ein Sensor ein paar Minuten ausgefallen ist), geht die Abfrage ins Leere. Dann musst du aufwändig suchen, welcher nachfolgende Messwert als Ersatz genommen wird - insofern hast du nichts gewonnen. Wenn du wieder aus dem vollen Datenbestand schöpfen willst, muss die Kombination aus Zeit und Messwert erst durch Joins wieder hergestellt werden, was wieder Zeit braucht. Das ist dann eine Frage der Abfragestrategie. Da geht es datenbanktechnisch ans Eingemachte.

    Da müsstest du uns noch etwas genauer beschreiben, welche Auswertungen du machen willst.
    Abgesehen davon: Ist es sinnvoll, jede Minute zu messen? Wie stark schwankt die Temperatur deiner Messobjekte? Bei Raumtemperaturen würde ich nicht unter 15 Minuten gehen... Hast du mal ein Datenmengengerüst aufgestellt? Bei 9 Bytes (1 für ID, 6 für Datum/Zeit, 2 für Messwert) hast du bei 10 Sensoren und minütlicher Messung 47 MB Netto-Daten im Jahr (dazu kommen Indexdaten und sonstiger DB-Overhead).
    Was meinst du mit "zwei Werte pro Station"? Verschiedene Werte (Temperatur, Feuchte) oder zweimal Temperatur (wenn ja, wozu?).

    Oh, man kann hier unliebsame Nutzer blockieren. Wie praktisch!

    Einmal editiert, zuletzt von Gnom (10. August 2017 um 10:52)

  • gnome: Du hast mit allem vollkommen Recht. Aber mal ehrlich, um ein paar Werte einer Wetterstation zu speichern muss man nicht so ein Aufwand betreiben. Normalisierung ist bei großen Datenbanken angebracht aber hier denke ich nicht. Auch die Performance ist zu vernachlässigen denke ich.

    Ich würde das ganze so einfach wie möglich gestalten und alles in eine Tabelle schreiben. So ist auch das auswerten (bei mir per PHP) deutlich angenehmer je nachdem was man den auswerten möchte. Da hast du Recht, wir wissen zu wenig.

    Einmal editiert, zuletzt von WaldiBVB (10. August 2017 um 10:28)

  • Die Idee War ja nicht von mir. Aber wenn schon getrennte Tabellen, dann richtig. Da der TO es hinsichtlich der Stationen flexibel haben will, kommt er an zwei Tabellen nicht vorbei (sonst muss er immer sein Programm ändern, wenn eine Station dazu kommt). Eine für die Messwerte (Mit ID des Messortes) und eine für die Bezeichnung der Messorte.
    Das mit der Performance würd ich mal nicht unterschätzen. Wenn erst mal 200 MB Daten aus vier Jahren in der DB sind und dann jede Abfrage 35 Sekunden dauert... dann geht das Gemaule los.
    Aber ich stimme dir zu: So einfach wie möglich. (Und so ausgefeilt wie eben nötig.)

    Oh, man kann hier unliebsame Nutzer blockieren. Wie praktisch!

  • Hallo,

    Zitat

    Aber mal ehrlich, um ein paar Werte einer Wetterstation zu speichern muss man nicht so ein Aufwand betreiben. Normalisierung ist bei großen Datenbanken angebracht aber hier denke ich nicht.

    .
    Das ist IMHO eine grundlegend falsche Einstellung. Wenn man ein RDBMS benutzt, dann _muss_ man sich Gedanken um das DB-Design machen - auch bei kleinen / Hobbyprojekten. Man muss vielleicht in der Tat nicht "die hard" auf die 3. Normalform normalisieren, aber auf die erste oder zweite darf man schon kommen. Weil: die Schmerzen, die man bei falsch- / nicht-normalisierten relationalen DBs bei der Abfrage dann hat, hat man auch bei Hobbyprojekten.

    Wer keine Lust hat, sich darüber Gedanken zu machen, der kann ja eine schemalose dokumentenorientierte DB nehmen. Was im gegebenen Fall aber wenig sinnvoll ist.

    Zitat

    Auch die Performance ist zu vernachlässigen denke ich.


    +1

    Gruß, noisefloor

  • Das ist unrealistisch. Zum einen wird es fast nie exakt gleiche Werte geben - zumindest nicht bei Auflösungen mit 1/10° und zum anderen wird dann die Auswertung der Datenbank wieder extrem kompliziert. Das würde gar nichts bringen. Dann lieber die Messwerte nicht so häufig erfassen.

    Oh, man kann hier unliebsame Nutzer blockieren. Wie praktisch!

  • Hallo,

    außerdem ist Speicherplatz heutzutage so billig, dass das kein Grund zum "Sparen" ist. Mal abgesehen davon, dass bei Wetterdaten wie Temperatur, Luftfeuchtigkeit, Windgeschwindigkeit und -richtung und Luftdruck nur eine kleine Menge Daten pro Messung zusammen kommt. Die "größte" Zahl ist der Luftdruck, der ist bestenfalls 4-stellig.

    Außerdem haben die "großen" RDBMS wie PostgreSQL, MySQL, MSQL oder Oracle ja auch kein Problem, wenn die Tabelle ein paar Millionen Datensätze enthält.

    Gruß, noisefloor

  • Hallo und Danke für die Diskussion die hier geführt wird.

    Also ich hab es hier schon gelesen das ihr es vermutet aber ich werde es gerne bestätigen: Es geht mir Grundsätzliche um Flexibilität. Ja noch ist es eine Wetterstation und ein Wert alle 30s ist viel und unnötig viel. Ich will mir aber irgendwie etwas flexibles aufbauen. Quasi das eine ähnliche Datenbankstruktur bei anderen Messeinsätzen der Station auch funktioniert. Eine Wetterstation ist es nur weil das so herrlich gut mit lernkurve geht :)
    Als Auswertung und Abfrage würde ich später gerne ein paar statistische Funktionen bemühen, beispielsweise gleitender Mittelwert o.ä.

    Ich lese hier aber als Quintessenz heraus das ich Zeitstempel MIT Sensordaten aller Stationen in einem Rutsch einschreiben. Das ganze in einer Tabelle. Auf Daten wie Ort und Sensortyp etc. kann durch Indexierung in andere weniger frequentierte Tabellen abgelegt werden.
    Hab ich das so richtig verstanden? Flexibel ergänzbar wird das ganze da aber nicht weil ich immer die Hauptabelle anpassen muss?

    Gesendet von meinem SM-G935F mit Tapatalk

  • Dein Verständnis samt Begrifflichkeiten geht noch ziemlich wirr durcheinander. Nimm dir mal ein Grundlagenskript zu Datenmodellierung und Datenbanken zur Hand und lies alles, was hier geschrieben wurde nochmal in Ruhe durch... Wenn man relationale Datenbanken in 20 Minuten verstehen könnte, gäbs keine gut bezahlten Spezialisten mit jahrelanger...

    Die Aussage, die "großen RDBMS haben kein Problem, wenn die Tabelle ein paar Millionen Datensätze enthält, halte ich für sehr gewagt. Das mag bei der einen oder anderen Wald- und Wiesen-Anwendung so sein, aber wenn man jenseits von Pillepalle-Datenbanken aus dem Lehrbuch arbeitet, sieht das schnell anders aus. Der Einfluss von Struktur, Indexierung und Abfragestrategie auf die Performance ist nicht zu unterschätzen. Wer glaubt, die Datenbank macht das alles wie von selbst, wird sich schnell eines Besseren belehren lassen müssen. Und da genügt es bei Weitem nicht, wenn man mit knapper Not die dritte Normalform zusammen kriegt. Dem Threadowner sei empfohlen, mal Testdaten mit 10 Mio Datensätzen (2 Jahre) zu erstellen und sich überraschen zu lassen...

    Oh, man kann hier unliebsame Nutzer blockieren. Wie praktisch!

  • Hallo,

    Zitat

    Die Aussage, die "großen RDBMS haben kein Problem, wenn die Tabelle ein paar Millionen Datensätze enthält, halte ich für sehr gewagt.


    ich nicht, weil die Aussage grundsätzlich richtig ist ;)

    Korrekt ist natürlich auch, dass der Raspi oder ein Rechner mit eher wenig Rechenpower das nicht mehr performant wuppen. Da braucht man schon eine "richtigen" DB-Server mir Rechenpower, bevorzugt SSD und viel RAM.
    Richtig ist auch, das man beim DBs mit vielen Tabellen und vielen Beziehungen untereinander (z.B. einem ERP-System) anders agieren muss als bei der Wettertabelle. Wir reden hier aber über zwei Tabellen mit einem Fremdschlüssel, also einem eher trivialen Fall.

    Und falls es mal so richtig richtig viele Daten werden würden, dann könnte / sollte man vielleicht so wie so auf eine DB wie Cassandra mit dem entsprechenden Cluster-Setup ausweichen. Also wenn DerSchlangen mal dutzende von Wetterstationen hat, pro Minute mehrere Werte liefern ;)

    Gruß, noisefloor

Jetzt mitmachen!

Du hast noch kein Benutzerkonto auf unserer Seite? Registriere dich kostenlos und nimm an unserer Community teil!