Der Analytics-Stack und SQL für Blockchain-Tabellen

Fast jede Zahl in einem On-Chain-Bericht hat eine Pipeline zwischen dem Knoten, der den Block erfasst hat, und der Abfrage, die die Zahl geliefert hat, durchlaufen. Wer als Ermittler weiß, zu welcher Schicht eine Tabelle gehört, wer sie erstellt hat, wie aktuell sie ist und welche Regeln sie geprägt haben, kann einen tatsächlichen Befund von einem Pipeline-Artefakt unterscheiden. SQL ist die gemeinsame Sprache an der Spitze dieses Stacks: Öffentliche Datensätze, gehostete Plattformen und interne Data Warehouses lassen sich damit abfragen. Eine Handvoll Klauseln deckt die meisten Fragestellungen ab, doch Blockchain-Tabellen bringen eigene Fallstricke mit sich – von Ganzzahlen mit 18 Dezimalstellen bis zu fehlgeschlagenen Transaktionen, die weiterhin in den Daten enthalten sind. Diese Lektion stellt den Stack vor, beschreibt das generische Schema, das im weiteren Verlauf dieses Moduls verwendet wird, und entwickelt die ersten Beispielabfragen.

Vom Knoten zur Tabelle

Ein Knoten stellt Blöcke, Transaktionen, Receipts und den Zustand über RPC-Aufrufe bereit (Lektion 2.1). Analysten benötigen Zeilen und Spalten. Der Prozess, der das eine in das andere überführt, ist eine ETL-Pipeline: Extrahieren, Transformieren, Laden.

• Extrahieren. Fragen Sie den Knoten für jeden Block in einem Bereich ab: den Block mit seinen Transaktionen, die Receipts mit ihren Logs und, sofern unterstützt, die Aufruf-Traces.
• Transformieren. Überführen Sie verschachteltes JSON in eine Zeile pro Datensatz, wandeln Sie Hex-Zeichenfolgen in Zahlen um, kopieren Sie den Blockzeitstempel in jede Transaktion und jedes Log, damit Abfragen für die Zeitangabe keinen Join benötigen, und normalisieren Sie Identifikatoren (beispielsweise Adressen in Kleinbuchstaben).
• Laden. Schreiben Sie die Zeilen in den Speicher, üblicherweise in spaltenorientierte Apache Parquet-Dateien, die nach Datum oder Blockbereich partitioniert sind, oder in Datenbanktabellen. Offene Tabellenformate wie Apache Iceberg und Delta Lake ergänzen darauf aufbauend versionierte Snapshots.

Der zentrale Begriff ist die Granularität: was eine einzelne Zeile repräsentiert. Ein Ethereum-Block mit 150 Transaktionen und 400 Ereignis-Logs wird zu einer Zeile in blocks, 150 in transactions und 400 in logs. Die meisten Abfragefehler entstehen, wenn die Granularität einer Tabelle außer Acht gelassen und auf der falschen Ebene gezählt oder summiert wird.

Pipelines laufen entweder als einmaliges Backfill historischer Daten oder als Streaming-Ladevorgänge (inkrementell), die der Kettenspitze folgen. In der Nähe der Kettenspitze können Blöcke durch eine Reorganisation ersetzt werden. Daher wartet eine Pipeline entweder auf eine bestimmte Bestätigungstiefe oder einen finalisierten Block, oder sie schreibt die jüngsten Blöcke bei Änderungen der Kette neu. Zu den Open-Source-Extraktoren zählen Ethereum ETL und Bitcoin ETL des Projekts blockchain-etl sowie cryo, das EVM-Daten direkt in Parquet schreibt. Es lohnt sich, diese Werkzeuge zu kennen, selbst wenn Sie sie nie ausführen: Viele öffentliche Datensätze wurden damit erstellt und übernehmen ihre Spaltennamen.

Rohtabellen, dekodierte und kuratierte Tabellen

Jeder Schritt nach oben verkürzt die Abfragen und fügt Entscheidungen hinzu, die jemand anderes getroffen hat. Enthält eine kuratierte Tabelle token_transfers auch ERC-721-Transfers oder nur fungible Token? Werden Minting und Burning als Zeilen mit der Nulladresse erfasst oder ausgeschlossen? Ist der Betrag ein Rohwert oder bereits durch den Dezimalfaktor geteilt? Zwei Datensätze, die beide eine Tabelle token_transfers nennen, können sich in allen drei Punkten unterscheiden. Die schützende Gewohnheit ist der Abgleich: Prüfen Sie die kuratierte Tabelle anhand der Rohdatenschicht für einen kleinen Blockbereich.

