NDJSON naar SQL omzetten

Elke niet-lege regel van de bron levert precies één INSERT-statement op, dus de uitvoer telt evenveel statements als het bestand records heeft. Er is geen kopregel om rekening mee te houden, en niets in de data kan een regeleinde veroorzaken, want een newline binnen een string is in JSON al geëscaped.

Meldt een lading vierhonderdduizend rijen ingevoegd en had het bestand vierhonderdduizend regels, dan was de lading compleet — geen reconciliatie nodig en geen twijfel over een halve rij die door een aanhalingsteken is verdwenen.

De kolomlijst moet in elk statement identiek zijn, en die wordt opgebouwd uit de vereniging van alle sleutels over elke regel — een veld dat alleen in de laatste regel voorkomt, wordt dus toch een kolom, en er kan pas geschreven worden nadat alles gelezen is.

Het gevolg is een geheugengrens in plaats van een groottelimiet: tientallen megabytes is geen probleem, enkele honderden is waar een browser begint te hijgen. Voorbij dat punt is het bestand splitsen de aangewezen weg — NDJSON splitst veilig op elke regelgrens.

Een logstroom met verzoeken, fouten en voltooide taken in één bestand heeft drie verschillende sleutelverzamelingen. De herleiding laat niets weg — elke sleutel wordt een kolom, een regel zonder die sleutel krijgt NULL — en de tabel die daaruit volgt is de vereniging van drie schema’s.

Voor een middagje analyse is dat vaak prima. Voor iets dat langer meegaat, is het filteren per gebeurtenistype en drie tabellen laden meestal beter: dat levert een schema op dat zinvol te indexeren is.

De uitvoer bevat bewust geen DDL. JSON vertelt of een waarde een getal is, niet of de kolom een integer of een numeriek veld met twee decimalen moet worden, of hij nullable is, of hoe lang de tekst mag zijn.

Leid de kolommen af van een omzetting van het hele bestand, nooit van een steekproef. Een zeldzaam veld dat in één regel van een half miljoen voorkomt, wordt toch een kolom in elk statement, en een tabel gedefinieerd op de eerste duizend records wijst dan de hele lading af op precies dat statement.

Een geneste request-of context-blok wordt met onderstrepingstekens in de kolomnaam gevouwen: request.method wordt request_method. Eén of twee niveaus levert een tabel op die prima te declareren is, wat de meeste logbibliotheken dekken.

Een record met een volledig geserialiseerde payload levert een kolom per veld daarbinnen op, en dan is de nuttige vraag welke subboom je eigenlijk wilt bevragen. Die eruit halen vóór het omzetten geeft een smallere tabel.

Waarden worden geschreven als SQL-literals: getallen los, booleans als TRUE en FALSE, null als NULL, en tekst tussen enkele aanhalingstekens met interne aanhalingstekens verdubbeld — de draagbare vorm die elke engine leest.

Backslashes worden ongewijzigd doorgeschreven, wat correct is volgens de SQL-standaard maar niet hoe MySQL met standaardinstellingen een string leest, waar een backslash een escape start. Logdata staat er ongewoon vol mee — Windows-paden, reguliere expressies. Zet NO_BACKSLASH_ESCAPES voor de sessie, of laad in Postgres.

De statements komen zonder transactie eromheen. Zo uitvoeren maakt elk statement zijn eigen transactie met eigen commit en eigen rondgang — de traagste manier om een half miljoen rijen in te voegen.

Het bestand in BEGIN en COMMIT wrappen is één regel aan elk uiteinde en meestal de grootste enkele verbetering. Indexen tijdelijk verwijderen en na de lading opnieuw opbouwen is de tweede, en op een tabel met drie indexen halveert dat de tijd vaak nogmaals.

Voorbij een bepaalde omvang is een reeks statements het verkeerde instrument. Elke engine heeft een bulkroute — COPY in Postgres, LOAD DATA in MySQL — die een gescheiden bestand rechtstreeks leest en de statement-parsing overslaat.

Dezelfde NDJSON omzetten naar CSV of TSV en die route gebruiken is de betere keus boven ruwweg honderdduizend rijen. Statements houden wel twee voordelen: ze werken overal waar een client kan verbinden, en ze zijn te reviewen als fixture in een repository.

Is een regel geen geldige JSON, dan mislukt de omzetting en de melding noemt het regelnummer. Er komt niets gedeeltelijks uit, en dat is precies het gedrag dat je hier wilt.

Een misvormde regel in een log betekent meestal een afgebroken schrijfactie, geen tikfout. Het regelnummer kennen betekent dat de staart afgeknipt kan worden en de rest bewust geladen, in plaats van een gat drie query’s later te ontdekken.

De statements worden gegenereerd door JavaScript in dit browsertabblad. Het bestand wordt niet geüpload, er is geen account en geen wachtrij, en de gratis laag accepteert tot 100 MB, met geheugen als de praktische grens.

Dat is voor dit paar geen bijzaak. Een logbestand is doorgaans het gevoeligste gewone bestand dat een ontwikkelaar tegenkomt — IP-adressen, sessie-ID’s, aanvraagpaden — en de reden om het om te zetten is meestal een incident, het slechtste moment om het naar een derde partij te sturen.