clickhouse-explorer

Use when the user asks to explore, analyze, query, or export data from a ClickHouse database. Also use when asked about table schemas, data distributions, or ClickHouse data profiling.

الانتقال إلى التثبيت

المستودع: samChang72/custom-skills
آخر نشاط في المصدر: ١٦ يونيو ٢٠٢٦ في ٠٦:١١
لغة SKILL.md المكتشفة: الإنجليزية
النجوم: ١
التفرعات: ٠

خيارات التثبيت

يُحدَّد Prompt الذي يراجع المصدر أولًا بشكل افتراضي. يمكنك التبديل إلى أمر مباشر أو تنزيل نسخة محلية.

مراجعة ملفات المصدر

اقرأ SKILL.md وأي ملفات مرافقة يعرضها SkillsMP قبل أن تقرر التثبيت.

العودة إلى البحث العودة إلى التثبيت

name	clickhouse-explorer
description	Use when the user asks to explore, analyze, query, or export data from a ClickHouse database. Also use when asked about table schemas, data distributions, or ClickHouse data profiling.

ClickHouse Explorer CLI Skill

MANDATORY: CLI Only

所有查詢必須透過 CLI 工具執行，無例外。

npx tsx src/index.ts <command>   # ✅ 唯一允許的方式

絕對禁止：

curl 直接打 ClickHouse HTTP API
Bash 中使用 @clickhouse/client
任何繞過 src/index.ts 的 Node.js 腳本

When to Use

User asks to "explore", "analyze", "look at", "check" a ClickHouse database
User asks about table structure, column types, data volume, or partitions
User wants to sample data, check value distributions, or run ad-hoc queries
User needs to export query results to CSV, JSON, or Parquet
User mentions ClickHouse in the context of data exploration

Prerequisites

Before first use, ensure environment variables are set:

export CLICKHOUSE_HOST=https://<host>:8443
export CLICKHOUSE_USER=<username>
export CLICKHOUSE_PASSWORD=<password>

Verify with: clickhouse-cli ping

Command Reference

Schema Discovery

clickhouse-cli databases                       # list all databases
clickhouse-cli tables <database>               # list tables (engine, rows, size)
clickhouse-cli describe <db>.<table>           
clickhouse-cli size <db>.<table>               
clickhouse-cli partitions <db>.<table>

clickhouse-cli sample <db>.<table> [n]         # sample n rows (default 10)
clickhouse-cli stats <db>.<table> [column]     # column statistics
clickhouse-cli distribution <db>.<table> <col> [topN]  # value distribution

clickhouse-cli query "<SQL>" [--format json] [--output exports/result.csv] [--limit 500]

Step 1: ping          → verify connection is alive
Step 2: databases     → discover available databases
Step 3: tables <db>   → identify relevant tables
Step 4: describe      → understand column structure
Step 5: size          → gauge data volume before querying
Step 6: sample 5      → see real data shape
Step 7: stats         → get column-level aggregates
Step 8: distribution  → find dominant values / cardinality
Step 9: query         → run targeted SQL based on findings

Rule	Detail
Identifiers	`^[A-Za-z0-9_]+$` only, max 128 chars
Output paths	Must resolve inside `exports/` directory
Password	Only via `CLICKHOUSE_PASSWORD` env var (no CLI flag)
Auto LIMIT	Appended to queries missing explicit LIMIT (default 1000)
Read-only	No DDL/DML — exploration and SELECT only
Max sample	10,000 rows
Max distribution	top 1,000 values

Blocked Pattern	Risk	Example
DDL statements	Data loss, schema corruption	`DROP TABLE`, `ALTER TABLE DROP COLUMN`, `TRUNCATE`
DML statements	Data corruption	`INSERT`, `DELETE`, `UPDATE`
System mutations	Cluster instability	`SYSTEM STOP MERGES`, `SYSTEM KILL`, `DETACH`
User/permission changes	Privilege escalation	`CREATE USER`, `GRANT`, `REVOKE`
External data exfiltration	Data breach	`SELECT ... INTO OUTFILE`, `url()` table function
Unbounded full scans	Server OOM / CPU spike	`SELECT * FROM <billion_row_table>` without LIMIT
Expensive JOINs without LIMIT	Memory exhaustion	`SELECT * FROM a JOIN b ON ...` (no LIMIT, no WHERE)
Nested subqueries on large tables	Query queue saturation	Deeply nested `SELECT` in `FROM` on billion-row tables
`SETTINGS max_execution_time=0`	Bypass timeout protection	Overriding server-side safety settings

Operation	Safe Limit	Rationale
Exploration queries (`sample`, `stats`)	`--limit 50`	Enough to understand data shape
Ad-hoc SELECT	`--limit 1000` (default)	Balanced for analysis
Export for downstream use	`--limit 100000`	Cap at 100K unless user explicitly justifies more
Concurrent queries	1 at a time	Avoid parallel queries that compound server load
Query timeout	30s (default)	Do not override `requestTimeout`

Error	Fix
Connection failed	Check `CLICKHOUSE_HOST`, `CLICKHOUSE_USER`, `CLICKHOUSE_PASSWORD`
Invalid identifier	Remove special characters from database/table/column names
Output path rejected	Use paths starting with `exports/` (e.g., `exports/data.csv`)
Parquet requires --output	Add `--output exports/file.parquet`
Timeout	Reduce `--limit` or simplify query; default timeout is 30s

src/
├── index.ts                      # CLI entry (Commander.js)
├── cli/commands/
│   ├── query.ts                  # query subcommand
│   └── explore.ts                # schema + analysis subcommands
├── cli/repl.ts                   # interactive REPL
├── client/
│   ├── connection.ts             # ClickHouse client singleton
│   └── query.ts                  # query execution (JSON, JSONEachRow, raw)
├── config/settings.ts            # Zod-validated settings (env + CLI merge)
├── explore/
│   ├── schema.ts                 # databases, tables, describe, size, partitions
│   ├── stats.ts                  # tableStats, columnDistribution
│   └── sample.ts                 # sampleData
├── output/
│   ├── formatter.ts              # format dispatcher + file write
│   ├── table.ts                  # cli-table3 terminal output
│   ├── json.ts                   # JSON formatting
│   ├── csv.ts                    # CSV via csv-stringify
│   └── parquet.ts                # ClickHouse FORMAT Parquet → file
└── validation/
    ├── identifiers.ts            # SQL identifier allowlist + backtick quoting
    └── paths.ts                  # output path sandboxing (exports/ only)