Lo stack di analisi e SQL per le tabelle blockchain

Quasi ogni numero presente in un report on-chain è passato attraverso una pipeline tra il nodo che ha osservato il blocco e la query che ha prodotto il dato. Un investigatore che sa a quale livello appartiene una tabella, chi l'ha costruita, quanto è aggiornata e quali regole l'hanno plasmata può distinguere un riscontro reale da un artefatto della pipeline. SQL è il linguaggio comune al vertice di questo stack: dataset pubblici, piattaforme ospitate e data warehouse interni lo utilizzano tutti. Un numero limitato di clausole copre la maggior parte delle domande, ma le tabelle blockchain presentano insidie specifiche, dagli interi con 18 decimali alle transazioni non riuscite che rimangono comunque nei dati. Questa lezione illustra lo stack, definisce lo schema generico utilizzato nel resto di questo modulo e sviluppa le prime query esemplificative.

Dal nodo alla tabella

Un nodo fornisce blocchi, transazioni, ricevute e stato tramite chiamate RPC (Lezione 2.1). Gli analisti hanno bisogno di righe e colonne. Il processo che trasforma gli uni nelle altre è una pipeline ETL: estrazione, trasformazione, caricamento.

• Estrazione. Interrogare il nodo per ogni blocco di un intervallo: il blocco con le relative transazioni, le ricevute con i relativi log e, ove supportate, le tracce delle chiamate.
• Trasformazione. Appiattire il JSON annidato in una riga per record, convertire le stringhe esadecimali in numeri, copiare il timestamp del blocco su ogni transazione e log affinché le query non richiedano un join per il tempo e normalizzare gli identificatori (ad esempio, gli indirizzi in minuscolo).
• Caricamento. Scrivere le righe nello storage, solitamente in file colonnari Apache Parquet partizionati per data o intervallo di blocchi, oppure in tabelle di database. Formati aperti per tabelle come Apache Iceberg e Delta Lake aggiungono snapshot con versionamento a questo livello.

Il concetto chiave è la granularità: ciò che rappresenta una singola riga. Un blocco Ethereum con 150 transazioni e 400 log di eventi diventa una riga in blocks, 150 in transactions e 400 in logs. La maggior parte degli errori nelle query deriva dal dimenticare la granularità di una tabella e dall'effettuare conteggi o somme al livello sbagliato.

Le pipeline vengono eseguite come caricamento storico una tantum oppure come caricamenti in streaming (incrementali) che seguono la testa della catena. In prossimità della testa, i blocchi possono essere sostituiti da una riorganizzazione; pertanto, una pipeline attende una determinata profondità di conferma o un blocco finalizzato, oppure riscrive i blocchi recenti quando la catena cambia. Tra gli estrattori open source figurano Ethereum ETL e Bitcoin ETL del progetto blockchain-etl e cryo, che scrive i dati EVM direttamente in Parquet. È utile conoscerli anche se non li si esegue mai: molti dataset pubblici sono stati costruiti con questi strumenti e ne ereditano i nomi delle colonne.

Tabelle grezze, decodificate e curate

Ogni passaggio al livello superiore abbrevia le query e aggiunge decisioni prese da altri. Una tabella curata token_transfers include i trasferimenti ERC-721 o soltanto i token fungibili? Le emissioni e le distruzioni sono righe con l'indirizzo zero oppure vengono escluse? L'importo è grezzo o è già stato diviso in base al numero di decimali? Due dataset che chiamano entrambi una tabella token_transfers possono differire su tutti e tre gli aspetti. L'abitudine che tutela da questi rischi è la riconciliazione: verificare la tabella curata rispetto al livello grezzo su un piccolo intervallo di blocchi.

-- La tabella curata token_transfers corrisponde ai log grezzi da cui deriva?
SELECT 'raw logs' AS layer, COUNT(*) AS n
FROM logs
WHERE topic0 = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
  AND topic3 IS NULL              -- solo trasferimenti fungibili (stile ERC-20)
  AND block_number BETWEEN 21000000 AND 21000099
UNION ALL
SELECT 'token_transfers', COUNT(*)
FROM token_transfers
WHERE block_number BETWEEN 21000000 AND 21000099;

