La pila de análisis y SQL para tablas de blockchain

Casi todas las cifras de un informe de actividad en cadena han pasado por una canalización entre el nodo que observó el bloque y la consulta que produjo la cifra. Un investigador que sabe a qué capa pertenece una tabla, quién la creó, qué tan actualizada está y qué reglas le dieron forma puede distinguir un hallazgo real de un artefacto de la canalización. SQL es el lenguaje común en la parte superior de esa pila: los conjuntos de datos públicos, las plataformas alojadas y los almacenes de datos internos permiten consultarlos mediante SQL. Unas pocas cláusulas cubren la mayoría de las preguntas, pero las tablas de blockchain añaden sus propias trampas, desde enteros con 18 decimales hasta transacciones fallidas que siguen presentes en los datos. Esta lección describe la pila, presenta el esquema genérico utilizado en el resto de este módulo y desarrolla las primeras consultas de ejemplo.

Del nodo a la tabla

Un nodo proporciona bloques, transacciones, recibos y estado mediante llamadas RPC (lección 2.1). Los analistas necesitan filas y columnas. El proceso que convierte unos en otros es una canalización ETL: extracción, transformación y carga.

• Extracción. Consultar al nodo para obtener cada bloque de un rango: el bloque con sus transacciones, los recibos con sus registros y, cuando se admitan, las trazas de llamadas.
• Transformación. Aplanar el JSON anidado para obtener una fila por registro, convertir cadenas hexadecimales en números, copiar la marca de tiempo del bloque en cada transacción y registro para que las consultas no necesiten una unión para obtener la hora, y normalizar los identificadores (por ejemplo, convertir las direcciones a minúsculas).
• Carga. Escribir las filas en el almacenamiento, normalmente en archivos columnares Apache Parquet particionados por fecha o rango de bloques, o en tablas de bases de datos. Los formatos de tabla abiertos, como Apache Iceberg y Delta Lake, añaden instantáneas versionadas sobre esa base.

La idea clave es la granularidad: qué representa una fila. Un bloque de Ethereum con 150 transacciones y 400 registros de eventos se convierte en una fila en blocks, 150 en transactions y 400 en logs. La mayoría de los errores de consulta se deben a olvidar la granularidad de una tabla y contar o sumar en el nivel equivocado.

Las canalizaciones se ejecutan como una carga histórica única o como cargas de flujo continuo (incrementales) que siguen la punta de la cadena. Cerca de la punta, una reorganización puede reemplazar bloques, por lo que una canalización espera una profundidad de confirmación o un bloque finalizado, o reescribe los bloques recientes cuando cambia la cadena. Entre los extractores de código abierto se encuentran Ethereum ETL y Bitcoin ETL, del proyecto blockchain-etl, y cryo, que escribe datos de EVM directamente en Parquet. Conviene conocerlos aunque nunca los ejecute: muchos conjuntos de datos públicos se crearon con ellos y heredan sus nombres de columnas.

Tablas sin procesar, decodificadas y depuradas

Cada paso hacia arriba acorta las consultas y añade decisiones tomadas por otra persona. ¿Una tabla depurada token_transfers incluye transferencias ERC-721 o solo tokens fungibles? ¿Las emisiones y quemas aparecen como filas con la dirección cero o se excluyen? ¿El importe está en unidades sin procesar o ya se ha dividido según los decimales? Dos conjuntos de datos que llaman token_transfers a una tabla pueden diferir en los tres aspectos. El hábito de protección es la conciliación: contrastar la tabla depurada con la capa sin procesar en un rango pequeño de bloques.

