OpenParable
Skip to content
← Back to skills

Clickhouse Query

Apps & Automation
clickhouse-query

Clickhouse Query is an Agent Skill for apps & automation published in the civitai/civitai repository on GitHub: a SKILL.md instruction file an AI agent can load to do this task.

Run ClickHouse queries for analytics, metrics analysis, and event data exploration. Use when you need to query ClickHouse directly, analyze metrics, check event tracking data, or test query performance. Read-only by default.

View on GitHub ↗
QUICK START

How to use this skill

Bring this guide into your coding agent with a prompt tailored to the tool you use.

  1. Open your project in Codex.
  2. Copy and paste the prompt below.
  3. Review files and risks before approving any action.
Prompt to paste
Inspect the Agent Skill at https://github.com/civitai/civitai/blob/HEAD/.claude/skills/clickhouse-query/SKILL.md and all supporting files. Report provenance, permissions, scripts, dependencies, and security risks. Propose an exact installation into .agents/skills/clickhouse-query, but wait for my approval before writing files or running scripts.

Copying this prompt does not install or run the skill. Review third-party files before use.

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

FlagDescription
--explainShow query execution plan
--writableAllow write operations (requires user permission)
--timeout <s>, -tQuery timeout in seconds (default: 30)
--file, -fRead query from a file
--jsonOutput results as JSON
--quiet, -qMinimal 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

  1. Read-only by default: Blocks INSERT/ALTER/DROP unless --writable flag is used
  2. 30 second timeout: Prevents runaway queries (override with --timeout)
  3. 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

TableDescription
viewsPage/entity view events
modelEventsModel create/publish/update events
modelVersionEventsModel version events including downloads
userActivitiesUser registration, login, subscription events
imagesImage upload/delete events
reactionsLike/dislike events
reportsContent report events
entityMetricEventsAggregated 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 CaseFunction
System tables (query_log, text_log, etc.)clusterAllReplicas(default, system.table_name)
Application tables (views, modelEvents, etc.)Direct query (already distributed)
Search multiple system tablesclusterAllReplicas(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