clickhouse-architect

ClickHouse アーキテクト

ClickHouse（v24.4 以降）向けの、推奨スキーマ設計、圧縮方式の選定、パフォーマンス最適化を扱います。ClickHouse Cloud（SharedMergeTree）とセルフホスト環境（ReplicatedMergeTree）の両方を対象とします。

自己進化型スキル：このスキルは使用を通じて改善されます。指示に誤りがある場合、パラメーターにずれが生じた場合、または回避策が必要だった場合は、先送りせず、ただちにこのファイルを修正してください。実際に再現可能な問題がある場合にのみ更新してください。

このスキルを使用する場面

次の場合にこのスキルを使用してください。

• ORDER BY キーの選定を含む ClickHouse テーブルスキーマの設計
• 列の型に応じた圧縮コーデックの選定
• データライフサイクル管理のためのパーティションキーの設定
• パフォーマンスを高める機能（プロジェクション、インデックス、辞書）の追加
• 既存の ClickHouse スキーマの監査と最適化

基本的な方法

スキーマ設計のワークフロー

ClickHouse スキーマを設計またはレビューする際は、次の順序に従ってください。

1. ORDER BY キーを定義する（列は 3～5 個、カーディナリティの低い順）
2. 列の型ごとに圧縮コーデックを選択する
3. データライフサイクル管理のために PARTITION BY を設定する
4. パフォーマンスを高める機能を追加する（プロジェクション、インデックス）
5. 監査クエリで検証する（scripts/ を参照）
6. COMMENT 文でドキュメント化する — ClickHouse のテーブルと列の COMMENT は、各列の意味、計算方法、適用される制約を示す**信頼できる唯一の情報源（SSoT）**です。COMMENT より優先される外部ドキュメント、スキル、Wiki はありません。references/schema-documentation.md を参照してください。

ORDER BY キーの選定

ORDER BY 句は、ClickHouse のスキーマ設計で最も重要な決定事項です。

ルール：

• 列は最大 3～5 個に制限します（列を追加するごとに効果は逓減します）
• カーディナリティの低い列を先に配置します（例：timestamp より前に tenant_id）
• 範囲クエリの WHERE 句で使用する列をすべて含めます
• PRIMARY KEY は ORDER BY のプレフィックスにする必要があります（または省略して ORDER BY 全体を使用します）

例：

-- Correct: Low cardinality first, 4 columns
CREATE TABLE trades (
    exchange LowCardinality(String),
    symbol LowCardinality(String),
    timestamp DateTime64(3),
    trade_id UInt64,
    price Float64,
    quantity Float64
) ENGINE = MergeTree()
ORDER BY (exchange, symbol, timestamp, trade_id);

-- Wrong: High cardinality first (10x slower queries)
ORDER BY (trade_id, timestamp, symbol, exchange);

圧縮コーデックのクイックリファレンス

列の型	デフォルトのコーデック	読み取り負荷の高い場合の代替	例
DateTime/DateTime64	CODEC(DoubleDelta, ZSTD)	CODEC(DoubleDelta, LZ4)	timestamp DateTime64(3) CODEC(DoubleDelta, ZSTD)
Float の価格／ゲージ	CODEC(Gorilla, ZSTD)	CODEC(Gorilla, LZ4)	price Float64 CODEC(Gorilla, ZSTD)
整数カウンター	CODEC(T64, ZSTD)	—	count UInt64 CODEC(T64, ZSTD)
変化の遅い整数	CODEC(Delta, ZSTD)	CODEC(Delta, LZ4)	version UInt32 CODEC(Delta, ZSTD)
文字列（低カーディナリティ）	LowCardinality(String)	—	status LowCardinality(String)
一般データ	CODEC(ZSTD(3))	CODEC(LZ4)	デフォルトの圧縮レベルは 3

