Claude Code skill pack for ClickHouse (24 skills)

24 Skills

MIT License

Installation

Open Claude Code and run this command:

/plugin install clickhouse-pack@claude-code-plugins-plus

Use --global to install for all projects, or --project for current project only.

What It Does

> 24 skills for building, operating, and scaling ClickHouse-powered analytics — real @clickhouse/client code, real SQL, real MergeTree engines.

Every skill uses the official ClickHouse Node.js client (@clickhouse/client with createClient), actual ClickHouse SQL syntax (MergeTree, ReplacingMergeTree, AggregatingMergeTree), real system tables (system.parts, system.query_log, system.merges), and production patterns (parameterized queries, streaming inserts, materialized views).

Links: tonsofskills.com | ClickHouse Docs | @clickhouse/client

Skills (24)

clickhouse-ci-integration View full skill →

Run ClickHouse integration tests in CI with GitHub Actions and Docker containers.

ReadWriteEditBash(gh:*)

ClickHouse CI Integration

Overview

Run integration tests against a real ClickHouse server in GitHub Actions using

Docker service containers. No mocks needed for schema and query validation —

the workflow spins up clickhouse/clickhouse-server, applies your schema, and

runs unit + integration tests against the live instance.

This skill produces four artifacts: a GitHub Actions workflow, a shared test

setup, integration/schema test files, and the package.json scripts that tie

them together. SKILL.md gives you the workflow skeleton and the moving parts;

the full copy-paste-ready test harness lives in

references/implementation.md and

references/examples.md.

Prerequisites

GitHub repository with Actions enabled
@clickhouse/client in project dependencies
Test suite (vitest or jest)

Instructions

Read any existing .github/workflows/ and package.json first, then create or

edit the four artifacts below.

Step 1: Add the workflow with a ClickHouse service container

Create .github/workflows/clickhouse-tests.yml. The core is a services.clickhouse

block with a health check plus a schema-apply step before tests run:


services:
  clickhouse:
    image: clickhouse/clickhouse-server:latest
    ports: ["8123:8123", "9000:9000"]
    options: >-
      --health-cmd "wget --no-verbose --tries=1 --spider http://localhost:8123/ping || exit 1"
      --health-interval 10s --health-timeout 5s --health-retries 5

Full workflow (checkout, Node setup, npm ci, schema-apply loop, unit +

integration steps, and credential handling): references/implementation.md Step 1.

Step 2: Wire the shared test setup

Add tests/setup-integration.ts — it creates a @clickhouse/client, pings on

beforeAll to fail fast if the service is unreachable, TRUNCATEs between

tests, and closes on afterAll. See

references/implementation.md Step 2 for the file.

Step 3: Write the integration and schema tests

Add tests/events.integration.test.ts (insert → aggregate → assert, plus

parameterized-query and empty-result cases) and

tests/schema.integration.test.ts (asserts column types + table engine via

system.columns / system.tables). Both files:

references/examples.md.

Step 4: Add package scripts and (optionally) a version matrix

Edit packag


                
                  
                  clickhouse-common-errors
                  View full skill →
                
                
                  Diagnose and fix the top 15 ClickHouse errors — query failures, insert problems, memory limits, and merge issues.
                  
                      ReadGrepBash(curl:*)
                    
                
                
                  ClickHouse Common Errors
Overview
Quick reference for the most common ClickHouse errors with real error codes,
diagnostic queries, and proven solutions. The three highest-frequency errors are
inline below; the full catalog of 10 errors plus system-table diagnostics lives
in references/error-reference.md.
Prerequisites

Access to a ClickHouse endpoint — either the native clickhouse-client or the

HTTP interface (curl against :8123).

Permission to read the system.* introspection tables (system.parts,

system.processes, system.query_log, system.columns, system.replicas).

The failing statement's text and, ideally, the raw exception string — the

parenthetical name (e.g. MEMORYLIMITEXCEEDED) and numeric code drive lookup.
Instructions
Follow this loop to turn a raw ClickHouse exception into a verified fix:

Capture the exception name and code. Read the error string the client

returned. If you only have a log file, use Grep to pull the matching line —
Grep for DB::Exception or a specific token like MEMORYLIMITEXCEEDED
across the log to isolate the failure.

Map it to a category. Use the Error Handling code table

to classify the error as Schema, Query, Performance, Permissions, Concurrency,
Resources, or Insert-pattern.

Apply the inline fix for the three top errors (Too Many Parts, Memory

Limit, Syntax) below, or open
references/error-reference.md for the other
seven plus copy-paste diagnostic queries.

Confirm with a system table. Re-run the relevant system.* query (part

count, system.processes, system.query_log) to prove the condition cleared
rather than assuming the fix took.
Top 3 errors (inline)
Too Many Parts (Code 252) — hundreds of tiny inserts outpace merges:

-- Check current part count per table
SELECT database, table, count() AS part_count
FROM system.parts WHERE active GROUP BY database, table ORDER BY part_count DESC;

-- Temporary relief; permanent fix is batching (10K+ rows per INSERT)
ALTER TABLE events MODIFY SETTING parts_to_throw_insert = 1000;  -- default 300

Memory Limit Exceeded (Code 241) — query wants more RAM than maxmemoryusage:

SET max_memory_usage = 20000000000;


                
                  
                  clickhouse-core-workflow-a
                  View full skill →
                
                
                  Design ClickHouse schemas with MergeTree engines, ORDER BY keys, and partitioning.
                  
                      ReadWriteEditBash(npm:*)Grep
                    
                
                
                  ClickHouse Schema Design (Core Workflow A)
Overview
Design ClickHouse tables with correct engine selection, ORDER BY keys,
partitioning, and codec choices for analytical workloads. This skill covers the
four schema decisions that determine query speed and storage cost — engine,
sort key, partition expression, and column codecs — then points to
references/ for full DDL and the programmatic apply path.
Prerequisites

@clickhouse/client connected (see clickhouse-install-auth)
Understanding of your query patterns (what you filter and group on)

Instructions
Step 1: Choose the Right Engine

Engine
Best For
Dedup?
Example


MergeTree
General analytics, append-only logs
No
Clickstream, IoT


ReplacingMergeTree
Mutable rows (upserts)
Yes (on merge)
User profiles, state


SummingMergeTree
Pre-aggregated counters
Sums numerics
Page view counts


AggregatingMergeTree
Materialized view targets
Merges states
Dashboards


CollapsingMergeTree
Stateful row updates
Collapses +-1
Shopping carts


ClickHouse Cloud uses SharedMergeTree — it is a drop-in replacement for
MergeTree on Cloud. You do not need to change your DDL.
Step 2: Design the ORDER BY (Sort Key)
The ORDER BY clause is the single most important schema decision. It defines:

Primary index — sparse index over sort-key granules (8192 rows default)
Data layout on disk — rows sorted physically by these columns
Query speed — queries filtering on ORDER BY prefix columns hit fewer granules

Rules of thumb:

Put low-cardinality filter columns first (event_type, status)
Then high-cardinality columns you filter on (userid, tenantid)
End with a time column if you use range filters (created_at)
Do NOT put high-cardinality columns you never filter on in ORDER BY


-- Good: filter by tenant, then by time ranges
ORDER BY (tenant_id, event_type, created_at)

-- Bad: UUID first means every query scans the full index
ORDER BY (event_id, created_at)  -- event_id is random UUID

Step 3: Write the Table DDL
Start from the append-only event skeleton below, then adapt the engine and sort
key to your access pattern. Full DDL for the three ca


                
                  
                  clickhouse-core-workflow-b
                  View full skill →
                
                
                  Insert, query, and aggregate data in ClickHouse with real SQL patterns.
                  
                      ReadWriteEditBash(npm:*)
                    
                
                
                  ClickHouse Insert & Query (Core Workflow B)
