A pilha de análise e SQL para tabelas de blockchain

Quase todo número em um relatório on-chain passou por um pipeline entre o nó que observou o bloco e a consulta que produziu o valor. Um investigador que sabe a qual camada uma tabela pertence, quem a construiu, quão atualizada ela está e quais regras a moldaram consegue distinguir uma constatação real de um artefato do pipeline. SQL é a linguagem comum no topo dessa pilha: conjuntos de dados públicos, plataformas hospedadas e data warehouses internos respondem a ela. Algumas cláusulas cobrem a maioria das perguntas, mas as tabelas de blockchain acrescentam suas próprias armadilhas, desde inteiros com 18 casas decimais até transações que falharam e ainda constam nos dados. Esta aula mapeia a pilha, apresenta o esquema genérico usado no restante deste módulo e desenvolve as primeiras consultas demonstradas.

Do nó à tabela

Um nó fornece blocos, transações, recibos e estado por meio de chamadas RPC (Aula 2.1). Analistas precisam de linhas e colunas. O processo que transforma um formato no outro é um pipeline ETL: extração, transformação e carga.

• Extração. Consultar o nó para cada bloco de um intervalo: o bloco com suas transações, os recibos com seus logs e, quando houver suporte, os rastros de chamadas.
• Transformação. Achatar o JSON aninhado em uma linha por registro, converter strings hexadecimais em números, copiar o timestamp do bloco para cada transação e log para que as consultas não precisem de uma junção para obter o horário e normalizar os identificadores (por exemplo, endereços em letras minúsculas).
• Carga. Gravar linhas no armazenamento, geralmente em arquivos colunares Apache Parquet particionados por data ou intervalo de blocos, ou em tabelas de banco de dados. Formatos de tabela abertos, como Apache Iceberg e Delta Lake, acrescentam snapshots versionados sobre essa base.

A ideia central é a granularidade: o que uma linha representa. Um bloco Ethereum com 150 transações e 400 logs de eventos se torna uma linha em blocks, 150 em transactions e 400 em logs. A maioria dos erros de consulta decorre de esquecer a granularidade de uma tabela e contar ou somar no nível errado.

Os pipelines executam uma carga retroativa única do histórico ou cargas em streaming (incrementais) que acompanham a ponta da cadeia. Próximo à ponta, os blocos podem ser substituídos por uma reorganização; por isso, um pipeline aguarda uma profundidade de confirmação ou um bloco finalizado, ou regrava blocos recentes quando a cadeia muda. Entre os extratores de código aberto estão Ethereum ETL e Bitcoin ETL, do projeto blockchain-etl, e cryo, que grava dados EVM diretamente em Parquet. Vale a pena conhecê-los mesmo que você nunca os execute: muitos conjuntos de dados públicos foram construídos com eles e herdam seus nomes de colunas.

Tabelas brutas, decodificadas e curadas

Cada passo para uma camada superior encurta as consultas e acrescenta decisões tomadas por outra pessoa. Uma tabela curada token_transfers inclui transferências ERC-721 ou apenas tokens fungíveis? Emissões e queimas são linhas com o endereço zero ou são excluídas? O valor é bruto ou já foi dividido conforme as casas decimais? Dois conjuntos de dados que chamam uma tabela de token_transfers podem divergir nos três aspectos. O hábito de proteção é a reconciliação: verificar a tabela curada em relação à camada bruta em um pequeno intervalo de blocos.