ZSTD より LZ4 を使う場合：LZ4 は解凍が 1.76 倍高速です。単調なシーケンス（タイムスタンプ、カウンター）を含む、読み取り負荷の高いワークロードには LZ4 を使用してください。圧縮率が重要な場合や、データパターンが不明な場合は、デフォルトの ZSTD を使用してください。

コーデックの組み合わせに関する注意：

Delta/DoubleDelta と Gorilla の組み合わせは、デフォルトで allow_suspicious_codecs によってブロックされます。これは、Gorilla が内部ですでに暗黙的なデルタ圧縮を行うためです。組み合わせは危険なのではなく、冗長です。過去の破損バグ（PR #45615、2023 年 1 月）は修正済みですが、ベストプラクティスを守るためのガードレールとしてブロックは維持されています。

各コーデックファミリーは、対象となるデータ型に合わせて個別に使用してください。

-- Correct usage
price Float64 CODEC(Gorilla, ZSTD)              -- Floats: use Gorilla
timestamp DateTime64 CODEC(DoubleDelta, ZSTD)   -- Timestamps: use DoubleDelta
timestamp DateTime64 CODEC(DoubleDelta, LZ4)    -- Read-heavy: use LZ4

PARTITION BY のガイドライン

PARTITION BY はデータライフサイクル管理のために使用するものであり、クエリの最適化には使用しません。

ルール：

• TTL とデータ管理のため、時間単位（月、週）でパーティション分割します
• すべてのテーブルを合計したパーティション数を 1000 未満に保ちます
• 各パーティションに含めるパーツは最大 1～300 個にします
• カーディナリティの高いカラムではパーティション分割しないでください

例：

-- Correct: Monthly partitions for TTL management
PARTITION BY toYYYYMM(timestamp)

-- Wrong: Daily partitions (too many parts)
PARTITION BY toYYYYMMDD(timestamp)

-- Wrong: High-cardinality partition key
PARTITION BY user_id

アンチパターンのチェックリスト（v24.4+）

パターン	深刻度	現在の状況	対処法
パーツが多すぎる（パーティションあたり >300）	致命的	引き続き致命的	パーティションの粒度を下げます
小規模なバッチ挿入（<1000）	致命的	引き続き致命的	10k～100k 行単位でバッチ処理します
ORDER BY の先頭が高カーディナリティ	致命的	引き続き致命的	カーディナリティが最も低いカラムを先頭に並べ替えます
メモリ制限がない	高	引き続き致命的	max_memory_usage を設定します
非正規化の過剰な使用	高	引き続き致命的	ディクショナリとマテリアライズドビューを使用します
大規模な JOIN	中	180x 改善	超低レイテンシが必要な場合は引き続き避けます
ミューテーション（UPDATE/DELETE）	中	1700x 改善	軽量な UPDATE を使用します（v24.4+）。下記の DELETE 戦略ガイドを参照してください

DELETE 戦略ガイド（v13.49.0+ のベストプラクティス）

対象範囲に応じて適切な DELETE 戦略を選択します。速度の速い順に記載しています。

戦略	構文	速度	使用する場面
DROP PARTITION	ALTER TABLE t DROP PARTITION (key1, key2, keyN)	即時（メタデータのみ）	パーティション範囲全体を削除する場合（月単位、破損データ、テストデータなど）
DELETE IN PARTITION	ALTER TABLE t DELETE IN PARTITION (...) WHERE condition	高速（1 つのパーティションをスキャン）	既知のパーティション内で対象行を削除する場合
ALTER TABLE DELETE	ALTER TABLE t DELETE WHERE condition	低速（すべてのパーツをスキャン）	パーティションが不明な場合の代替手段
DELETE FROM（軽量）	DELETE FROM t WHERE condition	可変	書き込みパイプラインではアンチパターン — 下記の警告を参照

アンチパターン：INSERT 前の軽量な DELETE FROM

DELETE FROM は、行を物理的に削除する代わりに _row_exists=0 マスクを設定します。これらのゴースト行は次のようになります。

