Konvertieren einer Tabelle aus Wikipedia nach MySQL

Im folgenden Artikel beschreibe ich, wie man mit einfachen Mitteln in zwei Stunden eine komplexe Tabelle von Wikipedia nach MySQL konvertieren kann und zwar so, dass die Tabelle dann „benutzbar“ ist, also frei von diversen Unschönheiten im Originaltext. Dabei benutze ich ausschließlich Mittel aus 2nd- und 4th-Generation-Languages (Siehe auch deutsche Version) (2nd-Generation sind zum Beispiel reguläre Ausdrücke, 4th ist zum Beispiel SQL, zu 3rd-Generation gehören Sprachen wie PHP). Der eigentliche Clou ist, dass die Konvertierung in der Datenbank stattfindet. Für manchen sicher ein alter Hut, aber viele haben das so sicher noch nicht gesehen. Anschließend beschreibe ich noch, was man generell beachten muss, wenn man so etwas produktiv einsetzen will und erkläre, was die generellen Vor- und Nachteile dieses Verfahrens sind.


… tja … also eigentlich fing es so an, dass ich eine Liste aller GSM-Codes in der Datenbank benötigte.

Dazu muss ich etwas ausholen: Jeder GSM-Provider hat so eine Art Identifikationscode von der ISO erhalten. Dadurch weiß ein Handy, in welchem Handynetz/bei welchem Provider es angemeldet ist. Das ist praktisch, denn die Provider operieren nämlich in aller Regel nicht länderübergreifend. Bzw. hat ein Provider für jedes Land einen eigenen GSM-Code.

Ich brauchte genau so eine Liste, um sagen zu können: Dieses Gerät meldet sich mit diesem GSM-Code, der gehört zu diesem Land, also trage ich diese Telefonnummer für den Rückruf ein.
So sieht die Liste auf Wikipedia aus
Sieht wild aus, aber das ist eigentlich gar nicht so schlimm.

Schritt 1: Wie bekomme ich die Daten aus Wikipedia raus und in ein leicht verarbeitbares Datenformat?

So mancher würde jetzt vorschlagen, zum Beispiel das HTML mit XPath zu analysieren und dann mittels eines mehr oder weniger komplexen Programms zu parsen. Am Schluss kommt ein XML-Format raus, welches man dann wiederum in die Datenbank spielen könnte.
Dazu muss man wissen: Man braucht die Liste nicht so häufig neu. Eigentlich ist es sogar nur eine einmalige Konvertierung, die ich da machen muss und da ist es sehr wahrscheinlich, dass das Datenformat der Originaltabelle in Wikipedia sich eher ändert, als wenn ich es doch wieder brauche. Also ist so etwas IMHO völlig oversized. KISS Rulz!
Was gibts denn dann?
Ein Kollege hatte die gute Idee: Man nehme nun diese Liste (fein im Browser markieren) und füge sie in Excel bzw. Calc ein.
Also Copy&Paste.
Danach sieht das dann so aus:

Wikipedia-Artikel-Tabelle mit Copy&Paste in ein Tabellenkalkulationsprogramm deiner Wahl kopieren und als CSV abspeichern…

Das ganze als CSV abspeichern und man bekommt das:

Bis jetzt sind 5 Minuten vergangen. Ich wüsste nicht, wie man das auf irgend eine andere Art schneller hinbekommen könnte.

Schritt 2: Eine Konvertierungstabelle in MySQL erstellen.

Ein paar Worte hierzu:

  • id ist nur dazu da, die Reihenfolge der Tabelle zu garantieren. Das ist essentiell wichtig, denn ohne die Information könnten nachfolgende Konvertierungsschritte scheitern
  • Da ich nicht genau weiß, wie groß die Spalten usw. sind wird das aktuell erstmal auf einen groben Wert festgelegt. Falls das irgendwann notwendig werden sollte kann man mittles

    verbessern. Was macht das? Kann man in der MySQL-Doku nachlesen!
  • die letzten 3 Spalten gibts im CSV nicht. Die brauche ich erst später, bei der ersten Implementierung der Tabelle waren die nicht dabei. Aber siehe Schritt 3!

Schritt 3: Textprocessing

