La pile analytique et SQL pour les tables de blockchain

Presque tous les chiffres d’un rapport d’analyse on-chain sont passés par un pipeline entre le nœud qui a observé le bloc et la requête qui a produit le chiffre. Un enquêteur qui sait à quelle couche appartient une table, qui l’a construite, à quel point ses données sont récentes et quelles règles l’ont façonnée peut distinguer une véritable constatation d’un artefact du pipeline. SQL est le langage commun au sommet de cette pile : les jeux de données publics, les plateformes hébergées et les entrepôts de données internes l’utilisent tous. Une poignée de clauses couvre la plupart des questions, mais les tables de blockchain ajoutent leurs propres pièges, des entiers représentant des valeurs à 18 décimales aux transactions échouées qui figurent toujours dans les données. Cette leçon présente la pile, définit le schéma générique utilisé dans le reste de ce module et construit les premières requêtes commentées.

Du nœud à la table

Un nœud fournit les blocs, les transactions, les reçus et l’état au moyen d’appels RPC (leçon 2.1). Les analystes ont besoin de lignes et de colonnes. Le processus qui transforme les uns en les autres est un pipeline ETL : extraction, transformation, chargement.

• Extraction. Interroger le nœud pour chaque bloc d’une plage : le bloc avec ses transactions, les reçus avec leurs journaux et, lorsque cela est pris en charge, les traces d’appels.
• Transformation. Aplatir le JSON imbriqué pour obtenir une ligne par enregistrement, convertir les chaînes hexadécimales en nombres, copier l’horodatage du bloc dans chaque transaction et chaque journal afin que les requêtes n’aient pas besoin de jointure pour le temps, et normaliser les identifiants (par exemple, les adresses en minuscules).
• Chargement. Écrire les lignes dans le stockage, généralement dans des fichiers colonnaires Apache Parquet partitionnés par date ou par plage de blocs, ou dans des tables de base de données. Les formats de table ouverts tels qu’Apache Iceberg et Delta Lake ajoutent des instantanés versionnés à cet ensemble.

L’idée clé est la granularité : ce que représente une ligne. Un bloc Ethereum contenant 150 transactions et 400 journaux d’événements devient une ligne dans blocks, 150 dans transactions et 400 dans logs. La plupart des erreurs de requête proviennent de l’oubli de la granularité d’une table et d’un comptage ou d’une somme effectués au mauvais niveau.

Les pipelines fonctionnent soit sous la forme d’un chargement rétrospectif ponctuel de l’historique, soit sous la forme de chargements en continu (incrémentaux) qui suivent la tête de chaîne. À proximité de la tête, des blocs peuvent être remplacés par une réorganisation ; un pipeline attend donc une profondeur de confirmation ou un bloc finalisé, ou réécrit les blocs récents lorsque la chaîne change. Parmi les extracteurs open source figurent Ethereum ETL et Bitcoin ETL du projet blockchain-etl, ainsi que cryo, qui écrit les données EVM directement au format Parquet. Il est utile de les connaître même si vous ne les exécutez jamais : de nombreux jeux de données publics ont été construits avec eux et en héritent les noms de colonnes.

Tables brutes, décodées et consolidées

Chaque montée d’un niveau raccourcit les requêtes et ajoute des décisions prises par quelqu’un d’autre. Une table consolidée token_transfers inclut-elle les transferts ERC-721 ou seulement les jetons fongibles ? Les créations et destructions de jetons correspondent-elles à des lignes avec l’adresse zéro, ou sont-elles exclues ? Le montant est-il brut ou déjà divisé selon le nombre de décimales ? Deux jeux de données qui nomment tous deux une table token_transfers peuvent différer sur ces trois points. Le réflexe protecteur est le rapprochement : vérifier la table consolidée par rapport à la couche brute sur une petite plage de blocs.

-- La table consolidée token_transfers correspond-elle aux journaux bruts dont elle provient ?
SELECT 'raw logs' AS layer, COUNT(*) AS n
FROM logs
WHERE topic0 = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
  AND topic3 IS NULL              -- transferts fongibles (de type ERC-20) uniquement
  AND block_number BETWEEN 21000000 AND 21000099
