[Tipp] MySQL-Infile: Fehlende Werte als NULL verstehen

[utopos]

Gleich vorweg:
Ich hatte ein Problem, zu dem ich zunächst im Internet eine Lösung gesucht habe. Dabei habe ich aber nur gelernt, dass viele vor mir schon das gleiche Problem hatten - eine Lösung war nicht dabei.
Inzwischen habe ich aber selbst eine Lösung gefunden, die ich für Interessierte hier gerne hinterlegen möchte.


################################

Beim Einspielen von Daten in eine MySQL (genauer gesagt, 5.5.29-MariaDB) gehe ich wie folgt vor:
Ich entwerfe pro Tabelle die hinzuzufügenden Zeilen in einem Tabellenkalkulationsprogramm (z.B. gnumeric).
Dieses verwende ich v.a. als angenehmen übersichtlichen Editor für Tabellen mit ein paar Zusatzfunktionen, z.B. um die Eindeutigkeit der Schlüssel vorab zu prüfen oder auch Strings zu bearbeiten.
Dann erstelle ich daraus eine Tabulator-getrennte Textdatei (Kopieren aus dem Programm in Text-Editor).

Diese Datei hoffte ich dann, in die Datenbank einspielen zu können mit einem Befehl wie

load data local infile '/lokaler/pfad/zur/gewünschten/datei.tab' into table tabelle fields terminated by '\t' escaped by '\\' lines terminated by '\n';

Das funktioniert auch - mit einer unschönen Nebenwirkung:
Im Tabellenverabeitungsprogramm nicht gefüllte Zellen werden mit einem Standardwert gefüllt, also leere Zeichenkette bei VARCHAR, 0 bei INT, '0000-00-00' bei DATE usw.
Häufig - insbesondere bei mir - ist aber gewünscht, dass diese Felder auf der Datenbank ungefüllt bleiben, also mit NULL-Werten.
NULL-Werte aber soll man in solchen Einspieldateien durch \N kennzeichnen.

Wer hat schon Lust, alle leeren Zellen mit \N vollzukopieren?

Wenn MySQL schon nicht nachgibt, muss man das wenigstens automatisieren.
Wie erkennt man in der (halb-)fertigen Einspieldatei, dass ein leeres Feld eingespielt werden soll?
Antwort: Zwei Feldbegrenzer - hier Tabulatoren - stoßen unmittelbar aufeinander.
Hier ist also immer ein "\N" einzufügen.
Ein einfacher Suchen-und-Ersetzen-Vorgang genügt aber nicht, da von zwei aufeinanderfolgenden leeren Feldern nur immer das erste angepasst würde.
Man muss den Prozess also iterieren.

Hier ist meine vollständige Lösung für das Problem:
Ich rufe im Verzeichnis mit der Einspieldatei (nach dem Speichern als Text, vor dem Einspielen) folgenden Befehl auf:

/lokaler/pfad/zur/gewünschten> perl -i.old -pe 's/\t\t/\t\\N\t/g while /\t\t/' datei.tab

Dies nimmt genau die oben erläuterten Ersetzungen vor,
und die Einspielung führt zum gewünschten Ergebnis.

Randbemerkung: Das Kennzeichen "g" im obigen Befehl kann man auch weglassen. Das Ergebnis ist dasselbe, die Laufzeit u.U. eine andere.
Allerdings: Meine Infiles sind klein, mein Herz ist rein, und ich kann nicht erkennen, in welcher Richtung der Performanz-Vorteil zu finden ist.

################################


Falls jemand bessere Lösungen kennt, freue ich mich sehr, sie zu erfahren!

In jedem Fall hoffe ich, dass ich anderen auf der Suche einige Experimente ersparen kann.

 

[spoensche]

Statt Tabulatoren als Trennzeichen solltest du mal ein ; oder ein | oder ein , verwenden. Dann ist es wesentlich einfacher.

