MySQl & Bash benötige Hilfe um etwas Vorhandenes Umzuarbeiten

[Cracymike]

Hallo zusammen ich habe ein Problem,

ich habe einen Ubuntu Server auf dem läuft MySQL und dort sind mehrere Datenbanken aktiv.
Dieser Server hat die Aufgabe sich automatisch via Open VPN auf verschieden andere Rechner zu verbinden von dort Kopiert er .txt Dateien , "Analysiert" diese und schreibt diese in eine Datenbank. Soweit funktioniert das auch, meistens jedenfalls. Das problem ist allerdings das die Daten so in der Datenbank landen das sie nicht wirklich weiterverarbeitet werden können.

Zur Erklärung:
Jede der .txt Dateien enthält Messwerte immer alle von einem Tag und einem Sensor. Aktuell werden in der Datenbank zu jedem Sensor eine Tabelle geführt. Um diese Daten vergleichend abzufragen ist ein enormer Aufwand nötig, zusätzlich wird auch noch für jedes Jahr eine zusätzliche Datenbank erzeugt.

Das würde ich gern ändern und zwar möchte Ich eine Datenbank welche nur drei Tabellen enthält:

MESSWERT
SENSOR
STANDORT

Diese sollen miteinander alle Abfragen möglich machen dazu enthalten die Tabellen folgende Felder:

MESSWERT:

ID
TIMESTAMP
WERT
SENSOR_ID

SENSOR:

ID
NAME
STANDORT_ID


STANDORT
ID
NAME


Um jetzt die vorhandenen Daten zu Transponieren und in die DB zu bekommen benötige ich Eure Hilfe.

Logisch habe ich mir das so vorgestellt:

Einen Neuen Standort lege ich manuell in einer Tabelle an die auch die Verbindungsdaten für die VPN,s enthält.

Beim abarbeiten des Scrips werden nacheinander die Standorte durchgegangen. Beim abarbeiten der Dateien ist also der Standort bereits bekannt. Jetzt kann in der kleinen STANDORT Tabelle geprüft werden ob der Standort schon existiert, wenn nicht anlegen und ID erzeugen, wenn ja nur ID entnehmen.

Der Name des Sensors ist im Namen der einzelnen Datei enthalten und muss extrahiert werden, Danach wieder in der SENSOR Tabelle nachsehen aber der Sensor mit dem Sandort schon existiert, wenn nicht dann anlegen und ID generieren wenn ja ID entnehmen.

Jetzt auf die Datei zugreifen, der Inhalt der Dateien besteht immer aus einer Anzahl n Zeilen mit zwei Spalten ZEIT und WERT .
Jetzt müsste einen neue CSV erzeugt werden in der die Zeilen um die zusätzlichen Informationen ergänzt werden und optimaler weise aus der Zeit und dem Datum ein UNIX Timstamp erzeugt wird. Und dann muss das ganze in die MESSWERT Tabelle.

Soweit die Theorie nur an der praktischen Umsetzung verzweifle ich.

Bei Fragen und Anregungen immer los.

Vielen Vielen dank.

Da er das Script nicht in diesem Post mag erzeuge ich einen Zweiten für das Script.

 

[Cracymike]

Script ist zu Lang mehr als 10000 Zeichen daher als Datei.

 

[Cracymike]

Also an dieser stelle mal eine Etwas präzisere Frage. Ich bin bereits Dabei das Script abzuspecken und anzupassen, daher hier eine Verständnis Frage.

Ich habe einen Select auf die Standorttabelle gesetzt, die Variable $MACHINE enthält den Standort, wenn
die Abfrage einen Fehler ergibt soll der Standort neu angelegt werden. bin ich da auf der richtige Fährte oder mach ich es falsch ?

SQL="SELECT NAME FROM \`STANDORT\` WHERE NAME = '$MACHINE' "
STANDORT=`mysql --skip-column-names dwh_bga -uroot -p$SQLPWD -e "$SQL"`
RETURN=$?
if [ $RETURN -ne 0 ]; then
    #echo "yes" > $ERROR
    SQL="INSERT INTO \`STANDORT\` (NAME) VALUES (`$MACHINE`)"
    STANDORT`mysql --skip-column-names dwh_bga -uroot -p$SQLPWD -e "$SQL"`

 

[akretschmer]

STANDORT=`mysql --skip-column-names dwh_bga -uroot -p$SQLPWD -e "$SQL"` RETURN=$? if [ $RETURN -ne 0 ]; then

