Convertire XLSX in NDJSON

Un array JSON è un unico valore. Per leggere l’ultimo oggetto bisogna aver interpretato tutto ciò che viene prima, il che significa tenere l’intero documento in memoria o usare un parser in streaming che capisca strutture parziali. Per un file di configurazione è irrilevante. Per quattro milioni di righe è la differenza fra un’importazione che funziona e un processo che il sistema operativo termina.

Il JSON delimitato da nuove righe toglie il problema togliendo il contenitore. Ogni riga è un valore JSON completo e indipendente, quindi chi legge prende una riga, ne fa qualcosa, la scarta e va avanti con un consumo di memoria costante. Quella singola proprietà è il motivo per cui caricatori in blocco, spedizionieri di log e lavori di ingestione in data warehouse sono tutti convergiti sulla stessa forma. È anche perché un caricamento fallito è recuperabile: uno stream che fallisce all’ottocentomillesimo record può ripartire da quella riga, mentre un documento che non si interpreta non dà nulla e nessuna indicazione su quale record fosse colpevole.

La garanzia regge solo se nulla nei dati può produrre una nuova riga propria, e questa conversione la fa rispettare. Una cella contenente un’interruzione di riga — un indirizzo su più righe, un commento digitato con Alt+Invio — viene scritta con l’interruzione trasformata in un escape dentro la stringa JSON, così il record resta su una sola riga. Una tabulazione dentro una cella viene trattata allo stesso modo.

È il vantaggio concreto rispetto a un file delimitato. Un’esportazione separata da tabulazioni della stessa tabella deve avvolgere in virgolette una cella scomoda e sperare che chi legge implementi la convenzione; qui non c’è nulla da implementare. Dividere il file in blocchi da diecimila righe è sicuro, contare le righe dà il numero di record, e nessuna riga può essere corrotta da quella sopra. Il paragone è concreto: la stessa cella con dentro una tabulazione qui viene scritta come un escape dentro una stringa JSON tra virgolette, in un’esportazione separata da tabulazioni come un campo racchiuso in doppie virgolette che gli strumenti da riga di comando non capiscono. Il primo lo gestisce ogni parser JSON mai scritto, il secondo è un ticket di supporto.

Un file delimitato ha un solo tipo: testo. Chi lo legge deve indovinare, e indovinare è dove gli identificativi diventano interi e i numeri di versione diventano decimali. Il JSON porta la distinzione esplicitamente, quindi una quantità viene scritta come 12, un flag come true, un valore mancante come null, e un codice prodotto che nel foglio era testo resta tra virgolette con gli zeri iniziali intatti.

Per un caricamento in una destinazione tipizzata vale tempo reale. Lo schema dichiarato e i valori forniti concordano senza un livello di coercizione in mezzo, e un lavoro di ingestione che rifiuta una riga dice quale campo non andava invece di riportare un errore di parsing su un numero di riga. I tipi vengono dal foglio, non da una deduzione sul testo — un valore che Excel teneva come numero è scritto qui come numero perché lo era, non perché un parser abbia guardato i caratteri e deciso che gli somigliavano.

Ogni riga ripete ogni chiave. Cinquantamila righe di sei colonne significano cinquantamila copie dei sei nomi di campo, il che su un foglio reale è arrivato a circa 4,4 MB contro 1,8 MB per gli stessi dati come testo separato da tabulazioni — circa due volte e mezzo, partendo da un .xlsx di circa 4,3 MB.

Quello scambio è di solito corretto per questa destinazione, perché il file verrà letto una volta da una macchina invece di essere conservato per sempre. Se gli stessi record verranno interrogati ripetutamente, la forma giusta è colonnare invece che orientata a riga, e lo stesso foglio arriva a una piccola frazione della dimensione. NDJSON è un formato di trasporto, non di archiviazione. In transito la dimensione conta comunque poco, perché il file si comprime benissimo: le chiavi ripetute sono la parte più comprimibile, e un gzip verso un object store toglie quasi tutto il sovrappiù descritto qui.

Gli strumenti seguono dalla forma. `wc -l` dà il numero di righe esatto, perché c’è un record per riga e una nuova riga finale. `head -1` mostra i nomi dei campi come sono davvero stati scritti, non come li ricordate. `split -l 10000` produce blocchi ciascuno valido da solo.

