analyzing-clickhouse

name	analyzing-clickhouse
description	Use when working with Clickhouse — clickHouse table analysis, MergeTree optimization, query performance tuning, parts management, and cluster health.
connection_type	clickhouse
preload	false

ClickHouse Analysis Skill

Analyze and optimize ClickHouse databases with safe, read-only operations.

MANDATORY: Two-Phase Execution

You MUST follow this two-phase pattern. Skipping Phase 1 causes hallucinated database/table names.

Phase 1: Discovery (ALWAYS run first)

#!/bin/bash

# 1. List databases
clickhouse-client --query "SHOW DATABASES"

# 2. List tables in a database
clickhouse-client --query "SHOW TABLES FROM my_database"

# 3. Get table schema (never assume column names)
clickhouse-client --query "DESCRIBE TABLE my_database.my_table"

# 4. Table engine and settings
clickhouse-client --query "SELECT engine, engine_full, partition_key, sorting_key, primary_key FROM system.tables WHERE database = 'my_database' AND name = 'my_table'"

# 5. Sample data
clickhouse-client --query "SELECT * FROM my_database.my_table LIMIT 5"

Phase 1 outputs:

• List of databases and tables
• Table schemas with column names and types
• Engine types and partition/sort keys

Phase 2: Analysis (only after Phase 1)

Only reference databases, tables, and columns confirmed in Phase 1.

Shell Script Patterns

Helper Function

#!/bin/bash

# Core ClickHouse query runner — always use this
ch_query() {
    local query="$1"
    local format="${2:-TabSeparatedWithNames}"
    clickhouse-client --host "${CH_HOST:-localhost}" --port "${CH_PORT:-9000}" \
        --user "${CH_USER:-default}" --password "${CH_PASSWORD:-}" \
        --query "$query" --format "$format"
}

# HTTP API alternative
ch_http() {
    local query="$1"
    curl -s "http://${CH_HOST:-localhost}:8123/?query=$(python3 -c "import urllib.parse; print(urllib.parse.quote('$query'))")&default_format=JSONCompact"
}

Anti-Hallucination Rules

• NEVER reference a database or table without confirming via SHOW DATABASES / SHOW TABLES
• NEVER reference column names without seeing them in DESCRIBE TABLE
• NEVER assume table engine — always check system.tables
• NEVER guess partition keys or sort keys — always read from table definition
• NEVER assume cluster name — check system.clusters

Safety Rules

• READ-ONLY ONLY: Use only SELECT, SHOW, DESCRIBE, EXPLAIN, system table queries
• FORBIDDEN: DROP, ALTER, INSERT, OPTIMIZE, TRUNCATE without explicit user request
• ALWAYS add LIMIT to queries on large tables — ClickHouse tables can have billions of rows
• Use EXPLAIN before running expensive queries
• Prefer system.* tables for metadata over scanning user data

Common Operations

Cluster Health Overview

#!/bin/bash
echo "=== ClickHouse Version ==="
ch_query "SELECT version()"

echo ""
echo "=== Cluster Nodes ==="
ch_query "SELECT cluster, host_name, port, is_local FROM system.clusters ORDER BY cluster, host_name" 2>/dev/null || echo "Standalone instance"

echo ""
echo "=== Databases & Table Counts ==="
ch_query "SELECT database, count() as tables, sum(total_rows) as total_rows, formatReadableSize(sum(total_bytes)) as total_size FROM system.tables WHERE database NOT IN ('system', 'INFORMATION_SCHEMA', 'information_schema') GROUP BY database ORDER BY sum(total_bytes) DESC"

echo ""
echo "=== Uptime & Memory ==="
ch_query "SELECT uptime() as uptime_seconds, formatReadableSize(totalMemory()) as total_memory"

Table & Parts Analysis

#!/bin/bash
DB="${1:-default}"
TABLE="${2:-my_table}"

echo "=== Table Info ==="
ch_query "SELECT database, name, engine, partition_key, sorting_key, total_rows, formatReadableSize(total_bytes) as size FROM system.tables WHERE database = '$DB' AND name = '$TABLE'"