• ClickHouse のバックグラウンドマージが実行されるまで残ります（実行時期は予測できません）
• FINAL を指定しないクエリでは、ファントムデータとして表示されます
• 監視／整合性チェックで誤った異常を引き起こします
• 本番環境で数か月にわたって発生した Stathera のファントムな欠損の根本原因でした（opendeviationbar #269）

DELETE FROM の使用は次の場合に限ります：ゴースト行が問題にならないアドホックなデータ修正（分析データのクリーンアップ、開発／テスト）。DELETE の後に INSERT が続く書き込みパイプラインでは絶対に使用しないでください。

すべての DELETE ミューテーションでは、完了まで処理をブロックするために SETTINGS mutations_sync = 1 を使用します（INSERT と DELETE の競合状態を防ぎます）。

パーティションを考慮した DELETE のヒント：パーティションキーに、フィルタリング対象のカラムが含まれている場合（例：PARTITION BY (symbol, threshold, toYYYYMM(timestamp))）、すべてのパーツをスキャンする代わりに DELETE IN PARTITION を使用すると、スキャンを単一のパーティションに限定できます。

テーブルエンジンの選択

デプロイ方法	エンジン	用途
ClickHouse Cloud	SharedMergeTree	クラウドデプロイのデフォルト
セルフホストのクラスター	ReplicatedMergeTree	レプリケーションを使用する複数ノード
セルフホストの単一ノード	MergeTree	単一ノードでの開発／テスト

クラウド（SharedMergeTree）：

CREATE TABLE trades (...)
ENGINE = SharedMergeTree('/clickhouse/tables/{shard}/trades', '{replica}')
ORDER BY (exchange, symbol, timestamp);

セルフホスト（ReplicatedMergeTree）：

CREATE TABLE trades (...)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/trades', '{replica}')
ORDER BY (exchange, symbol, timestamp);

スキルの委任ガイド

このスキルは、ClickHouse 関連のタスクにおけるハブです。ユーザーのニーズがスキーマ設計の範囲を超える場合は、以下の関連スキルを呼び出してください。

委任の判断マトリックス

ユーザーのニーズ	呼び出すスキル	トリガーフレーズ
データベースユーザーの作成、権限の管理	devops-tools:clickhouse-cloud-management	"create user", "GRANT", "permissions", "credentials"
DBeaver の設定、接続 JSON の生成	devops-tools:clickhouse-pydantic-config	"DBeaver", "client config", "connection setup"
ライブデータベースに対するスキーマコントラクトの検証	quality-tools:schema-e2e-validation	"validate schema", "Earthly E2E", "schema contract"

一般的なワークフローの手順

1. スキーマ設計（このスキル）→ ORDER BY、圧縮、パーティショニングを設計
2. ユーザー設定 → clickhouse-cloud-management（クラウドの認証情報が必要な場合）
3. クライアント設定 → clickhouse-pydantic-config（DBeaver の JSON を生成）
4. 検証 → schema-e2e-validation（CI/CD のスキーマコントラクト）

例：フルスタックのリクエスト

ユーザー：「ClickHouse Cloud 用の trades テーブルを設計し、クエリに使う DBeaver も設定したいです。」

期待される動作：

1. このスキルを使ってスキーマを設計する
2. データベースユーザーを作成するために clickhouse-cloud-management を呼び出す
3. DBeaver を設定するために clickhouse-pydantic-config を呼び出す

パフォーマンスの向上

プロジェクション

ClickHouse が自動的に選択する、別のソート順を作成します。

ALTER TABLE trades ADD PROJECTION trades_by_symbol (
    SELECT * ORDER BY symbol, timestamp
);
ALTER TABLE trades MATERIALIZE PROJECTION trades_by_symbol;

マテリアライズドビュー

ダッシュボードのクエリ向けに、集計を事前計算します。

