DuckDB per analisi ad hoc: trasformare migliaia di CSV in una dashboard

Amazon Seller Central genera dati a un ritmo che supera le capacità degli strumenti della maggior parte degli analisti. Report sul traffico, dati di conversione, riepiloghi sullo stato delle scorte, esportazioni dei risultati aziendali, analisi delle campagne pubblicitarie – tutti forniti come file CSV o TSV che, nel corso di mesi e anni, si accumulano in directory contenenti migliaia di file. La risposta standard è importare tutto in un database. In realtà, però, avviare, mantenere e interrogare un'istanza PostgreSQL per un carico analitico basato soprattutto su letture e con schema variabile comporta più oneri operativi di quanto il problema stesso giustifichi.

Abbiamo costruito una pipeline di reportistica per un venditore Amazon che opera in cinque marketplace europei. I dati grezzi: sei anni di esportazioni da Seller Central, 4.200 file, che coprono report aziendali, prestazioni pubblicitarie, istantanee delle scorte e registri delle commissioni FBA. L'approccio iniziale – uno schema PostgreSQL con script ETL – ha impiegato tre settimane per stabilizzarsi e richiedeva un'attenzione costante man mano che Amazon modificava i nomi delle colonne e le strutture dei report nelle versioni API. Le query erano veloci. La manutenzione no.

DuckDB cambia questa valutazione. È un database analitico integrato nell’applicazione – viene eseguito nello stesso processo del codice di analisi, non richiede server, daemon o stringhe di connessione, e legge direttamente dai file su disco. L'interfaccia di query è SQL standard. Le caratteristiche di prestazione sono quelle di un motore colonnare ottimizzato per l'aggregazione su larga scala. Per un carico di lavoro come questo, DuckDB non è solo adeguato – è lo strumento corretto.

Cos'è DuckDB in realtà

La distinzione più importante è l'archiviazione colonnare rispetto all'archiviazione per righe. PostgreSQL organizza i dati in righe: ogni campo per un determinato record è archiviato in modo contiguo su disco. Quando si esegue SELECT revenue, units_sold FROM orders WHERE marketplace = 'DE', una scansione sequenziale legge ogni campo di ogni riga visitata, scarta quelli non richiesti e restituisce i due richiesti. Gli indici, comprese le scansioni che accedono solo all’indice, possono limitare queste letture, ma raramente aiutano nelle aggregazioni estese alla maggior parte di una tabella. Questo è efficiente per i carichi di lavoro transazionali – inserimento, aggiornamento e recupero di singoli record. È generalmente poco adatto alle query analitiche che aggregano su milioni di righe toccando solo poche colonne.

DuckDB organizza i dati per colonna. Tutti i valori di revenue sono archiviati insieme; tutti i valori di units_sold sono archiviati insieme. La stessa query legge solo le tre colonne di cui ha bisogno (le due restituite e marketplace per il filtro), saltando il resto completamente. Su un dataset con quaranta colonne, questa non è un'ottimizzazione minore – può ridurre l'I/O di circa il 90 percento o più per le query che accedono solo a poche colonne. Grazie all’esecuzione vettorializzata – elaborazione dei dati in batch di 2.048 valori per impostazione predefinita usando istruzioni CPU SIMD piuttosto che una riga alla volta – DuckDB raggiunge prestazioni di query su carichi di lavoro analitici che possono superare ciò che un'istanza PostgreSQL ben ottimizzata offre su hardware identico, soprattutto con formati colonnari come Parquet.

L’architettura integrata è ugualmente importante. Non c'è nessuno scambio di rete, nessun costo aggiuntivo di autenticazione, nessun pool di connessioni da gestire. DuckDB apre un file o una directory, costruisce un piano di query ed esegue. Il binario occupa meno di 100 MB. Funziona su un laptop, all'interno di una funzione Lambda, o all'interno di uno script Python invocato da un cron job. Per le pipeline analitiche gestite sulla propria infrastruttura, il carico operativo è vicino a zero.

SQL direttamente sui file

La funzionalità che rende DuckDB pratico per i dati di Seller Central in modo specifico è la scansione di file tramite pattern glob. Una singola istruzione SQL interroga migliaia di CSV simultaneamente:

SELECT
  date_trunc('month', "Date") AS month,
  SUM("Units Ordered")          AS units,
  SUM("Ordered Product Sales")  AS revenue
FROM read_csv_auto('/data/seller-central/business-reports/*.csv')
GROUP BY 1
ORDER BY 1;

read_csv_auto deduce lo schema dal contenuto del file – campiona le righe, rileva i tipi di colonna, gestisce diversi delimitatori e, con union_by_name=true, unifica le colonne per nome tra file con set di colonne diversi (per impostazione predefinita usa lo schema del primo file e associa i file successivi per posizione). Non riconcilia autonomamente le colonne rinominate; per queste serve comunque un alias o un COALESCE nella query. Per i dati di Seller Central, dove Amazon ha rivisto i nomi delle colonne almeno quattro volte negli ultimi tre anni, questo è importante. La query viene eseguita su qualunque file esista nella directory al momento dell'esecuzione, senza passaggio di importazione, nessuna migrazione dello schema e nessuna cache obsoleta da invalidare.

Per le esportazioni TSV dalla console pubblicitaria, lo stesso approccio si applica con un’indicazione esplicita del formato:

FROM read_csv('/data/advertising/*.tsv', delim='	', header=true)

Non è una semplice comodità. È un modello mentale diverso per lavorare con i dati basati su file: il file system diventa il database. Lo schema viene dedotto, non dichiarato. Le query combinano dati di file diversi senza alcun passaggio di pre-elaborazione. Per l'analisi ad hoc, dove la domanda cambia più velocemente di quanto una pipeline ETL possa essere aggiornata, questa architettura è la scelta predefinita corretta.