MySQL Workbench kennst du oder? Damit kannst du die Datenbank auch grafisch als Diagramm entwerfen.

 

[utopos]

Statt Tabulatoren als Trennzeichen solltest du mal ein ; oder ein | oder ein , verwenden. Dann ist es wesentlich einfacher.

Lässt sich aber nicht so leicht herstellen: Wenn ich eine Datei gnumeric, kspread, oocalc oder MS-Excel als Text speichere bzw. den Text von dort in einen Texteditor kopiere, habe ich zunächst einmal Tabulatoren.

Im Übrigen würde es mit ; oder , nicht unbedingt funktionieren: Die Zeichenketten in den einzelnen Feldern können ja durchaus Satzzeichen enthalten, Tabulatoren hingegen sind nicht nur weniger bedeutsam, sie lassen sich auch in der Tabellenkalkulation meines Wissens gar nicht eingeben in einer Zelle.

MySQL Workbench kennst du oder? Damit kannst du die Datenbank auch grafisch als Diagramm entwerfen.

Nein, kenne ich nicht - danke für den Tipp! Sollte ich mir mal anschauen. Ich bin mich noch im Wesentlichen am Einfinden in MySQL - bisher kannte ich nur DB2SQL, und ich bin noch ein bisschen am Herumprobieren, wo die Unterschiede liegen.

Und die Hobelbank hilft auch beim Datenimport?

 

[spoensche]

Statt Tabulatoren als Trennzeichen solltest du mal ein ; oder ein | oder ein , verwenden. Dann ist es wesentlich einfacher.

Lässt sich aber nicht so leicht herstellen: Wenn ich eine Datei gnumeric, kspread, oocalc oder MS-Excel als Text speichere bzw. den Text von dort in einen Texteditor kopiere, habe ich zunächst einmal Tabulatoren.

Wieso in einen Texteditor kopieren? Datei->Speichern unter->bei Dateiformat csv auswählen und speichern. Bevor die Datei gespeichert wird wirst du in einem Dialog nach dem Trennzeichen gefragt.

Im Übrigen würde es mit ; oder , nicht unbedingt funktionieren: Die Zeichenketten in den einzelnen Feldern können ja durchaus Satzzeichen enthalten, Tabulatoren hingegen sind nicht nur weniger bedeutsam, sie lassen sich auch in der Tabellenkalkulation meines Wissens gar nicht eingeben in einer Zelle.

Und für diese Fälle verwendet man dann | als Trennzeichen. Das kommt in keinem Satz vor, es sei den es handelt sich um einen Befehl mit Ausgabeumleitung.

MySQL Workbench kennst du oder? Damit kannst du die Datenbank auch grafisch als Diagramm entwerfen.

Nein, kenne ich nicht - danke für den Tipp! Sollte ich mir mal anschauen. Ich bin mich noch im Wesentlichen am Einfinden in MySQL - bisher kannte ich nur DB2SQL, und ich bin noch ein bisschen am Herumprobieren, wo die Unterschiede liegen.

Und die Hobelbank hilft auch beim Datenimport?[/quote]

Hm, Jein. Du kannst SQL- Scripte importieren. Workbench hat aber eine eigene Scripting Shell, mit der man den Import dann umsetzen könnte.

 

[utopos]

Und für diese Fälle verwendet man dann | als Trennzeichen. Das kommt in keinem Satz vor, es sei den es handelt sich um einen Befehl mit Ausgabeumleitung.

Das überzeugt mich.

 

[utopos]

Wieso in einen Texteditor kopieren?

Bin gerade über ein gutes Argument gestolpert:
Weil nicht alle Zeilen in die Datenbank sollen.

Abgesehen etwa vom inkrementellen Befüllen muss ja stets mindestens die Zeile mit den Überschriften, außerdem Hilfsspalten mit Bemerkungen raus.
Man kann natürlich
1. Speichern
2. alles löschen was weg muss
3. um Himmels willen nicht Speichern!
4. Speichern als csv mit richtigem Trennzeichen
5. Schnell schließen, bevor man noch etwas ändert!