-- ¿La tabla depurada token_transfers coincide con los registros sin procesar de los que procede?
SELECT 'raw logs' AS layer, COUNT(*) AS n
FROM logs
WHERE topic0 = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
  AND topic3 IS NULL              -- solo transferencias fungibles (de tipo 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 primera mitad cuenta los eventos Transfer sin procesar (la cadena hexadecimal es el hash de la firma del evento, explicado en la lección 2.9); la segunda cuenta las filas depuradas de los mismos 100 bloques. Si los resultados difieren, averigüe por qué antes de usar la tabla: puede omitir algunos tokens, incluir NFT o tener bloques ausentes.

Un esquema genérico para tablas de blockchain

Los conjuntos de datos asignan nombres distintos a las tablas y columnas, por lo que este módulo utiliza un esquema genérico neutral basado en las herramientas ETL de código abierto. Adaptarlo a un conjunto de datos real suele consistir en cambiar nombres.

Convenciones: las direcciones y los hashes son cadenas hexadecimales en minúsculas con el prefijo 0x; los importes (value, amount_raw, precios del gas) son enteros sin procesar en unidades base almacenados como DECIMAL(38,0); las marcas de tiempo están en UTC; status es 1 para éxito y 0 para fallo; gas_used y effective_gas_price en transactions proceden del recibo; block_hash permite que las consultas unan las transacciones con blocks por hash en lugar de por altura, lo que descarta las filas residuales de bloques reemplazados en una reorganización (lección 2.1). labels.grade contiene la calificación de A a D del vínculo entre la dirección y la entidad, donde A es la más sólida (lecciones 3.2 y 4.5), por lo que ordenar por grade de forma ascendente sitúa primero las etiquetas más sólidas. Los ejemplos consultan una cadena a la vez; las tablas que contienen varias cadenas añaden una columna chain_id a cada fila y a cada clave y unión (lección 2.6). Las tablas UTXO inputs y outputs se presentan en la lección 2.9. Los conjuntos de datos reales difieren en detalles que debe comprobar: el conjunto de datos de Ethereum de BigQuery añade un prefijo a los campos del recibo (receipt_status, receipt_gas_used), llama address al contrato del registro y almacena los temas como un arreglo, y algunos conjuntos de datos almacenan los enteros grandes como cadenas.

SELECT, WHERE y ORDER BY

La primera pregunta en la mayoría de los casos es sencilla: ¿qué entró en esta dirección?

-- Transferencias de ETH exitosas enviadas a una dirección en un rango de bloques
SELECT block_number,
       block_timestamp,
       hash,
       from_address,
       value / 1e18 AS value_eth                -- de wei a ETH, para visualización
FROM transactions
WHERE to_address = '0x5e1f…c0de'               -- en minúsculas, como se almacena
  AND block_number BETWEEN 21000000 AND 21050000
  AND status = 1                                -- descartar transacciones fallidas
  AND value > 0
ORDER BY block_number, transaction_index;

Cada filtro importa:

• Mayúsculas y minúsculas. Las direcciones se comparan como cadenas, y las direcciones con suma de verificación combinan mayúsculas y minúsculas: normalice el literal con LOWER() o escríbalo en minúsculas.
• Alcance. Un rango de bloques limita la lectura y hace reproducible la consulta: el mismo rango devolverá las mismas filas el próximo año.
• Estado. Las transacciones fallidas quedan registradas y pagaron comisiones, pero su transferencia de valor se revirtió. Olvidar status = 1 es la forma más habitual de sobreestimar las entradas.
• Valor. Muchas transacciones dirigidas a una dirección llevan cero ETH porque son llamadas a contratos.

Preste atención a los NULL. Una transacción de creación de contrato no tiene destinatario, por lo que su to_address es NULL, y las comparaciones con NULL no son verdaderas ni falsas: to_address <> '0x5e1f…c0de' descarta silenciosamente todas las creaciones de contratos. Escriba to_address IS NULL OR to_address <> … cuando quiera decir "todo lo demás". Del mismo modo, COUNT(to_address) omite los NULL y COUNT(*) no.

Ordene por block_number, transaction_index para seguir el orden de la cadena; todas las transacciones de un bloque comparten una misma marca de tiempo. Esta consulta solo detecta transferencias de ETH de nivel superior; el ETH enviado por contratos aparece en las trazas (lección 2.9).

Unidades y tipos numéricos

Un entero de 64 bits con signo llega hasta aproximadamente 9,22 × 10^18, apenas 9,22 ETH en wei. Almacene y sume los importes como DECIMAL(38,0) o NUMERIC, sume primero los enteros sin procesar y divida una sola vez al final. DECIMAL(38,0) admite valores de hasta aproximadamente 10^38; los pocos que superan ese límite, como una autorización ilimitada de 2^256 − 1, necesitan un NUMERIC sin límite, un tipo de 256 bits o una cadena hexadecimal. Dividir entre 1e18 produce un número de punto flotante: adecuado para visualización, no para conciliación. Preste atención también a la división de enteros: en PostgreSQL, Trino y SQLite, 5 / 2 = 2, mientras que BigQuery y DuckDB devuelven 2,5; multiplique por 1.0 o convierta el tipo al calcular proporciones.

Agrupación por intervalos de tiempo

-- Entradas diarias a la dirección, agrupadas por día 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;

La muestra también contiene una transferencia fallida de 2 ETH el 1 de noviembre; sin status = 1, ese día mostraría 4,25 ETH. El filtro temporal es un intervalo semiabierto (>= inicio, < fin), que nunca cuenta dos veces una fila situada exactamente a medianoche. La función de truncamiento es una diferencia habitual entre dialectos: DATE_TRUNC('day', ts) en PostgreSQL, DuckDB, Trino, Snowflake y Spark SQL; TIMESTAMP_TRUNC(ts, DAY) o DATE(ts) en BigQuery; DATE(ts) en SQLite; toStartOfDay(ts) en ClickHouse. Agrupe en UTC, convierta solo para la presentación y recuerde que las marcas de tiempo de los bloques tienen una resolución de un segundo y, en Bitcoin, pueden desviarse más de una hora (lección 2.1).

Agregación con GROUP BY

GROUP BY reduce las filas a una por grupo; COUNT(*), COUNT(DISTINCT x), SUM, MIN, MAX y AVG resumen cada grupo; HAVING filtra los grupos después de la agregación, al igual que WHERE filtra las filas antes de ella. Los importes de tokens necesitan una unión para obtener los decimales de cada token:

-- Transferencias de tokens desde la dirección, convertidas según los decimales 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 solo por símbolo
ORDER BY transfers DESC;

La clave de agrupación es la dirección del token, no el símbolo. Cualquiera puede desplegar un contrato llamado "USDC": en los datos de muestra, una imitación con 18 decimales aparece junto al token real con 6, y agrupar por símbolo sumaría 1.000 tokens sin valor a 2.000 auténticos y dividiría la mezcla usando una única cantidad de decimales incorrecta. JOIN es una unión interna, por lo que las transferencias de tokens ausentes de tokens desaparecen silenciosamente; la lección 2.8 muestra cómo conservarlas con un LEFT JOIN. Las funciones aproximadas, como APPROX_COUNT_DISTINCT, son adecuadas para paneles, pero no deben proporcionar las cifras de un informe a menos que se indique.

Conjuntos de datos públicos, motores y cuadernos

• Conjuntos de datos públicos de Google BigQuery. crypto_bitcoin, crypto_ethereum y otros, creados con las herramientas blockchain-etl; se facturan según los datos leídos que excedan una cuota gratuita.
• AWS Public Blockchain Data. Archivos Parquet comprimidos en un bucket público, particionados por fecha y actualizados a diario; cualquier motor compatible con Parquet puede leerlos.
• Plataformas de consultas alojadas. Plataformas comunitarias con tablas sin procesar, decodificadas y depuradas, y un nivel gratuito; sus tablas depuradas siguen las reglas de cada proveedor.

Público no significa correcto ni actualizado: los conjuntos de datos llevan un retraso de entre unos minutos y un día, cambian de esquema y ocasionalmente omiten bloques. Examine el perfil de una tabla antes de confiar en ella:

-- Cobertura, actualidad y duplicados
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  -- debe ser igual a row_count
FROM transactions;

En un motor con cobro por datos leídos, ejecútela primero sobre un rango de fechas reciente (lección 3.6).

El núcleo de SQL es compartido, pero los nombres de funciones, el manejo de fechas, los arreglos y la nomenclatura de tablas difieren. Estas tres consultas cuentan los bloques de un día UTC:

-- Conjunto de datos público en BigQuery (proyecto.conjunto_de_datos.tabla):
SELECT COUNT(*) FROM `bigquery-public-data.crypto_ethereum.blocks`
WHERE DATE(timestamp) = '2024-11-01';
-- DuckDB leyendo archivos Parquet directamente:
SELECT COUNT(*) FROM read_parquet('eth/blocks/date=2024-11-01/*.parquet');
-- Cualquier motor que contenga el esquema genérico:
SELECT COUNT(*) FROM blocks WHERE CAST(block_timestamp AS DATE) = DATE '2024-11-01';

Los cuadernos (Jupyter y similares) combinan SQL, Python o R, gráficos y razonamiento escrito en un mismo documento, lo que resulta adecuado para las investigaciones. El trabajo iterativo con grafos, como los componentes conexos para la agrupación, no se adapta bien a SQL; los analistas exportan las aristas a una biblioteca de grafos (lección 2.9).

Elegir herramientas y evitar errores habituales

Sopese la cobertura (cadenas, rango de fechas, trazas, eventos decodificados), la actualidad, el modelo de costos, la reproducibilidad (¿puede fijar una instantánea o una altura de bloque?), la procedencia (¿puede rastrearse cada fila hasta un hash de bloque y un hash de transacción?) y la confidencialidad. Los servicios alojados registran las consultas, y algunas plataformas comunitarias las hacen públicas de forma predeterminada: introducir la dirección de un sospechoso en una plataforma compartida puede revelar una investigación. Para trabajos confidenciales, utilice su propio nodo o archivos descargados y un motor integrado local. Un periodista que verifica una billetera puede utilizar un conjunto de datos público y exportar las filas utilizadas; un exchange que examina depósitos necesita una canalización de flujo continuo hacia una base de datos columnar; un estudio que deba reproducirse con exactitud necesita una instantánea de Parquet fijada y código versionado. Sea cual sea su elección, registre con cada resultado el conjunto de datos, su versión o fecha de instantánea, el texto de la consulta y la hora de ejecución.

Enfoque del examen: espere preguntas que presenten una consulta o un conjunto de datos y pidan identificar qué está mal en el resultado: las etapas de ETL y qué hace la transformación; la granularidad y por qué un bloque se convierte en muchas filas; las capas sin procesar, decodificadas y depuradas y la necesidad de conciliar las tablas depuradas; los principales conjuntos de datos públicos y sus límites; las unidades base y el desbordamiento de 64 bits; excluir las transacciones fallidas del valor, pero no de las comisiones; to_address NULL en las creaciones de contratos; intervalos UTC semiabiertos y diferencias entre dialectos en el truncamiento de fechas; orden de la cadena por número de bloque e índice de transacción; agrupación de tokens por dirección del contrato; confidencialidad de las consultas alojadas y registro de la procedencia.