From clickhouse-best-practices
Sets up a complete local ClickHouse development environment using clickhousectl: install, initialize project structure, start server, create tables, seed data, and query.
How this skill is triggered — by the user, by Claude, or both
Slash command
/clickhouse-best-practices:clickhousectl-local-devThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
This skill walks through setting up a complete local ClickHouse development environment using `clickhousectl`. Follow these steps in order.
This skill walks through setting up a complete local ClickHouse development environment using clickhousectl. Follow these steps in order.
Use this skill when the user wants to:
Check if clickhousectl is already available:
which clickhousectl
If not found, install it:
curl -fsSL https://clickhouse.com/cli | sh
This installs clickhousectl to ~/.local/bin/clickhousectl and creates a chctl alias.
If the command is still not found after install: The user may need to add ~/.local/bin to their PATH or open a new terminal session. Suggest:
export PATH="$HOME/.local/bin:$PATH"
Once installed, clickhousectl skills can be used to install the latest ClickHouse Agent Skills.
Install the latest ClickHouse version and set it as the system default:
clickhousectl local use latest
This installs ClickHouse, sets it as the default version used by clickhousectl local commands, and symlinks ~/.local/bin/clickhouse to the binary, putting clickhouse on your PATH (meaning you can invoke clickhouse directly, e.g. clickhouse client if needed).
You can use other version specifiers like stable, 26.4, 26.4.2.10 when needed.
From the user's project root directory:
clickhousectl local init
This creates a standard folder structure:
clickhouse/
tables/ # CREATE TABLE statements
materialized_views/ # Materialized view definitions
queries/ # Saved queries
seed/ # Seed data / INSERT statements
Note: This step is optional. If the user already has their own folder structure for SQL files, skip this and adapt the later steps to use their paths.
clickhousectl local server start --name <name>
This starts a ClickHouse server in the background.
To check running servers and see their exposed ports:
clickhousectl local server list
Based on the user's application requirements, write CREATE TABLE SQL files.
Write each table definition to its own file in clickhouse/tables/:
# Example: clickhouse/tables/events.sql
CREATE TABLE IF NOT EXISTS events (
timestamp DateTime,
user_id UInt32,
event_type LowCardinality(String),
properties String
)
ENGINE = MergeTree()
ORDER BY (event_type, timestamp)
When designing schemas, if the clickhouse-best-practices skill is available, consult it for guidance on ORDER BY column selection, data types, and partitioning.
Apply the schema to the running server:
clickhousectl local client --name <name> --queries-file clickhouse/tables/events.sql
If the user needs sample data for development, write INSERT statements to clickhouse/seed/:
# Example: clickhouse/seed/events.sql
INSERT INTO events (timestamp, user_id, event_type, properties) VALUES
('2024-01-01 00:00:00', 1, 'page_view', '{"page": "/home"}'),
('2024-01-01 00:01:00', 2, 'click', '{"button": "signup"}');
Apply seed data:
clickhousectl local client --name <name> --queries-file clickhouse/seed/events.sql
Confirm tables were created:
clickhousectl local client --name <name> --query "SHOW TABLES"
Run a test query:
clickhousectl local client --name <name> --query "SELECT count() FROM events"
If the user wants to use a managed ClickHouse service, use the clickhousectl-cloud-deploy skill to help the user deploy to ClickHouse Cloud.
npx claudepluginhub clickhouse/agent-skills --plugin chdb-sqlSets up local ClickHouse via Docker Compose with schema init scripts, Node.js data seeding, for dev iteration and vitest integration tests.
Walks through deploying ClickHouse to ClickHouse Cloud using clickhousectl: account setup, CLI authentication, service creation, schema migration, and application connection.
Provides ClickHouse patterns for MergeTree table design, query optimization, aggregations, data ingestion, and analytics. Useful for OLAP workloads, schema design, performance tuning, and migrations from PostgreSQL/MySQL.