Overview
Move data into ClickHouse efficiently, then answer analytical questions with
aggregations, funnels, retention, window functions, and materialized views.
This skill covers the read/write half of the core workflow: the fast-path insert
patterns that avoid "too many parts", the parameterized query API for Node.js,
and pre-aggregation via materialized views. The high-frequency patterns live
inline below; the deep query library and advanced engine patterns are broken out
into references/ so you can drill in only when you need them.
Prerequisites

Tables already created — run clickhouse-core-workflow-a first if not.
@clickhouse/client installed and connected (CLICKHOUSEHOST, CLICKHOUSEUSER,

CLICKHOUSE_PASSWORD in the environment).

A target database/table (examples use analytics.events).

Instructions
Step 1: Bulk insert (the fast path)
Batch rows and let the client buffer. ClickHouse writes a new "part" per INSERT,
so many tiny inserts are the number-one performance mistake.

import { createClient } from '@clickhouse/client';

const client = createClient({
  url: process.env.CLICKHOUSE_HOST!,
  username: process.env.CLICKHOUSE_USER ?? 'default',
  password: process.env.CLICKHOUSE_PASSWORD ?? '',
});

// Insert many rows efficiently — @clickhouse/client buffers internally
await client.insert({
  table: 'analytics.events',
  values: events,   // Array of objects matching table columns
  format: 'JSONEachRow',
});

Streaming a file (CSV, Parquet, etc.) uses the same call with a read stream and
the matching format (e.g. CSVWithNames).
Insert best practices:

Batch rows: aim for 10K-100K rows per INSERT (not one at a time).
ClickHouse creates a new "part" per INSERT — too many small inserts cause "too many parts".
For real-time streams, buffer 1-5 seconds then flush.

Step 2: Analytical queries
Aggregate with count(), uniqExact(), and time filters. The canonical
"top events by tenant" shape:

SELECT tenant_id, event_type, count() AS event_count, uniqExact(user_id) AS unique_users
FROM analytics.events
WHERE created_at >= now() - INTERVAL 7 DAY
GROUP BY tenant_id, event_type
ORDER BY event_count DESC
LIMIT 100;

Funnel, retention, and safe parameterized-query patterns are in
references/queries.md.
Step 3: Pre-aggregation and windowing
For dashboards, pre-aggregate on INSERT with a materialized view backed by an


                
                  
                  clickhouse-cost-tuning
                  View full skill →
                
                
                  Optimize ClickHouse Cloud costs — compute scaling, storage tiering, compression, and query efficiency for lower bills.
                  
                      Read
                    
                
                
                  ClickHouse Cost Tuning
Overview
Reduce ClickHouse Cloud costs through storage optimization, compression tuning,
TTL policies, compute scaling, and query efficiency improvements. This skill walks
the bill from top driver to fix: identify what you actually pay for, then apply the
codec, TTL, compute, and query changes that move the number.
Deep copy-paste queries for every step live in
references/implementation.md; end-to-end scenarios
live in references/examples.md.
Prerequisites

ClickHouse Cloud account with billing access
Understanding of current data volumes and query patterns

Instructions
Step 1: Understand what you pay for
ClickHouse Cloud bills on four axes — and the biggest one is usually compute,
not storage, because ClickHouse compresses data 10-20x.

Component
Pricing Model
Key Driver


Compute
Per-hour per replica
vCPU + memory tier


Storage
Per GB-month
Compressed data on disk


Network
Per GB egress
Query result sizes


Backups
Per GB stored
Backup retention


Step 2: Find the top cost driver
Break storage down by table, then by column, to find bloated data. The starter
query — full breakdowns in references/implementation.md:

SELECT database, table,
    formatReadableSize(sum(bytes_on_disk)) AS compressed_size,
    round(sum(data_uncompressed_bytes) / sum(bytes_on_disk), 1) AS compression_ratio
FROM system.parts WHERE active
GROUP BY database, table ORDER BY sum(bytes_on_disk) DESC;

A column with a low compression ratio (e.g. 2x on a text/JSON blob) is your lever.
Step 3: Improve compression
Apply codecs matched to the data shape — ZSTD(3) for JSON/text, Delta, ZSTD
for sequential IDs, DoubleDelta, ZSTD for timestamps — then OPTIMIZE ... FINAL
to re-merge. Full codec cheat sheet and verification queries in
references/implementation.md.
Step 4: Expire and tier old data with TTL
Add TTL to delete or move cold data automatically, or drop whole partitions for an
immediate one-time reclaim. See the tiered hot/cold/delete TTL pattern in
references/implementation.md.
Step 5: Cut compute cost
Enable auto-scaling and idle suspension in the Cloud Console, cap per-query cores
and memory (maxthreads, maxmemory_usage), and batch


                
                  
                  clickhouse-data-handling
                  View full skill →
                
                
                  Handle data lifecycle in ClickHouse — TTL expiration, data deletion (GDPR), column-level encryption, and audit logging with real ClickHouse SQL.
                  
                      ReadWriteEdit
                    
                
                
                  ClickHouse Data Handling
Overview
Manage the full data lifecycle in ClickHouse: TTL-based expiration, GDPR/CCPA
deletion, data masking, partition management, and audit trails. This skill
produces migration SQL and TypeScript client code you write into your project,
then verifies the results against ClickHouse system.* tables.
The workflow below is the high-level path — each step links to the full,
copy-ready SQL/TypeScript in references/implementation.md,
with end-to-end scenarios in references/examples.md.
Prerequisites
Before starting, confirm you have:

Populated ClickHouse tables to operate on (schema comes from the companion

skill clickhouse-core-workflow-a).

A written data-retention policy: how long each data class is kept, and which

columns hold PII. The Data Classification table maps
each class to its ClickHouse handling.

ClickHouse 23.3+ if you plan to use lightweight DELETE FROM; older versions

must use mutation-based ALTER TABLE ... DELETE.

Access to system.mutations and system.parts to verify deletions.

Instructions
Work the six steps in order for a new table, or jump to the one you need. Use
Write/Edit to place the generated SQL into a migration file (or the
TypeScript into your data-access layer), then run it against ClickHouse and
verify via the system.* queries. Full code for each step lives in
references/implementation.md.

TTL-based expiration — attach a TTL clause so data self-deletes, or use

tiered TO VOLUME storage (hot → cold → delete) and column-level TTL to null
out PII while keeping the row. Skeleton:

   ALTER TABLE analytics.events
       MODIFY TTL created_at + INTERVAL 90 DAY;


GDPR/CCPA deletion — choose lightweight DELETE FROM (23.3+), verifiable

ALTER TABLE ... DELETE (the compliant path), or DROP PARTITION for bulk.
Always confirm completion in system.mutations.

Masking & anonymization — expose a CREATE VIEW that sipHash64-hashes

identifiers and shows only email domains, gated by a dictionary allowlist.

DSAR export & delete — the TypeScript exportUserData / deleteUserData

helpers loop every table for one user_id and log each deletion.


                
              

                
                  
                  clickhouse-debug-bundle
                  View full skill →
                
                
                  Collect ClickHouse diagnostic data — system tables, query logs, merge status, and server metrics for support tickets and troubleshooting.
                  
                      ReadBash(curl:*)Bash(tar:*)
                    
                
                
                  ClickHouse Debug Bundle
Overview
Collect comprehensive diagnostic data from ClickHouse system.* tables for
troubleshooting performance issues, merge problems, or support escalation. The
skill runs a graduated set of queries — server health, disk and table health,
query performance, and merge/mutation status — then packages the output into a
single artifact you can attach to a support ticket.
Prerequisites

Access to a ClickHouse server with SELECT permission on system.* tables

(grant SELECT ON system.* to a restricted user if needed).

Either curl (for the HTTP interface, port 8123) or clickhouse-client.
Connection settings exported as environment variables so no credentials are