-- Stimmt die kuratierte Tabelle token_transfers mit den zugrunde liegenden Roh-Logs überein?
SELECT 'raw logs' AS layer, COUNT(*) AS n
FROM logs
WHERE topic0 = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
  AND topic3 IS NULL              -- nur fungible Transfers (im ERC-20-Stil)
  AND block_number BETWEEN 21000000 AND 21000099
UNION ALL
SELECT 'token_transfers', COUNT(*)
FROM token_transfers
WHERE block_number BETWEEN 21000000 AND 21000099;

Die erste Hälfte zählt rohe Transfer-Ereignisse (die Hex-Zeichenfolge ist der Signatur-Hash des Ereignisses, erläutert in Lektion 2.9); die zweite zählt kuratierte Zeilen für dieselben 100 Blöcke. Falls die Zahlen abweichen, ermitteln Sie die Ursache, bevor Sie die Tabelle verwenden: Möglicherweise lässt sie bestimmte Token weg, enthält NFTs oder weist fehlende Blöcke auf.

Ein generisches Schema für Blockchain-Tabellen

Datensätze verwenden unterschiedliche Tabellen- und Spaltennamen. Deshalb nutzt dieses Modul ein neutrales generisches Schema, das sich an den Open-Source-ETL-Werkzeugen orientiert. Die Übertragung auf einen realen Datensatz besteht meist im Umbenennen.

Konventionen: Adressen und Hashes sind Hex-Zeichenfolgen in Kleinbuchstaben mit dem Präfix 0x; Beträge (value, amount_raw, Gaspreise) sind rohe Ganzzahlen in Basiseinheiten, gespeichert als DECIMAL(38,0); Zeitstempel sind in UTC; status ist 1 bei Erfolg und 0 bei Fehlschlag; gas_used und effective_gas_price in transactions stammen aus dem Receipt; block_hash ermöglicht es Abfragen, Transaktionen über den Hash statt über die Blockhöhe mit blocks zu verknüpfen. Dadurch entfallen Zeilen, die aus bei einer Reorganisation ersetzten Blöcken übrig geblieben sind (Lektion 2.1). labels.grade enthält die Einstufung A–D der Verbindung zwischen Adresse und Entität, wobei A die stärkste ist (Lektionen 3.2 und 4.5). Eine aufsteigende Sortierung nach grade stellt somit die stärksten Labels an den Anfang. Die Beispiele fragen jeweils eine Kette ab; Tabellen mit mehreren Ketten ergänzen jede Zeile sowie jeden Schlüssel und Join um eine Spalte chain_id (Lektion 2.6). Die UTXO-Tabellen inputs und outputs werden in Lektion 2.9 eingeführt. Reale Datensätze unterscheiden sich in Details, die Sie prüfen müssen: Der Ethereum-Datensatz von BigQuery versieht Receipt-Felder mit einem Präfix (receipt_status, receipt_gas_used), nennt den Contract des Logs address und speichert Topics als Array; einige Datensätze speichern große Ganzzahlen als Zeichenfolgen.

SELECT, WHERE und ORDER BY

Die erste Frage ist in den meisten Fällen einfach: Was ist bei dieser Adresse eingegangen?

-- Erfolgreiche ETH-Transfers an eine Adresse innerhalb eines Blockbereichs
SELECT block_number,
       block_timestamp,
       hash,
       from_address,
       value / 1e18 AS value_eth                -- wei in ETH, zur Anzeige
FROM transactions
WHERE to_address = '0x5e1f…c0de'               -- Kleinbuchstaben, wie gespeichert
  AND block_number BETWEEN 21000000 AND 21050000
  AND status = 1                                -- fehlgeschlagene Transaktionen ausschließen
  AND value > 0
ORDER BY block_number, transaction_index;

Jeder Filter ist wichtig:

• Groß- und Kleinschreibung. Adressen werden als Zeichenfolgen verglichen, und Adressen mit Prüfsumme verwenden gemischte Groß- und Kleinschreibung: Normalisieren Sie das Literal mit LOWER() oder geben Sie es in Kleinbuchstaben ein.
• Umfang. Ein Blockbereich begrenzt den Scan und macht die Abfrage reproduzierbar: Derselbe Bereich liefert im nächsten Jahr dieselben Zeilen.
• Status. Fehlgeschlagene Transaktionen werden erfasst und haben Gebühren verursacht, aber ihr Werttransfer wurde rückgängig gemacht. Das Vergessen von status = 1 ist die häufigste Ursache für zu hoch ausgewiesene Zuflüsse.
• Wert. Viele Transaktionen an eine Adresse übertragen null ETH, weil es sich um Contract-Aufrufe handelt.

Achten Sie auf NULL-Werte. Eine Transaktion zur Contract-Erstellung hat keinen Empfänger, daher ist ihr to_address NULL. Vergleiche mit NULL sind weder wahr noch falsch: to_address <> '0x5e1f…c0de' schließt stillschweigend jede Contract-Erstellung aus. Schreiben Sie to_address IS NULL OR to_address <> …, wenn Sie „alles andere“ meinen. Ebenso überspringt COUNT(to_address) NULL-Werte, COUNT(*) hingegen nicht.