La prima metà conta gli eventi Transfer grezzi (la stringa esadecimale è l'hash della firma dell'evento, illustrato nella Lezione 2.9); la seconda conta le righe curate per gli stessi 100 blocchi. Se i conteggi differiscono, occorre comprenderne il motivo prima di utilizzare la tabella: potrebbe escludere alcuni token, includere NFT o presentare blocchi mancanti.

Uno schema generico per le tabelle blockchain

I dataset assegnano nomi diversi a tabelle e colonne, quindi questo modulo utilizza uno schema generico neutrale modellato sugli strumenti ETL open source. L'adattamento a un dataset reale consiste solitamente nel rinominare gli elementi.

Convenzioni: gli indirizzi e gli hash sono stringhe esadecimali in minuscolo con il prefisso 0x; gli importi (value, amount_raw, prezzi del gas) sono interi grezzi in unità di base memorizzati come DECIMAL(38,0); i timestamp sono in UTC; status vale 1 per il successo e 0 per il fallimento; gas_used ed effective_gas_price in transactions provengono dalla ricevuta; block_hash consente alle query di collegare le transazioni a blocks tramite hash anziché tramite altezza, escludendo così le righe residue dei blocchi sostituiti in una riorganizzazione (Lezione 2.1). labels.grade contiene il grado A–D del collegamento tra l'indirizzo e l'entità, con A come grado più forte (Lezioni 3.2 e 4.5); pertanto, ordinando per grade in ordine crescente, le etichette più forti vengono presentate per prime. Gli esempi interrogano una catena alla volta; le tabelle che contengono più catene aggiungono una colonna chain_id a ogni riga e a ogni chiave e join (Lezione 2.6). Le tabelle UTXO inputs e outputs vengono introdotte nella Lezione 2.9. I dataset reali differiscono in dettagli che occorre verificare: il dataset Ethereum di BigQuery aggiunge un prefisso ai campi delle ricevute (receipt_status, receipt_gas_used), chiama address il contratto del log e memorizza i topic come array; inoltre, alcuni dataset memorizzano gli interi di grandi dimensioni come stringhe.

SELECT, WHERE e ORDER BY

La prima domanda nella maggior parte dei casi è semplice: che cosa è arrivato a questo indirizzo?

-- Trasferimenti ETH riusciti inviati a un indirizzo in un intervallo di blocchi
SELECT block_number,
       block_timestamp,
       hash,
       from_address,
       value / 1e18 AS value_eth                -- da wei a ETH, per la visualizzazione
FROM transactions
WHERE to_address = '0x5e1f…c0de'               -- in minuscolo, come memorizzato
  AND block_number BETWEEN 21000000 AND 21050000
  AND status = 1                                -- escludere le transazioni non riuscite
  AND value > 0
ORDER BY block_number, transaction_index;

Ogni filtro è importante:

• Maiuscole e minuscole. Gli indirizzi vengono confrontati come stringhe e gli indirizzi con checksum mescolano maiuscole e minuscole: normalizzare il valore letterale con LOWER() oppure digitarlo in minuscolo.
• Ambito. Un intervallo di blocchi limita la scansione e rende la query riproducibile: lo stesso intervallo restituisce le stesse righe l'anno successivo.
• Stato. Le transazioni non riuscite vengono registrate e hanno pagato commissioni, ma il relativo trasferimento di valore è stato annullato. Dimenticare status = 1 è il modo più comune di sovrastimare i flussi in entrata.
• Valore. Molte transazioni verso un indirizzo trasferiscono zero ETH perché sono chiamate a contratti.

Prestare attenzione ai NULL. Una transazione di creazione di un contratto non ha destinatario, quindi il suo to_address è NULL, e i confronti con NULL non sono né veri né falsi: to_address <> '0x5e1f…c0de' esclude silenziosamente tutte le creazioni di contratti. Scrivere to_address IS NULL OR to_address <> … quando si intende "tutto il resto". Analogamente, COUNT(to_address) ignora i NULL, mentre COUNT(*) non li ignora.

Ordinare per block_number, transaction_index per ottenere l'ordine nella catena; tutte le transazioni di un blocco condividono lo stesso timestamp. Questa query rileva soltanto i trasferimenti ETH di primo livello; gli ETH inviati dai contratti compaiono nelle tracce (Lezione 2.9).

Unità e tipi numerici