Caratteristiche delle prestazioni

Sul nostro dataset di riferimento – 4.200 file, circa 2,8 GB non compressi – un'aggregazione completa dei ricavi raggruppata per marketplace, ASIN e mese si completa in 4,1 secondi su un laptop standard (Apple M2, 16 GB RAM). La query PostgreSQL equivalente, su dati importati e indicizzati, si completa in 2,8 secondi. Ma quel confronto esclude l'importazione iniziale di quaranta minuti, le migrazioni dello schema richieste ogni volta che Amazon cambia il nome di una colonna e il costo di manutenzione ETL continuo. In termini di costo operativo totale, DuckDB vince decisamente per questo carico di lavoro.

Se le prestazioni delle query diventano un collo di bottiglia – tipicamente su scala multi-terabyte – DuckDB legge Parquet nativamente, e convertire i file CSV esistenti in Parquet è un'operazione una tantum:

COPY (FROM read_csv_auto('/data/**/*.csv'))
TO '/data/consolidated.parquet'
(FORMAT PARQUET);

Le query successive sul file Parquet vengono eseguite da 8 a 12 volte più velocemente rispetto ai CSV originali. Parquet archivia statistiche sulle colonne – valori minimi e massimi per gruppo di righe – che DuckDB usa per ignorare intere sezioni di dati senza leggerle. Un filtro su marketplace = 'DE' su un file Parquet ordinato può leggere meno del dieci percento dei dati su disco. Questa applicazione anticipata dei filtri (predicate pushdown) non è disponibile sui file CSV a struttura piatta.

Costruire una dashboard autonoma con Chart.js

Le query DuckDB restituiscono dati. La domanda pratica è cosa farne. Per la reportistica interna nelle attività Amazon di piccole e medie dimensioni, un file HTML autonomo basato su Chart.js è spesso più utile di una dashboard ospitata su un server – nessun server da gestire, nessuna credenziale da gestire, nessuna istanza Superset o Metabase da mantenere.

La pipeline funziona in tre passaggi. Prima, DuckDB esporta i risultati delle query in JSON:

COPY (
  SELECT
    strftime(date_trunc('week', "Date"), '%Y-%m-%d') AS week,
    SUM("Units Ordered")         AS units,
    SUM("Ordered Product Sales") AS revenue
  FROM read_csv_auto('/data/**/*.csv')
  GROUP BY 1 ORDER BY 1
) TO 'weekly.json' (FORMAT JSON, ARRAY true);

Secondo, uno script Python legge quei file JSON e incorpora i dati direttamente in un template HTML che fa riferimento a Chart.js via CDN. Terzo, il file HTML si apre in un browser senza necessità di un server web.

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Seller Dashboard</title>
  <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</head>
<body>
  <canvas id="revenue"></canvas>
  <script>
    const rows = /* JSON_DATA_HERE */;
    new Chart(document.getElementById('revenue'), {
      type: 'line',
      data: {
        labels: rows.map(r => r.week),
        datasets: [{ label: 'Revenue', data: rows.map(r => r.revenue), tension: 0.3 }]
      }
    });
  </script>
</body>
</html>

Il risultato è un singolo file HTML (carica Chart.js da una CDN, quindi per visualizzarlo serve accesso a internet, a meno di incorporare direttamente la libreria nel file) che può essere condiviso via email, salvato in un bucket S3 privato, o aperto direttamente da una directory locale. Per i team operativi che hanno bisogno di reportistica settimanale senza il costo dell'infrastruttura della dashboard online, questo approccio è pragmatico e manutenibile. La dashboard si rigenera eseguendo uno script shell: DuckDB interroga i CSV, Python costruisce l'HTML, fatto. Nessuna infrastruttura server, nessun costo ricorrente di hosting e pochi elementi da mantenere: le query e lo script del template.

Quando DuckDB batte PostgreSQL

DuckDB non è un sostituto di PostgreSQL. Il confronto è significativo solo quando il carico di lavoro è effettivamente analitico – grandi scansioni, aggregazioni, group-by – e i dati vengono principalmente letti piuttosto che scritti. PostgreSQL vince decisamente per le scritture concorrenti, l’integrità referenziale, il controllo degli accessi a livello di riga e i requisiti operativi completi di un'applicazione di produzione. È la scelta corretta per un sistema multiutente dove i record vengono inseriti, aggiornati e interrogati dal codice dell'applicazione.

DuckDB vince quando:

I dati esistono già come file su disco, senza alcun passaggio di importazione pianificato
Lo schema non è completamente noto in anticipo, o cambia frequentemente
Le query sono ad hoc e i pattern di accesso non possono essere previsti in anticipo
Il costo operativo di un server database non è giustificato dalla longevità del progetto o dalla scala

I dati di Amazon Seller Central soddisfano tutti e quattro i criteri pienamente. Lo schema cambia a ogni release di Seller Central. I file esistono già localmente dopo l'esportazione. Le domande aziendali cambiano settimana per settimana a seconda di cosa si sta ottimizzando. E l'alternativa – una pipeline ETL PostgreSQL da mantenere – rappresenta settimane di lavoro tecnico per un problema che DuckDB risolve in un pomeriggio.

La regola pratica: se normalmente useresti Excel o un notebook Jupyter per analizzare questi dati, usa invece DuckDB. SQL è più espressivo, le prestazioni sono generalmente molto migliori per qualsiasi cosa oltre poche migliaia di righe, e l'architettura basata su file significa che i dati rimangono dove sono già.