Convertire NDJSON in SQL

La mappatura è esattamente diretta come sembra. Ogni riga non vuota della sorgente produce un’istruzione INSERT, quindi l’output ha tante istruzioni quante ne ha record l’input. Nulla nei dati può produrre un ritorno a capo, perché un a capo dentro una stringa è codificato nel JSON.

Quella prevedibilità è la ragione per cui questa coppia è piacevole da usare. Se un caricamento riporta 412.000 righe inserite e il file ne aveva 412.000, il caricamento era completo — nessuna riconciliazione, nessuna domanda su righe parziali.

Questo è l’unico punto dove un formato sorgente in streaming non compra una conversione in streaming. L’elenco delle colonne deve essere identico in ogni istruzione, ed è costruito dall’unione delle chiavi su tutte le righe — quindi un campo che appare solo nell’ultimo record diventa comunque una colonna.

La conseguenza è un tetto di memoria piuttosto che una fascia di dimensione: l’intero file esiste nella scheda mentre viene convertito. Decine di megabyte sono normali, diverse centinaia è dove un browser comincia a faticare, e oltre, la risposta è dividere il file — NDJSON si divide in sicurezza a qualunque confine di riga.

Un flusso di log che emette richieste, errori e completamenti di lavoro in un file ha tre insiemi di chiavi diversi al suo interno. La riconciliazione gestisce questo senza scartare nulla — ogni chiave diventa una colonna, e una riga senza di essa fornisce NULL.

Per un pomeriggio di analisi è spesso accettabile. Per qualcosa che vivrà più a lungo, filtrare il file per tipo di evento e caricare tre tabelle produce uno schema che potete indicizzare sensatamente.

L’output non ha DDL, deliberatamente. Il JSON vi dice che un valore è un numero e non se la colonna è un intero o un numeric con due decimali, se è nullable, qual è la chiave primaria.

Un avvertimento che qui conta più che su file più piccoli: derivate le colonne da una conversione dell’intero file, mai da un campione. Un campo raro che appare in una riga su mezzo milione diventa comunque una colonna in ogni istruzione, e una tabella dichiarata dai primi mille record rifiuterà l’intero caricamento sull’istruzione che lo usa.

I record di log strutturati annidano — un oggetto request con un metodo e un percorso — e una tabella relazionale no. Il percorso viene piegato nel nome della colonna con underscore, quindi request.method diventa request_method.

Uno o due livelli producono una tabella che sareste felici di dichiarare. Un record che porta un intero payload serializzato produce una colonna per ogni campo al suo interno, e a quel punto la domanda utile è quale sottoalbero intendiate davvero interrogare.

I valori sono scritti come letterali SQL: numeri nudi, booleani come TRUE e FALSE, null come NULL, e testo tra virgolette singole con le virgolette interne raddoppiate. Il raddoppio è la forma portabile e ogni motore la legge.

I backslash sono scritti invariati, corretto secondo lo standard SQL e non come MySQL legge una stringa con le impostazioni predefinite, dove un backslash inizia una sequenza di escape. I dati di log ne sono insolitamente pieni — percorsi Windows, espressioni regolari, JSON con escape dentro un campo messaggio.

Le istruzioni arrivano una per riga senza transazione attorno. Eseguito così com’è, ogni istruzione è la propria transazione con il proprio commit e il proprio round trip, il modo più lento possibile per inserire mezzo milione di righe.

Due modifiche cambiano l’aritmetica. Avvolgere il file in BEGIN e COMMIT è di solito il singolo miglioramento più grande disponibile. Togliere gli indici prima del caricamento e ricrearli dopo è il secondo, e su una tabella con tre indici spesso dimezza di nuovo il tempo.

C’è una dimensione oltre la quale le istruzioni sono lo strumento sbagliato a prescindere da come vengono raggruppate. Ogni motore ha un percorso in blocco — COPY in Postgres, LOAD DATA in MySQL — che legge un file delimitato direttamente e salta del tutto l’analisi delle istruzioni.

Convertire lo stesso NDJSON in CSV o TSV e usare quel percorso è il piano migliore oltre circa centomila righe. Le istruzioni mantengono due vantaggi: girano ovunque un client possa connettersi, e possono essere revisionate e messe in un repository come fixture.

Se una riga non è JSON valido, la conversione fallisce e il messaggio nomina la riga. Nulla di parziale viene prodotto, il comportamento che volete qui: un file di istruzioni scritto a metà caricato in una tabella è considerevolmente peggio di nessun file.

Una riga malformata in un log di solito significa una scrittura troncata piuttosto che un errore di battitura. Sapere quale riga significa che potete tagliare la coda, contare cosa state scartando e caricare il resto deliberatamente.

Le istruzioni sono generate da JavaScript in questa scheda del browser. Il file non viene caricato, non c’è account né coda, e il livello gratuito accetta fino a 100 MB, con la memoria come vincolo pratico.

Quella proprietà fa un lavoro reale per questa coppia. I log di eventi sono il file ordinario più sensibile che la maggior parte degli ingegneri maneggia, e la ragione per convertirne uno è di solito un incidente, il peggior momento possibile per inviarlo a terzi.