Sortieren Sie nach block_number, transaction_index, um die Reihenfolge auf der Kette zu erhalten; alle Transaktionen in einem Block haben denselben Zeitstempel. Diese Abfrage erfasst nur ETH-Transfers auf oberster Ebene; von Contracts gesendete ETH erscheinen in Traces (Lektion 2.9).

Einheiten und numerische Datentypen

Eine vorzeichenbehaftete 64-Bit-Ganzzahl reicht nur bis etwa 9,22 × 10^18, also lediglich 9,22 ETH in wei. Speichern und summieren Sie Beträge als DECIMAL(38,0) oder NUMERIC, summieren Sie zuerst die rohen Ganzzahlen und teilen Sie einmal am Ende. DECIMAL(38,0) fasst Werte bis etwa 10^38; die wenigen darüberliegenden Werte, etwa eine unbegrenzte Freigabe von 2^256 − 1, benötigen ein unbegrenztes NUMERIC, einen 256-Bit-Typ oder eine Hex-Zeichenfolge. Die Division durch 1e18 ergibt eine Gleitkommazahl: für die Anzeige geeignet, nicht für den Abgleich. Achten Sie auch auf Ganzzahldivision: In PostgreSQL, Trino und SQLite gilt 5 / 2 = 2, während BigQuery und DuckDB 2,5 zurückgeben; multiplizieren Sie bei der Berechnung von Verhältnissen mit 1.0 oder verwenden Sie eine Typumwandlung.

Zeitliche Gruppierung

-- Täglicher Zufluss an die Adresse, nach UTC-Tag gruppiert
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;

Das Beispiel enthält außerdem einen fehlgeschlagenen Transfer von 2 ETH am 1. November; ohne status = 1 würden für diesen Tag 4,25 ETH angezeigt. Der Zeitfilter ist ein halboffenes Intervall (>= Beginn, < Ende), das eine Zeile genau um Mitternacht niemals doppelt zählt. Die Funktion zum Abschneiden des Zeitstempels ist ein häufiger Unterschied zwischen SQL-Dialekten: DATE_TRUNC('day', ts) in PostgreSQL, DuckDB, Trino, Snowflake und Spark SQL; TIMESTAMP_TRUNC(ts, DAY) oder DATE(ts) in BigQuery; DATE(ts) in SQLite; toStartOfDay(ts) in ClickHouse. Gruppieren Sie in UTC, rechnen Sie erst für die Darstellung um und bedenken Sie, dass Blockzeitstempel eine Auflösung von einer Sekunde haben und bei Bitcoin um mehr als eine Stunde abweichen können (Lektion 2.1).

Aggregation mit GROUP BY

GROUP BY fasst Zeilen zu einer Zeile pro Gruppe zusammen; COUNT(*), COUNT(DISTINCT x), SUM, MIN, MAX und AVG aggregieren jede Gruppe; HAVING filtert Gruppen nach der Aggregation, so wie WHERE Zeilen davor filtert. Token-Beträge benötigen einen Join, um die Dezimalstellenzahl des jeweiligen Tokens zu erhalten:

-- Token-Transfers von der Adresse, anhand der Dezimalstellenzahl des jeweiligen Tokens umgerechnet
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   -- niemals nur nach dem Symbol gruppieren
ORDER BY transfers DESC;

Der Gruppierungsschlüssel ist die Token-Adresse, nicht das Symbol. Jeder kann einen Contract namens „USDC“ bereitstellen: In den Beispieldaten steht eine Imitation mit 18 Dezimalstellen neben dem echten Token mit 6. Eine Gruppierung nach Symbol würde 1.000 wertlose Token zu 2.000 echten addieren und die Mischung durch einen einzigen falschen Dezimalfaktor teilen. JOIN ist ein Inner Join, sodass Transfers von Token, die in tokens fehlen, stillschweigend verschwinden; Lektion 2.8 zeigt, wie Sie diese mit einem LEFT JOIN beibehalten. Näherungsfunktionen wie APPROX_COUNT_DISTINCT sind für Dashboards geeignet, sollten aber keine Berichtszahlen liefern, sofern Sie dies nicht ausdrücklich kenntlich machen.

Öffentliche Datensätze, Engines und Notebooks