Da scheint mir das Kopieren in einen Texteditor (den ich sowieso immer offen habe) doch fehlerunanfälliger.

 

[utopos]

Habe mir jetzt auch MySQL-Workbench einmal angeschaut.
Der SQL-Editor-Bereich ist ja dem ziemlich ähnlich, was ich für DB2SQL vom Sybase Infomaker kannte (mit einigen wirklich praktischen zusätzlichen Features).

Den Modell-Bereich meintest Du wahrscheinlich. Das ist sicher eine praktische Sache, wenn man so etwas mit sehr großen Datenbanken (groß bzgl. der Tabelleanzahl) häufiger macht. Für mich ist das eher nicht das richtige, da ich nur gelegentlich Erweiterungen/Änderungen in Datenbanken vornehme und es mir dann wohler dabei ist, wenn ich genau weiß, welchen Befehl ich wann abgeschickt habe.

Trotzdem vielen Dank für den Hinweis!

 

[abgdf]

Für mich ist das eher nicht das richtige, da ich nur gelegentlich Erweiterungen/Änderungen in Datenbanken vornehme

Kenn' ich, ich habe z.B. festgestellt, daß für meine Bedürfnisse Datenbankserver wie MySQL gar nicht nötig sind, weil meine Datenmengen gar nicht so groß sind, daß es ins Gewicht fallen würde.
Ich bin also dazu übergegangen, mit Python einfach auf Nur-Text-Dateien 'rumzuhacken. Zwei der Skripte sind dabei dann allerdings auch etwas größer geworden. Andererseits hätte ich auch keine Ahnung gehabt, wie um alles in der Welt ich das in SQL hätte formulieren sollen. Wahrscheinlich wäre das auch gar nicht gegangen, weil da noch einigermaßen komplexe und spezielle Berechnungen mit den Daten gemacht wurden (Buchführung).

 

[utopos]

@abgdf:
Datenmenge ist eine Sache - die ist bei mir auch relativ klein, so dass der Zugriff auf Textdateien vermutlich schneller wäre (beide aber ohnehin schnell genug).

Mir ging es mehr darum, dass ich selten vollkommen neue Datenbanken anlege und sich deren "Datenmodell" nicht so schnell tiefgreifend ändert, dass so ein Modellier-Werkzeug wirklich hilfreich wäre. Neue Spalten hingegen kommen regelmäßig dazu - das ist für mich _ein_ Grund, eine echte Datenbank und SQL zu verwenden, da ich sonst die technische Implementierung immer wieder anpassen müsste.

Eine SQL-Datenbank sehe ich als eine "objektive" Implementierung von Datenhaltung an, während ich bei Textdateien Implementierungsentscheidungen immer selbst und eingedenk der dabei unvermeidlichen Willkür treffen müsste (Codierung, Trennzeichen, verschieden Tabellen in verschiedenen Dateien oder in einer? Pfad zu den Dateien? Wo sind die Überschriften? Wo die Datentypen?). Auch Standardwerte und die Definition und der Umgang mit NULL-Werten müsste ich dann selbst festlegen, was mir nicht sinnvoll erschien.

Als weiteren Grund für eine "echte" Datenbank war mir, dass die Datenmengen für SQL vielleicht klein, zum echten Lesen in einer Text- oder Tabellenkalkulationsdatei aber doch zu groß sind. Wenn mein einziger "Blick" in diese Kiste mein eigenes Programm ist, müsste das sehr, sehr flexibel sein. So kann ich spontan eine SQL-Abfrage zusammentippen und erfahre sofort etwas.