Per qualcosa di più, jq legge un flusso di valori invece di un documento unico, quindi `jq -c "select(.qty > 100)"` filtra un file più grande della memoria disponibile ed emette lo stesso formato che ha consumato. Quella componibilità è il motivo per convertire in questa forma invece che in un array da smontare di nuovo. È anche il modo più economico di ispezionare un foglio che qualcuno vi ha mandato: un comando dice i nomi dei campi, un altro il numero di righe, un terzo mostra ogni valore distinto di una colonna — nessuno dei quali richiede aprire il foglio o fidarsi di quanto mostra la prima schermata.

Non tutte le API che dicono JSON delimitato da nuove righe intendono solo quello. Le richieste bulk di Elasticsearch e OpenSearch intercalano una riga di istruzione prima di ogni documento, quindi il corpo ha il doppio delle righe rispetto ai record, e questo output è la materia prima piuttosto che il payload. Generare le righe di istruzione è poche righe di script sul file.

I caricatori che accettano il formato così com’è esistono e sono il caso comune: un lavoro di caricamento in un data warehouse puntato su JSON delimitato da nuove righe, un produttore di coda che legge una riga per messaggio, la routine di importazione di un’applicazione. Controllate la documentazione della destinazione per sapere se vuole record o un corpo di richiesta, perché i due si somigliano abbastanza da far perdere un pomeriggio. La domanda che li distingue è se la descrizione del formato menziona una riga di azione o metadati. Se sì, vi stanno chiedendo un protocollo e questo file è la metà payload; se descrive solo i record, questo output entra così com’è.

Le date in un foglio sono conteggi di giorni travestiti da formato di visualizzazione, e la conversione scrive il valore invece dell’aspetto: 1 gennaio 2024 diventa 45292, e una data con ora diventa quel numero con una frazione per l’ora. Il conteggio parte dalla fine di dicembre 1899.

Questo è il modo di fallire da osservare proprio su questa coppia, perché i numeri JSON sono legittimi e un caricatore con una colonna numerica prenderà 45292 senza problemi e non vi dirà nulla. Convertite quei campi deliberatamente — in un passaggio jq sul file, o nella destinazione dopo il caricamento — e fatene parte della pipeline invece di qualcosa che si nota su una dashboard sei settimane dopo.

Una cella vuota viene scritta come null invece di essere omessa, così ogni oggetto porta le stesse chiavi e chi consuma i dati aspettandosi una forma fissa la ottiene. Dove righe successive introducono un campo che le prime non avevano, l’insieme di chiavi è l’unione su tutto il foglio, quindi nulla viene scartato per essere comparso tardi.

Viene convertito solo il primo foglio della cartella di lavoro, perché uno stream di record non ha modo di esprimere «e ora una tabella diversa». Se il foglio che serve non è il primo, riordinate la cartella e convertite di nuovo; se ne servono diversi, convertite più volte e caricateli come stream separati, che è comunque ciò che vorrà la destinazione.

Tutto avviene nel vostro browser. La cartella di lavoro viene interpretata e le righe scritte in locale, senza caricamento e senza coda — il che per un’esportazione di dati clienti, ordini o qualunque cosa sotto una politica di protezione dati è la differenza fra uno strumento usabile al lavoro e uno che non lo è.

Il tetto è 100 MB per cartella di lavoro e cento file in un solo trascinamento, uguale per tutti e non un livello da pagare per superare. Ben prima di quella cifra sentirete la memoria, perché il foglio viene letto in un array prima di essere scritto: qualche decina di megabyte non è nulla di che, e una cartella vicina al tetto è dove una scheda del browser inizia a faticare. Oltre quello lo strumento giusto è un lettore in streaming in uno script, ed è più utile dirlo che far fallire tre quarti di un file molto grande.

Se chi consuma è un programma che leggerà l’intero file e lo passerà a qualcos’altro — una fixture, uno script di seed, il corpo di una richiesta — un array JSON è la forma che si aspetta, e NDJSON aggiunge solo un passaggio di assemblaggio. In quel caso convertite in JSON.

Se i record verranno interrogati ripetutamente invece che caricati una volta, convertite in Parquet: stesse righe, una frazione dei byte, e una query che tocca due campi legge due colonne invece di ogni riga. NDJSON è la risposta giusta mentre i dati si muovono, ed è una scelta povera una volta arrivati.