Un intero con segno a 64 bit si ferma a circa 9,22 × 10^18, ossia soltanto 9,22 ETH in wei. Memorizzare e sommare gli importi come DECIMAL(38,0) o NUMERIC, sommare prima gli interi grezzi e dividere una sola volta alla fine. DECIMAL(38,0) contiene valori fino a circa 10^38; i pochi che lo superano, come un'autorizzazione di spesa illimitata pari a 2^256 − 1, richiedono un NUMERIC senza limite prefissato, un tipo a 256 bit o una stringa esadecimale. Dividere per 1e18 produce un numero in virgola mobile: adeguato per la visualizzazione, non per la riconciliazione. Prestare attenzione anche alla divisione intera: in PostgreSQL, Trino e SQLite 5 / 2 = 2, mentre BigQuery e DuckDB restituiscono 2,5; moltiplicare per 1.0 o effettuare una conversione di tipo quando si calcolano rapporti.

Raggruppamento per intervalli temporali

-- Flusso giornaliero in entrata all'indirizzo, raggruppato per giorno UTC
SELECT DATE_TRUNC('day', block_timestamp)  AS day_utc,
       COUNT(*)                            AS tx_count,
       COUNT(DISTINCT from_address)        AS senders,
       SUM(value) / 1e18                   AS eth_received
FROM transactions
WHERE to_address = '0x5e1f…c0de'
  AND status = 1
  AND block_timestamp >= TIMESTAMP '2024-11-01 00:00:00'
  AND block_timestamp <  TIMESTAMP '2024-12-01 00:00:00'
GROUP BY DATE_TRUNC('day', block_timestamp)
ORDER BY day_utc;

Il campione contiene anche un trasferimento non riuscito di 2 ETH il 1° novembre; senza status = 1, quel giorno mostrerebbe 4,25 ETH. Il filtro temporale è un intervallo semiaperto (>= inizio, < fine), che non conta mai due volte una riga esattamente a mezzanotte. La funzione di troncamento è una differenza comune tra i dialetti: DATE_TRUNC('day', ts) in PostgreSQL, DuckDB, Trino, Snowflake e Spark SQL; TIMESTAMP_TRUNC(ts, DAY) o DATE(ts) in BigQuery; DATE(ts) in SQLite; toStartOfDay(ts) in ClickHouse. Raggruppare in UTC, convertire soltanto per la presentazione e ricordare che i timestamp dei blocchi hanno una risoluzione di un secondo e, su Bitcoin, possono discostarsi di oltre un'ora (Lezione 2.1).

Aggregazione con GROUP BY

GROUP BY riduce le righe a una per gruppo; COUNT(*), COUNT(DISTINCT x), SUM, MIN, MAX e AVG riepilogano ciascun gruppo; HAVING filtra i gruppi dopo l'aggregazione, così come WHERE filtra le righe prima di essa. Gli importi dei token richiedono un join per ottenere il numero di decimali di ciascun token:

-- Trasferimenti di token in uscita dall'indirizzo, convertiti con i decimali di ciascun token
SELECT tt.token_address,
       t.symbol,
       COUNT(*)                                     AS transfers,
       SUM(tt.amount_raw) / POWER(10, t.decimals)   AS amount
FROM token_transfers AS tt
JOIN tokens AS t
  ON t.token_address = tt.token_address
WHERE tt.from_address = '0x5e1f…c0de'
  AND tt.block_number BETWEEN 21000000 AND 21050000
GROUP BY tt.token_address, t.symbol, t.decimals   -- non raggruppare mai soltanto per simbolo
ORDER BY transfers DESC;

La chiave di raggruppamento è l'indirizzo del token, non il simbolo. Chiunque può distribuire un contratto chiamato "USDC": nei dati di esempio, un'imitazione con 18 decimali si trova accanto al token autentico con 6, e il raggruppamento per simbolo sommerebbe 1.000 token privi di valore a 2.000 autentici e dividerebbe il totale misto utilizzando un unico numero di decimali errato. JOIN è un inner join, quindi i trasferimenti dei token assenti da tokens scompaiono silenziosamente; la Lezione 2.8 mostra come conservarli con un LEFT JOIN. Le funzioni approssimate come APPROX_COUNT_DISTINCT sono adatte alle dashboard, ma non dovrebbero fornire dati per i report a meno che ciò non venga dichiarato.

Dataset pubblici, motori e notebook