CREATE MATERIALIZED VIEW trades_hourly_mv
ENGINE = SummingMergeTree()
ORDER BY (exchange, symbol, hour)
AS SELECT
    exchange,
    symbol,
    toStartOfHour(timestamp) AS hour,
    sum(quantity) AS total_volume,
    count() AS trade_count
FROM trades
GROUP BY exchange, symbol, hour;

ディクショナリ

大規模なスター型スキーマでは、JOIN を O(1) のディクショナリ検索に置き換えます。

ディクショナリを使用する場合（v24.4 以降）：

• 1 億行以上のファクトテーブルをディメンションテーブルと結合する場合
• 単調キーを持つ、1,000～500,000 行のディメンションテーブル
• LEFT ANY JOIN のセマンティクスが必要な場合

JOIN で十分な場合（v24.4 以降）：

• ディメンションテーブルが 500 行未満の場合（JOIN のオーバーヘッドは無視できる程度）
• v24.4 以降の述語プッシュダウンで 8～180 倍の性能向上が得られる場合
• 複雑な JOIN の種類（FULL、RIGHT、複数条件）

ベンチマークの背景：Star Schema Benchmark（14 億行）で 6.6 倍の高速化を測定しました。

CREATE DICTIONARY symbol_info (
    symbol String,
    name String,
    sector String
)
PRIMARY KEY symbol
SOURCE(CLICKHOUSE(TABLE 'symbols'))
LAYOUT(FLAT())  -- Best for <500k entries with monotonic keys
LIFETIME(3600);

-- Use in queries (O(1) lookup)
SELECT
    symbol,
    dictGet('symbol_info', 'name', symbol) AS symbol_name
FROM trades;

スクリプト

包括的なスキーマ監査を実行します。

clickhouse-client --multiquery < scripts/schema-audit.sql

監査スクリプトでは、以下を確認します。

• パーティションごとのパーツ数（しきい値：300）
• カラムごとの圧縮率
• クエリパフォーマンスのパターン
• レプリケーションの遅延（該当する場合）
• メモリ使用量のパターン

追加リソース

参考ファイル

参考資料	内容
references/schema-design-workflow.md	例を含む完全なワークフロー
references/compression-codec-selection.md	判断ツリーとベンチマーク
references/anti-patterns-and-fixes.md	13 の重大なアンチパターンと v24.4 以降の状況
references/audit-and-diagnostics.md	クエリ解釈ガイド
references/idiomatic-architecture.md	パラメーター化ビュー、辞書、重複排除
references/schema-documentation.md	COMMENT のパターンと AI が理解しやすい命名
references/cache-schema-evolution.md	キャッシュ無効化とスキーマ進化のパターン

外部ドキュメント

• ClickHouse のベストプラクティス
• Altinity ナレッジベース
• ClickHouse ブログ

Python ドライバーの方針

すべての Python インテグレーションには、公式の clickhouse-connect を使用してください。

# ✅ RECOMMENDED: clickhouse-connect (official, HTTP)
import clickhouse_connect

client = clickhouse_connect.get_client(
    host='localhost',
    port=8123,  # HTTP port
    username='default',
    password=''
)
result = client.query("SELECT * FROM trades LIMIT 1000")
df = client.query_df("SELECT * FROM trades")  # Pandas integration

clickhouse-driver を使用しない理由

要素	clickhouse-connect	clickhouse-driver
メンテナー	ClickHouse Inc.	個人開発者
週あたりのコミット数	あり（活発）	少ない（数か月ごと）
未解決の Issue	41（対応済み）	76（増加中）
週あたりのダウンロード数	2.7M	1.5M
バス係数のリスク	低（企業）	高（1 人）

大規模なエクスポートでは約 26% 高速ですが、clickhouse-driver は使用しないでください。メンテナンスのリスクがパフォーマンス上の利点を上回ります。

