metadata.json
{
"version": "0.3.0",
"organization": "ClickHouse Inc",
"date": "July 2026",
"abstract": "Workflows for running ClickHouse with clickhousectl, covering both local and cloud. Local: setting up a development environment — installing ClickHouse, initializing a project, starting a server, creating schemas, seeding data, and verifying the setup. Cloud: deploying to ClickHouse Cloud — signing up, authenticating the CLI, creating a service, migrating local schemas, and connecting an application with a dedicated user. The top-level skill routes between ref/local.md and ref/cloud.md, and the local workflow hands off to cloud when going to production. Supersedes the clickhousectl-local-dev and clickhousectl-cloud-deploy skills.",
"references": [
"https://clickhouse.com/docs",
"https://github.com/ClickHouse/clickhousectl",
"https://clickhouse.cloud"
]
}
README.md
# infra-clickhouse — maintainer guide
A workflow skill covering ClickHouse via `clickhousectl`, in the normalized infra-skill format (shared with `infra-postgres`):
```
SKILL.md # decision tree: local vs cloud, shared prerequisites
ref/local.md # local ClickHouse development (clickhousectl local ...)
ref/cloud.md # ClickHouse Cloud deployment (clickhousectl cloud ...)
```
`SKILL.md` stays thin — it routes to the right ref. `ref/local.md` ends with a "going to production" pointer to `ref/cloud.md`.
This skill merges and supersedes the former `clickhousectl-local-dev` and `clickhousectl-cloud-deploy` skills.
## Maintenance
The content mirrors the `clickhousectl` CLI surface. When the CLI changes, re-verify against the built-in help:
```bash
clickhousectl local --help
clickhousectl cloud --help
```
and each subcommand's `--help` (the `CONTEXT FOR AGENTS` sections in the help output are the source of truth).
Bump `version` in both the SKILL.md frontmatter and `metadata.json` when editing, and keep the SKILL.md frontmatter description emphasizing **both** the local and cloud cases — it is what triggers skill activation.
ref/cloud.md
# ClickHouse Cloud
Deploying to ClickHouse Cloud with `clickhousectl`: account setup, CLI authentication, service creation, schema migration, and connecting the application. Follow these steps in order.
## Step 1: Sign up for ClickHouse Cloud
Before using any cloud commands, the user needs a ClickHouse Cloud account.
**Ask the user:** "Do you already have a ClickHouse Cloud account?"
**If they do not have an account**, explain:
> ClickHouse Cloud is a fully managed service that runs ClickHouse for you — no infrastructure to maintain, automatic scaling, backups, and upgrades included. There's a free trial so you can get started without a credit card.
>
> To create an account, go to: **https://clickhouse.cloud**
>
> Sign up with your email, Google, or GitHub account. Once you're in the console, let me know and we'll continue with the next step.
**Wait for the user to confirm** they have signed up or already have an account before proceeding.
## Step 2: Authenticate the CLI
Authenticate `clickhousectl` with a ClickHouse Cloud API key. Write operations (creating services, users, etc.) require API key auth — OAuth login is read-only.
### Create an API key
Guide the user through creating one in the ClickHouse Cloud console:
> 1. Click the **gear icon** (Settings) in the left sidebar
> 2. Go to **API Keys**
> 3. Click **Create API Key**
> 4. Give it a name (e.g., "clickhousectl")
> 5. Select the **Admin** role for the key. Admin is needed because `cloud service query` auto-provisions a per-service query endpoint API key on first use, which requires permission to create keys. Developer-scoped keys can manage services but may not be able to complete the auto-provisioning step.
> 6. Click **Generate API Key**
> 7. **Copy both the Key ID and the Key Secret** — the secret is only shown once
### Authenticate clickhousectl with the key
Ask the user to **open a new terminal tab in the same working directory** and run the login command there with their Key ID and Secret — this keeps the secret out of the chat session. Tell them to come back and let you know once it's done.
```bash
clickhousectl cloud auth login --api-key <key> --api-secret <secret>
```
Both `--api-key` and `--api-secret` are required — if the user only has one, tell them both are needed.
**To verify authentication works:**
```bash
clickhousectl cloud auth status
clickhousectl cloud org list
```
This should return the user's organization.
## Step 3: Create a cloud service
Create a new ClickHouse Cloud service:
```bash
clickhousectl cloud service create --name <service-name>
```
From the output, add the HTTPS host and port to `.env` as `CLICKHOUSE_HOST` and `CLICKHOUSE_PORT`. Make sure `.env` is gitignored.
Then poll until the service state is `running`:
```bash
clickhousectl cloud service get <service-id>
```
## Step 4: Migrate schemas
If the user has local table definitions (e.g., from the local workflow in [local.md](local.md)), migrate them to the cloud service.
Use `cloud service query` to run SQL against the cloud service over HTTP. Just pass the service name (or `--id`).
**Read the local schema files** from `clickhouse/tables/` and apply each one to the cloud service:
```bash
clickhousectl cloud service query --name <service-name> \
--queries-file clickhouse/tables/<table>.sql
```
Apply them in dependency order — tables referenced by materialized views should be created first.
**Also apply materialized views** if they exist:
```bash
clickhousectl cloud service query --name <service-name> \
--queries-file clickhouse/materialized_views/<view>.sql
```
To target a specific database, pass `--database <name>`.
## Step 5: Verify the deployment
Connect to the cloud service and confirm tables exist:
```bash
clickhousectl cloud service query --name <service-name> --query "SHOW TABLES"
```
Run a test query to confirm the schema is correct:
```bash
clickhousectl cloud service query --name <service-name> --query "DESCRIBE TABLE <table-name>"
```
## Step 6: Create a dedicated user for the application
The `default` user has full admin rights and should not be used by the application. Create a dedicated user scoped to the schema deployed in Step 4.
Generate a strong random password and append the credentials to `.env` **before** creating the user, so the password is persisted even if a subsequent step fails:
```bash
PASSWORD=$(openssl rand -base64 32)
echo "CLICKHOUSE_USER=app_user" >> .env
echo "CLICKHOUSE_PASSWORD=$PASSWORD" >> .env
```
Then create the user and grant the minimum permissions the app needs. Replace `<database>` with the database the schema lives in (often `default`):
```bash
clickhousectl cloud service query --name <service-name> --query \
"CREATE USER app_user IDENTIFIED BY '$PASSWORD'"
clickhousectl cloud service query --name <service-name> --query \
"GRANT SELECT, INSERT ON <database>.* TO app_user"
```
Adjust the grants to fit the app:
- Read-only app → drop `INSERT`
- Needs to create/drop its own tables → also grant `CREATE TABLE, DROP TABLE` on the database (but prefer running migrations as the admin user instead)
- Multiple databases → repeat the `GRANT` per database, or scope per table with `ON <database>.<table>`
Verify the user exists and has the expected grants:
```bash
clickhousectl cloud service query --name <service-name> --query "SHOW GRANTS FOR app_user"
```
ClickHouse cannot reveal the password later, so if `.env` is lost, the user must reset the password via `ALTER USER app_user IDENTIFIED BY '<new>'`.
---
The application can now use the credentials in `.env` to connect to ClickHouse Cloud.
ref/local.md
# Local ClickHouse for development
Setting up a complete local ClickHouse development environment with `clickhousectl`. Follow these steps in order.
## Step 1: Install ClickHouse and set the default
Install the latest ClickHouse version and set it as the system default:
```bash
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.
## Step 2: Initialize the project
From the user's project root directory:
```bash
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.
## Step 3: Start a local server
```bash
clickhousectl local server start --name <name>
```
This starts a ClickHouse server in the background.
**To check running servers and see their exposed ports:**
```bash
clickhousectl local server list
```
## Step 4: Create the schema
Based on the user's application requirements, write CREATE TABLE SQL files.
**Write each table definition to its own file** in `clickhouse/tables/`:
```bash
# Example: clickhouse/tables/events.sql
```
```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:**
```bash
clickhousectl local client --name <name> --queries-file clickhouse/tables/events.sql
```
## Step 5: Seed data (optional)
If the user needs sample data for development, write INSERT statements to `clickhouse/seed/`:
```bash
# Example: clickhouse/seed/events.sql
```
```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:**
```bash
clickhousectl local client --name <name> --queries-file clickhouse/seed/events.sql
```
## Step 6: Verify the setup
Confirm tables were created:
```bash
clickhousectl local client --name <name> --query "SHOW TABLES"
```
Run a test query:
```bash
clickhousectl local client --name <name> --query "SELECT count() FROM events"
```
## Going to production
When the user is ready to move from local development to a managed ClickHouse Cloud service, read [cloud.md](cloud.md) — it covers authentication, creating the service, migrating the local schema, and connecting the application.
SKILL.md
---
name: infra-clickhouse
description: Sets up and manages ClickHouse using the clickhousectl CLI — installs and runs a local ClickHouse server for development, and creates managed ClickHouse Cloud services for production (authentication, service creation, schema migration, application connection). Use when the user wants to build an application with ClickHouse, set up a local ClickHouse dev environment, create tables and start querying, deploy ClickHouse to production or ClickHouse Cloud, or migrate from a local setup to the cloud.
license: Apache-2.0
metadata:
author: ClickHouse Inc
version: "0.3.0"
---
# ClickHouse with clickhousectl
`clickhousectl` manages ClickHouse in two environments:
- **Local** — ClickHouse installed and running on the user's machine, for development.
- **Cloud** — managed ClickHouse Cloud services, for production: fully managed, automatic scaling, backups, and upgrades.
This file routes to the right reference. The step-by-step workflows live in `ref/local.md` and `ref/cloud.md` — read the one that matches the user's situation before running commands.
## Which reference to use
| The user wants to... | Read |
|----------------------|------|
| Build an app with ClickHouse, develop or prototype locally, no cloud account needed | [ref/local.md](ref/local.md) |
| Go to production, host a managed ClickHouse, or use ClickHouse Cloud explicitly | [ref/cloud.md](ref/cloud.md) |
| Operate an existing cloud service (schemas, users, queries against it) | [ref/cloud.md](ref/cloud.md) |
| Develop locally now, ship to production later | Start with [ref/local.md](ref/local.md); it points to [ref/cloud.md](ref/cloud.md) when it's time to go to prod |
If it's genuinely ambiguous (e.g. "set up ClickHouse for my app"), default to local for development tasks and ask before creating anything in the cloud — cloud services cost money.
## Prerequisites (both workflows)
Check that `clickhousectl` is installed:
```bash
which clickhousectl
```
If not found, install it:
```bash
curl -fsSL https://clickhouse.com/cli | sh
```
This installs to `~/.local/bin/clickhousectl` (with a `chctl` alias). If the command is still not found, suggest `export PATH="$HOME/.local/bin:$PATH"` or a new terminal.
All commands accept `--json` for machine-readable output. Exit codes follow `gh` conventions: 0 success, 1 error, 2 cancelled, 4 auth required.
## Related
- When designing schemas, consult the `clickhouse-best-practices` skill for ORDER BY selection, data types, and partitioning.
- For Postgres (local development or managed ClickHouse Cloud Postgres), use the `infra-postgres` skill.