02 Modelar los datos

Crea tablas tipadas de mercados, ticks, operaciones y agregados de un minuto en ClickHouse Cloud.

Punto de partida

.env.polymarket está cargado y sabes por qué los identificadores de condición y token son distintos.

Las cinco consultas posteriores leen ventanas recientes de todos los mercados observados. Por eso, las claves de las tablas de eventos empiezan por una clave de hora/tiempo, seguida del token o condición empleado para agrupar. Los campos conocidos usan tipos nativos: UInt256 para tokens, DateTime64 para la hora del evento, decimales exactos para precios y tamaños, y enums para valores acotados. El payload opaco de origen permanece como string porque ninguna consulta lee sus campos.

No hay clave de partición. Este taller breve no tiene un límite de retención demostrado; añadir particiones antes de que exista un requisito de ciclo de vida crearía partes pequeñas sin beneficio.

Paso 1 — Crear la base de datos y las tablas sin procesar

Copia el bloque completo en SQL Console de ClickHouse Cloud y ejecútalo:

CREATE DATABASE IF NOT EXISTS polymarket;

CREATE TABLE IF NOT EXISTS polymarket.markets
(
    market_id UInt64,
    condition_id FixedString(66),
    token_id UInt256,
    outcome LowCardinality(String),
    question String,
    slug String,
    active Bool,
    accepting_orders Bool,
    volume_24h Decimal128(8),
    observed_at DateTime64(3, 'UTC')
)
ENGINE = ReplacingMergeTree(observed_at)
ORDER BY (condition_id, token_id);

CREATE TABLE IF NOT EXISTS polymarket.price_ticks
(
    event_id FixedString(64),
    condition_id FixedString(66),
    token_id UInt256,
    event_at DateTime64(3, 'UTC'),
    observed_at DateTime64(3, 'UTC'),
    event_kind Enum8(
        'book_snapshot' = 1,
        'price_change' = 2,
        'last_trade_price' = 3,
        'best_bid_ask' = 4,
        'rest_book' = 5
    ),
    source Enum8('WEBSOCKET' = 1, 'CLOB_REST' = 2, 'FIXTURE' = 3),
    price Decimal64(12),
    size Decimal128(8),
    side Enum8('UNKNOWN' = 0, 'BUY' = 1, 'SELL' = 2),
    best_bid Decimal64(12),
    best_ask Decimal64(12),
    midpoint Decimal64(12),
    source_hash String,
    raw_payload String
)
ENGINE = MergeTree
ORDER BY (toStartOfHour(event_at), token_id, event_at, event_id);

CREATE TABLE IF NOT EXISTS polymarket.trades
(
    trade_id FixedString(64),
    condition_id FixedString(66),
    token_id UInt256,
    event_at DateTime64(3, 'UTC'),
    observed_at DateTime64(3, 'UTC'),
    proxy_wallet FixedString(42),
    side Enum8('UNKNOWN' = 0, 'BUY' = 1, 'SELL' = 2),
    price Decimal64(12),
    size Decimal128(8),
    outcome LowCardinality(String),
    transaction_hash FixedString(66),
    title String
)
ENGINE = ReplacingMergeTree(observed_at)
ORDER BY (toStartOfHour(event_at), condition_id, event_at, trade_id);

CREATE OR REPLACE VIEW polymarket.trades_clean AS
SELECT *
FROM polymarket.trades FINAL;

El colector evita duplicados antes de insertar. ReplacingMergeTree es una segunda protección. La vista trades_clean hace deterministas las consultas pequeñas del taller mientras continúan los merges.

Paso 2 — Crear el agregado del punto medio por minuto

CREATE TABLE IF NOT EXISTS polymarket.market_midpoints_1m
(
    token_id UInt256,
    minute DateTime('UTC'),
    open AggregateFunction(argMin, Decimal64(12), Tuple(DateTime64(3, 'UTC'), FixedString(64))),
    high AggregateFunction(max, Decimal64(12)),
    low AggregateFunction(min, Decimal64(12)),
    close AggregateFunction(argMax, Decimal64(12), Tuple(DateTime64(3, 'UTC'), FixedString(64))),
    updates AggregateFunction(count)
)
ENGINE = AggregatingMergeTree
ORDER BY (minute, token_id);

CREATE MATERIALIZED VIEW IF NOT EXISTS polymarket.market_midpoints_1m_mv
TO polymarket.market_midpoints_1m
AS
SELECT
    token_id,
    toStartOfMinute(event_at) AS minute,
    argMinState(midpoint, tuple(event_at, event_id)) AS open,
    maxState(midpoint) AS high,
    minState(midpoint) AS low,
    argMaxState(midpoint, tuple(event_at, event_id)) AS close,
    countState() AS updates
FROM polymarket.price_ticks
WHERE midpoint > 0
  AND event_kind IN ('book_snapshot', 'price_change', 'best_bid_ask', 'rest_book')
GROUP BY token_id, minute;

Esta vista materializada solo agrega puntos medios de cotización. Excluye deliberadamente el precio modificado en el nivel de orden y el de la última operación, para que la serie OHLC tenga un único significado.

Paso 3 — Verificar todos los objetos

clickhouse client \
  --host "$CLICKHOUSE_HOST" \
  --port "$CLICKHOUSE_PORT" \
  --user "$CLICKHOUSE_USER" \
  --password "$CLICKHOUSE_PASSWORD" \
  --secure \
  --query "SHOW TABLES FROM polymarket"

Los nombres esperados incluyen:

market_midpoints_1m
market_midpoints_1m_mv
markets
price_ticks
trades
trades_clean

Terminado cuando

SHOW TABLES devuelva los seis objetos sin ningún servidor ClickHouse local en ejecución.

Siguiente: iniciar el colector en vivo.

Punto de partida

Por qué estas tablas

Paso 1 — Crear la base de datos y las tablas sin procesar

Paso 2 — Crear el agregado del punto medio por minuto

Paso 3 — Verificar todos los objetos

Terminado cuando

En esta página

¿Quieres seguir tu progreso?