hardcoded: CLICKHOUSEHOST, CLICKHOUSEUSER, CLICKHOUSE_PASSWORD.

For deep query-log analysis, log_queries = 1 must be enabled on the server.

Instructions
Work through the four diagnostic areas below. For an interactive investigation,
run the query for the symptom you are chasing; to produce a full artifact, run
the automated collector in Step 5. The complete query set for every step lives
in references/diagnostic-queries.md.
Step 1: Server health overview
Confirm the server version, uptime, and current-load gauges first — this frames
every later finding.

SELECT
    version()                       AS version,
    uptime()                        AS uptime_seconds,
    formatReadableTimeDelta(uptime()) AS uptime_human,
    currentDatabase()               AS current_db;

Then snapshot system.metrics for the key gauges (Query, Merge,
MemoryTracking, connection counts). Full metric list in the reference.
Step 2: Disk and table health
Find the largest tables and any table under merge pressure (too many active
parts). The full query set covers per-table disk usage, the parts > 100
merge-pressure check, and per-disk free space from system.disks.

-- Tables with too many parts (merge pressure)
SELECT database, table, count() AS parts
FROM system.parts WHERE active
GROUP BY database, table
HAVING parts > 100
ORDER BY parts DESC;

Step 3: Query performance analysis
Pull the slowest queries, failed queries, and normalized query patterns from
system.query_log over the last 24 hours. See the reference for the slow-query,
exception, and normalizedqueryhash aggregation queries.
Step 4: Merge and mutation status
Inspect syste

                

              

                
                  
                  clickhouse-deploy-integration
                  View full skill →
                
                
                  Deploy ClickHouse-backed applications to Vercel, Fly.
                  
                      ReadWriteEditBash(vercel:*)Bash(fly:*)Bash(gcloud:*)
                    
                
                
                  ClickHouse Deploy Integration
Overview
Deploy applications that connect to ClickHouse Cloud from serverless and
container platforms with proper connection management and secrets handling. The
same platform-agnostic connection module drives all three targets — Vercel,
Fly.io, and Cloud Run — so only the secrets mechanism and runtime model change
per platform.
Prerequisites

ClickHouse Cloud instance (or self-hosted with public endpoint)
Platform CLI installed (vercel, fly, or gcloud)
Application tested locally against ClickHouse

Instructions
Step 1: ClickHouse Connection Module (Platform-Agnostic)
Write a singleton client so a serverless cold start reuses one connection
instead of opening a new pool per invocation. Keep maxopenconnections low
for serverless and enable compression to cut egress.

// src/db.ts — singleton for serverless-safe connections
import { createClient, ClickHouseClient } from '@clickhouse/client';

let client: ClickHouseClient | null = null;

export function getClickHouse(): ClickHouseClient {
  if (!client) {
    client = createClient({
      url: process.env.CLICKHOUSE_HOST!,         // https://<host>:8443
      username: process.env.CLICKHOUSE_USER!,
      password: process.env.CLICKHOUSE_PASSWORD!,
      database: process.env.CLICKHOUSE_DATABASE ?? 'default',
      request_timeout: 30_000,
      max_open_connections: 5,    // Low for serverless (many cold starts)
      compression: {
        request: true,            // Saves egress bandwidth
        response: true,
      },
    });
  }
  return client;
}

Step 2: Pick a platform and deploy
Choose the target that matches your runtime model, then set secrets and deploy.
All three import the getClickHouse() module above unchanged.

Vercel (serverless functions) — vercel env add per secret; best for API

endpoints. Keep maxopenconnections at 1-3.

Fly.io (containers) — fly secrets set + a fly.toml health check; best

for long-running apps with persistent connections.

Cloud Run (containers) — secrets via Secret Manager (--set-secrets);

