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 的元数据数据库被重建或连接被重新添加时，它们会发生变化。切勿硬编码 ID。始终发现当前列表：

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

区域布局（名称可能不同；请使用 metabase:databases 重新检查）：

• US 公开一个 ClickHouse 数据库（用于 query_log 和数据读取）。
• EU 公开两个 ClickHouse 数据库 —— 一个查询层（用于 query_log 分析）和一个数据层（生产读取：events、persons 等）。选择名称表明为查询层的那个。
• 两个 Metabase 还公开 Postgres 数据库（应用数据库），在 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 —— 测试框架 会阻止来自 Agent shell 的 Keychain 访问，因此用户必须 交互式地进行身份验证。

Agent：使用 metabase:query

hogli metabase:query 在内部读取缓存的 cookie，并且只输出 结果 —— 会话值永远不会出现在 Agent 的记录中。 metabase:cookie 是为那些想手动对 Metabase 使用 curl 的人准备的。

运行即席查询

1. 发现当前的 ClickHouse 数据库 ID：hogli metabase:databases --region <region>。
2. 将该 ID 传入 hogli metabase:query。通过 stdin 或 --file 管道传入 SQL。

# 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 响应体。

如果数据库 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 小时内的慢查询 Top

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>

同样的操作可以通过 WHERE query_id = '...' 子句，针对正确区域的数据库 ID 通过 /api/dataset 以编程方式复现。

解析 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 响应超时。 原生查询默认约 60s；极宽的扫描会被截断。缩小时间范围或使用抽样表。
• log_comment JSON 漂移。 新字段会随时间出现；JSONExtractString(log_comment, 'foo') 在字段缺失时返回 '' —— 如果基于它过滤，务必加上 IS NOT NULL / != '' 保护。
• Cookie 作用域。 每个区域有独立的 cookie 缓存。每个需要的区域都要运行 hogli metabase:login --region <region>；--region 为必填项。