-- A tabela curada token_transfers corresponde aos logs brutos dos quais se originou?
SELECT 'raw logs' AS layer, COUNT(*) AS n
FROM logs
WHERE topic0 = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
  AND topic3 IS NULL              -- apenas transferências fungíveis (estilo 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;

A primeira metade conta eventos Transfer brutos (a string hexadecimal é o hash da assinatura do evento, explicado na Aula 2.9); a segunda conta linhas curadas para os mesmos 100 blocos. Se houver diferença, descubra o motivo antes de usar a tabela: ela pode descartar alguns tokens, incluir NFTs ou ter blocos ausentes.

Um esquema genérico para tabelas de blockchain

Os conjuntos de dados nomeiam tabelas e colunas de formas diferentes; por isso, este módulo usa um esquema genérico neutro, modelado a partir das ferramentas ETL de código aberto. A adaptação para um conjunto de dados real geralmente consiste em renomear elementos.

Convenções: endereços e hashes são strings hexadecimais em letras minúsculas com o prefixo 0x; valores (value, amount_raw, preços de gas) são inteiros brutos em unidades-base armazenados como DECIMAL(38,0); timestamps estão em UTC; status é 1 para sucesso e 0 para falha; gas_used e effective_gas_price em transactions vêm do recibo; block_hash permite que as consultas façam a junção de transações com blocks pelo hash, e não pela altura, o que elimina linhas remanescentes de blocos substituídos em uma reorganização (Aula 2.1). labels.grade contém a classificação de A a D do vínculo entre o endereço e a entidade, sendo A a mais forte (Aulas 3.2 e 4.5); portanto, ordenar por grade em ordem crescente coloca os rótulos mais fortes primeiro. Os exemplos consultam uma cadeia por vez; tabelas que contêm várias cadeias acrescentam uma coluna chain_id a cada linha e a cada chave e junção (Aula 2.6). As tabelas UTXO inputs e outputs são apresentadas na Aula 2.9. Conjuntos de dados reais diferem em detalhes que você deve verificar: o conjunto de dados Ethereum do BigQuery usa prefixos nos campos de recibo (receipt_status, receipt_gas_used), chama o contrato do log de address e armazena os tópicos como um array, e alguns conjuntos de dados armazenam inteiros grandes como strings.

SELECT, WHERE e ORDER BY

A primeira pergunta na maioria dos casos é simples: o que entrou neste endereço?

-- Transferências de ETH bem-sucedidas enviadas a um endereço em um intervalo de blocos
SELECT block_number,
       block_timestamp,
       hash,
       from_address,
       value / 1e18 AS value_eth                -- de wei para ETH, para exibição
FROM transactions
WHERE to_address = '0x5e1f…c0de'               -- em minúsculas, como armazenado
  AND block_number BETWEEN 21000000 AND 21050000
  AND status = 1                                -- descartar transações que falharam
  AND value > 0
ORDER BY block_number, transaction_index;

Cada filtro importa:

• Maiúsculas e minúsculas. Endereços são comparados como strings, e endereços com checksum misturam maiúsculas e minúsculas: normalize o literal com LOWER() ou digite-o em minúsculas.
• Escopo. Um intervalo de blocos limita a varredura e torna a consulta reproduzível: o mesmo intervalo retorna as mesmas linhas no ano seguinte.
• Status. Transações que falharam são registradas e pagaram taxas, mas sua transferência de valor foi revertida. Esquecer status = 1 é a forma mais comum de superestimar entradas.
• Valor. Muitas transações para um endereço carregam zero ETH porque são chamadas de contrato.

Atenção aos NULLs. Uma transação de criação de contrato não tem destinatário, portanto seu to_address é NULL, e comparações com NULL não são verdadeiras nem falsas: to_address <> '0x5e1f…c0de' descarta silenciosamente todas as criações de contrato. Escreva to_address IS NULL OR to_address <> … quando quiser dizer "todo o restante". Da mesma forma, COUNT(to_address) ignora NULLs e COUNT(*) não.

Ordene por block_number, transaction_index para obter a ordem da cadeia; todas as transações em um bloco compartilham o mesmo timestamp. Esta consulta vê apenas transferências de ETH de nível superior; ETH enviado por contratos aparece nos rastros (Aula 2.9).

Unidades e tipos numéricos

Um inteiro de 64 bits com sinal tem limite de cerca de 9,22 × 10^18, apenas 9,22 ETH em wei. Armazene e some valores como DECIMAL(38,0) ou NUMERIC, some primeiro os inteiros brutos e divida uma única vez no final. DECIMAL(38,0) comporta valores de até cerca de 10^38; os poucos que excedem esse limite, como uma aprovação ilimitada de 2^256 − 1, precisam de um NUMERIC sem limite de precisão, um tipo de 256 bits ou uma string hexadecimal. Dividir por 1e18 produz um número de ponto flutuante: adequado para exibição, não para reconciliação. Observe também a divisão inteira: em PostgreSQL, Trino e SQLite, 5 / 2 = 2, enquanto BigQuery e DuckDB retornam 2,5; multiplique por 1.0 ou faça uma conversão de tipo ao calcular razões.

Agrupamento em intervalos de tempo

-- Entrada diária no endereço, agrupada por dia 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;

A amostra também contém uma transferência de 2 ETH que falhou em 1º de novembro; sem status = 1, esse dia mostraria 4,25 ETH. O filtro de tempo é um intervalo semiaberto (>= início, < fim), que nunca conta duas vezes uma linha exatamente à meia-noite. A função de truncamento é uma diferença comum entre dialetos: DATE_TRUNC('day', ts) em PostgreSQL, DuckDB, Trino, Snowflake e Spark SQL; TIMESTAMP_TRUNC(ts, DAY) ou DATE(ts) em BigQuery; DATE(ts) em SQLite; toStartOfDay(ts) em ClickHouse. Agrupe em UTC, converta apenas para apresentação e lembre-se de que os timestamps dos blocos têm resolução de um segundo e, no Bitcoin, podem apresentar um desvio de mais de uma hora (Aula 2.1).

Agregação com GROUP BY

GROUP BY reduz as linhas a uma por grupo; COUNT(*), COUNT(DISTINCT x), SUM, MIN, MAX e AVG resumem cada grupo; HAVING filtra grupos após a agregação, assim como WHERE filtra linhas antes dela. Os valores de tokens precisam de uma junção com as casas decimais de cada token:

-- Transferências de tokens saindo do endereço, convertidas com as casas decimais de cada 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   -- nunca agrupar apenas pelo símbolo
ORDER BY transfers DESC;

A chave de agrupamento é o endereço do token, não o símbolo. Qualquer pessoa pode implantar um contrato chamado "USDC": nos dados de exemplo, uma imitação com 18 casas decimais aparece ao lado do token real com 6, e agrupar por símbolo somaria 1.000 tokens sem valor a 2.000 genuínos e dividiria a mistura usando uma única contagem incorreta de casas decimais. JOIN é uma junção interna, portanto transferências de tokens ausentes em tokens desaparecem silenciosamente; a Aula 2.8 mostra como mantê-las com um LEFT JOIN. Funções aproximadas, como APPROX_COUNT_DISTINCT, são adequadas para painéis, mas não devem fornecer números para relatórios, a menos que você informe isso.

Conjuntos de dados públicos, mecanismos e notebooks

• Conjuntos de dados públicos do Google BigQuery. crypto_bitcoin, crypto_ethereum e outros, construídos com as ferramentas blockchain-etl; a cobrança é feita pelos dados varridos além de uma franquia gratuita.
• AWS Public Blockchain Data. Arquivos Parquet compactados em um bucket público, particionados por data e atualizados diariamente; podem ser lidos por qualquer mecanismo compatível com Parquet.
• Plataformas de consulta hospedadas. Plataformas comunitárias com tabelas brutas, decodificadas e curadas e um nível gratuito; suas tabelas curadas seguem as regras de cada provedor.

Público não significa correto ou atualizado: os conjuntos de dados têm atrasos de minutos a um dia, mudam de esquema e ocasionalmente deixam de incluir blocos. Faça um perfil da tabela antes de confiar nela:

-- Cobertura, atualização e duplicatas
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  -- deve ser igual a row_count
FROM transactions;

Em um mecanismo com cobrança por varredura, execute-a primeiro em um intervalo de datas recente (Aula 3.6).

O núcleo do SQL é compartilhado, mas nomes de funções, tratamento de datas, arrays e nomenclatura de tabelas diferem. Estas três consultas contam os blocos de um dia UTC:

-- Conjunto de dados público no BigQuery (projeto.conjunto_de_dados.tabela):
SELECT COUNT(*) FROM `bigquery-public-data.crypto_ethereum.blocks`
WHERE DATE(timestamp) = '2024-11-01';
-- DuckDB lendo arquivos Parquet diretamente:
SELECT COUNT(*) FROM read_parquet('eth/blocks/date=2024-11-01/*.parquet');
-- Qualquer mecanismo que contenha o esquema genérico:
SELECT COUNT(*) FROM blocks WHERE CAST(block_timestamp AS DATE) = DATE '2024-11-01';

Notebooks (Jupyter e similares) combinam SQL, Python ou R, gráficos e raciocínio escrito em um único documento, o que é adequado para investigações. Trabalhos iterativos com grafos, como componentes conexos para agrupamento, não se adaptam bem ao SQL; analistas exportam arestas para uma biblioteca de grafos (Aula 2.9).

Escolha de ferramentas e prevenção de erros comuns

Avalie cobertura (cadeias, intervalo de datas, rastros, eventos decodificados), atualização, modelo de custos, reprodutibilidade (é possível fixar um snapshot ou uma altura de bloco?), proveniência (cada linha pode ser rastreada até um hash de bloco e um hash de transação?) e confidencialidade. Serviços hospedados registram consultas, e algumas plataformas comunitárias as tornam públicas por padrão: digitar o endereço de um suspeito em uma plataforma compartilhada pode revelar uma investigação. Para trabalhos confidenciais, use seu próprio nó ou arquivos baixados e um mecanismo embarcado local. Um jornalista verificando uma carteira pode usar um conjunto de dados público e exportar as linhas usadas; uma exchange que faz triagem de depósitos precisa de um pipeline de streaming para um banco de dados colunar; um estudo que deve ser reproduzido exatamente precisa de um snapshot Parquet fixado e código versionado. Seja qual for a escolha, registre o conjunto de dados, sua versão ou data do snapshot, o texto da consulta e o horário de execução junto com cada resultado.

Foco no exame: espere questões que apresentem uma consulta ou um conjunto de dados e perguntem o que há de errado com o resultado: as etapas do ETL e o que a transformação faz; granularidade e por que um bloco se torna muitas linhas; camadas brutas, decodificadas e curadas e a necessidade de reconciliar tabelas curadas; os principais conjuntos de dados públicos e seus limites; unidades-base e estouro de 64 bits; exclusão de transações que falharam dos valores, mas não das taxas; to_address NULL em criações de contrato; intervalos UTC semiabertos e diferenças de dialeto no truncamento de datas; ordem da cadeia por número do bloco e índice da transação; agrupamento de tokens por endereço do contrato; confidencialidade de consultas hospedadas e registro da proveniência.