UNION ALL
SELECT 'token_transfers', COUNT(*)
FROM token_transfers
WHERE block_number BETWEEN 21000000 AND 21000099;

La première moitié compte les événements Transfer bruts (la chaîne hexadécimale est le hachage de la signature de l’événement, expliqué dans la leçon 2.9) ; la seconde compte les lignes consolidées pour les mêmes 100 blocs. Si les résultats diffèrent, déterminez pourquoi avant d’utiliser la table : elle peut exclure certains jetons, inclure des NFT ou omettre des blocs.

Un schéma générique pour les tables de blockchain

Les jeux de données nomment les tables et les colonnes différemment ; ce module utilise donc un schéma générique neutre, inspiré des outils ETL open source. L’adaptation à un jeu de données réel consiste généralement à renommer les éléments.

Conventions : les adresses et les hachages sont des chaînes hexadécimales en minuscules avec le préfixe 0x ; les montants (value, amount_raw, prix du gaz) sont des entiers bruts en unités de base stockés au format DECIMAL(38,0) ; les horodatages sont en UTC ; status vaut 1 en cas de réussite et 0 en cas d’échec ; gas_used et effective_gas_price dans transactions proviennent du reçu ; block_hash permet aux requêtes de joindre les transactions à blocks par hachage plutôt que par hauteur, ce qui élimine les lignes résiduelles de blocs remplacés lors d’une réorganisation (leçon 2.1). labels.grade contient la note de A à D du lien entre l’adresse et l’entité, A étant la plus forte (leçons 3.2 et 4.5) ; un tri par grade croissant place donc les étiquettes les plus solides en premier. Les exemples interrogent une seule chaîne à la fois ; les tables contenant plusieurs chaînes ajoutent une colonne chain_id à chaque ligne ainsi qu’à chaque clé et jointure (leçon 2.6). Les tables UTXO inputs et outputs sont présentées dans la leçon 2.9. Les jeux de données réels diffèrent sur des détails que vous devez vérifier : le jeu de données Ethereum de BigQuery préfixe les champs du reçu (receipt_status, receipt_gas_used), nomme le contrat du journal address et stocke les topics sous forme de tableau, et certains jeux de données stockent les grands entiers sous forme de chaînes.

SELECT, WHERE et ORDER BY

Dans la plupart des cas, la première question est simple : qu’est-ce qui est entré sur cette adresse ?

-- Transferts d’ETH réussis envoyés à une adresse dans une plage de blocs
SELECT block_number,
       block_timestamp,
       hash,
       from_address,
       value / 1e18 AS value_eth                -- conversion de wei en ETH, pour l’affichage
FROM transactions
WHERE to_address = '0x5e1f…c0de'               -- en minuscules, comme dans le stockage
  AND block_number BETWEEN 21000000 AND 21050000
  AND status = 1                                -- exclure les transactions échouées
  AND value > 0
ORDER BY block_number, transaction_index;

Chaque filtre compte :

• Casse. Les adresses sont comparées comme des chaînes, et les adresses avec somme de contrôle mélangent majuscules et minuscules : normalisez le littéral avec LOWER() ou saisissez-le en minuscules.
• Périmètre. Une plage de blocs limite le volume de données parcourues et rend la requête reproductible : la même plage renverra les mêmes lignes l’année prochaine.
• Statut. Les transactions échouées sont enregistrées et ont payé des frais, mais leur transfert de valeur a été annulé. Oublier status = 1 est la manière la plus courante de surestimer les entrées.
• Valeur. De nombreuses transactions vers une adresse ne transportent aucun ETH, car ce sont des appels de contrat.

Attention aux NULL. Une transaction de création de contrat n’a pas de destinataire ; son champ to_address est donc NULL, et les comparaisons avec NULL ne sont ni vraies ni fausses : to_address <> '0x5e1f…c0de' exclut silencieusement toutes les créations de contrats. Écrivez to_address IS NULL OR to_address <> … lorsque vous voulez dire « tout le reste ». De même, COUNT(to_address) ignore les NULL, contrairement à COUNT(*).