Dieser Schritt ist deswegen wichtig; MySQL ist nicht besonders toll im Verarbeiten von Text. Zum Beispiel störten mich die Fußnoten.
Auch jetzt kämen viele auf die Idee: Ok, dann nehm ich als PHP (oder meine Lieblingsprogrammiersprache) und verarbeite das damit vor. Auch keine gute Idee. Es ist schlau, Tools zu nehmen, die es überall gibt. Es ist schlau, nicht mit Kanonen auf Spatzen zu schießen. Also nimmt man zum Beispiel SED. Wenn man mehr braucht, dann kann man das begründen, aber für so eine einfache Sache gleich mit der PHP- oder Perl-Keule zu kommen ist eventuell nicht schlau, denn dadurch kommt man auch auf komische Ideen. Zumindest Anfänger neigen dann eben dazu, Konvertierungsschritte, die man viel eleganter in der Datenbank lösen kann (dazu später) vorzuziehen.
Bevor ich die Tabelle jetzt also nach MySQL pumpe erzeuge ich mir eine Zwischendatei:

Was macht das?

  • Die erste Zeile findet wie gesagt alle Texte mit Zahlen in eckigen Klammern (also die Fußnoten) und entfernt sie.
  • Zeile zwei fügt zwei Kommas ans Ende einer Zeile. Das /.$/ findet eigentlich das letzte Zeichen einer Zeile und ersetzt es durch „,,“. Das sind die zusätzlichen Felder, die ich oben in der Tabellendefinition eingeführt habe. Das letzte Zeichen ist (weil es von einem Windows-Programm erzeugt wurde) ein Carriage Return. Das brauchen wir nicht, also weg damit.
  • Zeile drei ist kompliziert und zwar sucht die nach den Zeilen mit den Ländern:

    Und macht daraus:

    Also steht das Land und der CountryCode jetzt in den entsprechenden Spalten.

Hacky? Ja. Klar. Das ist gewollt. Wir befinden uns im Prototypenstatus. Schön ist was anderes. Aber es funktioniert und wenn man es ordentlich dokumentiert ist die Welt damit eigentlich in Ordnung.
[Nachtrag: Wenn ich das nochmal machen müsste, würde ich wohl Perl als Ersatz für SED verwenden. Weil die Syntax der regulären Ausdrücke in SED ist halt einfach etwas „altbacken“ und man kommt als PHP-Programmierer viel leichter mit Perl-Kompatiblen regulären Ausdrücken zurecht, als mit Posix-kompatiblen.]
Achja: Schritt 2 und 3 können sich mehrmals wiederholen, bis man die Daten einigermaßen sauber in Spalten getrennt hat. Im Idealfall hat man – so wie hier – für jede Iteration einen regulären Ausdruck.

Schritt 4: Ab nach MySQL

Was ist hier wichtig?

  • default-charset: Sehr wichtig, denn man vergisst das und wenn man nicht gerade passende Beispiele hat (also welche mit internationalen Zeichen), dann kommen Fehler, die man als Programmierer nie sieht, wenn man es sich nie in der anderen Sprache anschaut und der Nutzer nie meldet, weil er sich denkt „das muss so sein, das sind Computer, die können keine Umlaute“.
  • columns: Hauptsächlich wegen id. Ansonsten hätte ich id ans Ende der Tabelle setzen müssen. Kann man machen, ist aber unkonventionell und führt zu Verständnisproblemen, weil die Konventionen nicht eingehalten werden.

Es geht natürlich auch mit der LOAD DATA INFILE Syntax und das ist auch weitaus eleganter, aber mysqlimport ist der weitaus universellere Weg, weil er immer funktioniert, wenn man einen Zugriff auf die Datenbank hat.

Schritt 5: Die eigentliche Konvertierung

Bis jetzt war alles Vorgeplänkel, Spielerei! Wir kommen jetzt zum eigentlichen Kern des Blogeintrags!

Sinn dieses ganzen Vorgeplänkels war es, die Daten schon mal möglichst sauber und ein wenig vorgefiltert in die Datenbank zu bekommen, denn jetzt kann man viel einfacher konvertieren. Und man kann einfacher herausfinden, was man als nächsts tun muss. Ab jetzt kann also alles in der MySQL-Shell ablaufen und man konvertiert bei jedem Schrittimmer die ganze Tabelle. Das ist schneller als man denkt und optimieren kann man es immer.