• Dataset pubblici di Google BigQuery. crypto_bitcoin, crypto_ethereum e altri, costruiti con gli strumenti blockchain-etl; fatturati in base ai dati scansionati oltre una quota gratuita.
• AWS Public Blockchain Data. File Parquet compressi in un bucket pubblico, partizionati per data e aggiornati quotidianamente; leggibili da qualsiasi motore compatibile con Parquet.
• Piattaforme di query ospitate. Piattaforme della comunità con tabelle grezze, decodificate e curate e un livello di servizio gratuito; le relative tabelle curate seguono le regole di ciascun fornitore.

Pubblico non significa corretto o aggiornato: i dataset hanno ritardi che vanno da pochi minuti a un giorno, cambiano schema e occasionalmente presentano blocchi mancanti. Analizzare il profilo di una tabella prima di considerarla affidabile:

-- Copertura, aggiornamento e duplicati
SELECT MIN(block_number)     AS first_block,
       MAX(block_number)     AS last_block,
       MAX(block_timestamp)  AS latest_data_utc,
       COUNT(*)              AS row_count,
       COUNT(DISTINCT hash)  AS distinct_hashes  -- dovrebbe essere uguale a row_count
FROM transactions;

Su un motore con addebito per scansione, eseguire prima la query su un intervallo di date recente (Lezione 3.6).

Il nucleo di SQL è condiviso, ma i nomi delle funzioni, la gestione delle date, gli array e la denominazione delle tabelle differiscono. Queste tre query contano tutte i blocchi di un giorno UTC:

-- Dataset pubblico in BigQuery (project.dataset.table):
SELECT COUNT(*) FROM `bigquery-public-data.crypto_ethereum.blocks`
WHERE DATE(timestamp) = '2024-11-01';
-- DuckDB che legge direttamente i file Parquet:
SELECT COUNT(*) FROM read_parquet('eth/blocks/date=2024-11-01/*.parquet');
-- Qualsiasi motore che contenga lo schema generico:
SELECT COUNT(*) FROM blocks WHERE CAST(block_timestamp AS DATE) = DATE '2024-11-01';

I notebook (Jupyter e simili) combinano SQL, Python o R, grafici e ragionamenti scritti in un unico documento, caratteristica adatta alle indagini. Il lavoro iterativo sui grafi, come il calcolo delle componenti connesse per il clustering, si adatta poco a SQL; gli analisti esportano gli archi in una libreria per grafi (Lezione 2.9).

Scelta degli strumenti ed errori comuni da evitare

Valutare copertura (catene, intervallo di date, tracce, eventi decodificati), aggiornamento, modello di costo, riproducibilità (è possibile fissare uno snapshot o un'altezza di blocco?), provenienza (ogni riga è riconducibile a un hash di blocco e a un hash di transazione?) e riservatezza. I servizi ospitati registrano le query e alcune piattaforme della comunità le rendono pubbliche per impostazione predefinita: digitare l'indirizzo di un sospettato in una piattaforma condivisa può rivelare un'indagine. Per attività riservate, utilizzare un proprio nodo o file scaricati e un motore incorporato locale. Un giornalista che verifica un solo wallet può utilizzare un dataset pubblico ed esportare le righe utilizzate; un exchange che controlla i depositi necessita di una pipeline in streaming verso un database colonnare; uno studio che deve essere riprodotto esattamente richiede uno snapshot Parquet fissato e codice con versionamento. Qualunque sia la scelta, registrare insieme a ogni risultato il dataset, la sua versione o la data dello snapshot, il testo della query e il momento di esecuzione.

Focus sull'esame: sono previste domande che presentano una query o un dataset e chiedono quale sia il problema nel risultato: le fasi ETL e ciò che avviene nella trasformazione; la granularità e il motivo per cui un blocco diventa molte righe; i livelli grezzo, decodificato e curato e la necessità di riconciliare le tabelle curate; i principali dataset pubblici e i loro limiti; le unità di base e l'overflow a 64 bit; l'esclusione delle transazioni non riuscite dal valore ma non dalle commissioni; to_address NULL nelle creazioni di contratti; gli intervalli UTC semiaperti e le differenze tra dialetti nel troncamento delle date; l'ordine nella catena per numero di blocco e indice di transazione; il raggruppamento dei token per indirizzo del contratto; la riservatezza delle query ospitate e la registrazione della provenienza.