Skip to main content
skill CX Codex

clickhouse-query — Claude Code skill: ad-hoc ClickHouse queries

clickhouse-query runs ad-hoc ClickHouse queries from the chat: metrics, event data, execution plans. Read-only unless you say otherwise.

Apache-2.0 ★ 7,327 updated 2026-10-25

Install into Claude Code · Skills CLI (Vercel) All methods and platforms ↓
npx skills add civitai/civitai --skill clickhouse-query -a claude-code
Open on GitHub

What clickhouse-query does

The skill wraps one Node script, query.mjs, and takes SQL as a string: node .claude/skills/clickhouse-query/query.mjs "SELECT count() FROM views". Switches handle the usual cases. --explain prints the plan, --json returns structured output, --quiet leaves only rows, --file reads the SQL from disk, --timeout sets the time limit.

Writes are off out of the box. INSERT, ALTER and DROP are rejected until you pass --writable, and the skill is told to ask your permission before that flag goes in. Timeout sits at 30 seconds, so a heavy aggregation dies there; raise it with --timeout 60 when the query needs longer.

A short reference lists the tables you are likely to hit (views, modelEvents, modelVersionEvents, userActivities, images, reactions, reports, entityMetricEvents) and covers replicas. Production runs on a replica cluster, so system tables go through clusterAllReplicas(default, system.query_log) — a plain select reads only the node the script connected to.

What it helps with

  • count page views in views for the past week
  • inspect a query plan before it ships
  • find memory-hungry SELECTs across every replica
  • pipe an aggregate into JSON for downstream work
  • confirm event tracking writes the rows you expect

Example prompts for the agent

  • Count rows in views for the last 7 days and return the result as JSON.
  • Show the execution plan for SELECT * FROM views WHERE userId = 1 with --explain.
  • Pull the heaviest SELECTs by memory from the past day out of system.query_log, reading all replicas.

How to install clickhouse-query

1Skills CLI (Vercel)

npx skills add civitai/civitai --skill clickhouse-query -a claude-code

2Manually: put the files in place

~/.claude/skills/clickhouse-query/SKILL.md

Copy the whole skill folder; for one project use .claude/skills/clickhouse-query/

Step by step, with commands for each platform: How to install skills in Claude Code: five ways

Questions about clickhouse-query

How do I install clickhouse-query in Claude Code?

Run npx skills add civitai/civitai --skill clickhouse-query -a claude-code. The skill ends up at ~/.claude/skills/clickhouse-query/SKILL.md.

What does clickhouse-query do?

It runs ad-hoc ClickHouse SQL through query.mjs: counting metrics, exploring event data, checking a plan and switching output to JSON.

Can clickhouse-query write to the database?

Not by default: INSERT, ALTER and DROP are blocked. The --writable flag lifts that, and the skill must get your permission first.

Source text

SKILL.md · Apache-2.0
---
name: clickhouse-query
description: 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.
---

# 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:

```bash
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

```bash
# 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

| 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

```sql
-- 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

```sql
-- 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

```sql
-- 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
```

© civitai · Apache-2.0 · civitai/civitai

Similar skills

All skills for Data and databases →

From the same set11

The whole set →
quick-mockups quick-mockups generates 3 to 5 parallel UI mockups in HTML. Useful when you need to explore layouts, wireframes, and hierarchy variations before writing frontend code. CX Codex +1 1,700installs meilisearch-admin meilisearch-admin — a skill for monitoring Meilisearch: check indexes, tasks, server health, and search settings. Spot indexing errors and diagnose what's wrong with your search. CX Codex +1 132installs postgres-query postgres-query — a skill for Claude Code that executes PostgreSQL queries during debugging and performance analysis. It targets specific database environments with --prod or --dev flags under read-only mode. CX Codex +1 105installs agent-review Route code and prompts to external AI agents for review. Anthropic models use your local subscription via Claude Agent SDK; other models go through OpenRouter API. Useful for security audits, architecture validation, or a second opinion. CX Codex +1 3installs discord Posts messages, embeds, and announcements to Discord channels via REST API. Useful for team alerts and release notices during coding sessions. CX Codex +1 ★ 7,327repo stars redis-inspect redis-inspect — a skill that reads keys, values and TTLs across two Redis instances, the main cache and the system cache. Handy when you need to see what the cache holds right now. CX Codex +1 ★ 7,327repo stars postgres-query postgres-query runs PostgreSQL queries, inspects index usage, and analyzes execution plans using read-only connections by default. CX Codex +1 ★ 7,327repo stars browser-automation browser-automation runs browser sessions behind an HTTP server, keeps auth profiles, and supports concurrent users. Useful for repeatable flows and multi-user checks. CX Codex +1 ★ 7,327repo stars

Project sponsors

Companies that keep this analytics open