Triez par block_number, transaction_index pour respecter l’ordre de la chaîne ; toutes les transactions d’un bloc partagent le même horodatage. Cette requête ne voit que les transferts d’ETH de premier niveau ; les ETH envoyés par des contrats apparaissent dans les traces (leçon 2.9).

Unités et types numériques

Un entier signé sur 64 bits s’arrête à environ 9,22 × 10^18, soit seulement 9,22 ETH exprimés en wei. Stockez et additionnez les montants sous forme de DECIMAL(38,0) ou de NUMERIC, additionnez d’abord les entiers bruts et ne divisez qu’une fois à la fin. DECIMAL(38,0) peut contenir des valeurs allant jusqu’à environ 10^38 ; les rares valeurs qui dépassent cette limite, comme une autorisation illimitée de 2^256 − 1, nécessitent un NUMERIC sans limite de taille, un type sur 256 bits ou une chaîne hexadécimale. Diviser par 1e18 produit un nombre à virgule flottante : cela convient pour l’affichage, pas pour le rapprochement. Attention aussi à la division entière : dans PostgreSQL, Trino et SQLite, 5 / 2 = 2, tandis que BigQuery et DuckDB renvoient 2,5 ; multipliez par 1.0 ou effectuez une conversion de type lorsque vous calculez des ratios.

Regroupement par intervalles temporels

-- Entrées quotidiennes sur l’adresse, regroupées par jour 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;

L’échantillon contient également un transfert échoué de 2 ETH le 1er novembre ; sans status = 1, cette journée afficherait 4,25 ETH. Le filtre temporel est un intervalle semi-ouvert (>= début, < fin), qui ne compte jamais deux fois une ligne située exactement à minuit. La fonction de troncature est une différence courante entre dialectes : DATE_TRUNC('day', ts) dans PostgreSQL, DuckDB, Trino, Snowflake et Spark SQL ; TIMESTAMP_TRUNC(ts, DAY) ou DATE(ts) dans BigQuery ; DATE(ts) dans SQLite ; toStartOfDay(ts) dans ClickHouse. Effectuez les regroupements en UTC, ne convertissez que pour la présentation et rappelez-vous que les horodatages des blocs ont une résolution d’une seconde et que, sur Bitcoin, ils peuvent présenter un décalage de plus d’une heure (leçon 2.1).

Agrégation avec GROUP BY

GROUP BY réduit les lignes à une seule par groupe ; COUNT(*), COUNT(DISTINCT x), SUM, MIN, MAX et AVG résument chaque groupe ; HAVING filtre les groupes après l’agrégation, comme WHERE filtre les lignes avant celle-ci. Les montants de jetons nécessitent une jointure pour obtenir le nombre de décimales de chaque jeton :

-- Transferts de jetons sortant de l’adresse, convertis selon les décimales de chaque jeton
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   -- ne jamais regrouper uniquement par symbole
ORDER BY transfers DESC;

La clé de regroupement est l’adresse du jeton, et non le symbole. N’importe qui peut déployer un contrat appelé « USDC » : dans les données d’exemple, une imitation à 18 décimales côtoie le véritable jeton à 6 décimales, et un regroupement par symbole ajouterait 1 000 jetons sans valeur à 2 000 jetons authentiques, puis diviserait l’ensemble selon un nombre de décimales erroné. JOIN est une jointure interne ; les transferts de jetons absents de tokens disparaissent donc silencieusement ; la leçon 2.8 montre comment les conserver avec un LEFT JOIN. Les fonctions approximatives telles que APPROX_COUNT_DISTINCT conviennent aux tableaux de bord, mais ne doivent pas fournir les chiffres d’un rapport sans que vous le précisiez.

Jeux de données publics, moteurs et notebooks

