ClickHouse Query Testing

Use this skill to run ad-hoc ClickHouse queries for analytics, metrics analysis, and debugging.

Running Queries

Use the included query script:

node .claude/skills/clickhouse-query/query.mjs "SELECT count() FROM views"

Options

Flag Description

--explain

Show query execution plan

--writable

Allow write operations (requires user permission)

--timeout <s> , -t

Query timeout in seconds (default: 30)

--file , -f

Read query from a file

--json

Output results as JSON

--quiet , -q

Minimal output, only results

Examples

Count rows in a table

node .claude/skills/clickhouse-query/query.mjs "SELECT count() FROM views"

Query with filters

node .claude/skills/clickhouse-query/query.mjs "SELECT * FROM modelEvents WHERE modelId = 123 LIMIT 10"

Check query execution plan

node .claude/skills/clickhouse-query/query.mjs --explain "SELECT * FROM views WHERE userId = 1"

Override default 30s timeout for longer queries

node .claude/skills/clickhouse-query/query.mjs --timeout 60 "SELECT ... (complex aggregation)"

Query from file

node .claude/skills/clickhouse-query/query.mjs -f my-query.sql

JSON output for processing

node .claude/skills/clickhouse-query/query.mjs --json "SELECT type, count() FROM modelEvents GROUP BY type"

Safety Features

• Read-only by default: Blocks INSERT/ALTER/DROP unless --writable flag is used

• 30 second timeout: Prevents runaway queries (override with --timeout )

• Explicit permission required: Before using --writable , you MUST ask the user for permission

When to Use --writable

Only use the --writable flag when:

• The user explicitly requests write access

• You need to insert test data

• You're running maintenance operations

IMPORTANT: Always ask the user for permission before running with --writable .

Common Tables

Table Description

views

Page/entity view events

modelEvents

Model create/publish/update events

modelVersionEvents

Model version events including downloads

userActivities

User registration, login, subscription events

images

Image upload/delete events

reactions

Like/dislike events

reports

Content report events

entityMetricEvents

Aggregated metric events

Querying Replica Clusters

IMPORTANT: Production uses a ClickHouse replica cluster. When querying system tables (logs, metrics, etc.), you must use clusterAllReplicas() to get data from all nodes.

System Tables on Replica Clusters

-- WRONG: Only queries the node you're connected to SELECT * FROM system.query_log WHERE event_time > now() - INTERVAL 1 HOUR

-- CORRECT: Queries all replicas in the cluster SELECT * FROM clusterAllReplicas(default, system.query_log) WHERE event_time > now() - INTERVAL 1 HOUR

Common System Table Queries

-- Find recent queries across all nodes SELECT hostname(), event_time, query_duration_ms, formatReadableSize(memory_usage) AS memory, query FROM clusterAllReplicas(default, system.query_log) WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 5 MINUTE ORDER BY event_time DESC LIMIT 20

-- Find expensive queries by memory usage (last 24 hours) SELECT count() as query_count, user, sum(memory_usage) AS total_memory, normalized_query_hash FROM clusterAllReplicas(default, system.query_log) WHERE event_time > now() - INTERVAL 1 DAY AND query_kind = 'Select' AND type = 'QueryFinish' GROUP BY normalized_query_hash, user ORDER BY total_memory DESC LIMIT 10

-- Search query logs by pattern SELECT event_time, query_id, query, type FROM clusterAllReplicas(default, merge('system', '^query_log*')) WHERE query ILIKE '%some_table%' AND event_time > now() - INTERVAL 5 MINUTE

-- Debug a specific query across all nodes SELECT hostname(), message FROM clusterAllReplicas(default, system.text_log) WHERE query_id = 'your-query-id-here' ORDER BY event_time_microseconds ASC

When to Use clusterAllReplicas()

Use Case Function

System tables (query_log, text_log, etc.) clusterAllReplicas(default, system.table_name)

Application tables (views, modelEvents, etc.) Direct query (already distributed)

Search multiple system tables clusterAllReplicas(default, merge('system', '^pattern*'))

ClickHouse SQL Tips

-- Use count() not COUNT(*) SELECT count() FROM views

-- Date filtering with toDate() SELECT * FROM views WHERE toDate(time) = today()

-- Last 7 days SELECT * FROM modelEvents WHERE time > now() - INTERVAL 7 DAY

-- Aggregations SELECT type, count() as cnt FROM modelEvents GROUP BY type ORDER BY cnt DESC