echo ""
echo "=== Parts Summary ==="
ch_query "SELECT partition, count() as parts, sum(rows) as rows, formatReadableSize(sum(bytes_on_disk)) as size, min(modification_time) as oldest, max(modification_time) as newest FROM system.parts WHERE database = '$DB' AND table = '$TABLE' AND active GROUP BY partition ORDER BY partition"

echo ""
echo "=== Part Merges in Progress ==="
ch_query "SELECT database, table, elapsed, progress, num_parts, result_part_name FROM system.merges WHERE database = '$DB' AND table = '$TABLE'"

Query Performance Analysis

#!/bin/bash
echo "=== Slow Queries (last 1h) ==="
ch_query "SELECT query_start_time, query_duration_ms, read_rows, formatReadableSize(read_bytes) as read_size, formatReadableSize(memory_usage) as memory, substring(query, 1, 100) as query_preview FROM system.query_log WHERE type = 'QueryFinish' AND query_duration_ms > 1000 AND event_time > now() - INTERVAL 1 HOUR ORDER BY query_duration_ms DESC LIMIT 20"

echo ""
echo "=== Failed Queries ==="
ch_query "SELECT query_start_time, exception_code, substring(exception, 1, 100) as error, substring(query, 1, 80) as query_preview FROM system.query_log WHERE type = 'ExceptionWhileProcessing' AND event_time > now() - INTERVAL 1 HOUR ORDER BY query_start_time DESC LIMIT 10"

echo ""
echo "=== Current Queries ==="
ch_query "SELECT query_id, elapsed, read_rows, formatReadableSize(memory_usage) as memory, substring(query, 1, 100) as query_preview FROM system.processes ORDER BY elapsed DESC"

MergeTree Optimization Check

#!/bin/bash
DB="${1:-default}"

echo "=== Tables with Too Many Parts ==="
ch_query "SELECT database, table, count() as parts, sum(rows) as total_rows, formatReadableSize(sum(bytes_on_disk)) as total_size FROM system.parts WHERE active AND database = '$DB' GROUP BY database, table HAVING parts > 100 ORDER BY parts DESC"

echo ""
echo "=== Tables with Wide Partitions ==="
ch_query "SELECT database, table, partition, count() as parts FROM system.parts WHERE active AND database = '$DB' GROUP BY database, table, partition HAVING parts > 50 ORDER BY parts DESC LIMIT 20"

echo ""
echo "=== Mutations in Progress ==="
ch_query "SELECT database, table, mutation_id, command, create_time, parts_to_do FROM system.mutations WHERE is_done = 0"

Output Format

Present results as a structured report:

Analyzing Clickhouse Report
═══════════════════════════
Resources discovered: [count]

Resource       Status    Key Metric    Issues
──────────────────────────────────────────────
[name]         [ok/warn] [value]       [findings]

Summary: [total] resources | [ok] healthy | [warn] warnings | [crit] critical
Action Items: [list of prioritized findings]

Target ≤50 lines of output. Use tables for multi-resource comparisons.

Counter-Rationalizations

Shortcut	Counter	Why
"I'll skip discovery and check known resources"	Always run Phase 1 discovery first	Resource names change, new resources appear — assumed names cause errors
"The user only asked for a quick check"	Follow the full discovery → analysis flow	Quick checks miss critical issues; structured analysis catches silent failures
"Default configuration is probably fine"	Audit configuration explicitly	Defaults often leave logging, security, and optimization features disabled
"Metrics aren't needed for this"	Always check relevant metrics when available	API/CLI responses show current state; metrics reveal trends and intermittent issues
"I don't have access to that"	Try the command and report the actual error	Assumed permission failures prevent useful investigation; actual errors are informative

Common Pitfalls

• Too many parts: Excessive parts cause merge pressure and slow inserts — keep parts under a few hundred per table
• Wrong partition key: Over-partitioning creates too many parts; under-partitioning creates huge parts
• FINAL keyword: SELECT ... FINAL deduplicates on read and is slow — avoid on large tables
• Memory limits: ClickHouse can OOM on large GROUP BY — check max_memory_usage setting
• Nullable columns: Nullable columns use extra storage and slower processing — avoid when possible
• String vs LowCardinality: Use LowCardinality(String) for columns with < 10K distinct values
• Distributed tables: Queries on Distributed tables fan out to all shards — filter early