NDJSON in SQL umwandeln

Die Abbildung ist so direkt, wie sie klingt: Jede nicht leere Zeile der Quelle ergibt genau ein INSERT-Statement. Wer die Zeilen zählt, kennt die Zahl der Statements — es gibt keine Kopfzeile abzuziehen, und ein Zeilenumbruch innerhalb eines Werts kann nicht vorkommen, weil er im JSON bereits maskiert ist.

Diese Berechenbarkeit macht das Paar angenehm für Serverlogs. Meldet ein Ladevorgang 412.000 eingefügte Zeilen und die Datei hatte 412.000 Zeilen, war der Import vollständig — anders als bei einer CSV-Datei, wo ein falsch gequotetes Feld eine Zeilengrenze verschlucken kann.

Hier hilft ein zeilenweises Quellformat nicht bei einer zeilenweisen Umwandlung. Jedes Statement muss dieselbe Spaltenliste benutzen, und diese Liste ist die Vereinigung aller Schlüssel über alle Zeilen hinweg — ein Feld, das erst im letzten Datensatz auftaucht, wird trotzdem zur Spalte. Geschrieben werden kann also erst, wenn die ganze Datei gelesen wurde.

Praktisch bedeutet das eine Speichergrenze statt einer Größenstufe: Die gesamte Datei liegt während der Umwandlung im Tab. Einige Dutzend Megabyte sind unproblematisch, bei mehreren Hundert wird der Browser merklich langsamer. Darüber hilft nur Aufteilen — NDJSON lässt sich an jeder Zeilengrenze gefahrlos trennen, jedes Teilstück konvertiert und lädt für sich.

Ein Log, das Requests, Fehler und abgeschlossene Jobs in eine Datei schreibt, hat drei verschiedene Schlüsselmengen. Der Abgleich handhabt das, ohne etwas zu verwerfen: Jeder Schlüssel wird zur Spalte, eine Zeile ohne diesen Schlüssel liefert NULL — die Tabelle wird die Vereinigung aller drei Schemata.

Für eine einmalige Auswertung ist das oft in Ordnung. Für alles Dauerhafte lohnt es sich, die Datei vorher pro Ereignistyp zu filtern und drei Tabellen zu laden — das ergibt ein Schema, das sich sinnvoll indizieren lässt, statt Abfragen, die mit einem Filter auf eine Unterscheidungsspalte beginnen müssen.

Die Ausgabe enthält keine DDL. JSON sagt nur, dass ein Wert eine Zahl ist, nicht ob die Spalte eine Ganzzahl oder eine Dezimalzahl mit zwei Nachkommastellen sein soll, ob sie NULL erlauben darf, was der Primärschlüssel ist oder wie lang ein Text werden darf — genau das, wofür ein Schema eigentlich da ist.

Der praktische Ablauf: umwandeln, die Spaltenliste aus dem ersten Statement ablesen, die Tabelle danach anlegen, dann laden. Wichtig dabei: Die Spalten immer aus einer Umwandlung der ganzen Datei ableiten, nie aus einer Stichprobe — ein seltenes Feld, das nur in einer von einer halben Million Zeilen vorkommt, wird trotzdem zur Spalte in jedem Statement, und eine aus den ersten tausend Zeilen abgeleitete Tabelle lehnt den kompletten Import ab, sobald dieses Statement kommt.

Strukturierte Log-Einträge sind verschachtelt — ein Request-Objekt mit Methode und Pfad, ein Context-Block mit einer Trace-ID —, eine relationale Tabelle kennt das nicht. Der Pfad wird mit Unterstrichen in den Spaltennamen gefaltet, aus request.method wird request_method, und jeder Blattwert bekommt seine eigene Spalte.

Ein oder zwei Ebenen ergeben eine Tabelle, die man gern anlegt — das deckt die meisten Logging-Bibliotheken ab. Ein Datensatz mit einer ganzen serialisierten Nutzlast erzeugt eine Spalte pro enthaltenem Feld, und dann lohnt sich die Frage, welcher Teilbaum tatsächlich abgefragt werden soll. Ihn vor der Umwandlung herauszuziehen ergibt eine schmalere Tabelle und ein deutlich kürzeres CREATE TABLE.

