Analyzing Clickhouse

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

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