• 後継者計画のない単独メンテナー（mymarilyn）
• 対応されないまま Issue が増加
• 放棄されると、本番コードが壊れるリスクがある

例外：極めて高いパフォーマンスが必要（数百万行をエクスポートするなど）で、かつメンテナンスのリスクを受け入れる場合に限り、clickhouse-driver を検討してください。

ClickHouse COMMENT = 信頼できる唯一の情報源

ClickHouse のすべてのテーブルとカラムには、その意味、計算方法、制約を完全に記述する COMMENT が必須です。 COMMENT が信頼できる唯一の情報源（SSoT）であり、外部ドキュメント、スキル、Wiki がこれに優先することはありません。

理由

• ClickHouse の COMMENT に長さ制限はなく、改行、URL、Unicode に対応しています
• クエリへのパフォーマンス影響はゼロです（純粋なメタデータであり、データ処理経路には入りません）
• DESCRIBE table、SHOW CREATE TABLE、system.columns から確認できます
• スキーマ移行後も保持されます（ALTER 操作を通じて維持されます）

COMMENT に含める内容

• わかりやすい英語で記述したカラムの用途
• 計算式（派生値または計算値の場合）
• 単位（秒、ミリ秒、bps、比率）
• 有効範囲または列挙値
• アンチパターン（このカラムに対して行ってはいけないこと）
• 由来を示すGitHub Issue のリンク
• カラムに値を設定するソーススクリプト

例

ALTER TABLE t COMMENT COLUMN session_label
'STRICT session label. 8 values: sydney_only, tokyo_only, ...
Only set when ENTIRE bar (open→close) falls within one session.
cross_session = bar spans boundary. Use WHERE is_pure_session=1.
GitHub: https://github.com/org/repo/issues/54
Source: scripts/populate-sessions/populate_v3.py';

アンチパターン

COMMENT のない ClickHouse カラムは絶対に作成しないでください。ドキュメントのないカラムは、誤用されるカラムです。

関連スキル

スキル	目的
devops-tools:clickhouse-cloud-management	ユーザー／権限の管理
devops-tools:clickhouse-pydantic-config	DBeaver 接続の生成
quality-tools:schema-e2e-validation	YAML スキーマのコントラクト
quality-tools:multi-agent-e2e-validation	データベース移行の検証

---

トラブルシューティング

問題	原因	解決策
パーツが多すぎる	パーティションの過剰な分割	パーティションの粒度を下げます（日単位ではなく月単位）
クエリが遅い	ORDER BY の順序が不適切	カーディナリティが最も低い列を先頭に置きます
メモリ使用量が多い	メモリ制限が設定されていない	max_memory_usage 設定を構成します
Delta+Gorilla でコーデックエラー	不適切なコーデックの組み合わせ	各コーデックファミリーを個別に使用します
Projection が使用されない	オプティマイザーが別のプランを選択	EXPLAIN を確認して Projection の選択を検証します
Dictionary が古い	有効期間が切れている	LIFETIME を延長するか、更新を実行します
レプリケーションの遅延	パーツのマージが追いついていない	merge_tree 設定を確認し、リソースを追加します
INSERT が遅い	バッチサイズが小さい	INSERT 1 回あたり 10k～100k 行にまとめます

実行後の振り返り

このスキルの実行後、終了する前に次の点を確認してください。

1. コマンドは成功しましたか？ — 成功しなかった場合は、失敗の原因となった手順またはエラーテーブルを修正してください。
2. パラメーターまたは出力に変更はありましたか？ — 基盤となるツールのインターフェースに変更があった場合は、使用例とパラメーター表を更新して一致させてください。
3. 回避策が必要でしたか？ — 即興で対応した場合（異なるフラグや追加手順など）は、次回の実行で同じ回避策が不要になるよう、この SKILL.md を更新してください。

問題が実際に発生し、再現可能な場合に限って更新してください。推測に基づく更新は避けてください。