Werte werden als SQL-Literale geschrieben: Zahlen unverändert, boolesche Werte als TRUE und FALSE, null als NULL, Text einfach gequotet mit verdoppelten inneren Anführungszeichen. Diese Verdopplung ist die portable Form, die jede Engine versteht — eine Nachricht mit einem Apostroph lädt überall korrekt.

Backslashes werden dagegen unverändert durchgereicht, was dem SQL-Standard entspricht, aber nicht dazu passt, wie MySQL einen String standardmäßig liest, wo ein Backslash eine Escape-Sequenz einleitet. Logdaten enthalten davon ungewöhnlich viele — Windows-Pfade, reguläre Ausdrücke, escaptes JSON in einem Nachrichtenfeld. Wer in MySQL lädt, sollte NO_BACKSLASH_ESCAPES für die Sitzung setzen oder gleich in Postgres laden, und eine betroffene Zeile kontrollieren statt es anzunehmen.

Die Statements kommen ohne Transaktionsrahmen an. Wer sie so ausführt, lässt jedes Statement seine eigene Transaktion mit eigenem Commit und eigenem Roundtrip sein — die langsamste Art, eine halbe Million Zeilen einzufügen.

Zwei Änderungen verschieben die Rechnung deutlich: Die Datei in BEGIN und COMMIT einzurahmen ist eine Zeile an jedem Ende und meist der größte einzelne Gewinn. Indizes vor dem Laden zu entfernen und danach neu anzulegen ist der zweite und halbiert bei drei Indizes oft noch einmal die Zeit. Beides ist nicht spezifisch für diesen Konverter, sondern gilt für jeden Massenimport.

Ab einer bestimmten Größe sind Statements schlicht das falsche Werkzeug, wie auch immer man sie bündelt. Jede Engine hat einen Bulk-Pfad — COPY in Postgres, LOAD DATA in MySQL, ein Import im Client-Tool —, der eine Trennzeichendatei direkt liest und das Parsen von Statements ganz überspringt. Bei einer Million Zeilen ist der Unterschied Minuten gegen Stunden.

Dieselbe NDJSON-Datei nach CSV oder TSV zu wandeln und diesen Weg zu nehmen, ist ab etwa hunderttausend Zeilen die bessere Wahl. Statements behalten zwei Vorteile: Sie laufen überall dort, wo ein Client sich verbinden kann, auch bei einer verwalteten Datenbank ohne Dateizugriff auf dem Server, und sie lassen sich als Fixture in ein Repository einchecken und im Code-Review lesen.

Ist eine Zeile kein gültiges JSON, bricht die Umwandlung ab und die Meldung nennt die Zeilennummer. Es entsteht nichts Halbfertiges — genau das richtige Verhalten, denn eine unvollständige Statement-Datei, in eine Tabelle geladen, ist schlimmer als gar keine Datei.

Eine fehlerhafte Zeile in einem Log bedeutet meist einen abgebrochenen Schreibvorgang, nicht einen Tippfehler — ein mitten im Flush beendeter Prozess, eine an einer Grenze abgeschnittene rotierte Datei. Die Zeilennummer zu kennen erlaubt es, das Ende zu kappen, das Verworfene zu zählen und den Rest bewusst zu laden, statt eine Lücke in den Daten erst drei Abfragen später zu bemerken.

Die Statements entstehen durch JavaScript in diesem Browser-Tab. Die Datei wird nicht hochgeladen, es gibt kein Konto und keine Warteschlange, und die kostenlose Stufe erlaubt bis zu 100 MB — der Arbeitsspeicher ist aus dem oben genannten Grund die eigentliche Grenze.

Bei Event-Logs ist das mehr als eine Formalität: IP-Adressen, Session-Kennungen, Anfragepfade, gelegentlich ein Token in einer Query-String — das sind die sensibelsten gewöhnlichen Dateien, die man im Alltag anfasst, und der Anlass zur Umwandlung ist oft ein Vorfall, also der denkbar ungünstigste Moment, sie an einen Dritten zu schicken.