Richtig, das kann man bei Textdateien mit ad-hoc-Skripten auch, aber jetzt kommt wieder die "Objektivität" ins Spiel: Angenommen, ich lasse die Datenbank ein halbes Jahr unberührt liegen und versuche dann, wieder herauszufinden, was das alles sollte. Bei einer echten Datenbank hängt der Erfolg fast nur davon ab, wie sprechend und einleuchtend ich die Bezeichnungen der Tabellen und Spalten gewählt habe. Bei Text muss ich zusätzlich die Implementierung erraten oder im Code zusammenklauben, was nicht sehr ergötzlich ist und auch wenig mit Inhalt und Bedeutung der Daten zu tun hat.

Ein letzter, aber wichtiger Grund, der mich zur Entscheidung für SQL bewogen hat:

Beim Programmieren sollte alles auf der Ebene gekapselt sein, auf der es (für die Logik eines Code-Lesers) gekapselt gehört.

Nun ist das für jeden Leser potentiell anders; für mich jedenfalls ist das systematische Abrufen, Abmischen, Zusammenfassen und Sortieren von Daten in SQL sehr gut lesbar und daher dort gut aufgehoben. Über die technischen Details der Datenablage hingegen möchte ich, wenn es mir um den Datenzugriff geht, vermutlich gar nichts wissen; dort ist die untere Grenze, wo SQL sinnvoll zu sein beginnt. Und die obere Grenze für SQL sind Operationen, die Abfragen zu kompliziert machen - dazu gehören schon mittleren Rechenoperationen an (Floating-Point-Divisionen ...) und erst recht "echte" algorithmische Aufgaben, die über das Sortieren hinausgehen.

Das ist jedenfalls mein Statement für SQL ...

 

[abgdf]

Bei mir:

Codierung: ISO-8559-1
Trennzeichen: Tabulator (kann vim gut mit "set list" darstellen)
verschieden Tabellen in verschiedenen Dateien oder in einer: In einer.
Pfad zu den Dateien: Z.B. /home/user/data/mydatabase.txt oder so.
Wo sind die Überschriften: Ok, die sind in einer Extradatei. Die Tabellen (mehrere in einer Datei) haben daneben jeweils eine [Überschrift].
Wo die Datentypen: Brauche ich nicht zu definieren, ergibt sich.

Aber nimm ruhig MySQL, wenn Du damit gut/besser klarkommst. Schau Dir ggf. auch mal "knoda" als grafisches Frontend an.

 

[utopos]

@abgdf:
Beim Rechnen, u.U. auch schon bei Buchhaltung, wird es in SQL relativ schnell ungemütlich. Dekorationen wie

cast (1.00000000 * coalesce(betrag,0) / (1.0 * zeitraum) as decimal (16,4)) * ...

ist ein entscheidender Nachteil von SQL.

 

[spoensche]

cast (1.00000000 * coalesce(betrag,0) / (1.0 * zeitraum) as decimal (16,4)) * ...

ist ein entscheidender Nachteil von SQL.

Wenn man beim Anlegen der Tabelle direkt den passenden Datentyp verwendet, dann braucht man auch keinen Datentyp zum casten und wenn man ein SQL Script verwendet bzw. eine Stored Procedure, dann kann man z.B. den Variablen für Zwischenergebnisse ebenfalls den passenden Datentyp zuweisen.

Der von dir genannte Auszug ist zu umständlich. Den 1.000000000000000000000000000 wird immer = 1 bleiben, egal wie viele 0 du nach dem Komma hast.

Wenn du für die Spalten, die mit Zahlen gefüllt werden den Datentyp varchar, char o. sonstige Textdatentypen verwendet hast, dann solltest du dies schleunigst überarbeiten.

 

[utopos]

@spoensche:
Da hast Du mich missverstanden - es geht nicht um String-Typen, sondern um die Genauigkeitsregeln bei Decimal-Arithmetik, die bei DB2SQL etwas eigenwillig sind (kann nicht viel über andere Dialekte sagen, habe das Problem derzeit auch nicht).

