query-clickhouse-via-metabase

Metabase 経由での ClickHouse クエリ

PostHog の本番 ClickHouse クラスターには、社内の Metabase インスタンスを通じて アドホック分析のためにアクセスできます。両方の Metabase は Cognito OAuth 付きの AWS ALB の背後にあり、認証は SSO で制限されている ため、Metabase の API キー だけでは機能しません。

このスキルは、posthog リポジトリ内からの system.query_log 分析を対象としています。 あらかじめ用意された定型クエリ（スロークエリのサマリー、マテリアライゼーション分析） については、query-performance-analysis リポジトリを参照してください。これはそれらの 信頼できる情報源であり、同じ Metabase API サーフェスを使用しています。

環境

リージョン	Metabase URL
US	https://metabase.prod-us.posthog.dev
EU	https://metabase.prod-eu.posthog.dev

データベース ID は安定していません — Metabase のメタデータ DB が再構築されたり、 接続が再追加されたりすると変化します。ID をハードコードしないでください。常に現在の 一覧を取得してください：

hogli metabase:databases --region us
hogli metabase:databases --region eu

リージョンごとの構成（名前は異なる場合があります。metabase:databases で再確認してください）：

• US は 1 つの ClickHouse データベースを公開します（query_log とデータ読み取りに使用）。
• EU は 2 つの ClickHouse データベースを公開します — クエリ層（query_log 分析に使用）と データ層（本番の読み取り：イベント、persons など）。クエリ層を示す名前の方を使用してください。
• 両方の Metabase は Postgres データベース（アプリ DB）も公開しており、EU ではさらに インジェスト層とマイグレーションのデータベースも公開しています。

認証

hogli を使用して有効な Cookie を取得します。これは SSO のためにシステムブラウザーを開き、 ユーザーがログイン済みのブラウザープロファイルから Cookie を取得し、 ~/.config/posthog/metabase/cookie-{region}（モード 0600）にキャッシュします。

# Log in once per region. --region is required (no default — you pick which one).
# Already-valid sessions are fast-pathed (no browser tab opens), so re-running
# is cheap.
hogli metabase:login --region us
hogli metabase:login --region eu

ユーザー自身に hogli metabase:login を実行するよう促してください — ハーネスは エージェントのシェルからの Keychain アクセスをブロックするため、ユーザーが対話的に 認証を行う必要があります。

エージェント向け：metabase:query を使用する

hogli metabase:query はキャッシュされた Cookie を内部的に読み取り、結果のみを出力します — セッションの値がエージェントのトランスクリプトに現れることはありません。 metabase:cookie は、Metabase に対して curl を自分で実行したい人間向けに存在します。

アドホッククエリの実行

1. 現在の ClickHouse DB ID を取得します：hogli metabase:databases --region <region>。
2. その ID を hogli metabase:query に渡します。SQL は stdin または --file で パイプします。

# 1. Find the ClickHouse database ID for your region
hogli metabase:databases --region us
# e.g. output row:  42  ClickHouse  clickhouse

# 2. Run the query. The cookie is read internally; nothing leaks to stdout.
hogli metabase:query --region us --database-id 42 --save /tmp/out.tsv <<'SQL'
SELECT
    JSONExtractInt(log_comment, 'team_id') AS team_id,
    count() AS query_count,
    formatReadableSize(sum(read_bytes)) AS total_bytes
FROM clusterAllReplicas(posthog, system, query_log)
WHERE event_time > now() - INTERVAL 1 DAY
    AND is_initial_query
    AND query_duration_ms > 30000
GROUP BY team_id
ORDER BY query_count DESC
LIMIT 20
SQL

clusterAllReplicas(posthog, system, query_log) が標準的なテーブル参照です — これはクラスター全体にファンアウトします。

大きな結果セットの場合は、--save <path> を使用して、行がターミナルやトランスクリプトを 通じてストリーミングされるのではなくファイルに保存されるようにしてください。デフォルトの 出力は TSV です。--format json を指定すると、生の /api/dataset レスポンスボディが 得られます。

DB ID が間違っている場合、metabase:query は非ゼロで終了し、metabase:databases への ポインターを返します。フェイルファストは意図的なものです — 間違ったデータベースに対して 黙ってクエリを実行することは、失敗するよりも悪いことです。

何がスロークエリと見なされるか

query_duration_ms > 30000
OR exception_code IN (159, 160, 241)

コード	意味
159	TIMEOUT_EXCEEDED
160	TOO_SLOW
241	MEMORY_LIMIT_EXCEEDED

便利なクエリパターン

直近 24 時間のトップスロークエリ

SELECT
    query_id,
    JSONExtractInt(log_comment, 'team_id') AS team_id,
    query_duration_ms,
    formatReadableSize(memory_usage) AS memory,
    formatReadableSize(read_bytes) AS read_bytes,
    exception_code,
    substring(query, 1, 200) AS query_preview