Wir gehen das Zeile für Zeile durch:

  • #1: Löschen der Spaltenüberschriften und der Buchstaben-Suchhilfe
  • #2: Sonderfälle „International“ und „Test“
  • #3: Löschen von Zeilen, bei denen in der Spalte kein gültiger MCC-Code steht.
  • #4: Das ist sehr tricky und vielleicht kann man das auch anders machen. Hier wird eine Zwischentabelle erzeugt (weil man in einem UPDATE kein Subselect auf die gleiche Tabelle machen kann), dann wird von der aktuellen Zeile aus gesehen nach oben geschaut, was da das aktuelle Land ist, und das wird eingetragen.
  • #5: Hier wird versucht ungültige CountryCodes doch irgendwie zu finden und anzupassen (zum Beispiel Leerzeichen entfernen)
  • #6: Alles, was jetzt noch irgendwie kaputt ausschaut weg, damit fliegen auch die Überschriften raus.
  • #7: Hier wird der für mich relevane zusammengesetzte Code erzeugt, der dann auch in der Kommbox steht. Das Auffüllen mit 0 falls MCC nur ein Zeichen lang ist geht natürlich auch einfacher. Aber dann wirkt der Code finde ich nicht mehr ganz so verständlich. LPAD() ist halt die Funktion um links aufzufüllen… schlagt mich, aber ich finde CONCAT() ist dafür nicht ganz die richtige Funktion.
  • #8: Auf die relevante Spalte einen Index anlegen (UNIQUE geht wie gesagt nicht, denn es gibt Provider die in mehreren Ländern sind).
  • #9: Eine atomare Umbenennung der frisch erzeugten Tabelle mit der Produktionstabelle und anschließendes löschen der alten Produktivtabelle. Eigentlich oversized, siehe Fazit. Aber ich wollte es hier als kleines Gimmick bringen, damit man sieht, wie das geht. :)

Ergebnis

Das Endergebnis (Ansicht in phpMyAdmin)

Eine sauber konvertierte Tabelle, die mit agilen Mitteln schnell an eine Änderung angepasst werden kann, falls das je notwendig wird.

Der Konverter braucht für am Ende rund 2300 Original und 1500 Ziel-Zeilen etwa 3 Sekunden. Ich hab ungefähr einen Tag gebraucht, um die Datenquellen zu recherchieren (wo bekommt man eine zuverlässige Liste der GSM-Provider her? Es hätte auch andere Möglichkeiten als Wikipedia gegeben, dort waren die zusätzlichen Informationen aber am umfassendsten), die Quelle stichprobenweise zu prüfen (wer sagt, dass es stimmt, was Wikipedia da anbietet?), die Daten zu analysieren, die entsprechenden Queries zu schreiben und das ganze zu einem funktionierenden Prozess zusammen zu bauen, weniger ist möglich, aber nicht wünschenswert, weil man sonst leicht etwas übersieht. Jeder Query geht nämlich ein ausgiebiger Analyseprozess vor und man sitzt schnell eine Stunde oder länger an einer Query, bis sie so funktioniert, wie sie soll und es soll ja irgendwo schon Hand und Fuß haben.

Trick 86: Erzeuge SQL mit SQL

Eigentlich ist das wieder ein eigener Blogeintrag. Ich bau ihn aber mal hier mit ein. Vielleicht befasse ich mich mal ausführlicher damit.

Hintergrund: Es gibt Konvertierungsschritte, bei denen wieder eine Prozedurale Sprache benötigt. Das betrifft besonders Konvertierungsschritte, die Daten in Abhängigkeit von anderen Daten schreiben, also so richtig kompliziertes Zeug, was über eine einfache Konvertierung hinaus geht. Ich zeige hier aber einen Trick: Man kann SQL mit SQL erzeugen.

Generell sieht das so aus:

Verwirrend? Ja…
Der SELECT wirft UPDATE-Queries aus, die wiederum ziemlich kompliziert sind. Das aber wiederum ist einfacher, als ein noch viel komplexeres UPDATE-Query hinzuschreiben, was das alles in einem Rutsch macht. Das ist die Idee.
Nutzen kann man das so:

Das wiederum ist simpel: Ich pipe die Query nach MySQL und die Ausgabe pipe ich wieder in MySQL rein. Die Option ‚-q‘ schaltet den Cache aus und die Option ‚-N‘ dass die erste Zeile mit den Spaltennamen ausgegeben wird. Da die MySQL-shell sich automatisch im Batch-Modus befindet (‚-B‘), gibt sie auch keine störenden ‚|‘-Zeichen aus, man braucht daher kaum mehr Optionen. Aber weitere nützliche Optionen in diesem Zusammenhang (neben den obligatorischen Connect-Parametern): ‚-e‘, ‚–d‘, ‚–default-character-set‘ (siehe oben!).
Damit kann man komplexe Sachen machen. Aber natürlich ist das dann auch kaum noch verständlich, denn wer kann sich einen Query aus CONCAT schon zusammengesetzt vorstellen? Also ist viel Dokumentation nötig, damit man das in einem halben Jahr noch versteht! Und wenn viel Doku notwendig ist, dann ist das normalerweise immer ein gewisser „smell„.
Daher ist das keine gute generelle Vorgehensweise! Für immer wieder vorgenommene Konvertierungen bin ich dann schon sehr dafür, dies „ordentlich“, also in dem Fall in einer 3rd-Generation-Language zu schreiben.
Aber wenn es darum geht einen einmaligen Konverter zu schreiben (etwa um die Daten von Version 1 auf Version 2 zu hieven), dann ist das ein super Ansatz, denn er spart einfach eine Menge Arbeit und verhindert viele Dinge, die ansonsten (also mit dem üblichen 3rd-Generation-Ansatz) schiefgehen könnten. Außerdem eigent sich die Vorgehensweise super für schnelles Prototyping von scheinbar komplexen Abfragen/Konvertierungen und man kann damit auch hervorragend Geschwindigkeitsmessungen vornehmen.

Schritt 6: Test?!

Zur Wiederholung: Der Konverter hat nur Prototyp-Charakter, weil er nur einmal benutzt wurde. Wozu sollte man da Tests schreiben? Alles was ich testen muss, kann ich einfach „sehen“, indem ich durch die Tabelle scrolle. Dazu ein paar einfache SQL-Abfragen, fertig ist die Verifizierung.
Es wäre ein echter Fehler, wenn man die Daten mit diesem Konverter ohne nochmalige Prüfung in die produktive Datenbank einspielen würde. Richtig wäre die fertig konvertierten, per Hand geprüften Daten einspielen.
Ich reite da drauf rum, denn man sieht den Fehler häufig. Eine einmalige Konvertierung händisch zu testen ist ok. Falsch ist die Konvertierung auf dem Produktivsystem vorzunehmen und dann ohne nochmalige Prüfung davon auszugehen, dass es passt.
Denn die Ausgangsdaten werden sich ja nie ändern. Außerdem wird das Produktivsystem immer so funktionieren wie das Testsystem.

Aber was tun, wenn die Konvertierung in einen produktiven Einsatz übergehen soll?
Schlechte Idee. Wozu sollte man so eine Tabelle produktiv konvertieren? Natürlich ändern sich Daten oder es wird neue Software installiert und dadurch funktioniert der Konverter auf einmal nicht mehr. Oder noch schlimmer: Er funktioniert und macht alles kaputt.
Der Produktmanager besteht aber darauf. Ok, wenn er das so will… das heißt für uns, den Zustand „kaputt“ feststellen; im produktiven Einsatz muss das vollautomatisch geschehen und diverse Fehlerfälle müssen abgefangen werden, damit zum Beispiel nicht doch versehentlich die kaputte Tabelle in die Produktionstabelle umbenannt wird!
Um damit also produktiv zu gehen, benötigt man verschiedene Tests. Ich meine also jetzt nicht mehr speziell diese Konvertierung, sondern andere Datenquellen, andere Datenformate aus nicht nachweisbar zuverlässigen Quellen, in „merkwürdigen“ Textformaten und so weiter.

Aber wie würden Tests aussehen?

Naja, das wäre sicherlich schon mal die Überprüfung,

  • ob alle Constraints auch eingehalten wurden.
  • Sind die Ausgangsdaten im richtigen Format, im richtigen Zeichensatz?
  • Prüfungen auf die richtigen Formaten in den Spalten (siehe Schritt 5, Zeile 26/27) oder so; in diesem Fall etwa der Check, ob es den angebenen CountryCode auch wirklich gibt (indem man sich die Liste der Country-Codes aus Wikipedia konvertiert ::) ).
  • Sehr wichtig: Man sollte überprüfen, ob die Anzahl der Zeilen stimmt (es müssen mindestens so viele wie beim letzten mal sein, und weniger als 100 dürfen es wohl auch nie werden, oder?), weil man vergisst gern den Fall, dass die Datenquelle einfach gar keine Daten liefert.
  • Wurde die erste und letzte Zeile richtig konvertiert?
  • Typische Grenz-Tests: Erster, letzter (Zeile, Spalte…). Aber auch eine zufällge Stichprobe.
  • Stimmt die Anzahl der Spalten?
  • Sind Felder, die gefüllt sein müssten leer?
  • Sind (neue) Warnungen beim Konvertieren aufgetreten?

Der Fantasie sind da jedenfalls keine Grenzen gesetzt. :)
Wenn die Tests scheitern, dann muss sauber abgebrochen werden. Das heißt: Die angefangene Konvertierungstabelle darf niemals online gehen.
Das sollte man ebenfalls testen!

