Ronny Seffner ronny@seffner.de hat am 1. August 2012 um 18:43 geschrieben:
Hallo,
ich habe regelmäßig (täglich) ein paar Daten aus einer proprietären Software anfallenden Textdateien in eine MySQL Datenbank zu packen. Als Admin kann ich mir mit BASH und MySQL ein wenig helfen, Programmierer bin ich damit nicht. Nun fällt erschreckend auf, wie langsam das Ganze ist und ich hoffe man kann mir hier helfen (im Zweifel dann auch bezahlt). Aber erstmal zu den Fakten:
So sieht die Datei aus (es sind dann Dutzende dieser Dateien, mit je Tausenden Zeilen):
03.05.2012 00:00:25 0,03296 03.05.2012 00:00:25 0,03296 03.05.2012 00:00:25 0,03296 03.05.2012 00:00:25 0,03296 03.05.2012 00:00:25 0,03296 03.05.2012 00:00:25 0,03296 03.05.2012 00:00:26 0,03296 03.05.2012 00:00:26 0,03296 03.05.2012 00:00:26 0,03296 03.05.2012 00:00:26 0,03296 03.05.2012 00:00:26 0,03296 03.05.2012 00:01:44 0,04413 03.05.2012 00:01:45 0,42134 03.05.2012 00:01:46 0,39369 03.05.2012 00:01:47 0,44423 03.05.2012 00:01:48 0,54073 03.05.2012 00:01:49 0,56819 03.05.2012 00:01:50 0,61855 ...
Sind mehrere Zeitstempel identisch, soll der _LETZTE_ Wert genommen werden.
Das ist meine Tabelle:
CREATE TABLE IF NOT EXISTS `test` ( `timestamp` datetime NOT NULL, `value` decimal(10,5) NOT NULL, PRIMARY KEY (`timestamp`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Ich vermute schon hier unüberlegt strukturiert zu haben. Ich möchte den Zeitstempel aber weiterverarbeitbar haben, da macht doch ein MySQL Format Sinn, auch wenn völliger Käse geliefert wird. Und weil ich ja nur den Letzten Eintrag bei identischen Zeitstempeln merken will, habe ich mich für "UPSERT" entschieden, also das Feld auf PRIMARY KEY gesetzt.
Und hier nun mein primitives Skript, welches auf einer Core i3 3GHz Maschine mit 8gb RAM so 20 bis 30 Zeilen in der Sekunde ins MySQL schickt:
cat "$FILE" | while read ENTRY; do YEAR=`echo "$ENTRY" | awk '{print $1}' | awk -F. '{print $3}'` MONTH=`echo "$ENTRY" | awk '{print $1}' | awk -F. '{print $2}'` DAY=`echo "$ENTRY" | awk '{print $1}' | awk -F. '{print $1}'` TIMESTAMP=`echo "$ENTRY" | awk '{print $2}'` NEWDATE="$YEAR-$MONTH-$DAY $TIMESTAMP" # VALUE=`echo "$ENTRY" | awk '{ print $3 }' | sed -e 's/,/./g'` VALUE=`echo "$ENTRY" | awk '{ print $3 }'` SQL="INSERT INTO `test` (`timestamp`, `value`) VALUES ('$NEWDATE', '$VALUE') ON DUPLICATE KEY UPDATE `timestamp`= '$NEWDATE', value = '$VALUE';" mysql machines -uroot -p$SQLPWD -e "$SQL" done
Ich denke hier jedes Mal mehrere awk und sed zu starten nur um das unglückliche Datumsformat zu verändern muss nicht sein. Leider kann ich kein perl, das soll ja Wunder wirken ...
Aber auch ohne die Datumskonvertierung ist das alles nicht wirklich schnell. Die my.cnf ist noch UBUNTU Standard und vielleicht sind so viele kleine INSERTS auch gar nicht wirklich gut?
Viele Inserts (vor allem, wenn ein TX-basiertes System werkelt, und diese je in einer einzelnen TX laufen) kosten halt Zeit.
Mein Ansatz wäre aber generell anders: erst mal in die DB, als ein Textfeld. Demo mit PostgreSQL:
test=*# select * from ronny ; t --------------------------------- 03.05.2012 00:00:25 0,03296 03.05.2012 00:00:25 0,03296 03.05.2012 00:00:25 0,03296 03.05.2012 00:00:25 0,03296 03.05.2012 00:00:25 0,03296 03.05.2012 00:00:25 0,03296 03.05.2012 00:00:26 0,03296 03.05.2012 00:00:26 0,03296 03.05.2012 00:00:26 0,03296 03.05.2012 00:00:26 0,03296 03.05.2012 00:00:26 0,03296 03.05.2012 00:01:44 0,04413 03.05.2012 00:01:45 0,42134 03.05.2012 00:01:46 0,39369 03.05.2012 00:01:47 0,44423 03.05.2012 00:01:48 0,54073 03.05.2012 00:01:49 0,56819 03.05.2012 00:01:50 0,61855 (18 rows)
Das einzulesen geht mit entsprechenden COPY-Befehl sehr flott.
Daraus kannst Du dann abfragen:
test=*# select distinct on (time) time, value from (select to_timestamp(substring(t,1,20),'dd.mm.yyyy hh24:mi:ss') as time, to_number(substring(t,22,50),'99D99999') as value from ronny) foo ; time | value ------------------------+--------- 2012-05-03 00:00:25+02 | 0.03296 2012-05-03 00:00:26+02 | 0.03296 2012-05-03 00:01:44+02 | 0.04413 2012-05-03 00:01:45+02 | 0.42134 2012-05-03 00:01:46+02 | 0.39369 2012-05-03 00:01:47+02 | 0.44423 2012-05-03 00:01:48+02 | 0.54073 2012-05-03 00:01:49+02 | 0.56819 2012-05-03 00:01:50+02 | 0.61855 (9 rows)
Noch eine Bemerkung zu Deinem _LETZTEN_ Datensatz: ohne ein ORDER BY gibt es in Mengen, mit denen Datenbanken hantieren, kein Oben und kein Unten, kein Vorne und kein Hinten. Das mußt Du genauer definieren.
Andreas