02 データをモデリングする

ClickHouse Cloud に型付きの market、tick、trade、1 分足集計テーブルを作成します。

開始条件

.env.polymarket を source 済みで、コンディション ID とトークン ID の違いを理解しています。

このあとの 5 つのクエリは、監視対象のすべての市場について直近の時間窓を読みます。したがってイベントテーブルのキーは時刻/時間のキーから始まり、その後にグルーピングで使うトークンまたはコンディションが続きます。既知のフィールドはネイティブ型を使います。トークン ID は UInt256、イベント時刻は DateTime64、価格とサイズは正確な decimal、値域が限られたイベント値は enum です。ソースの不透明なペイロードは、どのクエリもそのフィールドを読まないため文字列のままにします。

パーティションキーはありません。この短命なワークショップには実証された保持境界がなく、ライフサイクル要件より先にパーティションを追加すると、利点なしに小さなパートを作るだけになります。

ステップ 1 — データベースと生データのテーブルを作成する

ブロック全体を ClickHouse Cloud の SQL コンソールに貼り付けて実行します:

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;

collector は挿入前に重複を防ぎます。ReplacingMergeTree は 2 番目の安全網です。trades_clean ビューは、マージが進行中でもワークショップの小さなクエリを決定的にします。

ステップ 2 — 1 分足の中間値集計を作成する

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;

この materialized view が集計するのはクォート中間値だけです。変更された注文レベルの価格や直近取引価格は意図的に除外しており、OHLC 系列の意味が 1 つに保たれます。

ステップ 3 — すべてのオブジェクトを検証する

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

期待される名前には次が含まれます:

market_midpoints_1m
market_midpoints_1m_mv
markets
price_ticks
trades
trades_clean

完了条件

ローカルの ClickHouse サーバーを一切動かさずに、SHOW TABLES が 6 つのオブジェクトすべてを返す。

次: ライブ collector を起動する。

開始条件

なぜこのテーブル構成なのか

ステップ 1 — データベースと生データのテーブルを作成する

ステップ 2 — 1 分足の中間値集計を作成する

ステップ 3 — すべてのオブジェクトを検証する

完了条件

このページの内容

Track your progress?