FAZIT

Man kann ohne eine Zeile in einer 3rd-Generation-Language (also Sprachen wie PHP, Perl, Bash usw.) relativ komplexe Konverter programmieren um so in gesetzter Zeit zum Ziel zu kommen. Der Ansatz, SQL (also eine 4th-Generation-Language) dafür zu verwenden und jeden Konvertierungsschritt einfach auf die ganze Tabelle anzuwenden, spart hunderte Zeilen komplexen Programmcode und macht eine Zeitschätzung für die Implementierung deutlich sicherer.

Vorteile
  • Die Wahrscheinlichkeit mit jeder Zeile Code einen Fehler zu machen ist viel niedriger, als bei einer „herkömmlichen“ Programmierung. Weniger Code, weniger Fehler. Weniger Fehler, weniger Arbeit.
  • Da man die Daten immer als ganzes Konvertiert, birgt die Vorgehensweise weniger Überraschungen und ist leichter abschätzbar, als wenn man auf „übliche Weise“ einen Konverter schreibt.
  • Es macht mehr Spaß, weil man die Daten viel besser wuseln sieht. :)

Sicher ist es einem geübten Programmierer möglich, so etwas in kürzerer Zeit auf die „übliche“ Art und Weise zu programmieren. Sicher auch „schön“, so mit Klassen und Abfangen der Fehler die auftreten können. Aber wer ist in der Konvertierung schon geübt?
Konverter sind am Anfang eines Projekts meistens lästige Arbeiten (es werden keine sauberen Testdaten geliefert oder so was), die sich dann im Lauf des Projekts noch ein paar dutzend mal ändern und oft erst nach Monaten/Jahren sauber reengeneert werden – wenn überhaupt. Meistens steckt der Teufel im Detail, man hat etwas übersehen, welches den Plan, den man beim Schreiben des Konverter ursprünglich hatte, völlig über den Haufen wirft. Man müsste von vorne anfangen. Hat dafür natürlich keine Zeit mehr und fängt an zu pfuschen; solchen Code hat wohl schon jeder von uns mal in der Hand gehabt?!
Mit dieser Vorgehensweise wird das aber zum System, denn es macht dass Schreiben des Konverters zum agilen Prozess. Weil alles recht übersichtlich ist (wenig Code) muss man nicht viel ändern, wenn sich was ändert. Es „sauber“ zu machen, ist jederzeit möglich, aber was ist sauberer als eine gut geschriebene Query? Es sieht nur scheinbar hingeschludert aus, ersetzt aber in Wirklichkeit dutzende Zeilen normalen Code.
Und: Wenn die Konvertierung zu langsam wird, dann ist eine Optimierung immer noch möglich. Immer! In diesem Zusammenhang ist die Benutzung/Nicht-Erzeugung von Indizen ein sehr wichtiger Faktor! [Das ist auch ein eigener Blog-Artikel.]

Nachteile
  • Komplizierte Stringmanipulation mit (My)SQL macht überhaupt keinen Spaß. Es eignet sich einfach nicht dafür. :(
    [Hier gezeigt: reinen Text vorher filtern (zum Beispiel mit SED). Braucht man mehr, sollte man nur so schwere Geschütze auspacken, wie man wirklich braucht, denn sonst verführt das Anfänger zu sehr, die Konvertierung an der falschen Stelle vorzunehmen. Auch der gezeigte „Trick 86“ ist eine Möglichkeit, da herum zu kommen, aber sicher nicht in jedem Fall die richtige Methode.]
  • Komplexe Konvertierungen (in Abhängigkeit von anderen Daten konvertieren, oder wenn man mehrere Tabellen quasi voneinander abhängig konvertieren muss, oder weil es „Querverknüpfungen“ gibt) sind mit dem Ansatz nicht immer möglich.

Wenn die Aufgabe zu komplex wird, dann die Daten so weit wie möglich erstmal in eine Tabelle in der DB konvertieren und dann gezielt die notwendigen Manipulationen in einer 3rd-Generation-Language vornehmen.Auch hier gilt: für jeden größeren Schritt einen Konverter schreiben!
Möglicherweise bietet aber auch der oben gezeigte „Trick 86“ einen Ansatz, da herum zu kommen, wenn man sich nicht von der scheinbaren Komplexität abschreckt und es sich tatsächlich um einen eher einmalig benutzten Konverter handelt.

In dem Sinne: Viel Spaß beim Konvertieren.

Für neue Blogupdates anmelden:


Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.