Importazione ed esportazione JSONL nei database: PostgreSQL, MongoDB e altri
Una guida completa per importare file JSONL nei database ed esportare i record in formato JSONL. Troverai i comandi COPY di PostgreSQL, gli strumenti da riga di comando di MongoDB, JSON_TABLE di MySQL, gli script per SQLite e le tecniche di importazione in blocco da usare in produzione.
Perché usare JSONL per importare ed esportare dati dai database?
JSONL (JSON Lines) è diventato uno dei formati di riferimento per trasferire dati tra database e sistemi esterni. Ogni riga è un oggetto JSON indipendente e completo: puoi quindi leggere i record in streaming, uno alla volta, senza caricare l’intero dataset in memoria. È una caratteristica fondamentale quando migri milioni di righe tra istanze PostgreSQL, sincronizzi collezioni MongoDB con un data warehouse o usi i dati esportati da un database come input per pipeline di machine learning.
Rispetto a CSV, JSONL mantiene i tipi di dati, supporta oggetti e array nidificati ed elimina le ambiguità legate all’escape dei delimitatori. Un file JSONL può contenere una colonna jsonb di PostgreSQL così com’è, mentre con CSV dovresti appiattire la struttura nidificata o applicarvi l’escape. A differenza di un unico array JSON, JSONL si presta all’elaborazione in streaming: puoi elaborare, validare o trasformare ogni record appena viene letto, senza aspettare che il parsing dell’intero file sia completato.
Tutti i principali database dispongono ormai di strumenti per JSONL. PostgreSQL può caricare le righe con COPY tramite un cast a jsonb; MongoDB include mongoimport e mongoexport, che usano JSONL come formato predefinito; MySQL 8.0 ha introdotto JSON_TABLE per estrarre dati strutturati; SQLite dispone dell’estensione JSON1. Nelle prossime sezioni troverai i comandi da eseguire, gli script e le best practice per ciascun database.
PostgreSQL: importare ed esportare dati JSONL
PostgreSQL offre diversi modi per lavorare con JSONL. Il comando COPY è la soluzione più veloce per l’importazione in blocco, mentre il tipo jsonb consente di memorizzare e interrogare dati semistrutturati in modo nativo. Se usi tabelle strutturate, puoi estrarre i campi JSONL e inserirli in colonne tipizzate durante l’importazione.
Il modo più veloce per importare JSONL in PostgreSQL è caricare ogni riga come valore jsonb con il comando COPY. Crea una tabella con una sola colonna jsonb, poi usa COPY FROM per caricare il file in streaming direttamente nel database.
-- Create a staging table with a single jsonb column
CREATE TABLE staging_import (data jsonb);
-- Import the JSONL file (each line becomes one row)
\COPY staging_import (data) FROM 'users.jsonl';
-- Verify the import
SELECT count(*) FROM staging_import;
SELECT data->>'name' AS name, data->>'email' AS email
FROM staging_import
LIMIT 5;
-- Move data into a structured table
INSERT INTO users (name, email, age)
SELECT
data->>'name',
data->>'email',
(data->>'age')::int
FROM staging_import;Per esportare i dati in formato JSONL, usa row_to_json() o to_jsonb() per convertire ogni riga in un oggetto JSON, poi salva il risultato in un file con COPY. A ogni riga della tabella corrisponde una riga nel file di output.
-- Export entire table as JSONL
\COPY (
SELECT row_to_json(t)
FROM (SELECT id, name, email, created_at FROM users) t
) TO 'users_export.jsonl';
-- Export with filtering and transformation
\COPY (
SELECT row_to_json(t)
FROM (
SELECT id, name, email,
created_at::text AS created_at
FROM users
WHERE active = true
ORDER BY id
) t
) TO 'active_users.jsonl';
-- Verify: each line is valid JSON
-- {"id":1,"name":"Alice","email":"[email protected]",...}Per i carichi di lavoro in produzione, usa uno script Python con psycopg2 per leggere un file JSONL e inserire i record in colonne tipizzate. In questo modo hai il pieno controllo sulla validazione, sulla gestione degli errori e sulle dimensioni dei batch.
import json
import psycopg2
from psycopg2.extras import execute_values
def import_jsonl_to_postgres(file_path, conn_string):
conn = psycopg2.connect(conn_string)
cur = conn.cursor()
batch = []
batch_size = 1000
total = 0
with open(file_path, 'r') as f:
for line_num, line in enumerate(f, 1):
line = line.strip()
if not line:
continue
try:
record = json.loads(line)
batch.append((
record['name'],
record['email'],
record.get('age'),
))
except (json.JSONDecodeError, KeyError) as e:
print(f"Skipping line {line_num}: {e}")
continue
if len(batch) >= batch_size:
execute_values(
cur,
"INSERT INTO users (name, email, age) "
"VALUES %s ON CONFLICT (email) DO NOTHING",
batch
)
total += len(batch)
batch = []
print(f"Imported {total} records...")
if batch:
execute_values(
cur,
"INSERT INTO users (name, email, age) "
"VALUES %s ON CONFLICT (email) DO NOTHING",
batch
)
total += len(batch)
conn.commit()
cur.close()
conn.close()
print(f"Done. Imported {total} records.")
# Usage
import_jsonl_to_postgres('users.jsonl', 'postgresql://localhost/mydb')MongoDB: supporto nativo per JSONL
MongoDB offre un supporto completo per JSONL attraverso i Database Tools da riga di comando. Le utility mongoimport e mongoexport usano JSONL come formato predefinito: questo rende MongoDB uno dei database più semplici da usare per scambiare dati in questo formato. Ogni riga di un file JSONL corrisponde direttamente a un documento MongoDB.
mongoimport legge un file JSONL e inserisce ogni riga come documento nella collezione specificata. Supporta la modalità upsert, la conversione automatica dei tipi dei campi e l’inserimento in parallelo per ottenere un throughput elevato.
# Basic import: each line becomes a document
mongoimport \
--uri="mongodb://localhost:27017/mydb" \
--collection=users \
--file=users.jsonl
# Upsert mode: update existing documents by _id
mongoimport \
--uri="mongodb://localhost:27017/mydb" \
--collection=users \
--file=users.jsonl \
--mode=upsert \
--upsertFields=email
# Drop collection before import (clean slate)
mongoimport \
--uri="mongodb://localhost:27017/mydb" \
--collection=users \
--file=users.jsonl \
--drop
# Import with parallel workers for large files
mongoimport \
--uri="mongodb://localhost:27017/mydb" \
--collection=users \
--file=large_users.jsonl \
--numInsertionWorkers=4mongoexport scrive ogni documento di una collezione su una singola riga JSON. Puoi filtrare i documenti, selezionare i campi da includere e ordinare l’output. Il risultato è un file JSONL valido, pronto per essere elaborato da qualsiasi sistema a valle.
# Export entire collection as JSONL (default format)
mongoexport \
--uri="mongodb://localhost:27017/mydb" \
--collection=users \
--out=users_export.jsonl
# Export with query filter
mongoexport \
--uri="mongodb://localhost:27017/mydb" \
--collection=users \
--query='{"active": true}' \
--out=active_users.jsonl
# Export specific fields only
mongoexport \
--uri="mongodb://localhost:27017/mydb" \
--collection=users \
--fields=name,email,created_at \
--out=users_partial.jsonl
# Export with sorting
mongoexport \
--uri="mongodb://localhost:27017/mydb" \
--collection=users \
--sort='{"created_at": -1}' \
--out=users_sorted.jsonlMySQL: lavorare con JSONL tramite JSON_TABLE e script
A differenza di MongoDB, MySQL non dispone di un comando integrato per importare JSONL. MySQL 8.0 ha però introdotto JSON_TABLE, che permette di estrarre dati strutturati da stringhe JSON. Per importare JSONL in blocco, la soluzione più affidabile è combinare LOAD DATA INFILE per il caricamento dei dati grezzi con JSON_TABLE per l’estrazione, oppure ricorrere a un linguaggio di scripting come Python.
Carica il file JSONL come testo grezzo in una tabella di staging, poi usa JSON_TABLE per estrarre i campi e inserirli in una tabella strutturata. Questi due passaggi si eseguono interamente in MySQL.
-- Step 1: Create a staging table for raw lines
CREATE TABLE jsonl_staging (
id INT AUTO_INCREMENT PRIMARY KEY,
raw_line TEXT NOT NULL
);
-- Step 2: Load the JSONL file as raw text
LOAD DATA INFILE '/var/lib/mysql-files/users.jsonl'
INTO TABLE jsonl_staging
LINES TERMINATED BY '\n'
(raw_line);
-- Step 3: Extract structured data with JSON_TABLE
INSERT INTO users (name, email, age)
SELECT jt.name, jt.email, jt.age
FROM jsonl_staging,
JSON_TABLE(
raw_line, '$'
COLUMNS (
name VARCHAR(255) PATH '$.name',
email VARCHAR(255) PATH '$.email',
age INT PATH '$.age'
)
) AS jt;
-- Step 4: Clean up staging table
DROP TABLE jsonl_staging;
-- Verify the import
SELECT * FROM users LIMIT 5;Se ti serve più flessibilità, usa uno script Python con mysql-connector-python per leggere un file JSONL e inserire i record in batch. Questo approccio gestisce in modo efficiente anche i file di grandi dimensioni e fornisce segnalazioni dettagliate degli errori.
import json
import mysql.connector
def import_jsonl_to_mysql(file_path, config):
conn = mysql.connector.connect(**config)
cursor = conn.cursor()
batch = []
batch_size = 1000
total = 0
insert_sql = (
"INSERT INTO users (name, email, age) "
"VALUES (%s, %s, %s) "
"ON DUPLICATE KEY UPDATE name=VALUES(name)"
)
with open(file_path, 'r') as f:
for line_num, line in enumerate(f, 1):
line = line.strip()
if not line:
continue
try:
record = json.loads(line)
batch.append((
record['name'],
record['email'],
record.get('age'),
))
except (json.JSONDecodeError, KeyError) as e:
print(f"Skipping line {line_num}: {e}")
continue
if len(batch) >= batch_size:
cursor.executemany(insert_sql, batch)
conn.commit()
total += len(batch)
batch = []
print(f"Imported {total} records...")
if batch:
cursor.executemany(insert_sql, batch)
conn.commit()
total += len(batch)
cursor.close()
conn.close()
print(f"Done. Imported {total} records.")
# Usage
import_jsonl_to_mysql('users.jsonl', {
'host': 'localhost',
'user': 'root',
'password': 'secret',
'database': 'mydb'
})SQLite: una soluzione leggera per importare JSONL
SQLite è un’ottima scelta per lo sviluppo locale e per dataset di dimensioni contenute. Non dispone di un comando nativo per importare JSONL, ma l’estensione JSON1 mette a disposizione json_extract() per lavorare con i dati JSON. Un semplice script Python può importare i file JSONL in modo efficiente sfruttando il supporto integrato di SQLite per le transazioni.
Usa il modulo sqlite3 incluso in Python per leggere un file JSONL e inserire i record in un database SQLite. Racchiudere tutti gli inserimenti in un’unica transazione migliora notevolmente le prestazioni con i file di grandi dimensioni.
import json
import sqlite3
def import_jsonl_to_sqlite(jsonl_path, db_path):
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
# Create table if it doesn't exist
cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE,
age INTEGER,
raw_json TEXT
)
""")
total = 0
errors = 0
with open(jsonl_path, 'r') as f:
# Use a transaction for bulk insert performance
conn.execute('BEGIN TRANSACTION')
for line_num, line in enumerate(f, 1):
line = line.strip()
if not line:
continue
try:
record = json.loads(line)
cursor.execute(
"INSERT OR IGNORE INTO users "
"(name, email, age, raw_json) "
"VALUES (?, ?, ?, ?)",
(
record['name'],
record.get('email'),
record.get('age'),
line, # Store original JSON
)
)
total += 1
except (json.JSONDecodeError, KeyError) as e:
errors += 1
print(f"Line {line_num}: {e}")
conn.commit()
conn.close()
print(f"Imported {total} records, {errors} errors")
# Usage
import_jsonl_to_sqlite('users.jsonl', 'app.db')
# Query with json_extract (SQLite JSON1 extension)
# SELECT json_extract(raw_json, '$.name') FROM users;Best practice per l’importazione in blocco
Quando importi file JSONL di grandi dimensioni in un database, devi prestare particolare attenzione alle transazioni, alla gestione degli errori e all’uso delle risorse. Queste best practice valgono per tutti i database e ti aiutano a creare pipeline di importazione affidabili, pronte per la produzione.
Raggruppare gli inserimenti in transazioni
PrestazioniNon usare mai una transazione per ogni singola riga. Raggruppa gli inserimenti in batch di 500-5000 righe per transazione: l’I/O su disco e l’overhead dei lock si riducono di un fattore di 10-100x. Nella maggior parte dei database, i benefici maggiori si ottengono con batch di 1000 righe. Aumentare ulteriormente le dimensioni offre vantaggi via via minori e rende più costoso il rollback in caso di errore.
Gestire gli errori riga per riga
AffidabilitàEsegui il parsing e la validazione di ogni riga JSONL separatamente. Per ogni riga che non supera la validazione o non viene inserita, registra il numero di riga e il messaggio di errore. Usa ON CONFLICT / ON DUPLICATE KEY per gestire le violazioni dei vincoli di unicità senza interrompere l’intera importazione. Salva le righe che hanno generato errori in un file separato, così da poterle esaminare in seguito.
Monitorare l’avanzamento
OsservabilitàPer i file con milioni di righe, registra l’avanzamento ogni N record (ad esempio, ogni 10.000). Tieni traccia del totale delle righe lette, degli inserimenti riusciti, dei duplicati ignorati e degli errori. Calcola e mostra la velocità di importazione (record/secondo) per individuare i colli di bottiglia. Queste informazioni sono essenziali per monitorare le importazioni di lunga durata.
Questo pattern generico combina batch, gestione degli errori e monitoraggio dell'avanzamento. Adattalo a qualsiasi database sostituendo la funzione di inserimento.
import json
import time
def batch_import_jsonl(file_path, insert_fn, batch_size=1000):
"""Generic JSONL batch importer with error handling."""
batch = []
stats = {
'total_lines': 0,
'imported': 0,
'skipped': 0,
'errors': 0
}
start_time = time.time()
error_file = open(file_path + '.errors', 'w')
with open(file_path, 'r') as f:
for line_num, line in enumerate(f, 1):
stats['total_lines'] = line_num
line = line.strip()
if not line:
stats['skipped'] += 1
continue
try:
record = json.loads(line)
batch.append(record)
except json.JSONDecodeError as e:
stats['errors'] += 1
error_file.write(f"{line_num}\t{e}\t{line}\n")
continue
if len(batch) >= batch_size:
inserted = insert_fn(batch)
stats['imported'] += inserted
batch = []
if line_num % 10000 == 0:
elapsed = time.time() - start_time
rate = stats['imported'] / elapsed
print(
f"Progress: {line_num:,} lines | "
f"{stats['imported']:,} imported | "
f"{rate:,.0f} records/sec"
)
# Flush remaining batch
if batch:
inserted = insert_fn(batch)
stats['imported'] += inserted
error_file.close()
elapsed = time.time() - start_time
print(f"\nComplete in {elapsed:.1f}s")
print(f" Lines: {stats['total_lines']:,}")
print(f" Imported: {stats['imported']:,}")
print(f" Errors: {stats['errors']:,}")
return statsPrepara i dati con gli strumenti JSONL gratuiti
Prima di importare i dati JSONL nel database, verifica che il formato del file sia valido e converti i dati da un formato all’altro. I nostri strumenti online gratuiti elaborano tutto localmente nel browser: i tuoi dati non lasciano mai il tuo computer.
Esporta i dati JSONL in fogli di calcolo CSV. Ideale per le analisi in Excel o Google Sheets.
Valida i file JSONL riga per riga. Individua subito errori di sintassi e problemi di codifica o di formato.
Scopri come validare file JSONL usando JSON Schema. Copre Python, Node.js, strumenti CLI e automazione CI/CD.
Strategie per elaborare gigabyte di dati JSONL: lettura in streaming, gestione della memoria, suddivisione dei file, compressione e strumenti online.
Impara a usare JSONL come formato di scambio nelle pipeline ETL. La guida tratta lo streaming con Apache Kafka, i DAG di Airflow, il caricamento in Snowflake e BigQuery e la progettazione di pipeline tolleranti ai guasti.