• Öffentliche Datensätze von Google BigQuery. crypto_bitcoin, crypto_ethereum und weitere, erstellt mit den blockchain-etl-Werkzeugen; die Abrechnung erfolgt nach der gescannten Datenmenge oberhalb eines Freikontingents.
• AWS Public Blockchain Data. Komprimierte Parquet-Dateien in einem öffentlichen Bucket, nach Datum partitioniert und täglich aktualisiert; mit jeder Parquet-fähigen Engine lesbar.
• Gehostete Abfrageplattformen. Community-Plattformen mit Rohtabellen, dekodierten und kuratierten Tabellen sowie einer kostenlosen Nutzungsstufe; ihre kuratierten Tabellen folgen den Regeln des jeweiligen Anbieters.

Öffentlich bedeutet nicht korrekt oder aktuell: Datensätze hinken um Minuten bis zu einem Tag hinterher, ändern ihre Schemata und lassen gelegentlich Blöcke aus. Prüfen Sie die Kenngrößen einer Tabelle, bevor Sie ihr vertrauen:

-- Abdeckung, Aktualität und Duplikate
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  -- sollte row_count entsprechen
FROM transactions;

Führen Sie dies bei einer Engine mit scanbasierter Abrechnung zunächst für einen aktuellen Datumsbereich aus (Lektion 3.6).

Der SQL-Kern ist gleich, aber Funktionsnamen, Datumsverarbeitung, Arrays und Tabellenbenennung unterscheiden sich. Diese drei Abfragen zählen alle die Blöcke eines UTC-Tages:

-- Öffentlicher Datensatz in BigQuery (project.dataset.table):
SELECT COUNT(*) FROM `bigquery-public-data.crypto_ethereum.blocks`
WHERE DATE(timestamp) = '2024-11-01';
-- DuckDB liest Parquet-Dateien direkt:
SELECT COUNT(*) FROM read_parquet('eth/blocks/date=2024-11-01/*.parquet');
-- Beliebige Engine mit dem generischen Schema:
SELECT COUNT(*) FROM blocks WHERE CAST(block_timestamp AS DATE) = DATE '2024-11-01';

Notebooks (Jupyter und ähnliche) kombinieren SQL, Python oder R, Diagramme und schriftliche Begründungen in einem Dokument, was sich für Ermittlungen eignet. Iterative Graphenanalysen wie die Bestimmung zusammenhängender Komponenten für das Clustering lassen sich mit SQL nur schlecht umsetzen; Analysten exportieren Kanten in eine Graphenbibliothek (Lektion 2.9).

Werkzeuge auswählen und häufige Fehler vermeiden

Wägen Sie Abdeckung (Ketten, Datumsbereich, Traces, dekodierte Ereignisse), Aktualität, Kostenmodell, Reproduzierbarkeit (können Sie einen Snapshot oder eine Blockhöhe festschreiben?), Herkunftsnachweis (lässt sich jede Zeile auf einen Block-Hash und einen Transaktions-Hash zurückführen?) und Vertraulichkeit gegeneinander ab. Gehostete Dienste protokollieren Abfragen, und einige Community-Plattformen veröffentlichen sie standardmäßig: Wenn Sie die Adresse eines Verdächtigen in eine gemeinsam genutzte Plattform eingeben, kann dies eine Ermittlung offenlegen. Verwenden Sie für vertrauliche Arbeiten Ihren eigenen Knoten oder heruntergeladene Dateien und eine lokale eingebettete Engine. Ein Journalist, der eine einzelne Wallet prüft, kann einen öffentlichen Datensatz verwenden und die genutzten Zeilen exportieren; eine Börse, die Einzahlungen überprüft, benötigt eine Streaming-Pipeline in eine spaltenorientierte Datenbank; eine Studie, die exakt reproduziert werden muss, benötigt einen festgeschriebenen Parquet-Snapshot und versionierten Code. Was auch immer Sie wählen: Dokumentieren Sie zu jedem Ergebnis den Datensatz, seine Version oder sein Snapshot-Datum, den Abfragetext und den Ausführungszeitpunkt.

Prüfungsschwerpunkt: Rechnen Sie mit Fragen, die Ihnen eine Abfrage oder einen Datensatz vorlegen und danach fragen, was am Ergebnis falsch ist: die ETL-Phasen und was beim Transformieren geschieht; Granularität und warum aus einem Block viele Zeilen werden; Rohdaten-, dekodierte und kuratierte Schichten sowie die Notwendigkeit, kuratierte Tabellen abzugleichen; die wichtigsten öffentlichen Datensätze und ihre Grenzen; Basiseinheiten und 64-Bit-Überlauf; Ausschluss fehlgeschlagener Transaktionen bei Wertbeträgen, aber nicht bei Gebühren; NULL in to_address bei Contract-Erstellungen; halboffene UTC-Intervalle und Unterschiede zwischen SQL-Dialekten beim Abschneiden von Zeitstempeln; Reihenfolge auf der Kette anhand von Blocknummer und Transaktionsindex; Gruppierung von Token nach Contract-Adresse; Vertraulichkeit gehosteter Abfragen und Dokumentation der Herkunft.