• Jeux de données publics Google BigQuery. crypto_bitcoin, crypto_ethereum et d’autres, construits avec les outils blockchain-etl ; facturation selon le volume de données parcourues au-delà d’un quota gratuit.
• AWS Public Blockchain Data. Fichiers Parquet compressés dans un compartiment public, partitionnés par date et mis à jour quotidiennement ; lisibles par tout moteur prenant en charge Parquet.
• Plateformes de requêtes hébergées. Plateformes communautaires proposant des tables brutes, décodées et consolidées ainsi qu’une offre gratuite ; leurs tables consolidées suivent les règles de chaque fournisseur.

Public ne signifie ni correct ni à jour : les jeux de données accusent un retard de quelques minutes à une journée, changent de schéma et omettent parfois des blocs. Établissez le profil d’une table avant de lui faire confiance :

-- Couverture, fraîcheur et doublons
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  -- doit être égal à row_count
FROM transactions;

Sur un moteur facturé au volume de données parcourues, exécutez d’abord cette requête sur une plage de dates récente (leçon 3.6).

Le socle SQL est commun, mais les noms de fonctions, le traitement des dates, les tableaux et la dénomination des tables diffèrent. Ces trois requêtes comptent toutes les blocs d’une journée UTC :

-- Jeu de données public dans BigQuery (projet.jeu_de_données.table) :
SELECT COUNT(*) FROM `bigquery-public-data.crypto_ethereum.blocks`
WHERE DATE(timestamp) = '2024-11-01';
-- DuckDB lisant directement les fichiers Parquet :
SELECT COUNT(*) FROM read_parquet('eth/blocks/date=2024-11-01/*.parquet');
-- Tout moteur contenant le schéma générique :
SELECT COUNT(*) FROM blocks WHERE CAST(block_timestamp AS DATE) = DATE '2024-11-01';

Les notebooks (Jupyter et outils similaires) combinent SQL, Python ou R, des graphiques et un raisonnement écrit dans un seul document, ce qui convient aux enquêtes. Les traitements itératifs sur graphes, comme les composantes connexes pour le regroupement, se prêtent mal à SQL ; les analystes exportent les arêtes vers une bibliothèque de graphes (leçon 2.9).

Choisir les outils et éviter les erreurs courantes

Évaluez la couverture (chaînes, plage de dates, traces, événements décodés), la fraîcheur, le modèle de coût, la reproductibilité (pouvez-vous figer un instantané ou une hauteur de bloc ?), la provenance (chaque ligne peut-elle être reliée à un hachage de bloc et à un hachage de transaction ?) et la confidentialité. Les services hébergés journalisent les requêtes, et certaines plateformes communautaires les rendent publiques par défaut : saisir l’adresse d’un suspect dans une plateforme partagée peut révéler une enquête. Pour les travaux confidentiels, utilisez votre propre nœud ou des fichiers téléchargés et un moteur embarqué local. Un journaliste qui vérifie un portefeuille peut utiliser un jeu de données public et exporter les lignes utilisées ; une plateforme d’échange qui contrôle les dépôts a besoin d’un pipeline en continu vers une base de données colonnaire ; une étude qui doit être reproduite à l’identique nécessite un instantané Parquet figé et du code versionné. Quel que soit votre choix, consignez avec chaque résultat le jeu de données, sa version ou la date de l’instantané, le texte de la requête et le moment de son exécution.

Points clés pour l’examen : attendez-vous à des questions qui vous présentent une requête ou un jeu de données et vous demandent ce qui ne va pas dans le résultat : les étapes ETL et le rôle de la transformation ; la granularité et la raison pour laquelle un bloc devient plusieurs lignes ; les couches brutes, décodées et consolidées et la nécessité de rapprocher les tables consolidées ; les principaux jeux de données publics et leurs limites ; les unités de base et le dépassement de capacité sur 64 bits ; l’exclusion des transactions échouées pour les montants, mais pas pour les frais ; le champ to_address NULL lors des créations de contrats ; les intervalles UTC semi-ouverts et les différences de dialecte dans la troncature des dates ; l’ordre de la chaîne par numéro de bloc et index de transaction ; le regroupement des jetons par adresse de contrat ; la confidentialité des requêtes hébergées et la consignation de la provenance.