skills.rest
PostHog
PostHogOFFICIAL
Explore all skills by this author →
Website𝕏@PostHogGitHub

query-clickhouse-via-metabase

Runs ClickHouse system.query_log analysis through internal Metabase API endpoints.

713|118|Updated Aug 11, 2020
Search Tags:#query analysis#clickhouse#metabase#PostHog#performance investigation#SSO authentication#query_log
One-click install
npx skills add https://github.com/PostHog/posthog-foss --skill query-clickhouse-via-metabase
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: query-clickhouse-via-metabase
Source: https://github.com/PostHog/posthog-foss/tree/main/.agents/skills/query-clickhouse-via-metabase
Command: npx skills add https://github.com/PostHog/posthog-foss --skill query-clickhouse-via-metabase

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

Investigating slow queries, memory issues, and per-team query costs on PostHog's production ClickHouse clusters requires access that is gated behind SSO-authenticated Metabase instances, making ad-hoc system.query_log analysis difficult to perform securely from the repo.

Core Features & Use Cases

  • SSO-Gated Authentication: Uses hogli metabase:login to capture browser SSO cookies per region (prod-us and prod-eu), cached securely so session values never leak into agent transcripts.
  • Ad-Hoc Query Execution: Discovers current ClickHouse database IDs with hogli metabase:databases and runs SQL via hogli metabase:query, with TSV or JSON output and file saving for large result sets.
  • Ready-Made Query Patterns: Includes templates for top slow queries, per-team cost summaries, and query_id lookups, plus error-handling guidance for expired cookies and timeouts.
  • Use Case: An engineer notices elevated ClickHouse memory usage, runs the per-team 7-day cost summary against the EU query tier, identifies the worst-offending team, and drills into individual slow queries by query_id.

Quick Start

Ask the agent to find the slowest ClickHouse queries in the last 24 hours on the US cluster, after running hogli metabase:login --region us yourself to authenticate.

Frequently Asked Questions about query-clickhouse-via-metabase

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I query ClickHouse system.query_log through Metabase?▼

First run hogli metabase:login for your region to cache an SSO cookie, then discover the ClickHouse database ID with hogli metabase:databases, and finally pass that ID to hogli metabase:query with your SQL piped via stdin or a file.

How do I find slow queries in ClickHouse query_log?▼

Filter system.query_log for type = 'QueryFinish' rows where query_duration_ms exceeds 30000 or exception_code is 159, 160, or 241, which indicate timeout, too-slow, and memory-limit errors. Group by team_id extracted from log_comment to rank offenders.

Why does Metabase return HTTP 302 or 401 when querying ClickHouse?▼

These errors mean your cached SSO cookie has expired or was rejected by the AWS ALB with Cognito OAuth. Re-run hogli metabase:login --region for the affected region to refresh the cookie, since Metabase API keys alone cannot bypass SSO.

Can I hardcode the Metabase database ID for ClickHouse queries?▼

No, database IDs are not stable and change when Metabase's metadata DB is rebuilt or connections are re-added. Always run hogli metabase:databases first to discover the current ID, and note that EU has separate query and data tiers.

What are the limitations of querying query_log through Metabase?▼

Metabase enforces a roughly 60-second timeout on native queries, so wide query_log scans get cut off. Narrow the event_time window, filter by team_id, and use --save to write large result sets to a file instead of streaming them.

Related Skills

Explore More Data & Analytics Skills→
langfuselangfuse
34.0k

debug-issue-with-datadog

Diagnose production issues by correlating Datadog telemetry with Langfuse source code.

Official
Advanced
obsessiondbobsessiondb
12

clickhouse-query-optimization

Optimize ClickHouse queries with over 20 rules for speed and memory.

Official
Advanced
wheels-devwheels-dev
203

Wheels Deployment

Configure CFWheels production settings for security, performance, and database handling.

Official
Basic
chmonitorchmonitor
255

data-analysis

Run read-only SQL recipes against ClickHouse system tables for custom analysis.

Official
Intermediate