best for event-driven workloads. Set --min-instances=1 to avoid cold-start
connection churn.
Full per-platform commands, the Next.js App Router query example, fly.toml,
the Cloud Run deploy script, and a platform comparison table:
Platform deployment walkthrough.
Step 3: Add health check and graceful shutdown
Expose a /health endpoint that pings ClickHouse (driv
                
              

                
                  
                  clickhouse-enterprise-rbac
                  View full skill →
                
                
                  Configure ClickHouse enterprise RBAC — SQL-based users, roles, row policies, column-level grants, and quota management.
                  
                      ReadWrite
                    
                
                
                  ClickHouse Enterprise RBAC
Overview
Implement enterprise-grade role-based access control in ClickHouse using SQL-based
user management, hierarchical roles, row-level policies, column grants, quotas, and
settings profiles. The workflow builds least-privilege access from the ground up:
create authenticated users, compose reusable roles, then narrow visibility with row
and column policies and cap resource use with quotas.
Follow the seven steps below at a high level from this file; drill into
the full implementation for every SQL statement, and
worked examples for two end-to-end scenarios plus audit queries.
Prerequisites

ClickHouse with access_management = 1 enabled (default in Cloud)
Admin user with GRANT OPTION

Instructions
The build-out is seven steps. Steps 1–3 (users, roles, row security) carry the core
skeleton here; Steps 4–7 (column grants, quotas, settings profiles, and the
application wrapper) are summarized here and fully specified in
references/implementation.md.
Step 1: Create Users with Authentication
Pick an authentication method per user: sha256_password (standard),
doublesha1password (MySQL wire protocol), or bcrypt_password (strongest — use
for admin accounts). Restrict network reach with HOST IP and cap per-user resources
inline with SETTINGS.

CREATE USER app_backend
    IDENTIFIED WITH sha256_password BY 'strong-password-here'
    DEFAULT DATABASE analytics
    HOST IP '10.0.0.0/8'           -- Restrict to VPC
    SETTINGS max_memory_usage = 10000000000,   -- 10GB per query
             max_execution_time = 60;          -- 60s timeout

SHOW CREATE USER app_backend;      -- Verify

Step 2: Create Role Hierarchy
Build leaf-level base roles (datareader, datawriter, schema_manager), then
compose them into job roles (analyst, developer, platform_admin). Grant roles to
users and set a default role that activates on connect.

CREATE ROLE data_reader;
GRANT SELECT ON analytics.* TO data_reader;

CREATE ROLE analyst;
GRANT data_reader TO analyst;      -- Composite inherits base

GRANT analyst TO app_backend;
SET DEFAULT ROLE analyst TO app_backend;
SHOW GRANTS FOR app_backend;       -- Verify the full chain

Step 3: Row-Level Security
Isolate multi-tenant data with row policies — each user sees only rows matching its
USING predicate. A permissive USING 1 = 1 policy lets an admin rol
                
              

                
                  
                  clickhouse-hello-world
                  View full skill →
                
                
                  Create your first ClickHouse table, insert data, and run analytical queries.
                  
                      ReadWriteEditBash(npm:*)Bash(node:*)
                    
                
                
                  ClickHouse Hello World
Overview
Create a MergeTree table, insert rows with JSONEachRow, and run your first
analytical query -- all using the official @clickhouse/client. This is the
smoke test that proves your connection works and teaches the four MergeTree
concepts (ORDER BY, PARTITION BY, TTL, LowCardinality) reused in every
real schema.
Prerequisites

@clickhouse/client installed and connected (see the clickhouse-install-auth

skill for connection setup).

A reachable ClickHouse server (local Docker, ClickHouse Cloud, or self-hosted)

with CLICKHOUSEHOST / CLICKHOUSEUSER / CLICKHOUSE_PASSWORD set as
environment variables.
Instructions
Step 1: Create a MergeTree Table

import { createClient } from '@clickhouse/client';

const client = createClient({
  url: process.env.CLICKHOUSE_HOST ?? 'http://localhost:8123',
  username: process.env.CLICKHOUSE_USER ?? 'default',
  password: process.env.CLICKHOUSE_PASSWORD ?? '',
});

await client.command({
  query: `
    CREATE TABLE IF NOT EXISTS events (
      event_id    UUID DEFAULT generateUUIDv4(),
      event_type  LowCardinality(String),
      user_id     UInt64,
      payload     String,
      created_at  DateTime DEFAULT now()
    )
    ENGINE = MergeTree()
    ORDER BY (event_type, created_at)
    PARTITION BY toYYYYMM(created_at)
    TTL created_at + INTERVAL 90 DAY
  `,
});
console.log('Table "events" created.');

Key concepts:

MergeTree() -- the foundational ClickHouse engine for analytics
ORDER BY -- defines the primary index (sort key); pick columns you filter/group on
PARTITION BY -- splits data into parts by month for efficient pruning
TTL -- automatic data expiration
LowCardinality(String) -- dictionary-encoded string, ideal for columns with < 10K distinct values

For the full engine menu (ReplacingMergeTree, SummingMergeTree, etc.) and the
column-type table, see MergeTree engines & data types.
Step 2: Insert Data with JSONEachRow

await client.insert({
  table: 'events',
  values: [
    { event_type: 'page_view', user_id: 1001, payload: '{"url":"/home"}' },
    { event_type: 'click',     user_id: 1001, payload: '{"button":"signup"}' },
    { event_type: 'page_view', user_id: 1002, payload: '{"url":"/pricing"}' },
    { event_type: 'purc

                

              

                
                  
                  clickhouse-incident-runbook
                  View full skill →
                
                
                  ClickHouse incident response — triage, diagnose, and remediate server issues using system tables, kill stuck queries, and execute recovery procedures.
                  
                      ReadBash(kubectl:*)Bash(curl:*)
                    
                
                
                  ClickHouse Incident Runbook
Overview
Step-by-step procedures for triaging and resolving ClickHouse incidents using
built-in system tables and SQL commands. Start here: assess severity, run quick
triage, walk the decision tree, then jump to the matching remediation procedure.
Prerequisites

Network access to the ClickHouse HTTP interface (default port 8123) or a

working clickhouse-client.

A user with rights to read system.* tables and issue KILL QUERY / ALTER.
Shell access to the host or container for P1 restarts (systemctl, docker,

or kubectl).
Severity Levels

Level
Definition
Response
Examples


P1
ClickHouse unreachable / all queries failing
< 15 min
Server down, OOM, disk full


P2
Degraded performance / partial failures
< 1 hour
Slow queries, merge backlog


P3
Minor impact / non-critical errors
< 4 hours
Single table issue, warnings


P4
No user impact
Next business day
Monitoring gaps, optimization


Instructions
Work the incident top to bottom: triage, classify with the decision tree, then
apply the matching procedure.
1. Quick triage (run first)

# 1. Is ClickHouse alive? (8123 is the default ClickHouse HTTP interface port)
curl -sf 'http://localhost:8123/ping' && echo "UP" || echo "DOWN"

# 2. Can it answer a query?
curl -sf 'http://localhost:8123/?query=SELECT+1' && echo "OK" || echo "QUERY FAILED"

# 3. Check ClickHouse Cloud status
curl -sf 'https://status.clickhouse.cloud' | head -5


-- 4. Server health snapshot (run if server responds)
SELECT
    version()                          AS version,
    formatReadableTimeDelta(uptime())  AS uptime,
    (SELECT count() FROM system.processes) AS running_queries,
    (SELECT value FROM system.metrics WHERE metric = 'MemoryTracking')
        AS memory_bytes,
    (SELECT count() FROM system.merges) AS active_merges;

-- 5. Recent errors
SELECT event_time, exception_code, exception, substring(query, 1, 200) AS q
FROM system.query_log
WHERE type = 'ExceptionWhileProcessing'
  AND event_time >= now() - INTERVAL 10 MINUTE
ORDER BY event_time DESC
LIMIT 10;

2. Decision tree — classify the failure

Server responds to ping?
├─ NO → Check process/container status, disk space, OOM killer logs
│       └─ Container/process dead → Restart, check logs
│       └─ Disk full → Emergency: drop old

                

              

                
                  
                  clickhouse-install-auth
                  View full skill →
                
                
                  Install @clickhouse/client and configure authentication to ClickHouse Cloud or self-hosted.
                  
                      ReadWriteBash(npm:*)Bash(pnpm:*)Bash(pip:*)
                    
                
                
                  ClickHouse Install & Auth
Overview
Set up the official ClickHouse client for Node.js or Python and configure
authentication to ClickHouse Cloud or a self-hosted instance. The workflow
below is the high-level path; each step links to a full walkthrough with
complete code in references/implementation.md.
Prerequisites

Node.js 18+ or Python 3.8+
A running ClickHouse instance (Cloud or self-hosted)
Connection credentials (host, port, user, password)

Instructions
Follow these five steps. Read the current project for an existing .env before
writing one; write credentials to .env (never commit it).

Install the official client. Node.js uses the HTTP-based

@clickhouse/client; Python uses clickhouse-connect.

   npm install @clickhouse/client   # Node.js
   pip install clickhouse-connect   # Python


Configure environment variables. Put host, user, and password in .env

and add it to .gitignore. Cloud hosts use port 8443 (HTTPS); self-hosted
uses 8123 (HTTP).

Create the client. Pass url, username, and password to

createClient() (Node.js) or get_client() (Python). Cloud requires TLS —
supply an https:// URL and the client handles it.

   import { createClient } from '@clickhouse/client';
   const client = createClient({
     url: process.env.CLICKHOUSE_HOST,
     username: process.env.CLICKHOUSE_USER,
     password: process.env.CLICKHOUSE_PASSWORD,
   });


Verify the connection with client.ping() plus a SELECT version()

probe.

Python alternative — same shape via clickhouseconnect.getclient(...)

with secure=True for Cloud.
Full code for every step (Cloud + self-hosted variants, the verify routine,
and the Python client): references/implementation.md.
Every createClient() option and a Cloud-vs-self-hosted comparison:
references/connection-reference.md.
Output
After completing the workflow you have:

The official client installed (@clickhouse/client or clickhouse-connect).
A .env holding CLICKHOUSEHOST / CLICKHOUSEUSER / CLICKHOUSE_PASSWORD

(gitignored).

An initialized 
                
              

                
                  
                  clickhouse-local-dev-loop
                  View full skill →
                
                
                  Run ClickHouse locally with Docker, configure test fixtures, and iterate fast.
                  
                      ReadWriteEditBash(docker:*)Bash(docker-compose:*)
                    
                
                
                  ClickHouse Local Dev Loop
Overview
Run ClickHouse in Docker for local development with fast schema iteration, seed
data, and integration testing using vitest. This skill scaffolds a
Docker-Compose-based dev loop: an auto-migrating init script, a seed generator,
a reusable client singleton, and a truncate-between-tests integration harness —
so schema changes and query work stay a docker compose up away.
Prerequisites

Docker or Docker Compose installed and running (docker info succeeds).
Node.js 18+ with the @clickhouse/client package for the seed/test scripts.
A local project directory you can Write files into. No cloud account or

network access is required — everything runs on localhost.
Authentication
This is a local dev setup with no external ClickHouse Cloud service. The
container's credentials are declared inline in docker-compose.yml
(CLICKHOUSEUSER: default, CLICKHOUSEPASSWORD: dev_password) and consumed by
the client via env vars (CLICKHOUSEUSER, CLICKHOUSEPASSWORD,
CLICKHOUSEHOST, CLICKHOUSEDATABASE). Keep real per-developer overrides in a
git-ignored .env.local; commit only a .env.example. Never reuse
dev_password outside local development.
Instructions
Follow the seven-step build. The lean skeleton is below; the
full walkthrough carries the complete file
contents for every step.

Docker Compose setup — Write a docker-compose.yml exposing 8123

(HTTP) and 9000 (native TCP), mounting ./init-db for auto-migration and a
named volume for persistence:

   services:
     clickhouse:
       image: clickhouse/clickhouse-server:latest
       ports: ["8123:8123", "9000:9000"]
       volumes:
         - clickhouse-data:/var/lib/clickhouse
         - ./init-db:/docker-entrypoint-initdb.d
       environment:
         CLICKHOUSE_PASSWORD: dev_password
         CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT: 1
   volumes:
     clickhouse-data:

Then docker compose up -d and verify with
curl http://localhost:8123/ping → Ok.

Init script — Write init-db/001-schema.sql with your

CREATE DATABASE / CREATE TABLE ... ENGINE = MergeTree() DDL. ClickHouse
auto-runs it on the container's first start.

Seed data — Write scripts/seed.ts that batch-inserts synthetic rows via

                
              

                
                  
                  clickhouse-migration-deep-dive
                  View full skill →
                
                
                  Execute ClickHouse schema migrations — ALTER TABLE operations, data migration between engines, versioned migration runners, and zero-downtime schema changes.
                  
                      ReadWriteEditBash(npm:*)Bash(node:*)Bash(kubectl:*)
                    
                
                
                  ClickHouse Migration Deep Dive
Overview
Plan and execute ClickHouse schema migrations: column changes, engine migrations,
ORDER BY modifications, and versioned migration runners. ClickHouse ALTER
operations behave unlike PostgreSQL/MySQL — most are asynchronous mutations
that rewrite data parts in the background, and some changes (ORDER BY, engine)
require full table recreation. This skill walks the safe path for each.
Prerequisites

ClickHouse admin access
Backup of production data (see clickhouse-prod-checklist)
Test environment for validation

Instructions
Follow these steps in order. SQL and runner code for each step live in the
linked reference files — keep them open while you work.
Step 1: Classify the operation
Decide whether your change is lightweight (instant, metadata only) or a
heavyweight mutation (rewrites parts in the background):

-- Lightweight (instant): ADD COLUMN, RENAME COLUMN, COMMENT COLUMN
ALTER TABLE events ADD COLUMN country LowCardinality(String) DEFAULT '';

-- Heavyweight (mutation): MODIFY COLUMN, DROP COLUMN, DELETE, UPDATE
ALTER TABLE events MODIFY COLUMN properties String CODEC(ZSTD(3));

-- Always monitor mutation progress
SELECT database, table, mutation_id, is_done, parts_to_do
FROM system.mutations WHERE NOT is_done ORDER BY create_time;

Step 2: Run column operations
Use Edit/Write to author the ALTER TABLE statements, then apply them.
Add/modify/drop columns, set materialized defaults, and attach codecs. Full DDL
semantics and every column-operation variant:
DDL & column operations.
Step 3: Recreate the table for ORDER BY / engine changes
ClickHouse has no MODIFY ORDER BY and no in-place engine change. Create a new
table, INSERT ... SELECT the data, then atomically RENAME TABLE to swap.
Full create → copy → swap → verify → drop recipe for both cases:
table recreation.
Step 4: Wire a versioned migration runner
For repeatable, tracked migrations, drive numbered .sql files through a runner
that records applied versions in a _migrations table and stops on first
failure. Use Write to scaffold runner.ts and the sql/NNN-*.sql files, then
run it with npm/node. Full runner, example migration files, and the
operation downtime matrix: migration runner.
Output
Applying this skill produces:

Executed ALTER statements or recreated-and-swapped tables,
                
              

                
                  
                  clickhouse-multi-env-setup
                  View full skill →
                
                
                  Configure ClickHouse across dev, staging, and production with environment-specific settings, secrets management, and infrastructure-as-code patterns.
                  
                      ReadWriteEditBash(aws:*)Bash(gcloud:*)Bash(vault:*)
                    
                
                
                  ClickHouse Multi-Environment Setup
Overview
Configure separate ClickHouse instances for development, staging, and production
with proper secrets management, environment detection, and infrastructure-as-code.
A single typed config module resolves the right instance from NODE_ENV, so
application code never branches on environment and destructive operations are
blocked outside dev/staging.
The full code for every step lives in
references/implementation.md; worked end-to-end
flows are in references/examples.md.
Prerequisites

ClickHouse Cloud account or self-hosted instances per environment
Secret management solution (Vault, AWS Secrets Manager, GCP Secret Manager)
CI/CD pipeline with environment variables

Instructions
Step 1: Choose an environment strategy
Provision one instance per tier, isolated by data sensitivity:

Environment
Instance
Purpose
Data


Development
Docker local
Fast iteration
Synthetic seed data


Staging
ClickHouse Cloud (Dev tier)
Pre-prod validation
Sampled prod copy


Production
ClickHouse Cloud (Prod tier)
Live traffic
Real data


Step 2: Build a typed config module
Create src/config/clickhouse.ts with one ClickHouseEnvConfig per environment,
keyed by NODE_ENV. Fail fast in non-dev environments — require a password and
enforce HTTPS at startup:

export function getConfig(): ClickHouseEnvConfig {
  const env = process.env.NODE_ENV ?? 'development';
  const config = configs[env];
  if (!config) throw new Error(`Unknown environment: ${env}`);
  if (env !== 'development') {
    if (!config.password) throw new Error(`CLICKHOUSE_PASSWORD not set for ${env}`);
    if (!config.url.startsWith('https://')) {
      throw new Error(`ClickHouse ${env} must use HTTPS`);
    }
  }
  return config;
}

Full per-environment config table (pool sizes, timeouts, compression):
references/implementation.md.
Step 3: Add a client factory
A lazily-initialized singleton in src/clickhouse/client.ts builds the pool once
per process from the resolved config. Full code:
references/implementation.md.
Step 4: Wire secrets per environment
Dev uses a git-ignored .env.local; every other tier pulls from a secret manager
(AWS Secrets Manager, GCP Secret Manager, or Vault) — never commit credentials.
                
              

                
                  
                  clickhouse-observability
                  View full skill →
                
                
                  Monitor ClickHouse with Prometheus metrics, Grafana dashboards, system table queries, and alerting for query performance, merge health, and resource usage.
                  
                      ReadWriteEdit
                    
                
                
                  ClickHouse Observability
Overview
Set up comprehensive monitoring for ClickHouse using built-in system tables,
Prometheus integration, Grafana dashboards, and alerting rules. The workflow
layers four signal sources: system.* tables (always available, zero
dependencies), a Prometheus scrape endpoint, application-level client
instrumentation, and alert rules that fire on the failure modes that actually
page an on-call — high error rate, latency creep, merge backlog, and resource
exhaustion.
Deep configs live in references/ so this file stays a fast, followable map.
Prerequisites

ClickHouse instance with system.* table access
Prometheus (or compatible: Grafana Alloy, Victoria Metrics)
Grafana for dashboards
AlertManager or PagerDuty for alerts

Instructions
Step 1: Query system tables for a health snapshot
Start with zero dependencies — the system.* tables already hold everything.
Run this for an instant server-health read:

SELECT
    (SELECT count() FROM system.processes) AS running_queries,
    (SELECT value FROM system.metrics WHERE metric = 'MemoryTracking') AS memory_bytes,
    (SELECT count() FROM system.merges) AS active_merges;

Query throughput, insert rates, and per-table part counts (the merge-health
signal), plus a full table of which system.* table to poll at what frequency:
system table queries & reference.
Step 2: Wire up Prometheus scraping
ClickHouse Cloud exposes a managed Prometheus endpoint (Basic auth with a Cloud
API key); self-hosted uses the built-in :9363 /metrics endpoint enabled in
config.xml. Write the scrape config to your prometheus.yml. Full Cloud +
self-hosted scrape configs and the config.xml block:
Prometheus scrape config & Grafana dashboards.
Step 3: Instrument the application client
Server metrics show what ClickHouse does; client metrics attribute latency,
error codes, and insert volume to your own code. Wrap queries in a prom-client
histogram/counter and expose /metrics. Full instrumentation + structured
logging: application-level instrumentation.
Step 4: Build Grafana dashboard panels
Panels for QPS, P50/P95/P99 latency, error rate, and insert throughput are in
the Grafana dashboards reference. Or import
the official community dashboard: https://grafana.com/grafana/dashboards/23415.
Step 5: Load alert rules
Write Prometh
                
              

                
                  
                  clickhouse-performance-tuning
                  View full skill →
                
                
                  Optimize ClickHouse query performance with indexing, projections, settings tuning, and query analysis using system tables.
                  
                      ReadWriteEdit
                    
                
                
                  ClickHouse Performance Tuning
Overview
Diagnose and fix ClickHouse performance issues using query analysis, proper indexing,
projections, materialized views, and server settings tuning. Work top-down: measure
first with system.query_log, then apply the single highest-leverage fix (usually the
ORDER BY key), then re-measure to confirm.
Prerequisites

ClickHouse tables with data (see clickhouse-core-workflow-a)
Access to system.query_log and system.parts

Instructions
The tuning workflow is seven independent steps. Diagnose first, then reach for the fix
that matches the bottleneck. Each step's full SQL lives in
references/implementation.md — start there for the
complete, copy-paste commands.

Diagnose slow queries — rank the last 24h of system.query_log by

querydurationms, then inspect a suspect query with EXPLAIN PLAN /
EXPLAIN PIPELINE.

ORDER BY key optimization — the primary lever. Filtering on the ORDER BY prefix

skips whole granules; a mismatched key forces a full scan.

Data skipping indexes — bloom_filter for high-cardinality lookups, set for

low-cardinality columns, minmax for range filters on non-key columns.

Projections — automatic pre-aggregation ClickHouse picks transparently when a

query matches the projection's shape.

Server settings — maxthreads, external sort/group-by spill, asyncinsert,

and friends, set per-query or per-session.

Materialized views — pre-aggregate on INSERT into an AggregatingMergeTree so

dashboard reads hit milliseconds, not seconds.

Query patterns — PREWHERE, LIMIT BY, and avoiding FINAL.

The essential first move — find the slowest queries:

SELECT event_time, query_duration_ms, read_rows, read_bytes,
       substring(query, 1, 300) AS query_preview
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 24 HOUR
  AND query_duration_ms > 1000   -- > 1 second
ORDER BY query_duration_ms DESC
LIMIT 20;

Output
Applying this workflow produces:

A ranked list of the slowest queries with their readrows / readbytes cost.
One or more concrete schema/query changes: a corrected ORDER BY key, added data

skipping indexes, a projection, a materiali
                
              

                
                  
                  clickhouse-prod-checklist
                  View full skill →
                
                
                  Production readiness checklist for ClickHouse — server tuning, backup, monitoring, and deployment verification.
                  
                      ReadBash(clickhouse-client:*)Bash(curl:*)
                    
                
                
                  ClickHouse Production Checklist
Overview
Comprehensive go-live checklist for ClickHouse covering schema and engine design,
server tuning, backup configuration, monitoring, security, application
integration, and operational readiness. Walk the eight sections top to bottom;
each is a gate that must be green before production traffic is allowed. Deep
configuration — server XML, backup SQL, and verification queries — lives in
references/ so this file stays a fast operational scan.
Prerequisites
Before starting the checklist, confirm the following are in place:

A ClickHouse instance is provisioned (ClickHouse Cloud or self-hosted) and

reachable from the environment you will run traffic from.

Application integration code has been exercised end-to-end in a staging

environment against a ClickHouse of the same major version.

You have admin credentials able to read system.* tables and, for

self-hosted, edit config.xml / users.xml.
Instructions
Work each section in order. Every box must be checked (or explicitly waived with
a reason) before go-live.
1. Schema & Engine Design

[ ] Tables use MergeTree family engines (not Memory, Log, or TinyLog)
[ ] ORDER BY columns match primary filter/group patterns
[ ] PARTITION BY is coarse (monthly or weekly, never by ID)
[ ] TTL configured for data retention policy
[ ] LowCardinality(String) used for low-cardinality columns
[ ] CODEC(ZSTD) applied to large String/JSON columns
[ ] ReplacingMergeTree used with FINAL or dedup logic if upserts needed

2. Server Configuration (Self-Hosted)
Set memory to ~80% of RAM, cap per-query memory and execution time, and size the
merge pools to the core count. ClickHouse Cloud manages these for you.

<max_server_memory_usage_to_ram_ratio>0.8</max_server_memory_usage_to_ram_ratio>
<max_concurrent_queries>150</max_concurrent_queries>
<max_execution_time>300</max_execution_time>  <!-- 5 min: cap runaway scans -->

Full annotated config.xml / users.xml block and tuning rationale:
server configuration reference.
3. Backup Configuration

[ ] Backup schedule configured (daily minimum)
[ ] Backup restore tested and documented
[ ] Point-in-time recovery possible (incremental backups)
[ ] Backup stored in different region/account from primary

Native BACKUP ... TO S3 syntax, incremental base+delta backups, a
                
              

                
                  
                  clickhouse-rate-limits
                  View full skill →
                
                
                  Configure ClickHouse query concurrency, memory quotas, and connection limits.
                  
                      ReadWriteEdit
                    
                
                
                  ClickHouse Rate Limits & Concurrency
Overview
ClickHouse has no REST API rate limits like a SaaS product. Instead it enforces
server-side concurrency limits, memory quotas, and per-user settings that
control resource usage. This skill configures those server-side limits and pairs
them with client-side controls so an application stays within them under load.
Prerequisites

ClickHouse admin access (or Cloud console) to create quotas and settings profiles.
The @clickhouse/client Node package for the client-side patterns.
A rough target for peak concurrent queries and per-query memory.

Instructions
Work top-down: cap resources at the server, then make the client respect the cap.
Step 1: Know the server-side limits
The defaults you tune most often:

Setting
Default
Controls


maxconcurrentqueries
100
Queries running simultaneously


max_connections
4096
Max TCP/HTTP connections


maxmemoryusage
~10GB
Per-query memory


maxexecutiontime
0 (unlimited)
Per-query timeout (seconds)


ClickHouse Cloud's management API (not the query interface) is separately limited
to 10 requests per 10 seconds. Full table in
references/implementation.md.
Step 2: Cap resources per user (essential skeleton)
Bind a quota and a settings profile to each application user:

CREATE SETTINGS PROFILE IF NOT EXISTS app_profile
    SETTINGS
        max_memory_usage = 5000000000,       -- 5GB per query
        max_execution_time = 30,             -- 30s timeout
        max_concurrent_queries_for_user = 10 -- 10 parallel queries
    TO app_user;

The full quota (CREATE QUOTA … FOR INTERVAL 1 HOUR MAX …) plus verification
queries are in references/implementation.md.
Step 3: Make the client respect the cap
Four client-side patterns keep the app inside the server limits — connection
pooling, an app-level concurrency queue (p-queue), retry-with-backoff on
TOOMANYSIMULTANEOUSQUERIES, and insert buffering to avoid TOOMANY_PARTS.
Each is a drop-in TypeScript snippet in
references/implementation.md, with the
concurrency queue as the smallest starting point:

import PQueue from 'p-queue';
const queryQueue = new PQueue({ concurrency: 5, timeout: 30_000, throwOnTimeout: true });
const rate

                

              

                
                  
                  clickhouse-reference-architecture
                  View full skill →
                
                
                  Production reference architecture for ClickHouse-backed applications — project layout, data flow, multi-tenant patterns, and operational topology.
                  
                      ReadGrep
                    
                
                
                  ClickHouse Reference Architecture
Overview
Production-grade architecture for ClickHouse analytics platforms covering project
layout, data flow, multi-tenancy, and operational patterns. Work through the five
steps below to get the high-level shape, then drill into the linked reference files
for the full DDL, client code, and tenancy trade-offs.
Prerequisites

Understanding of ClickHouse fundamentals — table engines, ORDER BY sort keys,

and partitioning.

A TypeScript/Node.js project (the client examples use @clickhouse/client).
When reviewing an existing codebase, Grep for createClient( to locate the

current client module and Read the SQL files under clickhouse/schemas/.
Instructions
Step 1: Project Structure
Keep SQL DDL as the source of truth under clickhouse/schemas/, named query
functions under clickhouse/queries/, and ingestion/API/jobs in sibling modules.

my-analytics-platform/
├── src/
│   ├── clickhouse/
│   │   ├── client.ts           # Singleton client with health checks
│   │   ├── schemas/            # SQL DDL files (source of truth)
│   │   │   ├── 001-events.sql
│   │   │   ├── 002-users.sql
│   │   │   └── 003-materialized-views.sql
│   │   ├── queries/            # Named query functions
│   │   └── migrations/         # Schema migrations (runner.ts + *.sql)
│   ├── ingestion/              # webhook-receiver, kafka-consumer, buffer
│   ├── api/                    # routes.ts, middleware.ts (auth, rate limit)
│   └── jobs/                   # daily-rollup.ts, cleanup.ts (TTL enforcement)
├── tests/                      # unit/ + integration/
├── docker-compose.yml          # Local ClickHouse
├── init-db/                    # Docker init scripts
└── config/                     # development / staging / production .env

Step 2: Data Flow Architecture
Data moves in one direction: sources → a batching ingestion layer → ClickHouse
(raw MergeTree → materialized views → aggregate tables) → an API that reads only
the aggregate tables → dashboards.

Data Sources (Webhooks, API, Kafka, S3)
        │
Ingestion Layer (Buffer + batch, 10K+ rows/insert)
        │
ClickHouse Server
   Raw Event Tables (MergeTree, append-only)
        │  auto-aggregate on INSERT
   Materialized Views (hourly, daily, tenant-level)
        │
   Aggregate Tables (AggregatingMergeTree)
        │
API Layer (queries aggregate tables, never raw events)
        │
Dashboards / Client Apps

Step 3: Schema Design (3-Layer Pattern)
Three layers — raw append-only events, hourly aggregation, and a daily rollup for
dashboards — with materialized views auto-populating each aggregate on INSERT.
The essential raw-table skeleton:

                
              

                
                  
                  clickhouse-sdk-patterns
                  View full skill →
                
                
                  Production-ready patterns for @clickhouse/client — streaming inserts, typed queries, error handling, and connection management.
                  
                      Read
                    
                
                
                  ClickHouse SDK Patterns
Overview
Production patterns for @clickhouse/client — typed queries, streaming inserts,
error handling, and connection lifecycle management. Start from the typed query
helper below, then drill into references/implementation.md for the streaming,
batching, and lifecycle patterns.
Prerequisites

@clickhouse/client installed and authenticated (see clickhouse-install-auth)
Node.js 18+ with a CLICKHOUSEHOST / CLICKHOUSEUSER / CLICKHOUSE_PASSWORD env set
Familiarity with async/await and Node.js streams (backpressure, drain, Readable)

Instructions
Apply the pattern that fits your workload. Steps 2–7 live in
references/implementation.md with full,
copy-pasteable code; the core typed-query skeleton stays here.

Typed query helper — the foundation every other pattern builds on. Define

a generic query wrapper that returns parsed rows (skeleton below).

Streaming insert (backpressure-safe) — stream large inserts through a

Readable instead of buffering in memory; honor drain.

Batch insert with retry — chunk rows (default 10k) with exponential-backoff

retries, returning { inserted, errors }.

Streaming SELECT (low memory) — consume large result sets as an

AsyncGenerator so you never load the full set into RAM.

Error handling — distinguish server-side ClickHouseError (code + message)

from network/client errors and normalize into a structured result.

Connection lifecycle — flush pending inserts on SIGTERM via

client.close(); expose a ping()-based health check.

Per-query settings — override maxthreads, maxmemory_usage,

maxexecutiontime, and maxresultrows for heavy queries.
Skeleton: Typed Query Helper

import { createClient } from '@clickhouse/client';

const client = createClient({
  url: process.env.CLICKHOUSE_HOST!,
  username: process.env.CLICKHOUSE_USER ?? 'default',
  password: process.env.CLICKHOUSE_PASSWORD ?? '',
});

// Generic typed query — returns parsed JSON rows
async function query<T>(sql: string, params?: Record<string, unknown>): Promise<T[]> {
  const rs = await client.query({
    query: sql,
    query_params: params,
    format: 'JSONEachRow',
  });

                

              

                
                  
                  clickhouse-security-basics
                  View full skill →
                
                
                  Secure ClickHouse with user management, network restrictions, TLS, and audit logging.
                  
                      Read
                    
                
                
                  ClickHouse Security Basics
Overview
Secure a ClickHouse deployment with SQL-based user management, network restrictions,
TLS encryption, and query audit logging. This skill walks the seven core hardening
steps at a high level; the full copy-pasteable SQL, XML, and connection code lives in
references/implementation.md.
Prerequisites

ClickHouse admin access
CLICKHOUSEDEFAULTACCESS_MANAGEMENT=1 for SQL-based user management
For self-hosted: access to server config files (config.xml, users.xml)

Instructions
Work through the seven steps in order. Each summary below gives the essential
first move; drill into references/implementation.md
for the complete, copy-ready code for every step.
Step 1: Create restricted users (SQL-based RBAC)
Create least-privilege users and REVOKE destructive verbs from application users.

CREATE USER analyst
    IDENTIFIED WITH sha256_password BY 'strong-password-here'
    DEFAULT DATABASE analytics
    SETTINGS readonly = 1, max_execution_time = 60;
GRANT SELECT ON analytics.* TO analyst;

Step 2: Use roles for permission groups
Define datareader / datawriter / schema_admin roles once, then grant roles
to users instead of hand-managing per-user grants. Verify with SHOW GRANTS.
Step 3: Row-level security
Isolate multi-tenant data with CREATE ROW POLICY, mapping each user to a tenant
via a custom setting (getSetting('customtenantid')).
Step 4: Network security
Restrict connection sources — SQL HOST IP '10.0.0.0/8' (22.6+), users.xml
per-user network allowlists for self-hosted, or the ClickHouse Cloud IP Access List.
Step 5: TLS configuration
Enable the HTTPS port (8443) in config.xml with a server cert, private key, and
strict verification mode.
Step 6: Audit logging
Query system.query_log (on by default) to see who ran what, and filter
exception_code = 516 to hunt failed logins.
Step 7: Application connection security
Connect over https://…:8443 with a minimal-privilege user (never default) and a
password sourced from a secret manager — see the client snippet in
references/examples.md.
Run through the Security Checklist in
references/implementation.md before declaring a
deployment hardened.
Output
Applying this skill produces:

Restricted use

                

              

                
                  
                  clickhouse-upgrade-migration
                  View full skill →
                
                
                  Use when upgrading ClickHouse server versions or the @clickhouse/client SDK, handling breaking changes between versions, or migrating from older client libraries — covers version checks, changelog review, staged upgrade, post-upgrade validation, and rollback.
                  
                      ReadEditBash(npm:*)Bash(git:*)
                    
                
                
                  ClickHouse Upgrade & Migration
Overview
Safely upgrade ClickHouse server and the @clickhouse/client Node.js SDK, with
rollback procedures and breaking-change detection. The workflow is check versions
→ review changelogs → upgrade the client → upgrade the server → validate →
rollback if needed. Full command sequences live in
references/implementation.md; the runnable
migration, validation, and rollback code lives in
references/examples.md.
Prerequisites

Current ClickHouse version known (SELECT version())
Git for version control (client changes land on an upgrade/ branch)
Test suite for integration validation (npm test)
Staging environment for pre-production testing
CLICKHOUSE_HOST set (and credentials — see Authentication)

Authentication
The client and validation scripts read the server URL from the
CLICKHOUSE_HOST environment variable (e.g. http://localhost:8123 locally, or
your ClickHouse Cloud endpoint). Keep credentials in the environment, never
hardcoded: pass username / password to createClient from
process.env.CLICKHOUSEUSER / CLICKHOUSEPASSWORD, and for raw curl send
them via the X-ClickHouse-User / X-ClickHouse-Key headers. ClickHouse Cloud
endpoints require TLS (https://) and a password; self-hosted default installs
often run open on 8123 (the HTTP port) in dev only.
Instructions
Work the steps in order — the client upgrade and the server upgrade are separate,
independently reversible changes. Read
references/implementation.md for the full command
sequence of each step.
Step 1: Check Current Versions
Capture the server version, the installed client version, and the latest
published client before changing anything — this is your rollback target.

curl 'http://localhost:8123/?query=SELECT+version()'   # server
npm list @clickhouse/client                            # installed client
npm view @clickhouse/client version                    # latest available

Step 2: Review Changelog
Read the client and server changelogs and note breaking changes: createClient
option renames, default setting changes (compression, timeouts), query
result-format behavior, removed SQL functions, and renamed MergeTree settings.
Full checklist and links: implementation.md Step 2.
Step 3: Upgrade the Node.js Client
Isolate the client bump on a branch so it i
                
              

                
                  
                  clickhouse-webhooks-events
                  View full skill →
                
                
                  Ingest data into ClickHouse from webhooks, Kafka, and streaming sources with batching, dedup, and exactly-once patterns.
                  
                      ReadWriteEditBash(curl:*)
                    
                
                
                  ClickHouse Data Ingestion
Overview
Build data ingestion pipelines into ClickHouse from HTTP webhooks, Kafka, and
streaming sources with proper batching, deduplication, and error handling.
The core rule: ClickHouse hates one-row-at-a-time inserts — buffer events and
flush them in batches. This skill covers four ingestion paths (application-side
webhook receiver, server-side Kafka engine, managed ClickPipes, and HTTP bulk
loads) plus idempotent dedup and insert monitoring.
Prerequisites

A ClickHouse table with an appropriate engine already exists (a MergeTree

variant, e.g. analytics.events) — see clickhouse-core-workflow-a.

The @clickhouse/client package is installed and connected via

CLICKHOUSE_HOST.

For the Kafka paths, a reachable Kafka broker and topic.

Instructions
Step 1: Webhook Receiver with Batched Inserts
Buffer incoming events in memory, flush on a size threshold or a timer, and
re-queue the batch on failure so no event is lost. This is the application-side
core of the skill:

import express from 'express';
import { createClient } from '@clickhouse/client';

const client = createClient({ url: process.env.CLICKHOUSE_HOST! });
const app = express();
app.use(express.json());

// Buffer for batching — ClickHouse hates one-row-at-a-time inserts
const buffer: Record<string, unknown>[] = [];
const BATCH_SIZE = 5_000;
const FLUSH_INTERVAL_MS = 5_000;

async function flushBuffer() {
  if (buffer.length === 0) return;
  const batch = buffer.splice(0, buffer.length);

  try {
    await client.insert({
      table: 'analytics.events',
      values: batch,
      format: 'JSONEachRow',
    });
    console.log(`Flushed ${batch.length} events to ClickHouse`);
  } catch (err) {
    console.error('Insert failed, re-queuing:', (err as Error).message);
    buffer.unshift(...batch);  // Put back at front for retry
  }
}

// Flush periodically
setInterval(flushBuffer, FLUSH_INTERVAL_MS);

// Webhook endpoint
app.post('/ingest', async (req, res) => {
  const events = Array.isArray(req.body) ? req.body : [req.body];

  for (const event of events) {
    buffer.push({
      event_type: event.type ?? 'unknown',
      user_id: event.userId ?? 0,
      properties: JSON.stringify(event.properties ?? {}),
      created_at: new Date().toISOString().replace('T', ' ').slice(0, 19),
    });
  }

  if (buffer.length >= BATCH_SIZE) {
    await flushBuffer();
  }

  res.status(202).json({ queued: events.length, buffer_size: buffer.length });
});

Step 2: Choose a Server-Side or Managed Path
For high-volume streams, prefer a path that needs no application consumer:

Kafka t

                

              

          

        


      
      

      
      

      
      

      
      
  Ready to use clickhouse-pack?
  
    
    
  




      
      
          Related Plugins
          
            
  supabase-pack
  Complete Supabase integration skill pack with 30 skills covering authentication, database, storage, realtime, edge functions, and production operations. Flagship+ tier vendor pack.
  /plugin install supabase-pack@claude-code-plugins-plus
  

  vercel-pack
  Complete Vercel integration skill pack with 30 skills covering deployments, edge functions, preview environments, performance optimization, and production operations. Flagship+ tier vendor pack.
  /plugin install vercel-pack@claude-code-plugins-plus
  

  clay-pack
  Complete Clay integration skill pack with 30 skills covering data enrichment, waterfall workflows, AI agents, and GTM automation. Flagship+ tier vendor pack.
  /plugin install clay-pack@claude-code-plugins-plus
  

  cursor-pack
  Complete Cursor integration skill pack with 30 skills covering AI code editing, composer workflows, codebase indexing, and productivity features. Flagship+ tier vendor pack.
  /plugin install cursor-pack@claude-code-plugins-plus
  

  exa-pack
  Complete Exa integration skill pack with 30 skills covering neural search, semantic retrieval, web search API, and AI-powered discovery. Flagship+ tier vendor pack.
  /plugin install exa-pack@claude-code-plugins-plus
  

  firecrawl-pack
  Complete Firecrawl integration skill pack with 30 skills covering web scraping, crawling, markdown conversion, and LLM-ready data extraction. Flagship+ tier vendor pack.
  /plugin install firecrawl-pack@claude-code-plugins-plus
  

          
        

      
      
          Tags
          
            clickhousesaassdkintegration

Engine	Best For	Dedup?	Example
`MergeTree`	General analytics, append-only logs	No	Clickstream, IoT
`ReplacingMergeTree`	Mutable rows (upserts)	Yes (on merge)	User profiles, state
`SummingMergeTree`	Pre-aggregated counters	Sums numerics	Page view counts
`AggregatingMergeTree`	Materialized view targets	Merges states	Dashboards
`CollapsingMergeTree`	Stateful row updates	Collapses +-1	Shopping carts

Component	Pricing Model	Key Driver
Compute	Per-hour per replica	vCPU + memory tier
Storage	Per GB-month	Compressed data on disk
Network	Per GB egress	Query result sizes
Backups	Per GB stored	Backup retention

Level	Definition	Response	Examples
P1	ClickHouse unreachable / all queries failing	< 15 min	Server down, OOM, disk full
P2	Degraded performance / partial failures	< 1 hour	Slow queries, merge backlog
P3	Minor impact / non-critical errors	< 4 hours	Single table issue, warnings
P4	No user impact	Next business day	Monitoring gaps, optimization

Environment	Instance	Purpose	Data
Development	Docker local	Fast iteration	Synthetic seed data
Staging	ClickHouse Cloud (Dev tier)	Pre-prod validation	Sampled prod copy
Production	ClickHouse Cloud (Prod tier)	Live traffic	Real data

Setting	Default	Controls
`maxconcurrentqueries`	100	Queries running simultaneously
`max_connections`	4096	Max TCP/HTTP connections
`maxmemoryusage`	~10GB	Per-query memory
`maxexecutiontime`	0 (unlimited)	Per-query timeout (seconds)