FROM clusterAllReplicas(posthog, system, query_log)
WHERE event_time > now() - INTERVAL 1 DAY
    AND type = 'QueryFinish'
    AND (query_duration_ms > 30000 OR exception_code IN (159, 160, 241))
    AND JSONExtractString(log_comment, 'workload') NOT IN ('Workload.OFFLINE', 'OFFLINE')
    AND JSONExtractString(log_comment, 'kind') NOT IN ('temporal')
    AND JSONExtractString(log_comment, 'access_method') NOT IN ('personal_api_key')
    AND is_initial_query
    AND JSONExtractInt(log_comment, 'team_id') != 0
ORDER BY query_duration_ms DESC
LIMIT 100

チームごとのクエリコストサマリー（7日間）

SELECT
    JSONExtractInt(log_comment, 'team_id') AS team_id,
    count() AS queries,
    countIf(query_duration_ms > 30000) AS slow_queries,
    formatReadableSize(sum(read_bytes)) AS total_read,
    formatReadableSize(max(memory_usage)) AS peak_memory,
    quantile(0.95)(query_duration_ms) AS p95_duration_ms
FROM clusterAllReplicas(posthog, system, query_log)
WHERE event_time > now() - INTERVAL 7 DAY
    AND type = 'QueryFinish'
    AND JSONExtractString(log_comment, 'workload') NOT IN ('Workload.OFFLINE', 'OFFLINE')
    AND JSONExtractString(log_comment, 'kind') NOT IN ('temporal')
    AND JSONExtractString(log_comment, 'access_method') NOT IN ('personal_api_key')
    AND is_initial_query
    AND JSONExtractInt(log_comment, 'team_id') != 0
GROUP BY team_id
ORDER BY total_read DESC
LIMIT 20

query_id で特定のクエリを検索する

両方のリージョンで利用可能な保存済みカード — クエリが実行された場所に URL を合わせてください：

# US
https://metabase.prod-us.posthog.dev/question/795-look-up-query-by-query-id?query_id=<ID>&include_query_start=No&event_date=<YYYY-MM-DD>

# EU (same card ID may differ — find it in EU Metabase if 795 doesn't resolve)
https://metabase.prod-eu.posthog.dev/question/795-look-up-query-by-query-id?query_id=<ID>&include_query_start=No&event_date=<YYYY-MM-DD>

同じことは、適切なリージョンの DB ID に対して /api/dataset 経由で WHERE query_id = '...' 句を使ってプログラム的に再現できます。

Metabase レスポンスのパース

{
  "data": {
    "cols": [{"name": "team_id", "base_type": "type/Integer"}, ...],
    "rows": [[55348, 142, "1.23 TiB"], ...]
  },
  "status": "completed",
  "row_count": 20
}

簡単な TSV パイプ：

... | python3 -c "
import json, sys
d = json.load(sys.stdin)
cols = [c['name'] for c in d['data']['cols']]
print('\t'.join(cols))
for row in d['data']['rows']:
    print('\t'.join(str(v) for v in row))
"

エラー応答

症状	原因	対処
HTTP 302 で /auth/... へ	Cookie の有効期限切れまたは未設定	ユーザーに hogli metabase:login --region <region> の実行を伝える
HTTP 401	Cookie が ALB に拒否された	302 と同じ
"status": "failed" + error	ClickHouse エラー（構文、テーブルなど）	error を読み、SQL を修正する
ハング / タイムアウト	広範囲な query_log スキャン	event_time の範囲を狭め、team_id フィルターを追加し、cluster() を使用する

調査ワークフロー

1. 問いを明確にします。 チームごとに遅いのか？ 特定のクエリパターンか？ コストやメモリのリグレッションか？
2. その問いに答える最小の時間枠を選びます。 query_log は巨大なので、デフォルトでは 1h〜24h とし、必要なときだけ広げます。
3. 「実際に実行されたもの」を見るには type = 'QueryFinish' でフィルターします。 QueryStart と ExceptionBeforeStart の行も存在します。
4. まず集計してから掘り下げます。 最初にチーム単位またはパターン単位で集計し、次に最悪の対象で WHERE して個々のクエリを確認します。
5. どのレポートにも query_id の例を記録します。 そうすればレビュアーが自分で query_log から完全な行を取得できます。

既知の制限

• Metabase の応答タイムアウト。 ネイティブクエリのデフォルトは約 60 秒で、非常に広範囲なスキャンは打ち切られます。時間範囲を狭めるか、サンプリングされたテーブルを使用してください。
• log_comment JSON の変動。 新しいフィールドは時間の経過とともに追加されます。JSONExtractString(log_comment, 'foo') は存在しない場合 '' を返すため、これでフィルタリングする場合は常に IS NOT NULL / != '' のガードを入れてください。
• Cookie のスコープ。 各リージョンには独自の Cookie キャッシュがあります。必要なリージョンごとに hogli metabase:login --region <region> を実行してください。--region は必須です。