5万
金融サービスにおいて、市場データシステムは通常、リアルタイム層とヒストリカル層の 2 つに分かれています。
リアルタイム層はライブフィードと当日の取引を処理し、マイクロ秒単位での応答が求められるクエリを処理します。一方、ヒストリカル層は過去のすべての取引日の永続アーカイブとして機能し、リサーチ、バックテスト、取引コスト分析、規制対応の取引再現といった用途のクエリに対応します。
また、ヒストリカル層は新しいデータベースを試す場所として最もリスクが低い領域でもあります。実際の取引経路に触れず、マイクロ秒単位のレイテンシも不要で、ワークロードの性質がよく理解されているため(テストが容易です)、検証に適しています。
本記事では、Binance の公開アーカイブから実際のティックデータを取り込み、ClickHouse が安価に保存し、高速にクエリを実行し、トレーディングデスクで重視される VWAP、OHLC ローソク足、ASOF JOIN といった処理に対応できることを示します。
なぜ過去のティッカーデータに ClickHouse なのか？
具体的な実践例に入る前に、ClickHouse がこのワークロードに適している理由を簡単に説明します。理由は大きく 2 点、データの保存方法とクエリの実行方法にあります。
コスト効率
各レコードを個別に保存する行指向データベースとは異なり、ClickHouse はデータをカラムごとに保存し、同じカラムの値をディスク上でまとめて格納します。分析ワークロードにおいて、この仕組みはストレージ効率に大きなメリットをもたらします。
類似したデータを隣接して保存することで、データが占める領域を圧縮技術によって削減できます。例として以下が挙げられます。
- 辞書エンコーディング: カラムのカーディナリティが低い場合に容量を節約できます。同じ文字列を何千回も保存する代わりに、一意な値の辞書と行ごとの整数参照を保存します。
- デルタエンコーディング: タイムスタンプや、隣接する値が単調増加し差分が小さいあらゆるカラムで有効です。毎回完全な値を保存するのではなく、直前の値との差分のみを保存します。
- 連長圧縮(ランレングスエンコーディング): 連続する同一の値を、1 つの値と出現回数のペアに置き換えます。
ティックデータはまさにこれらの特性を備えているため、特に優れた圧縮効果が得られます。規則的かつわずかな刻みで増加するタイムスタンプ、緩やかに変動する価格、数百万回繰り返されるシンボル、単調増加する取引 ID などです。
これらの特殊なエンコーディングに加え、類似した値をまとめて保存することで、ZSTD や LZ4 といった汎用圧縮アルゴリズムが利用しやすい反復的なバイトパターンが生まれます。これらがすべてのカラムに適用され、データ保存容量が削減されます。
クエリの扱いやすさ
過去のティックデータに ClickHouse を検討すべき 2 つ目の理由は、クエリの容易さです。ClickHouse は SQL を採用しています。SQL はデータエンジニアリング、分析、ビジネスインテリジェンスの共通言語であり、2025年の Stack Overflow Developer Survey によると、開発者の 58.6% が使用しています。
専門的な独自言語を学ぶ必要も、限られた人材プールから採用する必要も、クエリを書ける担当者にすべての質問を集約させてボトルネックを作ることもありません。この点は、以下の実践例で実際に確認できます。
実践例: 過去のティッカーデータに ClickHouse を活用する
それでは、ClickHouse を使って過去のティッカーデータをクエリする実践例を見ていきましょう。 ここでは、数十億件のレコードを含み、無償で再配布が認められている Binance の公開市場データアーカイブ を使用します。
clickhouse-local による 1 か月分のデータ処理
まずは手元のコンピューターで実行できる例から始めます。取引が最も活発な 5 つのシンボル(BTC、ETH、SOL、BNB、XRP)を対象とした、USD-M 無期限先物の 1 か月分(2024年1月)のデータです。
これだけで数億件の約定データ(trades)と約 20 億件の気配値データ(quotes)が得られます。このデータは、合計 21 GB・155 個の ZIP ファイルで構成されており、スクリプトを使ってダウンロードできます。
約定データ(Trades)
まずは約定データから見ていきます。約定とは成立した 1 件の取引であり、買い手と売り手が価格に合意し、数量が受け渡された瞬間を指します。
各約定には、シンボル、タイムスタンプ(ミリ秒単位)、受け渡された価格と数量が含まれます。さらに、取引の想定元本である quote_qty(USDT 建ての価格 × 数量)、単調増加する一意の取引 ID、そしてどちら側がアグレッサー(テイカー)であったかを示す is_buyer_maker フラグも含まれます。
CREATE TABLE trades
(
symbol LowCardinality(String),
ts DateTime64(3) CODEC(DoubleDelta, ZSTD(1)),
price Decimal(12, 2) CODEC(ZSTD(1)),
qty Decimal(16, 6) CODEC(ZSTD(1)),
quote_qty Decimal(20, 6) CODEC(ZSTD(1)),
trade_id UInt64 CODEC(DoubleDelta, ZSTD(1)),
is_buyer_maker Boolean CODEC(ZSTD(1))
)
ENGINE = MergeTree
ORDER BY (symbol, ts);次に、約定データをテーブルに取り込みます。
INSERT INTO trades SELECT
splitByChar('-', _file)[1] AS symbol,
fromUnixTimestamp64Milli(time),
price, qty, quote_qty, id, is_buyer_maker
FROM file('data/*-trades-*.zip :: *.csv', CSVWithNames);ClickHouse バージョン 23.8 から導入された、アーカイブファイルを直接クエリする機能 を利用している点にご注目ください。このデータの取り込みは 90 秒弱で完了します。
373502424 rows in set. Elapsed: 81.706 sec. Processed 373.50 million rows, 19.38 GB (4.57 million rows/s., 237.17 MB/s.)
Peak memory usage: 226.02 MiB.約定データのロードが完了したので、単一取引日における全シンボルの出来高加重平均価格(VWAP)を計算してみましょう。
VWAP は、各価格帯での取引出来高で加重平均した資産の平均取引価格です。トレーディングデスクで最も頻繁に用いられるベンチマークの 1 つであり、ファンドはこれをもとに約定品質を評価し、アルゴリズムはこれに追従するよう取引し、「その日のこの資産の適正価格はいくらだったか」に対する標準的な回答となります。
VWAP は、すべての取引の想定元本(価格 × 数量である quote_qty)の合計を、取引された総数量で割ることで算出します。以下のクエリでは、シンボルを取引数量(ユニット数)ではなく、取引された想定元本(実際に動いた金額)の順に並べています。
WITH
sum(quote_qty) AS raw_volume_usdt,
sum(qty) AS raw_volume_base
SELECT
symbol,
round(sum(quote_qty) / sum(qty), 2) AS vwap,
formatReadableQuantity(raw_volume_base) AS volume_base,
formatReadableQuantity(raw_volume_usdt) AS volume_usdt,
bar(raw_volume_usdt, 0, 13440000000, 20) AS chart
FROM trades
WHERE toDate(ts) = '2024-01-31'
GROUP BY symbol
ORDER BY raw_volume_usdt DESC;┌─symbol──┬─────vwap─┬─volume_base─────┬─volume_usdt────┬─chart────────────────┐
│ BTCUSDT │ 42940.53 │ 313.07 thousand │ 13.44 billion │ ████████████████████ │
│ ETHUSDT │ 2314.4 │ 2.61 million │ 6.04 billion │ ████████▉ │
│ SOLUSDT │ 99.82 │ 36.56 million │ 3.65 billion │ █████▍ │
│ XRPUSDT │ 0.5 │ 1.47 billion │ 740.63 million │ █ │
│ BNBUSDT │ 303.86 │ 883.20 thousand │ 268.37 million │ ▍ │
└─────────┴──────────┴─────────────────┴────────────────┴──────────────────────┘
5 rows in set. Elapsed: 0.126 sec. Processed 12.36 million rows, 308.92 MB (97.74 million rows/s., 2.44 GB/s.)
Peak memory usage: 621.91 KiB.取引数量の面では XRP が最も活発ですが、1 単位あたりわずか 0.50 ドルの価値しかないため、取引金額ベースでは 4 位にとどまります。BTC は取引数量こそ最も少ないものの、134 億ドルと他を圧倒しています。
次に、指定したシンボルの特定の日における OHLC(始値・高値・安値・終値)ローソク足を取得するクエリを作成します。以下のクエリは、2024年1月16日の Bitcoin について 1 時間ごとの値を算出します。
SELECT toStartOfInterval(ts, INTERVAL 1 HOUR)::Time AS bucket,
argMin(price, ts) AS open,
max(price) AS high,
min(price) AS low,
argMax(price, ts) AS close,
round(sum(qty), 2) AS volume
FROM trades
WHERE symbol = 'BTCUSDT' AND toDate(ts) = '2024-01-16'
GROUP BY bucket
ORDER BY bucket;┌───bucket─┬────open─┬────high─┬─────low─┬───close─┬───volume─┐
│ 00:00:00 │ 42515 │ 42679 │ 42466.1 │ 42604.7 │ 4807.99 │
│ 01:00:00 │ 42604.6 │ 42733.7 │ 42556.6 │ 42591.7 │ 4728.84 │
│ 02:00:00 │ 42591.7 │ 42732.8 │ 42552 │ 42702.1 │ 5519.02 │
│ 03:00:00 │ 42702.1 │ 42942 │ 42695.7 │ 42921.2 │ 7032.13 │
│ 04:00:00 │ 42919.3 │ 42945 │ 42759.9 │ 42805.2 │ 5516.79 │
│ 05:00:00 │ 42805.2 │ 42932.5 │ 42759 │ 42765.3 │ 4148.77 │
│ 06:00:00 │ 42765.4 │ 42779.1 │ 42648.3 │ 42744.4 │ 5399.94 │
│ 07:00:00 │ 42744.5 │ 42788.1 │ 42600.5 │ 42788 │ 4965.14 │
│ 08:00:00 │ 42788.5 │ 43029.7 │ 42718.8 │ 43021.5 │ 8975.58 │
│ 09:00:00 │ 43021.5 │ 43164.9 │ 42899.6 │ 42937 │ 12361.57 │
│ 10:00:00 │ 42937.1 │ 42969.1 │ 42850.1 │ 42870 │ 4996.4 │
│ 11:00:00 │ 42870 │ 42944 │ 42753.6 │ 42895.3 │ 5799.53 │
│ 12:00:00 │ 42894.6 │ 43170 │ 42854.7 │ 43160.1 │ 10657.02 │
│ 13:00:00 │ 43160.1 │ 43181.7 │ 42705.6 │ 42805.7 │ 13452.14 │
│ 14:00:00 │ 42805.6 │ 42954.9 │ 42050 │ 42700.1 │ 55063.01 │
│ 15:00:00 │ 42700.1 │ 43333 │ 42545.6 │ 43251.7 │ 42560.65 │
│ 16:00:00 │ 43250.6 │ 43450 │ 43003.2 │ 43249.8 │ 24381.08 │
│ 17:00:00 │ 43249.8 │ 43249.8 │ 43027.6 │ 43046.6 │ 8896.75 │
│ 18:00:00 │ 43051.4 │ 43159.9 │ 42825.5 │ 43008.8 │ 10532.47 │
│ 19:00:00 │ 43008.8 │ 43182.3 │ 42940.8 │ 43164.8 │ 6539.24 │
│ 20:00:00 │ 43164.8 │ 43376.2 │ 43061.7 │ 43195.9 │ 13191 │
│ 21:00:00 │ 43195.9 │ 43589 │ 43181.6 │ 43427.5 │ 14845.85 │
│ 22:00:00 │ 43427.4 │ 43499.2 │ 43200 │ 43208.6 │ 6772.76 │
│ 23:00:00 │ 43208.6 │ 43285 │ 43105.2 │ 43133 │ 6063.55 │
└──────────┴─────────┴─────────┴─────────┴─────────┴──────────┘
24 rows in set. Elapsed: 0.041 sec. Processed 3.62 million rows, 90.24 MB (87.39 million rows/s., 2.18 GB/s.)
Peak memory usage: 5.28 MiB.以下は、このデータを可視化した plot.ly によるチャートです。
価格は 1 日を通して上昇基調にあり、始値の 42,515 ドルから終値の 43,133 ドルへと、日中で 1.5% の上昇を記録しました。
最も注目すべきは 14 時台です。この 1 時間の間に価格は一時 42,050 ドルまで急落し(開始時点から約 750 ドル下落)、その後持ち直しました。取引も異例の活発さを見せ、通常の 1 時間あたり出来高の 5 倍を超える 55,063 BTC が取引されました。
大量の売り手によって価格は急激に押し下げられたものの、十分な買い手が現れたことで同一時間内に価格が押し戻された格好です。ただし、この 1 時間のどの時点で売りが最も集中したのかは、ここからは分かりません。
そこで次に、それを詳しく見ていきます。以下のクエリは、14:00 から 15:00 までの間で価格が最も安値を付けた 1 分間のインターバルを特定します。
SELECT
toStartOfMinute(ts) AS minute, count() AS trades,
round(min(price), 2) AS low, round(max(price), 2) AS high,
round(sum(qty), 3) AS volume
FROM trades
WHERE symbol = 'BTCUSDT'
AND ts >= '2024-01-16 14:00:00'
AND ts < '2024-01-16 15:00:00'
GROUP BY minute
ORDER BY low ASC
LIMIT 10;┌──────────────minute─┬─trades─┬─────low─┬────high─┬───volume─┐
│ 2024-01-16 14:44:00 │ 17191 │ 42050 │ 42167 │ 2494.94 │
│ 2024-01-16 14:45:00 │ 16390 │ 42090.2 │ 42212.8 │ 2053.689 │
│ 2024-01-16 14:43:00 │ 15723 │ 42115 │ 42244.4 │ 1914.875 │
│ 2024-01-16 14:46:00 │ 16682 │ 42143.2 │ 42275.2 │ 2163.598 │
│ 2024-01-16 14:42:00 │ 18611 │ 42152 │ 42273.4 │ 2241.841 │
│ 2024-01-16 14:47:00 │ 9863 │ 42175 │ 42278.6 │ 1106.466 │
│ 2024-01-16 14:48:00 │ 6575 │ 42187.3 │ 42265.3 │ 793.072 │
│ 2024-01-16 14:41:00 │ 10948 │ 42243.2 │ 42341.2 │ 1268.806 │
│ 2024-01-16 14:40:00 │ 15944 │ 42247.5 │ 42389.5 │ 1858.954 │
│ 2024-01-16 14:49:00 │ 9880 │ 42256.9 │ 42321.9 │ 1225.524 │
└─────────────────────┴────────┴─────────┴─────────┴──────────┘
10 rows in set. Elapsed: 0.017 sec. Processed 524.29 thousand rows, 12.69 MB (30.45 million rows/s., 736.98 MB/s.)
Peak memory usage: 62.55 KiB.投げ売りは一瞬で終わったわけではありませんでした。14:42 から 14:46 までの 5 分間はいずれも取引が活発で価格変動が大きく、売り圧力が強まり、その後徐々に和らいでいった様子が見て取れます。しかし、最も激しかったのは 14:44 でした。価格はこの時間帯の最安値(42,050 ドル)を付け、60 秒間で 2,494 BTC が取引されました。
しかし、発生したタイミングが分かるだけでは十分ではありません。1 時間単位のデータでは、それがどのように起きたのかまでは分かりません。買い手がほとんど存在しなかったためにわずかな売りでも価格が下落したのか、あるいは強い下押し圧力の下でも売り買い双方が実際に活発に取引され、価格を支えていたのか。
これに答えるには、各約定を個別に、かつ約定時点の市場価格と照らし合わせて確認する必要があります。
気配値データ(Quotes)
trades テーブルが実際に発生した取引を記録するのに対し、quotes テーブルは各瞬間において取引可能だった価格を記録します。各レコードは、オーダーブック(気配値情報)が更新されたすべての瞬間における最良買気配値(買い手が提示した最高価格、ベストビッド)と最良売気配値(売り手が提示した最低価格、ベストアスク)を捉えています。
このテーブルには、最良買気配値で購入を希望する数量である bid_qty や、最良売気配値で売却を希望する数量である ask_qty も含まれます。symbol と ts はその名のとおりです。update_id は単調増加する整数値です。
最良買気配値または最良売気配値が変動するたび(注文の提示、キャンセル、約定など)、Binance は quotes テーブルに新しいレコードを追加します。
quotes テーブルのスキーマは以下のとおりです。
CREATE TABLE quotes
(
symbol LowCardinality(String),
ts DateTime64(3) CODEC(DoubleDelta, ZSTD(1)),
bid_price Decimal(12, 4) CODEC(ZSTD(1)),
bid_qty Decimal(16, 3) CODEC(ZSTD(1)),
ask_price Decimal(12, 4) CODEC(ZSTD(1)),
ask_qty Decimal(16, 3) CODEC(ZSTD(1)),
update_id UInt64 CODEC(DoubleDelta, ZSTD(1))
)
ENGINE = MergeTree
ORDER BY (symbol, ts);データの取り込みには以下のクエリを使用します。
INSERT INTO quotes SELECT
splitByChar('-', _file)[1] AS symbol,
fromUnixTimestamp64Milli(transaction_time),
best_bid_price, best_bid_qty, best_ask_price, best_ask_qty, update_id
FROM file('data/*-bookTicker-*.zip :: *.csv', CSVWithNames);20 億件弱のレコードの取り込みには、約 8 分かかります。
1997061823 rows in set. Elapsed: 509.946 sec. Processed 2.00 billion rows, 185.91 GB (3.92 million rows/s., 364.57 MB/s.)
Peak memory usage: 273.32 MiB.気配値データのロードが完了したため、2024年1月16日 14:44 の約定状況の調査を再開できます。
この分析には ASOF JOIN を使用します。これは、各約定をそれが起きた瞬間に利用可能だった直近の気配値データとマッチングする結合です。これにより、約定価格だけでなく、まさにその瞬間に市場で提示されていた気配価格も把握できます。
このクエリは、該当の 1 分間を 1 秒ごとのスナップショットに分割し、各秒における取引件数、価格範囲、総出来高、平均スリッページを表示します。
スリッページは、実際の約定価格が気配価格からどれだけ乖離したかを測定する指標です。大量の売り注文が買い注文を圧倒すると、取引は次第に不利な価格で成立するようになり、スリッページが急上昇します。売りが落ち着き買い手が戻ると、スリッページは再び低下します。
SELECT
toStartOfSecond(t.ts) AS second, count() AS trades,
round(min(t.price), 2) AS low, round(max(t.price), 2) AS high,
round(sum(t.qty), 3) AS volume,
round(avg(abs(t.price - (q.bid_price + q.ask_price) / 2)), 4) AS avg_slippage
FROM (
SELECT ts, price, qty, symbol
FROM trades
WHERE symbol = 'BTCUSDT'
AND ts >= '2024-01-16 14:44:00' AND ts < '2024-01-16 14:45:00'
) AS t
ASOF JOIN (
SELECT ts, bid_price, ask_price, symbol
FROM quotes
WHERE symbol = 'BTCUSDT'
AND ts >= '2024-01-16 14:44:00' AND ts < '2024-01-16 14:45:00'
) AS q
ON t.symbol = q.symbol AND t.ts >= q.ts
GROUP BY second
ORDER BY second;┌──────────────────second─┬─trades─┬─────low─┬────high─┬──volume─┬─avg_slippage─┐
│ 2024-01-16 14:44:00.000 │ 172 │ 42163.5 │ 42167 │ 94.074 │ 0.1831 │
│ 2024-01-16 14:44:01.000 │ 131 │ 42157.6 │ 42166.9 │ 16.363 │ 0.1912 │
│ 2024-01-16 14:44:02.000 │ 557 │ 42138.3 │ 42157.7 │ 44.769 │ 0.2707 │
│ 2024-01-16 14:44:03.000 │ 296 │ 42135 │ 42147.2 │ 37.372 │ 0.81 │
│ 2024-01-16 14:44:04.000 │ 190 │ 42135.5 │ 42149.7 │ 18.159 │ 0.3334 │
│ 2024-01-16 14:44:05.000 │ 221 │ 42139.5 │ 42166.8 │ 25.645 │ 0.6104 │
│ 2024-01-16 14:44:06.000 │ 136 │ 42155.5 │ 42166.8 │ 10.639 │ 0.1783 │
│ 2024-01-16 14:44:07.000 │ 90 │ 42147 │ 42155.5 │ 5.536 │ 0.2694 │
│ 2024-01-16 14:44:08.000 │ 259 │ 42136 │ 42150.4 │ 20.764 │ 0.873 │
│ 2024-01-16 14:44:09.000 │ 83 │ 42146.6 │ 42152.1 │ 10.873 │ 0.2054 │
│ 2024-01-16 14:44:10.000 │ 221 │ 42135 │ 42146.7 │ 32.567 │ 0.6351 │
│ 2024-01-16 14:44:11.000 │ 212 │ 42123.3 │ 42135.1 │ 15.032 │ 0.3255 │
│ 2024-01-16 14:44:12.000 │ 1162 │ 42106 │ 42138.8 │ 319.489 │ 3.973 │
│ 2024-01-16 14:44:13.000 │ 2529 │ 42055.2 │ 42116.2 │ 324.301 │ 1.8386 │
│ 2024-01-16 14:44:14.000 │ 1231 │ 42055.3 │ 42091.9 │ 170.522 │ 1.9567 │
│ 2024-01-16 14:44:15.000 │ 1990 │ 42050 │ 42116.5 │ 304.494 │ 13.0489 │
│ 2024-01-16 14:44:16.000 │ 511 │ 42071 │ 42092.1 │ 57.994 │ 1.3883 │
│ 2024-01-16 14:44:17.000 │ 370 │ 42070.5 │ 42101.1 │ 69.605 │ 3.0209 │
│ 2024-01-16 14:44:18.000 │ 240 │ 42094.2 │ 42108 │ 24.206 │ 1.13 │
│ 2024-01-16 14:44:19.000 │ 176 │ 42081.6 │ 42095 │ 19.469 │ 1.3463 │
│ 2024-01-16 14:44:20.000 │ 209 │ 42081.6 │ 42100 │ 19.652 │ 0.8911 │
│ 2024-01-16 14:44:21.000 │ 231 │ 42087.3 │ 42104.4 │ 26.879 │ 1.082 │
│ 2024-01-16 14:44:22.000 │ 90 │ 42087.3 │ 42088.8 │ 5.938 │ 0.0767 │
│ 2024-01-16 14:44:23.000 │ 235 │ 42089.5 │ 42100.1 │ 26.002 │ 1.7451 │
....
│ 2024-01-16 14:44:57.000 │ 453 │ 42095 │ 42114.4 │ 56.588 │ 2.7029 │
│ 2024-01-16 14:44:58.000 │ 169 │ 42100.4 │ 42110 │ 13.492 │ 0.6219 │
│ 2024-01-16 14:44:59.000 │ 151 │ 42101.8 │ 42107.4 │ 6.062 │ 1.2255 │
└──────────────────second─┴─trades─┴─────low─┴────high─┴──volume─┴─avg_slippage─┘
60 rows in set. Elapsed: 0.053 sec. Processed 155.65 thousand rows, 2.36 MB (2.93 million rows/s., 44.38 MB/s.)
Peak memory usage: 1.92 MiB.結果を見ると、この 1 分間の中に明確な 3 つのフェーズがあったことが分かります。これは以下のビジュアライゼーションで見るとより明確です。
前兆フェーズ(00-11 秒): 価格は 1 分間の開始時点の 42,167 ドルから 11 秒までに 42,123 ドルへと下落していますが、市場はまだ正常に機能しています。取引件数は控えめで(毎秒 83〜557 件)、出来高も低く、平均スリッページは 0.18〜0.87 ドルにとどまります。売り手が優勢であるものの、流動性は保たれています。
急落フェーズ(12-15 秒): わずか 4 秒で状況が一変します。取引件数が爆発的に増加し、毎秒 1,162 件、2,529 件、1,231 件、1,990 件に達します。出来高も毎秒 300 BTC 以上へと急増します。価格は 42,138 ドルから 42,050 ドルへと崩壊します。そして 15 秒時点では、平均スリッページがベースラインである 0.5 ティックの 260 倍に相当する 13.05 ドルに達します。オーダーブックの板が払底し、買い手は約定のためならいくらでも支払う状態に陥っています。
回復フェーズ(16 秒以降): 崩壊が早かったのと同様に、市場の安定も速やかでした。22 秒までにスリッページは 0.08 ドルに戻り、30 秒までに取引件数も通常レベルへ落ち着きます。価格は 42,095〜42,105 ドル付近で下げ止まり、その分の残りの時間はその水準を維持しました。事象全体は 15 秒間で収束しています。
他の分についても同様の現象が起きていたかドリルダウンして調査することもできますが、それは読者への演習課題として残しておきます。
使用ストレージ容量
分析をさらに進める前に、これらの全データが実際にどれほどの容量を消費しているかを確認しておきましょう。
1 か月分の圧縮前 ZIP ファイルのサイズは 21 GiB ですが、解凍後は合計 191 GiB になります。ClickHouse 内の各テーブルが使用しているディスク容量は、以下のクエリで確認できます。
SELECT table,
formatReadableSize(sum(bytes_on_disk)) AS on_disk
FROM system.parts
WHERE active AND database = currentDatabase() AND table IN ('trades', 'quotes')
GROUP BY table;┌─table──┬─on_disk───┐
│ quotes │ 7.85 GiB │
│ trades │ 2.06 GiB │
└────────┴───────────┘取引が最も活発な 5 つの先物ペアに関する 1 か月分の約定および気配値データは、ディスク上でわずか 10 GiB に圧縮され、展開後の raw CSV ファイル(191 GiB)から 19 分の 1 に削減されています。
気配値データは約定データよりも圧縮率が高くなっています。これは買気配値と売気配値の変動が緩やかで予測しやすいため、コーデックが効率的に機能した結果です。
trades テーブルのカラムごとの内訳は、以下のクエリで確認できます。
SELECT name,
formatReadableSize(sum(data_compressed_bytes)) AS on_disk,
round(sum(data_uncompressed_bytes)
/ sum(data_compressed_bytes), 1) AS ratio
FROM system.columns
WHERE database = currentDatabase()
AND table = 'trades'
GROUP BY name
ORDER BY sum(data_compressed_bytes) DESC;┌─name───────────┬─on_disk────┬─ratio─┐
│ quote_qty │ 1.11 GiB │ 5 │
│ qty │ 628.66 MiB │ 4.5 │
│ ts │ 175.32 MiB │ 16.2 │
│ price │ 115.87 MiB │ 24.6 │
│ is_buyer_maker │ 32.06 MiB │ 11.1 │
│ trade_id │ 9.75 MiB │ 292.1 │
│ symbol │ 1.70 MiB │ 209.9 │
└────────────────┴────────────┴───────┘ClickHouse の各種コーデックがデータを効果的に圧縮していることが分かります。
symbol には 5 つの一意な値しか存在しないため、LowCardinality によって容易に圧縮されます。LowCardinality はその 5 つの値のみを保持する辞書を保持し、各行の文字列を整数参照に置き換えます。
タイムスタンプはソートされ均等な間隔で並んでおり、trade_id は単調増加するカウンターであるため、どちらも DoubleDelta 圧縮に最適です。
圧縮率が最も低い 2 つのカラムは qty と quote_qty です。これらは実質的にランダムな値であるため、コーデックによる効果はあまり得られません。quote_qty は qty と price から算出できるため、容量をさらに削減したい場合は、他の 2 つのカラムからオンデマンドで計算する設計も考えられます。
ClickHouse Cloud による約 1 年分のデータ処理
ここからは規模を(ほぼ)1 年分のデータへとスケールアップし、ローカル PC の代わりに、1 レプリカ、64 GiB RAM、16 vCPU 構成の ClickHouse Cloud サービスを使用します。
2023年5月から2024年3月までのデータをロードします。丸 1 年分を対象としたかったところですが、気配値データの公開が2024年3月で終了しているためです。
10 か月分の ZIP ファイルの容量は 162 GiB であり、ローカルファイル解凍時に確認した 9 倍の展開倍率を適用すると、非圧縮時でおよそ 1.46 TB に相当します。
Python クライアントを使用して、1 日ごと・1 シンボルごとにデータをロードするスクリプトを用意しました。16 本の INSERT 文を並列実行することで、約定データに 886 秒、気配値データに 1,972 秒でロードが完了しました。
データロード後、以下のクエリを実行してデータ件数と消費容量を確認できます。
SELECT table, sum(rows) AS rows,
formatReadableSize(sum(bytes_on_disk)) AS on_disk
FROM system.parts
WHERE active AND table IN ('trades', 'quotes')
GROUP BY table;┌─table──┬────────rows─┬─on_disk───┐
│ quotes │ 13062612165 │ 58.20 GiB │
│ trades │ 3131315443 │ 18.61 GiB │
└────────┴─────────────┴───────────┘取引が最も活発な暗号資産先物 5 ペアの 10 か月分のティックデータは、合計 162 億行に達しますが、圧縮後のディスク容量はわずか 76 GiB に収まっています。ここでも、raw CSV ファイルの容量と比較して約 19 分の 1 に削減されています。
ClickHouse Cloud のストレージは 1 TB/月あたり 25.30 ドルで課金されるため、この 10 か月分のティックデータのストレージコストは月額 3.29 ドルとなります。
$25.30 × (133 / 1024) = $25.30 × 0.07421875 = $1.88/月
ローカルで実行したクエリは、データセットが大幅に大きくなった後でもほぼ同じ所要時間で完了します。これはプライマリインデックスによって不要なデータが適切に除外されるためです。
最初の ASOF JOIN クエリの実行計画(クエリプラン)を確認すると、その理由がよく分かります。
┌─explain─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ Expression ((Project names + (Before ORDER BY + Projection) [lifted up part])) │
│ Sorting (Sorting for ORDER BY) │
│ Expression ((Before ORDER BY + Projection)) │
│ Aggregating │
│ Expression (Before GROUP BY) │
│ Expression (Post Join Actions) │
│ Join (JOIN FillRightFirst) │
│ Expression (Left Pre Join Actions) │
│ Expression ((Change column names to column identifiers + (Project names + Projection))) │
│ Expression ((WHERE + Change column names to column identifiers)) │
│ ReadFromMergeTree (default.trades) │
│ Indexes: │
│ PrimaryKey │
│ Keys: │
│ symbol │
│ ts │
│ Condition: and((ts in (-Inf, '1705416300')), and((ts in ['1705416240', +Inf)), (symbol in ['BTCUSDT', 'BTCUSDT']))) │
│ Parts: 2/3 │
│ Granules: 4/383083 │
│ Search Algorithm: binary search │
│ Ranges: 2 │
│ Expression (Right Pre Join Actions) │
│ Expression ((Change column names to column identifiers + (Project names + Projection))) │
│ Expression ((WHERE + Change column names to column identifiers)) │
│ ReadFromMergeTree (default.quotes) │
│ Indexes: │
│ PrimaryKey │
│ Keys: │
│ symbol │
│ ts │
│ Condition: and((ts in (-Inf, '1705416300')), and((ts in ['1705416240', +Inf)), (symbol in ['BTCUSDT', 'BTCUSDT']))) │
│ Parts: 9/9 │
│ Granules: 13/1597699 │
│ Search Algorithm: binary search │
│ Ranges: 9 │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘注目すべきは 20 行目と 34 行目です。20 行目では、クエリエンジンは trades テーブルの 383,083 グラニュールのうちわずか 4 グラニュールしか読み取っておらず、34 行目では quotes テーブルの 1,597,699 グラニュールのうち 13 グラニュールしか読み取っていません。
1 グラニュールあたり 8,192 行であるため、trades テーブルの約 33,000 行(0.001%)と quotes テーブルの約 106,000 行(0.0008%)をスキャンするだけで済みます。
その数行上の条件式が、読み取るべきグラニュールをどのように特定したかを示しています。
Condition: and((ts in (-Inf, '1705416300')), and((ts in ['1705416240', +Inf)), (symbol in ['BTCUSDT', 'BTCUSDT'])))
主キー (symbol, ts) はディスク上でソートされています。ClickHouse はスパースインデックスに対して二分探索を実行し、symbol = BTCUSDT かつ指定した 60 秒の範囲内に ts が収まるグラニュールを正確に特定し、それ以外のすべてをスキップします。
まとめ
ClickHouse が過去のティックデータをいかに効率的に処理できるかをご理解いただけたかと思います。
取引の多い主要先物 5 ペアの 10 か月分に及ぶ約定および気配値データ(162 億行)は、ディスク上で 76 GiB に圧縮され、ClickHouse Cloud 上でのコストは月額 1.88 ドルに抑えられます。これを可能にしているコーデックは、ソートされたタイムスタンプ、緩やかに変動する価格、低カーディナリティのシンボルといったティックデータの構造に最適化されています。
そして、その分析はすべて標準の SQL で完結します。トレーディングデスクが日常的に実行する処理(VWAP、OHLC ローソク足、ASOF JOIN)はいずれも標準的なクエリであり、アナリストであれば誰でも直感的に作成、読解、拡張できます。
データセットが 160 億行規模に達しても、これらのクエリのレイテンシは変わりません。各クエリがシンボルと時間でフィルタリングを行うため、プライマリインデックスによって 1 行も読み込むことなくデータの大部分がスキップされます。
これらのディメンションでフィルタリングしないクエリではより多くのデータがスキャンされますが、「このシンボルの、この時間帯」を対象とする一般的なティックデータのワークロードにおいて、インデックスは期待どおりの性能を発揮します。