Ich denke, das ist Quatsch. Verwechsle bitte nicht ein leeres Resultat mit einer fehlerhaften Abfrage.

 

[Cracymike]

OK und wie dann besser ich stehe hier echt auf dem Schlauch wie würdest du es machen?

 

[akretschmer]

OK und wie dann besser ich stehe hier echt auf dem Schlauch wie würdest du es machen?

für

test=*# select * from foo;
 i
---
 1
 2
(2 rows)

ohne Fehlerbehandlung und so:

[root@akretschmer ~]# res=$(psql test -q -At -c "select count(1) from foo where i = 1")
[root@akretschmer ~]# echo $res
1
[root@akretschmer ~]# res=$(psql test -q -At -c "select count(1) from foo where i = 3")
[root@akretschmer ~]# echo $res
0

 

[akretschmer]

OK und wie dann besser ich stehe hier echt auf dem Schlauch wie würdest du es machen?

Davon abgesehen: ich würde mehr Grips in die DB auslagern. Die Daten erst mal alle in eine Tabelle und dann, in einer TX, aufgereiten. So springst Du immer zwischen Shell und DB herum. Ineffizient, fehleranfällig.

 

[Hony%]

Wenn du einfach nur sicherstellen willst, dass es die Maschine in der Datenbank gibt kannst du auch einfach ein INSERT IGNORE benutzen. Abschicken und vergessen.

Wenn du mehr Kontrolle brauchst kannst du auf gespeicherte Routinen zurück greifen. Dadurch kannst du dein Script deutlich beschleunigen weil die dafür notwendige Programmlogik in der Datenbank und nicht im Script steckt.

 

[Cracymike]

Die Sache mit der Shell ist mir durchaus bewusst, trotz dem muss ich da durch.

Wenn ich das mit dem INSERT IGNORE richtig verstanden habe versucht er den Eintrag zu erstellen und wenn es ihn schon gibt passiert nichts. Ist das so richtig.

Leider sind meine Kenntnisse nur rudimentär ich habe das Script und den Task geerbt und habe zwei Wochen Zeit das hin zu bekommen, daher benötige ich eventuell manchmal einen Hinweis mehr.

Danke schon mal für euer Verständnis.

 

[Hony%]

Wenn ich das mit dem INSERT IGNORE richtig verstanden habe versucht er den Eintrag zu erstellen und wenn es ihn schon gibt passiert nichts. Ist das so richtig.

Richtig. Zumindest so lange nicht die ON DUPLICATE KEY UPDATE Option benutzt wird.

 

[Cracymike]

Wenn ich das jetzt an meinem Beispiel von oben umsetzten möchte ist das dann So korrekt?

SQL="INSERT IGNORE \`STANDORT\` (NAME) VALUES (`$MACHINE`)"
    `mysql --skip-column-names dwh_bga -uroot -p$SQLPWD -e "$SQL"`

 

[Hony%]

Wenn ich das jetzt an meinem Beispiel von oben umsetzten möchte ist das dann So korrekt?

Ich gehe davon aus, dass du einen Syntax-Error bekommst weil INTO fehlt. Aber ansonsten sollte sollte es den Datensatz einfügen wenn kein UNIQUE Constraint verletzt wird. UNIQUE wird auch implizit durch den PK gesetzt.

 

[Cracymike]

Also habe mir mal ein Testscript angelegt wie folgt:

root@sbdb-server-01:/usr/local/sbin# vi test.sh

#!/bin/bash

NO_OF_DAYS_BACK=160
SQLPWD=

MACHINE="test"

SQL="INSERT IGNORE INTO  \`STANDORT\` (NAME) VALUES (`$MACHINE`)"
`mysql --skip-column-names dwh_bga -uroot -p$SQLPWD -e "$SQL"`

Ergebniss:

ERROR 1136 (21S01) at line 1: Column count doesn't match value count at row 1

Und was ist da jetzt Falsch?
Die in der Tabelle enthaltenen ID ist PK und AUTO_INCREMENT.

 

[Hony%]

Und was ist da jetzt Falsch?

INSERT IGNORE funktioniert nicht mit autoincrement. Wie soll MySQL feststellen ob es den Schlüssel schon gibt wenn du keinen angibst.

Zugegeben, ich hätte es oben sehen können. Dieses Beispiel ist nebenbei ein richtig gutes Argument gegen unnötige Auto-ID Felder. Du musst entweder das Design ändern oder auf IGNORE verzichten.

 

[Cracymike]

Jetzt habe ich IGNORE rausgeworfen --- Selber Fehler.