Es ist manchmal bei eigentlich nicht sonderlich komplex anmutenden Rechnungen nicht trivial, sowohl einen Overflow ("Fehler in einer Zeile") als auch ein ungenaues Ergebnis zu verhindern. Es hilft eine kurze Überlegung anhand der DB2-Regeln und dann etwas Rumprobieren mit oben zitierten Scheußlichkeiten.

Dass man das ggf. in Skripte kapseln kann, ist natürlich richtig.

 

[spoensche]

@spoensche:
Da hast Du mich missverstanden - es geht nicht um String-Typen, sondern um die Genauigkeitsregeln bei Decimal-Arithmetik, die bei DB2SQL etwas eigenwillig sind (kann nicht viel über andere Dialekte sagen, habe das Problem derzeit auch nicht).

SQL ist ein Standard. D.h. bis auf die herstellerspezifischen Erweiterungen, sind die nahezu indentisch.

Es ist manchmal bei eigentlich nicht sonderlich komplex anmutenden Rechnungen nicht trivial, sowohl einen Overflow ("Fehler in einer Zeile") als auch ein ungenaues Ergebnis zu verhindern. Es hilft eine kurze Überlegung anhand der DB2-Regeln und dann etwas Rumprobieren mit oben zitierten Scheußlichkeiten

.

Ein Overflow tritt dann auf, wenn der Datentyp die max. Größe, die beim Anlegen der Tabelle definiert wird, erreicht ist. Daher ist das designen eines DB-Schemas auch nicht mal eben nebenbei zwischen Tür und Angel beim Zähneputzen erledigt, sondern es ist einiges an Hirnschmalz Investition zu tätigen.

Dass man das ggf. in Skripte kapseln kann, ist natürlich richtig.

Mit SQL Scripten meine ich keine Kapselung der Daten, die so wieso erst bei der Entwicklung der jeweiligen Objekte (Entitäten, Controller usw.) erfolgt.
Mit SQL definierst du nur die jeweiligen Datenstrukturen und wie sie Beziehung zu einander stehen.

Mit SQL- Scripten meine ich z.B. selbst geschriebene Funktionionen oder Stored Procedures die auf dem DB-Server ausgeführt werden, aber von diversen Anwendungen aufgerufen werden können.

 

[abgdf]

Ein Overflow tritt dann auf, wenn der Datentyp die max. Größe, die beim Anlegen der Tabelle definiert wird, erreicht ist.

Hehe, dieses Problem habe ich bei meiner csv-Textdatei nicht: Die Daten, die dort in einer einfachen Textzeile durch Tabulatoren getrennt werden, können nahezu beliebig groß (oder klein) sein, ohne daß ich mich darum kümmern müßte.

Daher ist das designen eines DB-Schemas auch nicht mal eben nebenbei zwischen Tür und Angel beim Zähneputzen erledigt, sondern es ist einiges an Hirnschmalz Investition zu tätigen.

Das ist dann leider wieder auch bei meiner csv-Datenbankdatei richtig.

 

[spoensche]

Ein Overflow tritt dann auf, wenn der Datentyp die max. Größe, die beim Anlegen der Tabelle definiert wird, erreicht ist.

Hehe, dieses Problem habe ich bei meiner csv-Textdatei nicht: Die Daten, die dort in einer einfachen Textzeile durch Tabulatoren getrennt werden, können nahezu beliebig groß (oder klein) sein, ohne daß ich mich darum kümmern müßte.

Daher ist das designen eines DB-Schemas auch nicht mal eben nebenbei zwischen Tür und Angel beim Zähneputzen erledigt, sondern es ist einiges an Hirnschmalz Investition zu tätigen.

Das ist dann leider wieder auch bei meiner csv-Datenbankdatei richtig.

[/quote]

Ab einer gewissen Größe ist deine CSV-DB nebenbei erwähnt auch sehr viel langsamer beim laden und beim durchsuchen.