--- title: "ClickHouse/agent-skills" description: "The official Agent Skills for ClickHouse and ClickHouse Cloud" source: https://github.com/ClickHouse/agent-skills ref: main license: Apache-2.0 licenseName: "Apache License 2.0" canonical: https://skillsdocs.com/ClickHouse/agent-skills base: https://github.com/ClickHouse/agent-skills/blob/main/ chapters: 11 inlined: 11 withheld: 0 words: 7905 updated: 2026-08-06T14:07:59Z generator: "Skills Docs" --- > **ClickHouse/agent-skills** — every Agent Skill in this repository, inlined verbatim. > > Canonical HTML: https://skillsdocs.com/ClickHouse/agent-skills > Per-chapter Markdown: https://skillsdocs.com/ClickHouse/agent-skills/.md > Machine manifest: https://skillsdocs.com/ClickHouse/agent-skills/.well-known/agent-skills/index.json > JSON: https://skillsdocs.com/api/v1/books/ClickHouse/agent-skills > Install: `npx skills add ClickHouse/agent-skills` > Upstream: https://github.com/ClickHouse/agent-skills @ `main` > Licence: Apache-2.0 > > Content is mirrored from GitHub and © its authors, served unmodified. Takedown: https://github.com/DreambaseAI/skillsdocs/issues/new?labels=takedown&title=Takedown+request # ClickHouse/agent-skills The official Agent Skills for ClickHouse and ClickHouse Cloud - **Chapters:** 11 - **Inlined:** 11 (licence detected) - **Words:** 7,905 - **Reading time:** 36 min - **Stars:** 514 ## Table of contents 1. [chdb-datastore](https://skillsdocs.com/ClickHouse/agent-skills/chdb-datastore.md) — Use when the user has tabular data (pandas DataFrame, parquet, csv, Arrow, json) and wants to filter, group, aggregate, join, or speed up slow pandas. Provides… 2. [chdb-sql](https://skillsdocs.com/ClickHouse/agent-skills/chdb-sql.md) — Use when the user wants to run SQL — especially analytical SQL — on local files (parquet/csv/json), URLs, S3 paths, or remote databases (Postgres, MySQL, Mongo… 3. [clickhouse-architecture-advisor](https://skillsdocs.com/ClickHouse/agent-skills/clickhouse-architecture-advisor.md) — MUST USE when designing ClickHouse architectures, selecting between ingestion or modeling patterns, or translating best practices into workload-specific system… 4. [clickhouse-best-practices](https://skillsdocs.com/ClickHouse/agent-skills/clickhouse-best-practices.md) — MUST USE when reviewing ClickHouse schemas, queries, or configurations. Contains 31 rules that MUST be checked before providing recommendations. Always read re… 5. [clickhouse-js-node-coding](https://skillsdocs.com/ClickHouse/agent-skills/clickhouse-js-node-coding.md) — Write idiomatic application code with the ClickHouse Node.js client (`@clickhouse/client`). Use this skill whenever a user is *building* against the Node.js cl… 6. [clickhouse-js-node-rowbinary](https://skillsdocs.com/ClickHouse/agent-skills/clickhouse-js-node-rowbinary.md) — Generate TypeScript/JavaScript code that reads/decodes AND writes/encodes ClickHouse RowBinary streams for the ClickHouse HTTP server. Use this skill whenever… 7. [clickhouse-js-node-troubleshooting](https://skillsdocs.com/ClickHouse/agent-skills/clickhouse-js-node-troubleshooting.md) — Troubleshoot and resolve common issues with the ClickHouse Node.js client (@clickhouse/client). Use this skill whenever a user reports errors, unexpected behav… 8. [clickhouse-managed-postgres-rca](https://skillsdocs.com/ClickHouse/agent-skills/clickhouse-managed-postgres-rca.md) — MUST USE when investigating performance issues on a ClickHouse-managed Postgres instance. Provides an evidence-based RCA workflow that scrapes the Prometheus e… 9. [clickstack-otel-collector](https://skillsdocs.com/ClickHouse/agent-skills/clickstack-otel-collector.md) — Use when a user wants to wire an OpenTelemetry collector into a Managed ClickStack service on ClickHouse Cloud, either by deploying a new local collector (Dock… 10. [infra-clickhouse](https://skillsdocs.com/ClickHouse/agent-skills/infra-clickhouse.md) — Sets up and manages ClickHouse using the clickhousectl CLI — installs and runs a local ClickHouse server for development, and creates managed ClickHouse Cloud… 11. [infra-postgres](https://skillsdocs.com/ClickHouse/agent-skills/infra-postgres.md) — Sets up and manages Postgres using the clickhousectl CLI — runs a local Docker-backed Postgres for development, and creates and operates managed ClickHouse Clo… ## Front matter _The repository README, verbatim except that relative links are resolved against https://github.com/ClickHouse/agent-skills/blob/main/._ # ClickHouse Agent Skills The official Agent Skills for [ClickHouse](https://clickhouse.com/). These skills help LLMs and agents to adopt best practices when working with ClickHouse and [chdb](https://clickhouse.com/docs/chdb) (in-process ClickHouse for Python). You can use these skills with open-source ClickHouse and managed ClickHouse Cloud. [Try ClickHouse Cloud with $300 in free credits](https://clickhouse.com/cloud?utm_medium=github&utm_source=github&utm_ref=agent-skills). ## Installation ### npx ```bash npx skills add clickhouse/agent-skills ``` The CLI auto-detects installed agents and prompts you to select where to install. ### clickhousectl Use the ClickHouse CLI [`clickhousectl`](https://github.com/ClickHouse/clickhousectl) to install the agent skills: ```bash clickhousectl skills ``` ## What is this? Agent Skills are packaged instructions that extend AI coding agents (Claude Code, Cursor, Copilot, etc.) with domain-specific expertise. This repository provides skills for ClickHouse databases and chdb — covering schema design, query optimization, data ingestion patterns, and in-process analytics with Python. When an agent loads these skills, it gains knowledge of ClickHouse best practices and chdb APIs, and can apply them while helping you design tables, write queries, analyze data, or troubleshoot performance issues. Skills follow the open specification at [agentskills.io](https://agentskills.io). ## Available Skills ### ClickHouse Best Practices **28 rules** covering schema design, query optimization, and data ingestion—prioritized by impact. | Category | Rules | Impact | |----------|-------|--------| | Primary Key Selection | 4 | CRITICAL | | Data Type Selection | 5 | CRITICAL | | JOIN Optimization | 5 | CRITICAL | | Insert Batching | 1 | CRITICAL | | Mutation Avoidance | 2 | CRITICAL | | Partitioning Strategy | 4 | HIGH | | Skipping Indices | 1 | HIGH | | Materialized Views | 2 | HIGH | | Async Inserts | 2 | HIGH | | OPTIMIZE Avoidance | 1 | HIGH | | JSON Usage | 1 | MEDIUM | **Location:** [`skills/clickhouse-best-practices/`](https://github.com/ClickHouse/agent-skills/blob/main/skills/clickhouse-best-practices/) **For humans:** Read [SKILL.md](https://github.com/ClickHouse/agent-skills/blob/main/skills/clickhouse-best-practices/SKILL.md) for an overview, or [AGENTS.md](https://github.com/ClickHouse/agent-skills/blob/main/skills/clickhouse-best-practices/AGENTS.md) for the complete compiled guide. **For agents:** The skill activates automatically when you work with ClickHouse—creating tables, writing queries, or designing data pipelines. ### ClickHouse Architecture Advisor **5 decision frameworks** covering workload-aware architecture decisions for real-time ClickHouse deployments. | Decision Area | Impact | |---------------|--------| | Ingestion Strategy | CRITICAL | | Join & Enrichment Patterns | CRITICAL | | Late-Arriving Data & Upserts | CRITICAL | | Time-Series Partitioning | HIGH | | Real-Time Pre-Aggregation | HIGH | Complements `clickhouse-best-practices` by answering *when*, *why*, and *how* — not just *what*. All recommendations are explicitly classified as `official`, `derived`, or `field` guidance. **Location:** [`skills/clickhouse-architecture-advisor/`](https://github.com/ClickHouse/agent-skills/blob/main/skills/clickhouse-architecture-advisor/) **For humans:** Read [SKILL.md](https://github.com/ClickHouse/agent-skills/blob/main/skills/clickhouse-architecture-advisor/SKILL.md) for an overview, or [AGENTS.md](https://github.com/ClickHouse/agent-skills/blob/main/skills/clickhouse-architecture-advisor/AGENTS.md) for the compiled guide. **For agents:** The skill activates during architecture design sessions — when choosing ingestion patterns, designing time-series schemas, selecting enrichment strategies, or handling mutable state. ### ClickHouse JS Node Troubleshooting **Troubleshooting guide** for the ClickHouse Node.js client (`@clickhouse/client`). Covers common failure modes including socket hang-up / `ECONNRESET`, Keep-Alive misconfiguration, data type mismatches, read-only user restrictions, proxy / pathname URL confusion, TLS certificate errors, compression issues, logging setup, and query parameter interpolation. **Location:** [`skills/clickhouse-js-node-troubleshooting/`](https://github.com/ClickHouse/agent-skills/blob/main/skills/clickhouse-js-node-troubleshooting/) **For agents:** The skill activates when users report errors, unexpected behavior, or configuration questions involving the ClickHouse Node.js client — including vague symptoms like "my inserts keep failing" or "connection drops randomly" in a Node.js context. Not used for browser/Web client issues. ### chdb DataStore **Pandas-compatible API** for chdb — drop-in pandas replacement backed by ClickHouse. Write `import chdb.datastore as pd` and use the same pandas API, 10-100x faster. Supports 16+ data sources (MySQL, PostgreSQL, S3, MongoDB, Iceberg, Delta Lake, etc.) with cross-source joins. **Location:** [`skills/chdb-datastore/`](https://github.com/ClickHouse/agent-skills/blob/main/skills/chdb-datastore/) **For agents:** The skill activates when you analyze data with pandas-style syntax, speed up slow pandas code, query remote databases as DataFrames, or join data across different sources. ### chdb SQL **In-process ClickHouse SQL** for Python — run SQL queries on local files, remote databases, and cloud storage without a server. Covers `chdb.query()`, Session, DB-API 2.0, parametrized queries, UDFs, streaming, and all ClickHouse table functions. **Location:** [`skills/chdb-sql/`](https://github.com/ClickHouse/agent-skills/blob/main/skills/chdb-sql/) **For agents:** The skill activates when you write SQL queries against files, use ClickHouse table functions, build stateful analytical pipelines, or use advanced ClickHouse SQL features. ### Infra ClickHouse **Local and cloud workflows** for running ClickHouse with [`clickhousectl`](https://github.com/ClickHouse/clickhousectl). The top-level `SKILL.md` is a decision tree that routes to the right reference: [`ref/local.md`](https://github.com/ClickHouse/agent-skills/blob/main/skills/infra-clickhouse/ref/local.md) for local development (install ClickHouse, start a server, create schemas, seed data) and [`ref/cloud.md`](https://github.com/ClickHouse/agent-skills/blob/main/skills/infra-clickhouse/ref/cloud.md) for ClickHouse Cloud (authenticate, create a service, migrate schemas, connect an application). The local workflow hands off to cloud when going to production. Supersedes `clickhousectl-local-dev` and `clickhousectl-cloud-deploy`. **Location:** [`skills/infra-clickhouse/`](https://github.com/ClickHouse/agent-skills/blob/main/skills/infra-clickhouse/) **For agents:** The skill activates when a user wants to build an application with ClickHouse, set up a local development environment, deploy to production, or manage a ClickHouse Cloud service. ### Infra Postgres **Local and cloud workflows** for running Postgres with [`clickhousectl`](https://github.com/ClickHouse/clickhousectl). The top-level `SKILL.md` is a decision tree that routes to the right reference: [`ref/local.md`](https://github.com/ClickHouse/agent-skills/blob/main/skills/infra-postgres/ref/local.md) for local Docker-backed Postgres development (start, psql client, `.env` wiring, lifecycle) and [`ref/cloud.md`](https://github.com/ClickHouse/agent-skills/blob/main/skills/infra-postgres/ref/cloud.md) for managed ClickHouse Cloud Postgres services (beta) — authentication, service creation, connections and TLS, runtime configuration, read replicas, failover, and point-in-time restore. The local workflow hands off to cloud when going to production. **Location:** [`skills/infra-postgres/`](https://github.com/ClickHouse/agent-skills/blob/main/skills/infra-postgres/) **For agents:** The skill activates when a user wants to set up a local Postgres for development, connect an application to Postgres, or create and manage a managed Postgres service in ClickHouse Cloud. ### ClickStack OTel Collector **Step-by-step workflow** for wiring an OpenTelemetry collector into a Managed ClickStack service on ClickHouse Cloud. Covers deploying a new local collector (Docker run or Docker Compose) or configuring an existing collector, creating a dedicated ingest SQL user, sending rich synthetic telemetry, and verifying the data is visible in ClickStack. **Location:** [`skills/clickstack-otel-collector/`](https://github.com/ClickHouse/agent-skills/blob/main/skills/clickstack-otel-collector/) **For agents:** The skill activates when a user wants to connect an OpenTelemetry collector to a Managed ClickStack service, send telemetry (logs, traces, metrics) into ClickStack, or verify their observability data pipeline end-to-end. ## Quick Start After installation, your AI agent will reference these skills when: - Creating new tables with `CREATE TABLE` - Choosing `ORDER BY` / `PRIMARY KEY` columns - Selecting data types for columns - Optimizing slow queries - Writing or tuning JOINs - Designing data ingestion pipelines - Handling updates or deletes - Analyzing data with pandas-style DataStore API - Querying files or databases with chdb SQL - Joining data across different sources (MySQL + S3 + local files) - Setting up a local ClickHouse development environment or deploying to ClickHouse Cloud with `clickhousectl` - Setting up a local Postgres or a managed ClickHouse Cloud Postgres service with `clickhousectl` - Wiring an OpenTelemetry collector into Managed ClickStack Example prompts: > "Create a table for storing user events with fields for user_id, event_type, properties (JSON), and timestamp" The agent will apply relevant ClickHouse best practices rules. > "Load this Parquet file and group by country, show top 10 by revenue" The agent will use chdb DataStore or SQL to query the file directly. > "Join my MySQL customers table with this local orders.parquet file" The agent will use chdb's cross-source join capabilities. ## Supported Agents Skills are **agent-agnostic**—the same skill works across all supported AI coding assistants: | Agent | Config Directory | |-------|------------------| | [Claude Code](https://claude.ai/code) | `.claude/skills/` | | [Cursor](https://cursor.sh) | `.cursor/skills/` | | [Windsurf](https://codeium.com/windsurf) | `.windsurf/skills/` | | [GitHub Copilot](https://github.com/features/copilot) | `.github/skills/` | | [Gemini CLI](https://github.com/google-gemini/gemini-cli) | `.gemini/skills/` | | [Cline](https://github.com/cline/cline) | `.cline/skills/` | | [Codex](https://openai.com/codex) | `.codex/skills/` | | [Goose](https://github.com/block/goose) | `.goose/skills/` | | [Roo Code](https://roo.ai) | `.roo/skills/` | | [OpenHands](https://github.com/All-Hands-AI/OpenHands) | `.openhands/skills/` | And 13 more including Amp, Kiro CLI, Trae, Zencoder, and others. The installer detects which agents you have by checking for their configuration directories. If an agent isn't listed, either install it first or create its config directory manually (e.g., `mkdir -p ~/.cursor`). ## License Apache 2.0 — see [LICENSE](https://github.com/ClickHouse/agent-skills/blob/main/LICENSE) for details. --- ## 1. chdb-datastore - **Source:** https://github.com/ClickHouse/agent-skills/blob/main/skills/chdb-datastore/SKILL.md - **Raw:** https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/chdb-datastore/SKILL.md - **Markdown:** https://skillsdocs.com/ClickHouse/agent-skills/chdb-datastore.md - **Licence:** Apache-2.0 — https://spdx.org/licenses/Apache-2.0.html Bundled files (6), referenced from this skill's directory: - `examples/examples.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/chdb-datastore/examples/examples.md - `metadata.json` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/chdb-datastore/metadata.json - `README.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/chdb-datastore/README.md - `references/api-reference.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/chdb-datastore/references/api-reference.md - `references/connectors.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/chdb-datastore/references/connectors.md - `scripts/verify_install.py` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/chdb-datastore/scripts/verify_install.py --- name: chdb-datastore description: >- Use when the user has tabular data (pandas DataFrame, parquet, csv, Arrow, json) and wants to filter, group, aggregate, join, or speed up slow pandas. Provides chDB DataStore — same pandas API, ClickHouse engine underneath. Also handles reading from S3, MySQL, PostgreSQL, MongoDB, ClickHouse Cloud, Iceberg, Delta Lake as DataFrames and joining across sources. TRIGGER when: user mentions DataFrame, parquet, csv, "fast pandas", "speed up pandas", or cross-source DataFrame joins; user imports `chdb.datastore` or `from datastore import DataStore`. SKIP this skill for raw SQL syntax (use chdb-sql instead), ClickHouse server administration, or non-Python DataStore API work. license: Apache-2.0 compatibility: Requires Python 3.9+, macOS or Linux. pip install chdb. metadata: author: chdb-io version: "4.1" homepage: https://clickhouse.com/docs/chdb --- # chdb DataStore — It's Just Faster Pandas ## The Key Insight ```python # Change this: import pandas as pd # To this: import chdb.datastore as pd # Everything else stays the same. ``` DataStore is a **lazy, ClickHouse-backed pandas replacement**. Your existing pandas code works unchanged — but operations compile to optimized SQL and execute only when results are needed (e.g., `print()`, `len()`, iteration). ```bash pip install chdb ``` ## Decision Tree: Pick the Right Approach ``` 1. "I have a file/database and want to analyze it with pandas" → DataStore.from_file() / from_mysql() / from_s3() etc. → See references/connectors.md 2. "I need to join data from different sources" → Create DataStores from each source, use .join() → See examples/examples.md #3-5 3. "My pandas code is too slow" → import chdb.datastore as pd — change one line, keep the rest 4. "I need raw SQL queries" → Use the chdb-sql skill instead ``` ## Connect to Any Data Source — One Pattern ```python from datastore import DataStore # Local file (auto-detects .parquet, .csv, .json, .arrow, .orc, .avro, .tsv, .xml) ds = DataStore.from_file("sales.parquet") # Database ds = DataStore.from_mysql(host="db:3306", database="shop", table="orders", user="root", password="pass") # Cloud storage ds = DataStore.from_s3("s3://bucket/data.parquet", nosign=True) # URI shorthand — auto-detects source type ds = DataStore.uri("mysql://root:pass@db:3306/shop/orders") ``` All 16+ sources and URI schemes → [connectors.md](references/connectors.md) ## After Connecting — Full Pandas API ```python result = ds[ds["age"] > 25] # filter result = ds[["name", "city"]] # select columns result = ds.sort_values("revenue", ascending=False) # sort result = ds.groupby("dept")["salary"].mean() # groupby result = ds.assign(margin=lambda x: x["profit"] / x["revenue"]) # computed column ds["name"].str.upper() # string accessor ds["date"].dt.year # datetime accessor result = ds1.join(ds2, on="id") # join result = ds.head(10) # preview print(ds.to_sql()) # see generated SQL ``` 209 DataFrame methods supported. Full API → [api-reference.md](references/api-reference.md) ## Cross-Source Join — The Killer Feature ```python from datastore import DataStore customers = DataStore.from_mysql(host="db:3306", database="crm", table="customers", user="root", password="pass") orders = DataStore.from_file("orders.parquet") result = (orders .join(customers, left_on="customer_id", right_on="id") .groupby("country") .agg({"amount": "sum", "rating": "mean"}) .sort_values("sum", ascending=False)) print(result) ``` More join examples → [examples.md](examples/examples.md) ## Writing Data ```python source = DataStore.from_mysql(host="db:3306", database="shop", table="orders", user="root", password="pass") target = DataStore("file", path="summary.parquet", format="Parquet") target.insert_into("category", "total", "count").select_from( source.groupby("category").select("category", "sum(amount) AS total", "count() AS count") ).execute() ``` ## Troubleshooting | Problem | Fix | |---------|-----| | `ImportError: No module named 'chdb'` | `pip install chdb` | | `ImportError: cannot import 'DataStore'` | Use `from datastore import DataStore` or `from chdb.datastore import DataStore` | | Database connection timeout | Include port in host: `host="db:3306"` not `host="db"` | | Join returns empty result | Check key types match (both int or both string); use `.to_sql()` to inspect | | Unexpected results | Call `ds.to_sql()` to see the generated SQL and debug | | Environment check | Run `python scripts/verify_install.py` (from skill directory) | ## References - [API Reference](references/api-reference.md) — Full DataStore method signatures - [Connectors](references/connectors.md) — All 16+ data source connection methods - [Examples](examples/examples.md) — 10+ runnable examples with expected output - [Verify Install](scripts/verify_install.py) — Environment verification script - [Official Docs](https://clickhouse.com/docs/chdb) > Note: This skill teaches how to *use* chdb DataStore. > For raw SQL queries, use the `chdb-sql` skill. > For contributing to chdb source code, see CLAUDE.md in the project root. --- ## 2. chdb-sql - **Source:** https://github.com/ClickHouse/agent-skills/blob/main/skills/chdb-sql/SKILL.md - **Raw:** https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/chdb-sql/SKILL.md - **Markdown:** https://skillsdocs.com/ClickHouse/agent-skills/chdb-sql.md - **Licence:** Apache-2.0 — https://spdx.org/licenses/Apache-2.0.html Bundled files (7), referenced from this skill's directory: - `examples/examples.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/chdb-sql/examples/examples.md - `metadata.json` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/chdb-sql/metadata.json - `README.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/chdb-sql/README.md - `references/api-reference.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/chdb-sql/references/api-reference.md - `references/sql-functions.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/chdb-sql/references/sql-functions.md - `references/table-functions.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/chdb-sql/references/table-functions.md - `scripts/verify_install.py` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/chdb-sql/scripts/verify_install.py --- name: chdb-sql description: >- Use when the user wants to run SQL — especially analytical SQL — on local files (parquet/csv/json), URLs, S3 paths, or remote databases (Postgres, MySQL, MongoDB, ClickHouse Cloud, Iceberg, Delta Lake) without setting up a server. Provides chDB — embedded ClickHouse SQL in Python with 1000+ functions, Session for stateful multi-step pipelines, parametrized queries, and cross-source joins via `s3()`, `mysql()`, `postgresql()`, `iceberg()`, `deltaLake()`, `remoteSecure()` table functions. TRIGGER when: user wants SQL on parquet/csv/files or across remote analytical sources; uses ClickHouse SQL features (window functions, windowFunnel, geoToH3, JSON path ops, Session, parametrized queries); imports `chdb` or calls `chdb.query()`. SKIP this skill for pandas-style DataFrame method-chaining (use chdb-datastore instead) or ClickHouse server administration. license: Apache-2.0 compatibility: Requires Python 3.9+, macOS or Linux. pip install chdb. metadata: author: chdb-io version: "4.1" homepage: https://clickhouse.com/docs/chdb --- # chdb SQL — ClickHouse in Your Python Process Run ClickHouse SQL directly in Python — no server needed. Query local files, remote databases, and cloud storage with full ClickHouse SQL power. ```bash pip install chdb ``` ## Decision Tree: Pick the Right API ``` 1. One-off query on files or databases → chdb.query() 2. Multi-step analysis with tables → Session 3. DB-API 2.0 connection → chdb.connect() 4. Pandas-style DataFrame operations → Use chdb-datastore skill instead ``` ## chdb.query() — One Line, Any Data ```python import chdb chdb.query("SELECT * FROM file('data.parquet', Parquet) WHERE price > 100 LIMIT 10") # local files chdb.query("SELECT * FROM mysql('db:3306', 'shop', 'orders', 'root', 'pass')") # databases chdb.query("SELECT * FROM s3('s3://bucket/data.parquet', NOSIGN) LIMIT 10") # cloud storage chdb.query("SELECT * FROM deltaLake('s3://bucket/delta/table', NOSIGN) LIMIT 10") # data lakes # Cross-source join chdb.query(""" SELECT u.name, o.amount FROM mysql('db:3306', 'crm', 'users', 'root', 'pass') AS u JOIN file('orders.parquet', Parquet) AS o ON u.id = o.user_id ORDER BY o.amount DESC """) data = {"name": ["Alice", "Bob"], "score": [95, 87]} chdb.query("SELECT * FROM Python(data) ORDER BY score DESC") # Python data df = chdb.query("SELECT * FROM numbers(10)", "DataFrame") # output formats chdb.query("SELECT toDate({d:String}) + number FROM numbers({n:UInt64})", "DataFrame", params={"d": "2025-01-01", "n": 30}) # parametrized ``` Table functions → [table-functions.md](references/table-functions.md) | SQL functions → [sql-functions.md](references/sql-functions.md) | Full API → [api-reference.md](references/api-reference.md) ## Session — Stateful Analysis Pipelines ```python from chdb import session as chs sess = chs.Session("./analytics_db") # persistent; Session() for in-memory sess.query("CREATE TABLE users ENGINE=MergeTree() ORDER BY id AS SELECT * FROM mysql('db:3306','crm','users','root','pass')") sess.query("CREATE TABLE events ENGINE=MergeTree() ORDER BY (ts,user_id) AS SELECT * FROM s3('s3://logs/events/*.parquet',NOSIGN)") sess.query(""" SELECT u.country, count() AS cnt, uniqExact(e.user_id) AS users FROM events e JOIN users u ON e.user_id = u.id WHERE e.ts >= today() - 7 GROUP BY u.country ORDER BY cnt DESC """, "Pretty").show() sess.close() ``` ## Connection API (DB-API 2.0) ```python from chdb import dbapi conn = dbapi.connect() cur = conn.cursor() cur.execute("SELECT * FROM file('data.parquet', Parquet) WHERE value > 100") print(cur.fetchall()) cur.close() conn.close() ``` ## Troubleshooting | Problem | Fix | |---------|-----| | `ImportError: No module named 'chdb'` | `pip install chdb` | | `DB::Exception: FILE_NOT_FOUND` | Check file path; use absolute path or verify cwd | | `DB::Exception: Unknown table function` | Check function name spelling (e.g., `deltaLake` not `deltalake`) | | Connection refused to remote DB | Check host:port format; ensure remote DB allows connections | | Environment check | Run `python scripts/verify_install.py` (from skill directory) | ## References - [API Reference](references/api-reference.md) — query/Session/connect signatures - [Table Functions](references/table-functions.md) — All ClickHouse table functions - [SQL Functions](references/sql-functions.md) — Commonly used SQL functions - [Examples](examples/examples.md) — 9 runnable examples with expected output - [Official Docs](https://clickhouse.com/docs/chdb) > Note: This skill teaches how to *use* chdb SQL. > For pandas-style operations, use the `chdb-datastore` skill. > For contributing to chdb source code, see CLAUDE.md in the project root. --- ## 3. clickhouse-architecture-advisor - **Source:** https://github.com/ClickHouse/agent-skills/blob/main/skills/clickhouse-architecture-advisor/SKILL.md - **Raw:** https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-architecture-advisor/SKILL.md - **Markdown:** https://skillsdocs.com/ClickHouse/agent-skills/clickhouse-architecture-advisor.md - **Licence:** Apache-2.0 — https://spdx.org/licenses/Apache-2.0.html Bundled files (14), referenced from this skill's directory: - `AGENTS.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-architecture-advisor/AGENTS.md - `examples/finserv-market-surveillance.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-architecture-advisor/examples/finserv-market-surveillance.md - `examples/observability-high-throughput.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-architecture-advisor/examples/observability-high-throughput.md - `examples/README.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-architecture-advisor/examples/README.md - `examples/siem-security-analytics.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-architecture-advisor/examples/siem-security-analytics.md - `mappings/doc_links.yaml` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-architecture-advisor/mappings/doc_links.yaml - `metadata.json` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-architecture-advisor/metadata.json - `README.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-architecture-advisor/README.md - `rules/decision-ingestion-strategy.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-architecture-advisor/rules/decision-ingestion-strategy.md - `rules/decision-join-enrichment.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-architecture-advisor/rules/decision-join-enrichment.md - `rules/decision-late-arriving-upserts.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-architecture-advisor/rules/decision-late-arriving-upserts.md - `rules/decision-partitioning-timeseries.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-architecture-advisor/rules/decision-partitioning-timeseries.md - `rules/decision-real-time-preaggregation.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-architecture-advisor/rules/decision-real-time-preaggregation.md - `schemas/recommendation_schema.yaml` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-architecture-advisor/schemas/recommendation_schema.yaml --- name: clickhouse-architecture-advisor description: MUST USE when designing ClickHouse architectures, selecting between ingestion or modeling patterns, or translating best practices into workload-specific system designs. Complements clickhouse-best-practices with decision frameworks and explicit provenance labels. license: Apache-2.0 metadata: author: ClickHouse Inc version: "0.1.0" --- # ClickHouse Architecture Advisor This skill adds workload-aware architecture decisioning on top of `clickhouse-best-practices`. > **Official docs remain the source of truth.** > This skill must always prefer official ClickHouse documentation when available. ## Required behavior Before producing recommendations: 1. Identify the workload shape - observability - security / SIEM - product analytics - IoT / telemetry - market data / financial services - mixed OLAP with point-lookups 2. Read the relevant decision rule files in `rules/` 3. Use `mappings/doc_links.yaml` to attach official documentation 4. Classify every recommendation as: - `official` - `derived` - `field` 5. Never present field guidance as official guidance 6. If a recommendation is uncertain, say so explicitly ## Provenance rules ### `official` Use this when the recommendation is directly backed by official docs. ### `derived` Use this when the recommendation is not stated verbatim in docs but follows logically from documented ClickHouse behavior. ### `field` Use this only for experience-based guidance that may be situational. When using `field`, include: - a disclaimer that the advice is heuristic - a relevant official doc if one partially applies - the reason the advice depends on workload context ## Read these rule files by scenario ### Real-time ingestion design 1. `rules/decision-ingestion-strategy.md` 2. `rules/decision-real-time-preaggregation.md` 3. Relevant best-practices insert rules ### Time-series and retention design 1. `rules/decision-partitioning-timeseries.md` 2. Relevant best-practices schema partition rules ### Enrichment and dimension lookups 1. `rules/decision-join-enrichment.md` 2. Relevant best-practices query join rules ### Mutable state / late-arriving events 1. `rules/decision-late-arriving-upserts.md` 2. Relevant best-practices mutation avoidance rules ## Output format Structure responses like this: ```markdown ## Workload Summary - workload: - latency target: - data shape: - primary query patterns: - operational constraints: ## Key Decisions - ... - ... ## Recommendations ### **What** ... **Why** ... **How** ... **Category** official | derived | field **Confidence** high | medium | heuristic **Source** - doc link(s) **Validation** - concrete SQL, metric, or smoke test ``` ## Architecture-specific guidance Prefer decision frameworks over generic advice. Good responses should: - explain tradeoffs - identify the likely operating bottleneck - separate immediate actions from structural redesign - provide target architecture patterns, not just isolated settings ## Full reference See `AGENTS.md` for the compiled version and `examples/` for sample outputs. --- ## 4. clickhouse-best-practices - **Source:** https://github.com/ClickHouse/agent-skills/blob/main/skills/clickhouse-best-practices/SKILL.md - **Raw:** https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/SKILL.md - **Markdown:** https://skillsdocs.com/ClickHouse/agent-skills/clickhouse-best-practices.md - **Licence:** Apache-2.0 — https://spdx.org/licenses/Apache-2.0.html Bundled files (36), referenced from this skill's directory: - `AGENTS.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/AGENTS.md - `metadata.json` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/metadata.json - `README.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/README.md - `rules/_sections.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/rules/_sections.md - `rules/_template.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/rules/_template.md - `rules/agent-connect-mcp.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/rules/agent-connect-mcp.md - `rules/agent-discovery-schema.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/rules/agent-discovery-schema.md - `rules/agent-query-safety.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/rules/agent-query-safety.md - `rules/insert-async-small-batches.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/rules/insert-async-small-batches.md - `rules/insert-batch-size.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/rules/insert-batch-size.md - `rules/insert-format-native.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/rules/insert-format-native.md - `rules/insert-mutation-avoid-delete.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/rules/insert-mutation-avoid-delete.md - `rules/insert-mutation-avoid-update.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/rules/insert-mutation-avoid-update.md - `rules/insert-optimize-avoid-final.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/rules/insert-optimize-avoid-final.md - `rules/query-index-skipping-indices.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/rules/query-index-skipping-indices.md - `rules/query-join-choose-algorithm.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/rules/query-join-choose-algorithm.md - `rules/query-join-consider-alternatives.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/rules/query-join-consider-alternatives.md - `rules/query-join-filter-before.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/rules/query-join-filter-before.md - `rules/query-join-null-handling.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/rules/query-join-null-handling.md - `rules/query-join-use-any.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/rules/query-join-use-any.md - `rules/query-mv-incremental.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/rules/query-mv-incremental.md - `rules/query-mv-refreshable.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/rules/query-mv-refreshable.md - `rules/schema-json-when-to-use.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/rules/schema-json-when-to-use.md - `rules/schema-partition-lifecycle.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-best-practices/rules/schema-partition-lifecycle.md - …and 12 more, listed in https://skillsdocs.com/api/v1/books/ClickHouse/agent-skills/skills/clickhouse-best-practices --- name: clickhouse-best-practices description: MUST USE when reviewing ClickHouse schemas, queries, or configurations. Contains 31 rules that MUST be checked before providing recommendations. Always read relevant rule files and cite specific rules in responses. license: Apache-2.0 metadata: author: ClickHouse Inc version: "0.4.0" --- # ClickHouse Best Practices Comprehensive guidance for ClickHouse covering schema design, query optimization, data ingestion, and AI agent connectivity. Contains 31 rules across 4 main categories (schema, query, insert, agent), prioritized by impact. > **Official docs:** [ClickHouse Best Practices](https://clickhouse.com/docs/best-practices) ## IMPORTANT: How to Apply This Skill **Before answering ClickHouse questions, follow this priority order:** 1. **Check for applicable rules** in the `rules/` directory 2. **If rules exist:** Apply them and cite them in your response using "Per `rule-name`..." 3. **If no rule exists:** Use the LLM's ClickHouse knowledge or search documentation 4. **If uncertain:** Use web search for current best practices 5. **Always cite your source:** rule name, "general ClickHouse guidance", or URL **Why rules take priority:** ClickHouse has specific behaviors (columnar storage, sparse indexes, merge tree mechanics) where general database intuition can be misleading. The rules encode validated, ClickHouse-specific guidance. --- ## Agent Connectivity & Query Workflow Before querying ClickHouse, agents must establish a connection and follow the discovery workflow: 1. `rules/agent-connect-mcp.md` - Connection setup (MCP + CLI), credential discovery, output format selection 2. `rules/agent-discovery-schema.md` - **CRITICAL**: 7-step schema discovery workflow 3. `rules/agent-query-safety.md` - **CRITICAL**: LIMIT, timeouts, progressive exploration **Every agent session should follow this sequence:** 1. **Connect** — establish connection via MCP or CLI (see `agent-connect-mcp`) 2. **Discover** — databases → tables → columns + comments → sort keys → skip indexes → sample → EXPLAIN 3. **Plan** — use sort key and skip index knowledge to write efficient WHERE clauses 4. **Execute** — run queries with LIMIT and timeouts 5. **Recover** — on timeout/memory errors, narrow filters and retry (see `agent-query-safety`) ### Subagent architecture notes If your system dispatches ClickHouse tasks to specialized subagents: - **Schema discovery + query execution**: any model — the steps are procedural - **EXPLAIN analysis + query optimization**: benefits from mid-tier reasoning - **Schema design review against all 28 rules**: benefits from mid-tier reasoning --- ## Review Procedures ### For Schema Reviews (CREATE TABLE, ALTER TABLE) **Read these rule files in order:** 1. `rules/schema-pk-plan-before-creation.md` - ORDER BY is immutable 2. `rules/schema-pk-cardinality-order.md` - Column ordering in keys 3. `rules/schema-pk-prioritize-filters.md` - Filter column inclusion 4. `rules/schema-types-native-types.md` - Proper type selection 5. `rules/schema-types-minimize-bitwidth.md` - Numeric type sizing 6. `rules/schema-types-lowcardinality.md` - LowCardinality usage 7. `rules/schema-types-avoid-nullable.md` - Nullable vs DEFAULT 8. `rules/schema-partition-low-cardinality.md` - Partition count limits 9. `rules/schema-partition-lifecycle.md` - Partitioning purpose **Check for:** - [ ] PRIMARY KEY / ORDER BY column order (low-to-high cardinality) - [ ] Data types match actual data ranges - [ ] LowCardinality applied to appropriate string columns - [ ] Partition key cardinality bounded (100-1,000 values) - [ ] ReplacingMergeTree has version column if used ### For Query Reviews (SELECT, JOIN, aggregations) **Read these rule files:** 1. `rules/query-join-choose-algorithm.md` - Algorithm selection 2. `rules/query-join-filter-before.md` - Pre-join filtering 3. `rules/query-join-use-any.md` - ANY vs regular JOIN 4. `rules/query-index-skipping-indices.md` - Secondary index usage 5. `rules/schema-pk-filter-on-orderby.md` - Filter alignment with ORDER BY **Check for:** - [ ] Filters use ORDER BY prefix columns - [ ] JOINs filter tables before joining (not after) - [ ] Correct JOIN algorithm for table sizes - [ ] Skipping indices for non-ORDER BY filter columns ### For Insert Strategy Reviews (data ingestion, updates, deletes) **Read these rule files:** 1. `rules/insert-batch-size.md` - Batch sizing requirements 2. `rules/insert-mutation-avoid-update.md` - UPDATE alternatives 3. `rules/insert-mutation-avoid-delete.md` - DELETE alternatives 4. `rules/insert-async-small-batches.md` - Async insert usage 5. `rules/insert-optimize-avoid-final.md` - OPTIMIZE TABLE risks **Check for:** - [ ] Batch size 10K-100K rows per INSERT - [ ] No ALTER TABLE UPDATE for frequent changes - [ ] ReplacingMergeTree or CollapsingMergeTree for update patterns - [ ] Async inserts enabled for high-frequency small batches --- ## Output Format Structure your response as follows: ``` ## Rules Checked - `rule-name-1` - Compliant / Violation found - `rule-name-2` - Compliant / Violation found ... ## Findings ### Violations - **`rule-name`**: Description of the issue - Current: [what the code does] - Required: [what it should do] - Fix: [specific correction] ### Compliant - `rule-name`: Brief note on why it's correct ## Recommendations [Prioritized list of changes, citing rules] ``` --- ## Rule Categories by Priority | Priority | Category | Impact | Prefix | Rule Count | |----------|----------|--------|--------|------------| | 1 | Primary Key Selection | CRITICAL | `schema-pk-` | 4 | | 2 | Data Type Selection | CRITICAL | `schema-types-` | 5 | | 3 | JOIN Optimization | CRITICAL | `query-join-` | 5 | | 4 | Insert Batching | CRITICAL | `insert-batch-` | 1 | | 5 | Mutation Avoidance | CRITICAL | `insert-mutation-` | 2 | | 6 | Partitioning Strategy | HIGH | `schema-partition-` | 4 | | 7 | Skipping Indices | HIGH | `query-index-` | 1 | | 8 | Materialized Views | HIGH | `query-mv-` | 2 | | 9 | Async Inserts | HIGH | `insert-async-` | 2 | | 10 | OPTIMIZE Avoidance | HIGH | `insert-optimize-` | 1 | | 11 | JSON Usage | MEDIUM | `schema-json-` | 1 | | 12 | Agent Schema Discovery | CRITICAL | `agent-discovery-` | 1 | | 13 | Agent Query Safety | CRITICAL | `agent-query-` | 1 | | 14 | Agent Connectivity + Formats | HIGH | `agent-connect-` | 1 | --- ## Quick Reference ### Schema Design - Primary Key (CRITICAL) - `schema-pk-plan-before-creation` - Plan ORDER BY before table creation (immutable) - `schema-pk-cardinality-order` - Order columns low-to-high cardinality - `schema-pk-prioritize-filters` - Include frequently filtered columns - `schema-pk-filter-on-orderby` - Query filters must use ORDER BY prefix ### Schema Design - Data Types (CRITICAL) - `schema-types-native-types` - Use native types, not String for everything - `schema-types-minimize-bitwidth` - Use smallest numeric type that fits - `schema-types-lowcardinality` - LowCardinality for <10K unique strings - `schema-types-enum` - Enum for finite value sets with validation - `schema-types-avoid-nullable` - Avoid Nullable; use DEFAULT instead ### Schema Design - Partitioning (HIGH) - `schema-partition-low-cardinality` - Keep partition count 100-1,000 - `schema-partition-lifecycle` - Use partitioning for data lifecycle, not queries - `schema-partition-query-tradeoffs` - Understand partition pruning trade-offs - `schema-partition-start-without` - Consider starting without partitioning ### Schema Design - JSON (MEDIUM) - `schema-json-when-to-use` - JSON for dynamic schemas; typed columns for known ### Query Optimization - JOINs (CRITICAL) - `query-join-choose-algorithm` - Select algorithm based on table sizes - `query-join-use-any` - ANY JOIN when only one match needed - `query-join-filter-before` - Filter tables before joining - `query-join-consider-alternatives` - Dictionaries/denormalization vs JOIN - `query-join-null-handling` - join_use_nulls=0 for default values ### Query Optimization - Indices (HIGH) - `query-index-skipping-indices` - Skipping indices for non-ORDER BY filters ### Query Optimization - Materialized Views (HIGH) - `query-mv-incremental` - Incremental MVs for real-time aggregations - `query-mv-refreshable` - Refreshable MVs for complex joins ### Insert Strategy - Batching (CRITICAL) - `insert-batch-size` - Batch 10K-100K rows per INSERT ### Insert Strategy - Async (HIGH) - `insert-async-small-batches` - Async inserts for high-frequency small batches - `insert-format-native` - Native format for best performance ### Insert Strategy - Mutations (CRITICAL) - `insert-mutation-avoid-update` - ReplacingMergeTree instead of ALTER UPDATE - `insert-mutation-avoid-delete` - Lightweight DELETE or DROP PARTITION ### Insert Strategy - Optimization (HIGH) - `insert-optimize-avoid-final` - Let background merges work ### Agent Integration - Discovery (CRITICAL) - `agent-discovery-schema` - Always discover schema before querying ### Agent Integration - Safety (CRITICAL) - `agent-query-safety` - LIMIT, timeouts, progressive exploration ### Agent Integration - Connectivity + Formats (HIGH) - `agent-connect-mcp` - MCP + CLI setup, credential discovery, output format selection --- ## When to Apply This skill activates when you encounter: - AI agent connecting to ClickHouse (MCP, CLI, HTTP) - Agent workflow design for ClickHouse - Schema discovery or exploration requests - `CREATE TABLE` statements - `ALTER TABLE` modifications - `ORDER BY` or `PRIMARY KEY` discussions - Data type selection questions - Slow query troubleshooting - JOIN optimization requests - Data ingestion pipeline design - Update/delete strategy questions - ReplacingMergeTree or other specialized engine usage - Partitioning strategy decisions --- ## Rule File Structure Each rule file in `rules/` contains: - **YAML frontmatter**: title, impact level, tags - **Brief explanation**: Why this rule matters - **Incorrect example**: Anti-pattern with explanation - **Correct example**: Best practice with explanation - **Additional context**: Trade-offs, when to apply, references --- ## Full Compiled Document For the complete guide with all rules expanded inline: `AGENTS.md` Use `AGENTS.md` when you need to check multiple rules quickly without reading individual files. --- ## 5. clickhouse-js-node-coding - **Source:** https://github.com/ClickHouse/agent-skills/blob/main/skills/clickhouse-js-node-coding/SKILL.md - **Raw:** https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-coding/SKILL.md - **Markdown:** https://skillsdocs.com/ClickHouse/agent-skills/clickhouse-js-node-coding.md - **Licence:** Apache-2.0 — https://spdx.org/licenses/Apache-2.0.html Bundled files (12), referenced from this skill's directory: - `reference/async-insert.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-coding/reference/async-insert.md - `reference/client-configuration.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-coding/reference/client-configuration.md - `reference/compression.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-coding/reference/compression.md - `reference/custom-json.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-coding/reference/custom-json.md - `reference/data-types.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-coding/reference/data-types.md - `reference/insert-columns.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-coding/reference/insert-columns.md - `reference/insert-formats.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-coding/reference/insert-formats.md - `reference/insert-values.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-coding/reference/insert-values.md - `reference/ping.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-coding/reference/ping.md - `reference/query-parameters.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-coding/reference/query-parameters.md - `reference/select-formats.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-coding/reference/select-formats.md - `reference/sessions.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-coding/reference/sessions.md --- name: clickhouse-js-node-coding description: > Write idiomatic application code with the ClickHouse Node.js client (`@clickhouse/client`). Use this skill whenever a user is *building* against the Node.js client — configuring the client, pinging, inserting rows in JSON or raw formats, selecting and parsing results, binding query parameters, managing sessions and temporary tables, working with data types or customizing JSON parsing. Do NOT use for browser/Web client code. --- # ClickHouse Node.js Client — Coding Reference: https://clickhouse.com/docs/integrations/javascript > **⚠️ Node.js runtime only.** This skill covers the `@clickhouse/client` > package running in a **Node.js runtime** exclusively — including **Next.js > Node runtime** API routes, React Server Components, Server Actions, and > standard Node.js processes. Do **not** apply this skill to browser client > components, Web Workers, **Next.js Edge runtime**, Cloudflare Workers, or > any usage of `@clickhouse/client-web`. For browser/edge environments, the > correct package is `@clickhouse/client-web`. --- ## How to Use This Skill 1. **Match the user's intent** to a row in the Task Index below and read the corresponding reference file before writing code. After reading it, scan any **Answer checklist** in that reference and make sure the final answer covers each relevant item; those checklists capture details users usually need but are easy to omit in short answers. 2. **Always import from `@clickhouse/client`** (never `@clickhouse/client-web`) and create a client with `createClient({ url })` or rely on supported defaults when appropriate. Close it with `await client.close()` preferably when it's no longer needed or during graceful shutdown for global resources. 3. **Prefer `JSONEachRow` for typical row inserts/selects** unless the user has already chosen another format or is streaming raw bytes (CSV / TSV / Parquet — see `examples/node/performance/`). **Note on `clickhouse_settings`:** settings passed to `createClient` are defaults for every request; they can be overridden per-call by passing `clickhouse_settings` directly to `insert()`, `query()`, or `command()`. Always mention this when the user configures settings at the client level. 4. **Always use `query_params` for user-supplied values** — never template- literal-interpolate them into SQL. See `reference/query-parameters.md`. **When answering a parameter-binding question, your response must explicitly name template-literal interpolation as a "SQL injection risk"** — even when the user only asked about syntax and did not raise security. The literal phrase "SQL injection" needs to appear; this is the most common mistake from PostgreSQL/MySQL users and the security framing is part of the correct answer, not an optional aside. 5. **Pick the right method for the job:** - `client.insert()` — write rows. - `client.query()` + `resultSet.json()` / `.text()` / `.stream()` — read rows that return data. - `client.command()` — DDL and other statements that don't return rows (`CREATE`, `DROP`, `TRUNCATE`, `ALTER`, `SET` in a session, etc.). - `client.exec()` — when you need the raw response stream of an arbitrary statement (rare in coding scenarios). - `client.ping()` — health check; returns `{ success, error? }`, never throws on connection failure. 6. **Note version constraints** when relevant. Examples: - `pathname` config option: client `>= 1.0.0`. - `BigInt` values in `query_params`: client `>= 1.15.0`. - `TupleParam` and JS `Map` in `query_params`: client `>= 1.9.0`. - Configurable `json.parse` / `json.stringify`: client `>= 1.14.0`. - `Time` / `Time64` data types: ClickHouse server `>= 25.6`. - `QBit` data type: ClickHouse server `>= 25.10` (GA on `26.x`). - `Dynamic` / `Variant` / new `JSON` types: ClickHouse server `>= 24.1` / `24.5` / `24.8` (no longer experimental since `25.3`). --- ## Task Index Identify the user's task and read the matching reference file. | Task | Triggers / symptoms | Reference file | | -------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------- | | **Configure / connect the client** | Building a `createClient` call, URL parameters, `clickhouse_settings`, default format, custom HTTP headers | `reference/client-configuration.md` | | **Compress requests / responses** | `compression`, gzip vs `zstd`, `{ codec }` option shape, Node version requirements, web limitations | `reference/compression.md` | | **Ping the server** | Health checks, readiness probes, "is ClickHouse up?" | `reference/ping.md` | | **Choose an insert format** | "Which format should I use to insert?", JSON vs raw, `JSONEachRow` vs `JSON` vs `JSONObjectEachRow` | `reference/insert-formats.md` | | **Insert into a subset of columns / different database** | `insert({ columns })`, excluding columns, ephemeral columns, cross-DB inserts | `reference/insert-columns.md` | | **Insert values, expressions, dates, decimals** | `INSERT … VALUES` with SQL functions, `Date`/`DateTime` from JS, `Decimal` precision, `INSERT … SELECT`; inserting a UUID into a `UInt128` column is tricky — use when the user is writing code that stores a UUID as `UInt128` | `reference/insert-values.md` | | **Async inserts (server-side batching)** | `async_insert=1`, fire-and-forget vs wait-for-ack | `reference/async-insert.md` | | **Select and parse results** | `JSONEachRow` reads, `JSON` with metadata, picking a select format | `reference/select-formats.md` | | **Parameterize queries** | Binding values, special characters / escaping, "SQL injection?", `{name: Type}` syntax | `reference/query-parameters.md` | | **Sessions & temporary tables** | `session_id`, `CREATE TEMPORARY TABLE`, per-session `SET` commands | `reference/sessions.md` | | **Modern data types** | `Dynamic`, `Variant`, `JSON` (object), `Time`, `Time64`, `QBit` (vector search) | `reference/data-types.md` | | **Custom JSON parse/stringify** | Plug in `JSONBig` / `safe-stable-stringify` / a `BigInt`-aware serializer | `reference/custom-json.md` | --- ## Conventions used in answers - Always show `import { createClient } from '@clickhouse/client'` (Node, never Web). - Always `await client.close()` at the end of self-contained snippets; in long-running services, close on graceful shutdown. - For inserts, prefer `format: 'JSONEachRow'` and `values: [...]` unless the user's scenario requires otherwise. - For selects, prefer `await (await client.query({...})).json()` for small / medium result sets; for bigger results suggest streaming. - When showing parameter binding, use ClickHouse's native `{name: Type}` syntax — never `$1`, `?`, or `:name`. - For DDL inside a cluster or behind a load balancer, set `clickhouse_settings: { wait_end_of_query: 1 }` on the `command()` call so the server only acknowledges after the change is applied. See https://clickhouse.com/docs/en/interfaces/http/#response-buffering. --- ## Out of scope This skill covers day-to-day coding against `@clickhouse/client` (Node). The following topics are intentionally **not** covered here: - **Errors, hangs, type mismatches, proxy pathname surprises, log silence, socket hang-ups, `ECONNRESET`** → use the `clickhouse-js-node-troubleshooting` skill. - **Streaming, Parquet, file streams, server-side bulk moves, progress streaming, async-insert throughput tuning** — see [`examples/node/performance/`](https://github.com/ClickHouse/clickhouse-js/tree/main/examples/node/performance). - **TLS, RBAC / read-only users, deeper SQL-injection guidance** — see [`examples/node/security/`](https://github.com/ClickHouse/clickhouse-js/tree/main/examples/node/security). - **`CREATE TABLE` patterns, deployment-shaped connection strings, replication / sharding choices** — see [`examples/node/schema-and-deployments/`](https://github.com/ClickHouse/clickhouse-js/tree/main/examples/node/schema-and-deployments). - **Browser, Web Worker, Next.js Edge, Cloudflare Workers** — use `@clickhouse/client-web` and see [`examples/web/`](https://github.com/ClickHouse/clickhouse-js/tree/main/examples/web). --- ## Still Stuck? - [`examples/node/coding/`](https://github.com/ClickHouse/clickhouse-js/tree/main/examples/node/coding) — the runnable corpus this skill is built on. - [ClickHouse JS client docs](https://clickhouse.com/docs/integrations/javascript) - [ClickHouse supported formats](https://clickhouse.com/docs/interfaces/formats) - [ClickHouse data types](https://clickhouse.com/docs/sql-reference/data-types) --- ## 6. clickhouse-js-node-rowbinary - **Source:** https://github.com/ClickHouse/agent-skills/blob/main/skills/clickhouse-js-node-rowbinary/SKILL.md - **Raw:** https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/SKILL.md - **Markdown:** https://skillsdocs.com/ClickHouse/agent-skills/clickhouse-js-node-rowbinary.md - **Licence:** Apache-2.0 — https://spdx.org/licenses/Apache-2.0.html Bundled files (192), referenced from this skill's directory: - `.gitignore` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/.gitignore - `AGENTS.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/AGENTS.md - `case-studies/iot-rowbinary-vs-json.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/case-studies/iot-rowbinary-vs-json.md - `case-studies/ledger-rowbinary-vs-json.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/case-studies/ledger-rowbinary-vs-json.md - `case-studies/logs-json-wins.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/case-studies/logs-json-wins.md - `case-studies/wasm-vs-js.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/case-studies/wasm-vs-js.md - `CHANGELOG.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/CHANGELOG.md - `eval_result_composer.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/eval_result_composer.md - `eval_result_haiku.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/eval_result_haiku.md - `eval_result_sonnet.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/eval_result_sonnet.md - `eval_result.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/eval_result.md - `EXAMPLES.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/EXAMPLES.md - `package-lock.json` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/package-lock.json - `package.json` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/package.json - `reader.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/reader.md - `README.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/README.md - `src/examples/carts.ts` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/src/examples/carts.ts - `src/examples/events.ts` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/src/examples/events.ts - `src/examples/iot.ts` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/src/examples/iot.ts - `src/examples/ledger.ts` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/src/examples/ledger.ts - `src/examples/logs.ts` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/src/examples/logs.ts - `src/examples/observability.ts` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/src/examples/observability.ts - `src/examples/orders.ts` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/src/examples/orders.ts - `src/examples/profiles.ts` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-rowbinary/src/examples/profiles.ts - …and 168 more, listed in https://skillsdocs.com/api/v1/books/ClickHouse/agent-skills/skills/clickhouse-js-node-rowbinary --- name: clickhouse-js-node-rowbinary description: > Generate TypeScript/JavaScript code that reads/decodes AND writes/encodes ClickHouse RowBinary streams for the ClickHouse HTTP server. Use this skill whenever a user wants to parse or produce `RowBinary`, `RowBinaryWithNames`, or `RowBinaryWithNamesAndTypes`. Node.js only, doesn't cover browsers. --- # ClickHouse JS RowBinary Codec Generator for Node.js This skill generates both directions of the wire format: **readers** (decode bytes → values) and **writers** (encode values → bytes, the mirror). A given task normally needs only one side. This file is the shared entry point — the format gate plus the principles common to both directions; the per-direction decisions, guidance, and the per-type reference tables live in two sibling files. **Pick your side — read only the one you need:** - **Decoding a `RowBinary*` response** from ClickHouse into JS values → **[reader.md](reader.md)**. Streaming vs whole-buffer, row-objects vs columnar, fixed vs runtime schema, and the per-type reader reference. - **Encoding JS values into a `RowBinary` payload** to send to ClickHouse → **[writer.md](writer.md)**. The `Sink`/`writeX` building blocks, `writeRows` streaming, and the per-type writer reference. The per-type code is real, split by direction under `src/readers/` and `src/writers/`. ## First: is RowBinary even the right format? RowBinary exists for throughput, but it is **not automatically the fastest path** — match the format to the shape of the data before committing to a bespoke parser. **Prefer a `JSON*` format (e.g. `JSONEachRow`) when** the result is mostly strings / JSON-like values that you consume wholesale — randomly accessing essentially every field, running string/regexp methods on them, treating values as text. V8's native `JSON.parse` is heavily optimized C++ and builds JS strings and objects faster than a JS-level RowBinary decoder can; pair it with HTTP response compression (`gzip` / `zstd`, which crushes JSON's repetitive keys) and the wire cost shrinks too. **RowBinary clearly wins when** the result is dominated by: - **Wide numerics** — `Int128`/`Int256`/`UInt128`/`UInt256`, `Decimal128`/`Decimal256`. - **Binary / fixed-width blobs** — `IPv4`, `IPv6`, `UUID`, `FixedString`. - **High-volume fixed-width numeric columns** generally, where each value is a single `DataView` read. **Prefer the `Native` format when** columnar load and client-side analytics are the main goal (fold/scan/filter columns, feed typed arrays to a Worker or WASM). `Native` is column-major, so it loads straight into one typed array per column with no transpose. For help choosing and consuming a `JSON*` format (or CSV / TSV) instead, use the **`clickhouse-js-node-coding`** skill. ## Core guidance (both directions) These principles apply whether you are generating a reader or a writer; the side-specific operational guidance is in [reader.md](reader.md) / [writer.md](writer.md). - **Little-endian only.** RowBinary is little-endian; target x86/ARM. Read and write every multi-byte number with `DataView` accessors passing a **literal** `true` for the `littleEndian` flag. - **Correct first, then optimize.** First emit a correct codec built from the plain per-type API. Only after it's correct (and tested) specialize it. Don't bake performance assumptions in before correctness. - **Monomorphize generic/composite types.** Emit specialized, inlined code per type combination instead of passing functions as arguments where the type is known ahead of time. - **Inline the leaf ops.** The per-type `readX`/`writeX` functions are the correct, composable reference; the generated codec should INLINE their bodies, not call them, so the row loop is straight-line with no per-field indirection (and so the fixed-width coalescing can fold the offset arithmetic together). - **Annotate the type per column.** Inlining erases the type structure, so put a short comment above each column's encode/decode block naming the ClickHouse type it handles. - **Shared scratch is not reentrant.** Some hot methods reuse a module-level scratch buffer as a write-then-read pair — correct only because the access is fully synchronous. An `async`/`yield` boundary between populating and reading it corrupts the value. - **TypeScript by default.** Generate TypeScript code and helpers unless the user explicitly asks for plain JavaScript. ## Worked examples Six end-to-end examples with real speedup are catalogued in [EXAMPLES.md](EXAMPLES.md). ## Out of scope - **JSON / CSV / TSV / Parquet parsing** → use `clickhouse-js-node-coding`. - **Connection errors, hangs, type mismatches** → use `clickhouse-js-node-troubleshooting`. - **Browser / Web Worker / Edge** → `@clickhouse/client-web`. ## Still Stuck? - [ClickHouse RowBinary format](https://clickhouse.com/docs/interfaces/formats#rowbinary) - [ClickHouse data types](https://clickhouse.com/docs/sql-reference/data-types) - [ClickHouse JS client docs](https://clickhouse.com/docs/integrations/javascript) --- ## 7. clickhouse-js-node-troubleshooting - **Source:** https://github.com/ClickHouse/agent-skills/blob/main/skills/clickhouse-js-node-troubleshooting/SKILL.md - **Raw:** https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-troubleshooting/SKILL.md - **Markdown:** https://skillsdocs.com/ClickHouse/agent-skills/clickhouse-js-node-troubleshooting.md - **Licence:** Apache-2.0 — https://spdx.org/licenses/Apache-2.0.html Bundled files (9), referenced from this skill's directory: - `reference/compression.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-troubleshooting/reference/compression.md - `reference/data-types.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-troubleshooting/reference/data-types.md - `reference/logging.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-troubleshooting/reference/logging.md - `reference/proxy-pathname.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-troubleshooting/reference/proxy-pathname.md - `reference/query-format-clause.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-troubleshooting/reference/query-format-clause.md - `reference/query-params.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-troubleshooting/reference/query-params.md - `reference/readonly-users.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-troubleshooting/reference/readonly-users.md - `reference/socket-hangup.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-troubleshooting/reference/socket-hangup.md - `reference/tls.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-js-node-troubleshooting/reference/tls.md --- name: clickhouse-js-node-troubleshooting description: > Troubleshoot and resolve common issues with the ClickHouse Node.js client (@clickhouse/client). Use this skill whenever a user reports errors, unexpected behavior, or configuration questions involving the Node.js client specifically — including socket hang-up errors, Keep-Alive problems, stream handling issues, data type mismatches, read-only user restrictions, proxy/TLS setup problems, or long-running query timeouts. Trigger even when the user hasn't precisely named the issue; vague symptoms like "my inserts keep failing" or "connection drops randomly" in a Node.js context are strong signals to use this skill. Do NOT use for browser/Web client issues. --- # ClickHouse Node.js Client Troubleshooting Reference: https://clickhouse.com/docs/integrations/javascript > **⚠️ Node.js runtime only.** This skill covers the `@clickhouse/client` package running in a **Node.js runtime** exclusively — including **Next.js Node runtime** API routes, React Server Components, Server Actions, and standard Node.js processes. Do **not** apply this skill to browser client components, Web Workers, **Next.js Edge runtime**, Cloudflare Workers, or any usage of `@clickhouse/client-web`. For browser/edge environments, the correct package is `@clickhouse/client-web`. --- ## How to Use This Skill 1. **Identify the issue** — match symptoms to the Issue Index below and read the corresponding reference file. 2. **Lead with the diagnosis** — explain what's likely causing the issue before giving the fix. 3. **Note version constraints** — flag if a fix requires a minimum client version and check it against what the user provided. 4. **Ask only what's missing** — if the fix is version-dependent and you don't know their version, ask; otherwise help immediately. --- ## Issue Index Identify the user's issue from the list below and read the corresponding reference file for detailed troubleshooting steps. | Issue | Symptoms | Reference file | | ------------------------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------- | | **Socket Hang-Up / ECONNRESET** | `socket hang up`, `ECONNRESET`, intermittent connection drops, long-running queries timing out | `reference/socket-hangup.md` | | **Data Type Mismatches** | Large integers returned as strings, decimal precision loss, Date/DateTime insertion failures, `CANNOT_PARSE_INPUT_ASSERTION_FAILED` inserting a UUID into a `UInt128` column | `reference/data-types.md` | | **Read-Only User Errors** | Errors when using response compression with `readonly=1` users | `reference/readonly-users.md` | | **Proxy / Pathname URL Confusion** | Wrong database selected, requests failing behind a proxy with a path prefix | `reference/proxy-pathname.md` | | **TLS / Certificate Errors** | TLS handshake failures, certificate verification issues, mutual TLS setup | `reference/tls.md` | | **Compression Not Working** | GZIP compression not activating for requests or responses | `reference/compression.md` | | **Logging Not Showing Anything** | No log output, need custom logger integration | `reference/logging.md` | | **Query Parameters Not Interpolated** | Parameterized queries not working, SQL injection concerns | `reference/query-params.md` | | **FORMAT Clause / `SHOW POLICIES` Errors** | Syntax error from a duplicate `FORMAT`, or `SHOW [ROW] POLICIES` failing even with a format provided | `reference/query-format-clause.md` | --- ## Still Stuck? - [JS client source + full examples](https://github.com/ClickHouse/clickhouse-js/tree/main/examples) - [ClickHouse JS client docs](https://clickhouse.com/docs/integrations/javascript) - [ClickHouse supported formats](https://clickhouse.com/docs/interfaces/formats) --- ## 8. clickhouse-managed-postgres-rca - **Source:** https://github.com/ClickHouse/agent-skills/blob/main/skills/clickhouse-managed-postgres-rca/SKILL.md - **Raw:** https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-managed-postgres-rca/SKILL.md - **Markdown:** https://skillsdocs.com/ClickHouse/agent-skills/clickhouse-managed-postgres-rca.md - **Licence:** Apache-2.0 — https://spdx.org/licenses/Apache-2.0.html Bundled files (12), referenced from this skill's directory: - `AGENTS.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-managed-postgres-rca/AGENTS.md - `metadata.json` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-managed-postgres-rca/metadata.json - `README.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-managed-postgres-rca/README.md - `rules/heuristic-full-scan.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-managed-postgres-rca/rules/heuristic-full-scan.md - `rules/heuristic-hot-loop.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-managed-postgres-rca/rules/heuristic-hot-loop.md - `rules/heuristic-write-congestion.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-managed-postgres-rca/rules/heuristic-write-congestion.md - `rules/openapi-discovery.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-managed-postgres-rca/rules/openapi-discovery.md - `rules/output-template.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-managed-postgres-rca/rules/output-template.md - `rules/prometheus-scrape.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-managed-postgres-rca/rules/prometheus-scrape.md - `rules/recommend-only.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-managed-postgres-rca/rules/recommend-only.md - `rules/slow-query-patterns-fields.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-managed-postgres-rca/rules/slow-query-patterns-fields.md - `rules/triage.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickhouse-managed-postgres-rca/rules/triage.md --- name: clickhouse-managed-postgres-rca description: MUST USE when investigating performance issues on a ClickHouse-managed Postgres instance. Provides an evidence-based RCA workflow that scrapes the Prometheus endpoint for system signal, pulls per-digest evidence from the Slow Query Patterns API, and recommends (does not apply) a fix. license: Apache-2.0 metadata: author: ClickHouse Inc version: "0.1.0" --- # ClickHouse Managed Postgres RCA ## When to use Trigger whenever a user reports slowness, high CPU, low throughput, cache thrash, or any unexplained pain on a ClickHouse-managed Postgres instance. ## What you have access to Two APIs on `https://api.clickhouse.cloud` (HTTP Basic auth using a ClickHouse Cloud API key/secret pair): - **Prometheus metrics** — operation `postgresInstancePrometheusGet` under the Prometheus tag. Returns Prometheus exposition format. System and workload metrics for one Postgres service. - **Slow Query Patterns** — operation `slowQueryPatternsGetList` under the Postgres tag. Returns per-digest latency, IO, and call statistics for normalized query patterns. **Beta.** Both endpoints require an `organizationId` and a `serviceId` as path parameters. The user must supply both, plus the API key/secret pair. ## What you do NOT have - Query plans / EXPLAIN output. - Per-table scan-type counters (`seq_scan` / `idx_scan`). - Autovacuum or last-ANALYZE timestamps. Reason from IO and timing signals, not from a plan tree. ## Workflow Six steps, in order. Do not skip ahead. Steps 2 and 3 only share auth — no data dependency between them. Run them in parallel (background curls, `&` + `wait`) to cut wall time from sequential ~2s to ~1s. ### 1. Discover the live API shape These endpoints are Beta — paths, params, and JSON field names can shift. Follow `rules/openapi-discovery.md` to: 1. Fetch the OpenAPI spec from `https://api.clickhouse.cloud/v1`. 2. Locate the two operations by `operationId`: - `postgresInstancePrometheusGet` (Prometheus tag) - `slowQueryPatternsGetList` (Postgres tag) 3. Resolve their path templates, required query parameters, and (for the slow-query endpoint) the response schema. 4. Build a session-scoped role map from the schema property descriptions: `{ semantic role → actual field name }`. Use the resolved names in every subsequent request and citation. Never hardcode field names from memory. ### 2. Scrape Prom once for system gauges Follow `rules/prometheus-scrape.md`. **One scrape, no wait.** You're after gauges (current values) that don't need a delta: `CacheHitRatio`, `ActiveConnections`, `MemoryUsedPercent`, `FilesystemUsedPercent`. A `CacheHitRatio` well below ~95% on a workload that should fit in cache is a real signal on its own. Climbing `ActiveConnections` toward the pool ceiling is a real signal on its own. These don't need rate-of-change. A second scrape for counter deltas is **opt-in**, used only when Step 4 triage points at write-congestion (where deadlock and rollback *rates* matter and the Slow Query Patterns API can't substitute). For the read-path case (the most common RCA shape) the single scrape is enough. ### 3. Pull top slow query patterns Request the slow query patterns. Follow `rules/slow-query-patterns-fields.md` for the fields that matter and how to read them. This is the primary diagnostic — it returns per-pattern accumulated totals (call count, runtime, blocks, rows) over the window you request, which is the "rate-of-change" data you'd otherwise derive from two Prom scrapes — but per query and without waiting. If no patterns return a meaningful `totalDurationUs`, the report may be overstated or the issue isn't query-shaped. Stop and tell the user what you looked at. ### 4. Triage: pick the right heuristic Follow `rules/triage.md`. Match the combined Prom + slow-query signal to one of the heuristic shapes. Each shape points to a specific heuristic file: - `rules/heuristic-full-scan.md` — read-path full scan. - `rules/heuristic-hot-loop.md` — N+1 / hot loop from the app. - `rules/heuristic-write-congestion.md` — deadlocks, slow writes, high rollback rate. If the signal does not match any shape cleanly, do not invent a hypothesis. Surface the top patterns and ask the user which workload they recognize. New heuristics are welcome as PRs. ### 5. Reason, then recommend Use the format in `rules/output-template.md`. Always include: symptom, evidence, hypothesis (noting any alternative cause you cannot rule out from this surface alone), short-term fix, and long-term follow-ups. ### 6. Do not apply the fix Follow `rules/recommend-only.md`. Never run DDL. Never call `pg_cancel_backend` or `pg_terminate_backend`. Write the recommendation, explain why, and let the human apply it. ## Full Compiled Document For the complete guide with every rule expanded in a single context load: `AGENTS.md`. --- ## 9. clickstack-otel-collector - **Source:** https://github.com/ClickHouse/agent-skills/blob/main/skills/clickstack-otel-collector/SKILL.md - **Raw:** https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickstack-otel-collector/SKILL.md - **Markdown:** https://skillsdocs.com/ClickHouse/agent-skills/clickstack-otel-collector.md - **Licence:** Apache-2.0 — https://spdx.org/licenses/Apache-2.0.html Bundled files (1), referenced from this skill's directory: - `metadata.json` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/clickstack-otel-collector/metadata.json --- name: clickstack-otel-collector description: Use when a user wants to wire an OpenTelemetry collector into a Managed ClickStack service on ClickHouse Cloud, either by deploying a new local collector (Docker run or Docker Compose) or by configuring their own existing collector, then send rich synthetic telemetry and verify it is visible in ClickStack. license: Apache-2.0 metadata: author: ClickHouse Inc version: "0.6.0" --- # Set up an OpenTelemetry collector for Managed ClickStack This skill wires an OpenTelemetry collector into a Managed ClickStack service running on ClickHouse Cloud, sends rich synthetic telemetry through it, and confirms the data is actually visible in ClickStack. It uses [`clickhousectl`](https://clickhouse.com/docs/interfaces/cli) for all cloud and SQL operations. **Scope.** This skill supports two paths, chosen in Step 0: 1. **Deploy a new collector** locally. You can do this **two ways: individual `docker` commands, or a `docker compose` file** (recommended, fewer commands and one file to start/stop). Make the user aware of both up front and let them pick in Step 0; do not assume plain `docker`. Either way runs the ClickStack distribution of the collector, preconfigured for Managed ClickStack. 2. **Configure your own existing collector** by adding the ClickHouse exporter configuration. We give you the exact config to drop in; you reload your collector. Use this if you already run a collector in a **gateway** role. A full Kubernetes deployment (Helm, secrets in K8s Secrets) is out of scope here; the config we generate in path 2 can be applied to a collector running anywhere. The end state is: - A dedicated `hyperdx_ingest` SQL user on the target service, with exactly the grants the collector needs (it creates the `otel.*` schema on first write). - A collector forwarding logs, traces, and metrics into the `otel` database on the service, either the new local ClickStack collector or your existing one. - Rich synthetic telemetry across several services, severities, span statuses, and metric types, so ClickStack's Search, Service Map, and dashboards have something real to show. - The service confirmed **awake**, and the user walked through the ClickStack onboarding in the Cloud console so they can actually *see* their data. Secrets (the OTLP auth token and the SQL password) are generated locally, written **once** to a `0600` env file, and passed to Docker via `--env-file`. They are never pasted into the chat, never passed with `docker run -e`, and never echoed back after creation. Follow these steps in order. Each step depends on state established by the previous one. --- ## Step 0: Choose your path Ask the user two short questions before doing anything else, because they determine which later steps run. **Question 1: Do you already have an OpenTelemetry collector running in a gateway role?** - **No, set one up for me.** -> the **new-collector** path. Continue to Question 2. - **Yes, I have one.** -> the **existing-collector** path. Skip Question 2 (it does not apply), and in Step 6 you will configure their collector rather than deploy a new one. **Question 2 (new-collector path only): Run the collector with individual Docker commands, or a Docker Compose file?** - **Docker Compose (recommended).** Fewer commands, one file to start and stop, easiest to re-run. Best if `docker compose` is available. - **Individual Docker commands.** Use if Compose is not installed or you prefer explicit commands. Record the answers as `COLLECTOR_PATH` (`new` or `existing`) and, for the new path, `DEPLOY_MODE` (`compose` or `run`). Refer back to them in Step 6 and Step 7. --- ## Step 1: Batch the permissions up front Coding agents prompt for approval the first time they see each shell command. To avoid interrupting the user every few steps, ask them once, up front, to allowlist the command prefixes below (the "always allow for this project / session" option in their agent). There are no destructive operations and nothing targets anything outside this project or their ClickHouse Cloud service. | Command prefix | Used for | Needed when | | --- | --- | --- | | `openssl rand …` | generate the OTLP token and SQL password | always | | `clickhousectl cloud …` | auth, resolve the service, run SQL via the Query API | always | | `jq …` | parse JSON from `clickhousectl` | always | | `docker …` / `docker compose …` | run/inspect the collector and the telemetry generator | new-collector path, and the optional telemetry check | | `curl …` | local health check against `localhost:13133` (and installing `clickhousectl` if missing) | new-collector path | Tell the user, in your own words: *"If your agent supports it, choose 'always allow' for each of these the first time it asks. The whole run is read-only against your machine except for the collector container, and write operations against ClickHouse are limited to creating the ingest user and the `otel` schema."* If the user is on the existing-collector path and does not want to run the optional telemetry check, you can drop `docker` and `curl` from the list. **Two approvals are semantic, not prefix-based, so allowlisting won't pre-clear them.** Warn the user to expect these and approve them explicitly when they appear: - The `clickhousectl` install in Step 3 uses `curl … | sh`, which many agent sandboxes flag as "downloading and running untrusted code" regardless of any `curl` allowlist rule. - The `CREATE USER` / `GRANT` in Step 5 may be flagged as "modifying shared production infrastructure," again independent of the `clickhousectl` prefix rule. Neither is solved by the table above; they are one-time, intentional, and safe to approve. Then continue. --- ## Step 2: Confirm the target service and lay down the secrets file The user's prompt contains a service identifier, either a service ID (UUID) or a service name. Treat that value as `SERVICE_REF`. Create a working directory and a **`0600` env file** that will hold all configuration and secrets for this run. The key names match exactly what the collector image reads, so this same file is passed straight to `docker run --env-file` (or referenced by Compose) in Step 6. Write it under a tight `umask` so the secret is never briefly world-readable: ```bash WORKDIR="${WORKDIR:-$HOME/clickstack-otel-collector}" mkdir -p "$WORKDIR" && chmod 700 "$WORKDIR" ENV_FILE="$WORKDIR/collector.env" # Generate secrets WITHOUT printing them; write straight into a private file. ( umask 177 { echo "SERVICE_REF=$SERVICE_REF" echo "OTLP_AUTH_TOKEN=$(openssl rand -hex 32)" echo "CLICKHOUSE_USER=hyperdx_ingest" echo "CLICKHOUSE_PASSWORD=$(openssl rand -hex 24)Aa1-" echo "HYPERDX_OTEL_EXPORTER_CLICKHOUSE_DATABASE=otel" } > "$ENV_FILE" ) chmod 600 "$ENV_FILE" ls -l "$ENV_FILE" ``` Two things about these values matter and are easy to get wrong: - **Key names are exact.** The collector reads `CLICKHOUSE_USER`, `CLICKHOUSE_PASSWORD`, `CLICKHOUSE_ENDPOINT`, and `HYPERDX_OTEL_EXPORTER_CLICKHOUSE_DATABASE`. Store the SQL password under `CLICKHOUSE_PASSWORD` (not a custom name); if it is missing, the collector starts with an **empty** password and dies with `code: 516, Authentication failed`. - **The password charset is constrained from three directions at once.** ClickHouse Cloud rejects passwords without at least one uppercase character and one special character, so a plain hex string fails at `CREATE USER`. At the same time, the collector's migration tool embeds the password in a connection URL, so `@`, `:`, `/`, `?`, `#`, and `%` corrupt it (symptom: `code: 516` at startup even though the password is "correct"). The recipe above is random hex (lowercase + digits) plus the suffix `Aa1-`, which adds the required uppercase, a digit, and a **URL-unreserved** special character (`-`). The OTLP token has no such rules (it is just a bearer token), so plain hex is fine for it. The env file uses **bare `KEY=VALUE` lines with no quotes**: Docker's `--env-file` does not do shell parsing, so any quotes you add become part of the value. On the **existing-collector path** the `OTLP_AUTH_TOKEN` is not used by your collector (auth on your receiver is your own setup); it is generated only so the same file works if you later switch to the local collector. The `CLICKHOUSE_*` values are still used: they go into the exporter config you add to your collector in Step 6. **Every later step runs in a fresh shell, so `WORKDIR`, `ENV_FILE`, and any exported credentials do not persist, and `WORKDIR`/`ENV_FILE` are not stored inside the env file, so sourcing it can't recover them.** Begin each subsequent step's shell with this **standard preamble**, which re-derives the paths from the deterministic default, loads the saved credentials (Step 3), and loads the config: ```bash WORKDIR="${WORKDIR:-$HOME/clickstack-otel-collector}"; ENV_FILE="$WORKDIR/collector.env" [ -f "$WORKDIR/creds.env" ] && . "$WORKDIR/creds.env"; set -a; . "$ENV_FILE"; set +a ``` If you chose a non-default `WORKDIR`, set it explicitly at the top of every step (the `${WORKDIR:-…}` default only covers the standard location). Later steps refer to this as "the standard preamble". **Confirm with the user** that `SERVICE_REF` is correct. Tell them the working directory and that `collector.env` (mode `0600`) now holds the OTLP token and the SQL password. Do **not** print either secret. If they want to see a value, point them at the file (`grep OTLP_AUTH_TOKEN "$ENV_FILE"`). If the user supplied their own token or password, write those into the file instead of the generated ones, but keep the same `0600` discipline and make sure any custom password still meets the charset rules above. --- ## Step 3: Authenticate `clickhousectl` (separate terminal by default) Check `clickhousectl` is on `PATH`. Run this presence check **on its own**, not chained to the installer: the `|| curl … | sh` form drags a harmless check into a compound command that sandboxes deny wholesale as an untrusted-code download. ```bash which clickhousectl ``` Only if that prints nothing, install it (the user may need to approve this explicitly, see Step 1): ```bash curl -fsSL https://clickhouse.com/cli | sh ``` Check authentication: ```bash clickhousectl cloud auth status ``` This skill needs **API key authentication**: OAuth is read-only and cannot create users or run write queries. If the `API key` row is not `Active`, the user must authenticate. **Do not ask the user to paste their API key and secret into the chat.** Anything pasted into the conversation lives in the transcript and has to be rotated afterward. Instead, ask them to authenticate in a **separate terminal**, then tell you when they are done: > I need a ClickHouse Cloud **Admin** API key to create the ingest user and verify the data. > Please don't paste it here. Instead: > > 1. In the [Cloud console](https://console.clickhouse.cloud), open **Organization → API keys > → New API key**, and give it the **Admin** role. (Developer-scoped keys can't provision the > per-service Query API endpoint that `cloud service query` uses.) > 2. In a **separate terminal**, run: > > ```bash > clickhousectl cloud auth login --api-key --api-secret > ``` > > 3. Tell me when that's done and I'll re-check the auth status. Poll until the API key row reports `Active`, then confirm with a real privileged call rather than trusting the status table alone. Use a **ref-agnostic** call here: `SERVICE_REF` may be a name, and `cloud service get` only accepts a UUID, so confirming with `get` would fail on a name for reasons unrelated to auth. `cloud service list` needs no ref and proves the API key works: ```bash clickhousectl cloud auth status clickhousectl cloud service list --json | jq -r '.[].name' ``` If the list returns your services, you are authenticated; continue. The actual name-or-UUID resolution of `SERVICE_REF` happens in Step 4. **Expect to need the env-var credentials (common, not an edge case).** Many `clickhousectl` builds save the credentials file but a freshly spawned shell (such as the one your tool calls run in) doesn't read it, so `auth status` shows `Active` yet the very next `clickhousectl` call reports `No credentials found`. Rather than treat this as a rare fallback, write a small **sourceable creds file** once, then load it in every later shell. This keeps each subsequent shell to a single `.` line instead of two `jq` re-derivations, and keeps the secret out of the chat: ```bash # Write a private, sourceable creds file next to collector.env. ( umask 177 { echo "export CLICKHOUSE_CLOUD_API_KEY=$(jq -r .api_key "$HOME/.clickhouse/credentials.json")" echo "export CLICKHOUSE_CLOUD_API_SECRET=$(jq -r .api_secret "$HOME/.clickhouse/credentials.json")" } > "$WORKDIR/creds.env" ) chmod 600 "$WORKDIR/creds.env" ``` **From now on, open every shell that calls `clickhousectl` with both loads**, because env vars do not persist across shells: ```bash . "$WORKDIR/creds.env"; set -a; . "$ENV_FILE"; set +a ``` Re-run the `service list` check above with the creds loaded; it should now succeed. Do not continue until a real call works. (If `clickhousectl auth status` already shows `API key … Active` and calls succeed without `creds.env`, you can skip this; but most agent shells need it.) --- ## Step 4: Resolve the service and capture the HTTPS endpoint Run the standard preamble (Step 2) so the paths, credentials, and config are all loaded in this shell, then resolve the service. If `SERVICE_REF` is a UUID, use it directly; otherwise look it up by name: ```bash WORKDIR="${WORKDIR:-$HOME/clickstack-otel-collector}"; ENV_FILE="$WORKDIR/collector.env" [ -f "$WORKDIR/creds.env" ] && . "$WORKDIR/creds.env"; set -a; . "$ENV_FILE"; set +a ``` ```bash # UUID form clickhousectl cloud service get "$SERVICE_REF" --json > "$WORKDIR/svc.json" # Name form (note the double quotes: service names can contain spaces or apostrophes, # e.g. "Alex's test") clickhousectl cloud service list --json \ | jq --arg n "$SERVICE_REF" '.[] | select(.name==$n)' > "$WORKDIR/svc.json" ``` Extract the values you need, coercing the port to an integer. The port serializes as a float (`8443.0`); if `:8443.0` leaks into the endpoint the collector's ClickHouse exporter cannot dial it: ```bash SERVICE_ID=$(jq -r '.id' "$WORKDIR/svc.json") SERVICE_NAME=$(jq -r '.name' "$WORKDIR/svc.json") STATE=$(jq -r '.state' "$WORKDIR/svc.json") CLICKHOUSE_ENDPOINT=$(jq -r '.endpoints[] | select(.protocol=="https") | "https://\(.host):\(.port | tonumber | floor)"' "$WORKDIR/svc.json") # Persist the resolved values back into the env file for later steps and docker --env-file. # Append only if the key is not already present, so a second run does not duplicate lines. grep -q '^SERVICE_ID=' "$ENV_FILE" || echo "SERVICE_ID=$SERVICE_ID" >> "$ENV_FILE" grep -q '^CLICKHOUSE_ENDPOINT=' "$ENV_FILE" || echo "CLICKHOUSE_ENDPOINT=$CLICKHOUSE_ENDPOINT" >> "$ENV_FILE" printf 'service=%q state=%s endpoint=%s\n' "$SERVICE_NAME" "$STATE" "$CLICKHOUSE_ENDPOINT" ``` `STATE` must be `running`. If it is `stopped` or `starting`, ask the user to start the service (or wait), and do not proceed. ClickHouse Cloud services **idle-suspend**, so even a "running" service can be asleep; the next query both checks reachability and wakes it: ```bash clickhousectl cloud service query --id "$SERVICE_ID" --query "SELECT version()" ``` A successful response confirms the service is awake and that the per-service Query API key is provisioned. On the first call `clickhousectl` prints `Provisioning Query API endpoint + key for service ''...`, which is expected. --- ## Step 5: Create the `hyperdx_ingest` SQL user and grant it `otel.*` This step is the same on both paths: the collector (new or existing) authenticates to ClickHouse as `hyperdx_ingest`. Open the shell with the combined load so `$CLICKHOUSE_PASSWORD` (and credentials) are set. > **Expect an approval prompt here.** The `CREATE USER` / `GRANT` statements below are DDL against > a Cloud service, so some agent sandboxes flag them as "modifying shared production > infrastructure" even when `clickhousectl` is allowlisted. This is expected; the operations are > scoped to a single dedicated ingest user and the `otel` schema, and the user should approve them > explicitly when prompted. **Never put the plaintext password in the SQL. Hash it locally and use `sha256_hash`.** Two problems rule out `IDENTIFIED WITH sha256_password BY '$CLICKHOUSE_PASSWORD'`: the secret would land in the process arg list (visible in `ps`) and shell history, and, critically, **the Query API echoes the failing statement verbatim in its error JSON**, so any error (a transient failure, a charset slip) leaks the password into output an agent may surface. Passing it over stdin does not help, the error echo still contains it. Instead compute the SHA-256 hash of the password locally (`sha256_hash` stores exactly what `sha256_password` would, so the collector still logs in with the plaintext from the env file) and put only the **hash** in the statement. A hash is non-reversible, so even an echoed error cannot leak the password: ```bash WORKDIR="${WORKDIR:-$HOME/clickstack-otel-collector}"; ENV_FILE="$WORKDIR/collector.env" [ -f "$WORKDIR/creds.env" ] && . "$WORKDIR/creds.env"; set -a; . "$ENV_FILE"; set +a # SHA-256 of the password. openssl is already a dependency; this is portable (macOS + Linux). # Only this hash ever reaches SQL, output, or `ps`; the plaintext stays in the env file. PW_HASH=$(printf %s "$CLICKHOUSE_PASSWORD" | openssl dgst -sha256 | awk '{print $NF}') # Send statements ONE AT A TIME: the Query API runs over HTTP and rejects multi-statement input # ("Multi-statements are not allowed"), so a single ; -separated batch fails. clickhousectl cloud service query --id "$SERVICE_ID" --query \ "CREATE USER IF NOT EXISTS hyperdx_ingest IDENTIFIED WITH sha256_hash BY '$PW_HASH'" # Re-run safe: force the password to this run's value if the user already existed. clickhousectl cloud service query --id "$SERVICE_ID" --query \ "ALTER USER hyperdx_ingest IDENTIFIED WITH sha256_hash BY '$PW_HASH'" ``` Grant the least privilege the collector needs to create and write the `otel.*` schema. On the current image the schema migrations and their version table also live in `otel`, so `otel.*` is sufficient (this statement carries no secret): ```bash clickhousectl cloud service query --id "$SERVICE_ID" --query \ "GRANT SELECT, INSERT, CREATE DATABASE, CREATE TABLE, CREATE VIEW ON otel.* TO hyperdx_ingest" ``` > **Older image builds:** some earlier collector versions ran their goose migrations against a > version table in the `default` database, so startup looped on `ACCESS_DENIED` until `default.*` > was also granted. If you see `ACCESS_DENIED` referencing `default` in the collector logs > (Step 6), add this and restart the container: > > ```bash > clickhousectl cloud service query --id "$SERVICE_ID" --query \ > "GRANT SELECT, INSERT, CREATE TABLE ON default.* TO hyperdx_ingest" > ``` Verify: ```bash clickhousectl cloud service query --id "$SERVICE_ID" --query "SHOW GRANTS FOR hyperdx_ingest" ``` You should see `GRANT SELECT, INSERT, CREATE DATABASE, CREATE TABLE, CREATE VIEW ON otel.* TO hyperdx_ingest`. --- ## Step 6: Set up the collector Follow the sub-section that matches the path and mode you chose in Step 0. All three converge on the same end state: a collector accepting OTLP and writing into the `otel` database on the service. Every code block in this step assumes you have run the **standard preamble** (Step 2) first, so `$WORKDIR`, `$ENV_FILE`, `$SERVICE_ID`, and the secrets are set in the shell. Make sure Docker is running (new-collector path only): ```bash WORKDIR="${WORKDIR:-$HOME/clickstack-otel-collector}"; ENV_FILE="$WORKDIR/collector.env" [ -f "$WORKDIR/creds.env" ] && . "$WORKDIR/creds.env"; set -a; . "$ENV_FILE"; set +a docker info > /dev/null ``` ### Step 6a: New collector with Docker Compose (`DEPLOY_MODE=compose`) Write a Compose file in the working directory. It reads the same `collector.env` for secrets, publishes the OTLP and health ports, and pins a named network so the telemetry generator in Step 7 can reach the collector by container name: ```bash cat > "$WORKDIR/docker-compose.yaml" <<'EOF' name: clickstack services: otel-collector: image: clickhouse/clickstack-otel-collector:latest container_name: clickstack-otel-collector env_file: ./collector.env ports: - "4317:4317" # OTLP gRPC - "4318:4318" # OTLP HTTP - "13133:13133" # health restart: unless-stopped networks: [clickstack-net] networks: clickstack-net: name: clickstack-net EOF # Compose refuses to adopt a clickstack-net it did not create (a leftover from the docker run # path, a prior failed Compose run, or a DEPLOY_MODE switch), failing with "network clickstack-net # was found but has incorrect label". If an orphan exists with no containers attached, remove it so # Compose can recreate it with its own labels. if docker network inspect clickstack-net >/dev/null 2>&1 \ && [ -z "$(docker network inspect clickstack-net -f '{{range .Containers}}{{.Name}} {{end}}')" ]; then docker network rm clickstack-net fi ( cd "$WORKDIR" && docker compose up -d ) ``` Compose creates the `clickstack-net` network for you (the guard above clears an orphaned one from a prior run first). Skip to **Step 6d** to confirm health. ### Step 6b: New collector with individual Docker commands (`DEPLOY_MODE=run`) Create a user-defined network so the telemetry generator in Step 7 can reach the collector by container name: ```bash docker network create clickstack-net 2>/dev/null || true ``` Start the collector, passing **all secrets via `--env-file`** (never `-e`, which would put the secret on the command line, in shell history, and in `ps`). The `docker rm -f` first makes the step safe to re-run: ```bash docker rm -f clickstack-otel-collector 2>/dev/null || true docker run -d \ --name clickstack-otel-collector \ --network clickstack-net \ --env-file "$ENV_FILE" \ -p 4317:4317 \ -p 4318:4318 \ -p 13133:13133 \ clickhouse/clickstack-otel-collector:latest ``` The image reads `OTLP_AUTH_TOKEN`, `CLICKHOUSE_ENDPOINT`, `CLICKHOUSE_USER`, `CLICKHOUSE_PASSWORD`, and `HYPERDX_OTEL_EXPORTER_CLICKHOUSE_DATABASE` from the env file. It enables bearer-token auth on the OTLP receiver with an empty scheme, so callers send the raw token as the `authorization` header (no `Bearer ` prefix). Continue to **Step 6d**. ### Step 6c: Configure your existing collector (`COLLECTOR_PATH=existing`) Add the ClickHouse exporter to your existing collector configuration. The config below matches the behavior of the ClickStack distribution, including the Session Replay (`rrweb`) routing path, and writes into the `otel` database the ClickStack UI expects. **Reference the endpoint and password as environment variables (`${env:…}`), do not hardcode them into the config file.** The contrib collector expands `${env:VAR}` at load time, so keeping the plaintext password out of the config file is both safer and consistent with the rest of this skill. Start your collector with the env vars available, the simplest way is the same `--env-file` the local collector uses: ```bash # When running the contrib collector in Docker, pass collector.env so ${env:CLICKHOUSE_*} resolve: # docker run -d --env-file "$ENV_FILE" -p 4317:4317 -p 4318:4318 \ # -v "$WORKDIR/your-config.yaml:/etc/otelcol-contrib/config.yaml:ro" \ # otel/opentelemetry-collector-contrib:latest # For a non-Docker collector, export CLICKHOUSE_ENDPOINT and CLICKHOUSE_PASSWORD into its # environment (e.g. an EnvironmentFile= in the systemd unit) before it starts. ``` Add this to your collector config and reload it: ```yaml receivers: otlp/hyperdx: protocols: grpc: include_metadata: true endpoint: "0.0.0.0:4317" http: cors: allowed_origins: ["*"] allowed_headers: ["*"] include_metadata: true endpoint: "0.0.0.0:4318" processors: batch: memory_limiter: limit_mib: 1500 spike_limit_mib: 512 check_interval: 5s connectors: routing/logs: default_pipelines: [logs/out-default] error_mode: ignore table: - context: log statement: route() where IsMatch(attributes["rr-web.event"], ".*") pipelines: [logs/out-rrweb] exporters: clickhouse: database: otel endpoint: ${env:CLICKHOUSE_ENDPOINT} username: hyperdx_ingest password: ${env:CLICKHOUSE_PASSWORD} ttl: 720h timeout: 5s retry_on_failure: enabled: true initial_interval: 5s max_interval: 30s max_elapsed_time: 300s clickhouse/rrweb: database: otel endpoint: ${env:CLICKHOUSE_ENDPOINT} username: hyperdx_ingest password: ${env:CLICKHOUSE_PASSWORD} ttl: 720h logs_table_name: hyperdx_sessions timeout: 5s retry_on_failure: enabled: true initial_interval: 5s max_interval: 30s max_elapsed_time: 300s service: pipelines: traces: receivers: [otlp/hyperdx] processors: [memory_limiter, batch] exporters: [clickhouse] metrics: receivers: [otlp/hyperdx] processors: [memory_limiter, batch] exporters: [clickhouse] logs/in: receivers: [otlp/hyperdx] exporters: [routing/logs] logs/out-default: receivers: [routing/logs] processors: [memory_limiter, batch] exporters: [clickhouse] logs/out-rrweb: receivers: [routing/logs] processors: [memory_limiter, batch] exporters: [clickhouse/rrweb] ``` Notes for this path: - If you use your own distribution, ensure it includes the ClickHouse exporter. The upstream [contrib image](https://github.com/open-telemetry/opentelemetry-collector-contrib) already does. - Authentication on the OTLP receivers is your existing setup. The `OTLP_AUTH_TOKEN` generated in Step 2 is not used here unless you wire it into your own auth (for example `bearertokenauth`). - After reloading, skip the health check below (that is specific to the local container) and go straight to **Step 7** to send a verification burst (point the generator at your own collector's OTLP endpoint). ### Step 6d: Confirm the local collector is healthy (new-collector path) ```bash docker ps --filter name=clickstack-otel-collector --format '{{.Status}}' curl -fsS http://localhost:13133/ && echo docker logs --tail 40 clickstack-otel-collector 2>&1 | tail -40 ``` A healthy start shows the seed migrations running to completion (`[seed] OK ...` lines ending in `goose: up to current file version: N`), then `Everything is ready. Begin running and processing data.` (or equivalent), `docker ps` reporting `Up ... (healthy)`, and the health check returning HTTP 200. A seed line like `ClickHouse 25.12 < 26.2, falling back to compatibility logs and traces schemas` on an older server version is **expected and harmless**, not an error; do not pause on it. If instead the container exits, the cause is almost always in the seed step: - `code: 516, Authentication failed: password is incorrect` -> `CLICKHOUSE_PASSWORD` is empty or wrong in the env file. The most common slip is storing the password under a different key name (it **must** be `CLICKHOUSE_PASSWORD`), or using a password containing `@ : / ? # %`, which corrupts the migration tool's connection URL. - `[HTTP 403]` / `data size should be 0 < ` at "server hello" -> same root cause: an empty/wrong password against the HTTPS endpoint. - TLS / dial errors -> `CLICKHOUSE_ENDPOINT` is malformed (it must be `https://:8443`, with no `.0` on the port). - `ACCESS_DENIED` referencing `default` -> only on older image builds; apply the `default.*` grant from the Step 5 note and restart. --- ## Step 7: Send rich synthetic telemetry and verify ingestion Use `telemetrygen` (the OpenTelemetry Collector Contrib generator) from its **Docker image**, so nothing is installed on the host. Instead of one flat burst, send telemetry across several **services**, **severities**, **span statuses**, and **metric types**, so ClickStack's Search, Service Map, and dashboards have realistic, varied data rather than a single uniform stream. Load the env file so the token is available, then reference `$OTLP_AUTH_TOKEN`. The `tg` helper below **redirects all generator output to a log file** and prints only an exit code, because `telemetrygen` echoes its full config, **including the `authorization` header (your OTLP token)**, to stdout. Never surface that raw output in the chat. `telemetrygen`'s header syntax requires the value to be a quoted string: `key="value"`. ```bash WORKDIR="${WORKDIR:-$HOME/clickstack-otel-collector}"; ENV_FILE="$WORKDIR/collector.env" [ -f "$WORKDIR/creds.env" ] && . "$WORKDIR/creds.env"; set -a; . "$ENV_FILE"; set +a TG_IMAGE=ghcr.io/open-telemetry/opentelemetry-collector-contrib/telemetrygen:latest NET=clickstack-net ENDPOINT=clickstack-otel-collector:4317 TG_LOG="$WORKDIR/telemetrygen.log"; : > "$TG_LOG" tg() { # usage: tg [extra telemetrygen flags...] # Output (which contains the token in the echoed config) goes to $TG_LOG, never the terminal. local signal="$1"; shift docker run --rm --network "$NET" "$TG_IMAGE" "$signal" \ --otlp-endpoint "$ENDPOINT" \ --otlp-insecure \ --otlp-header "authorization=\"$OTLP_AUTH_TOKEN\"" \ --rate 10 --duration 15s "$@" >>"$TG_LOG" 2>&1 echo "$signal exit=$?" } ``` > **Existing-collector path:** set `NET` and `ENDPOINT` to reach *your* collector instead. If it > runs on this host, use `--network host` style access or point `ENDPOINT` at its published > address, and set the `authorization` header (or other auth) to whatever your receiver expects. > Everything below is otherwise identical. **Judge success by exit code and row counts, never by the generator's logs.** Two reasons. First, the log contains your OTLP token (see above), so do not print it. Second, it is noisy and every run ends with `rpc error: code = Canceled desc = grpc: the client connection is closing` once `--duration` elapses, which is **expected shutdown, not a failure**. The `tg` helper already prints ` exit=0` on success. If you must inspect a failure, grep the log for the real signal without dumping it, for example `grep -c Unauthenticated "$TG_LOG"` (a non-zero count plus a non-zero exit means the `authorization` header did not match). Confirm overall success with the row counts in the verification queries below. **Quote attribute values so the inner double quotes survive the shell.** `telemetrygen` requires each attribute as `key="value"` (with literal double quotes), and rejects a bare `key=value` with `value should be a string wrapped in double quotes`. If you write `--otlp-attributes deployment.environment="production"`, bash strips the quotes and the container receives `deployment.environment=production`, which hard-fails. Wrap the **whole argument in single quotes** so the inner double quotes reach the container, exactly as the `tg` helper already does for the auth header. Logs across two services with different severities and bodies, including an error line: ```bash tg logs --service checkout --severity-text Info --severity-number 9 \ --body "checkout completed" \ --otlp-attributes 'deployment.environment="production"' \ --telemetry-attributes 'http.method="POST"' tg logs --service payment --severity-text Error --severity-number 17 \ --body "payment gateway timeout" \ --otlp-attributes 'deployment.environment="production"' \ --telemetry-attributes 'http.status_code="500"' ``` Traces with child spans, a healthy service and an erroring one (this is what populates the Service Map and the error views): ```bash tg traces --service checkout --child-spans 4 --span-duration 120ms --status-code Ok \ --otlp-attributes 'deployment.environment="production"' \ --telemetry-attributes 'http.route="/cart"' tg traces --service payment --child-spans 3 --span-duration 400ms --status-code Error \ --otlp-attributes 'deployment.environment="production"' \ --telemetry-attributes 'http.route="/charge"' ``` Metrics across the three common types, so dashboards have gauges, counters, and a distribution: ```bash tg metrics --service checkout --metric-type Sum tg metrics --service checkout --metric-type Gauge tg metrics --service payment --metric-type Histogram ``` (`--metric-type` accepts `Gauge`, `Sum`, `Histogram`, or `ExponentialHistogram`. Add `--otlp-http` with `--otlp-endpoint clickstack-otel-collector:4318` to exercise the HTTP path instead of gRPC.) Wait ~15 seconds for the collector to flush its batch, then confirm the tables exist: ```bash clickhousectl cloud service query --id "$SERVICE_ID" --query \ "SELECT name FROM system.tables WHERE database='otel' ORDER BY name" ``` Then confirm rows are landing. Count by `parts.rows`, which is signal-agnostic and avoids hard-coding per-signal column names: ```bash clickhousectl cloud service query --id "$SERVICE_ID" --query \ "SELECT table, sum(rows) AS rows FROM system.parts WHERE database='otel' AND active AND table IN ('otel_logs','otel_traces', 'otel_metrics_sum','otel_metrics_gauge', 'otel_metrics_histogram','otel_metrics_exponential_histogram', 'otel_metrics_summary') GROUP BY table ORDER BY table" ``` You should see non-zero `rows` for `otel_logs`, `otel_traces`, `otel_metrics_sum`, `otel_metrics_gauge`, and `otel_metrics_histogram`. If a signal is missing: 1. Tail the collector logs (`docker logs --tail 50 clickstack-otel-collector`) for export errors. 2. Confirm the `authorization` header matches `$OTLP_AUTH_TOKEN`: `grep -c Unauthenticated "$TG_LOG"` (a non-zero count means a mismatch, the full message is `code = Unauthenticated desc = provided authorization does not match expected scheme or token`). Grep rather than print the log, since it contains the token. 3. Re-check `CLICKHOUSE_ENDPOINT` has the `https://` scheme and `:8443` port. 4. Some metric kinds flush slowly. Re-run the count after another 30 seconds before declaring failure. Do not proceed until every expected signal has non-zero rows. --- ## Step 8: Confirm the service is awake, then complete onboarding in ClickStack Rows in ClickHouse are **not** the same as the user seeing telemetry in ClickStack. The ClickStack UI requires a one-time onboarding step that auto-detects the data sources, and that step fails if the ClickHouse service has idle-suspended in the meantime. **First, confirm the service is awake.** Do not skip this; it is the most common reason onboarding shows no sources. Run a real query and require it to succeed: ```bash clickhousectl cloud service query --id "$SERVICE_ID" --query "SELECT 1" ``` If this returns `1`, the service is awake; continue immediately to the console steps below while it stays warm. If it errors or times out, the service was asleep and this call is waking it: wait a few seconds and re-run until it returns `1`. Only proceed once it succeeds. **Then walk the user through onboarding explicitly.** Do not just say "done"; spell out each click, because the sources only appear after this flow is completed: 1. Go to the [ClickHouse Cloud console](https://console.clickhouse.cloud) and open the target service. 2. In the **left-hand menu, select ClickStack**. 3. Click through to **Getting Started** and follow the onboarding flow. 4. **Ignore any prompt that asks you to set up or configure a collector / start ingestion.** You have already done that in the steps above. Skip straight past those screens (click through / "Next") to source detection. Re-running the console's collector setup is unnecessary and only causes confusion. 5. The data sources are **auto-detected**: logs, traces, and metrics for the `otel` database are picked up automatically, and your data appears in the Search and dashboard views. The direct link is `https://console.clickhouse.cloud/services//clickstack` (substitute `$SERVICE_ID`). **If source detection shows nothing**, the service almost certainly idle-suspended between the data send and the console step. Re-run the `SELECT 1` wake query above for the user, then have them re-run the detection, rather than leaving them to debug an opaque failure. --- ## Step 9: Summarize and hand off (without echoing secrets) Print a summary in roughly this format. Note the token is **referenced, not printed**, the SQL password is not shown at all, and the collector keeps running until the user stops it. Adjust the "how to stop" line to the deploy mode they chose. ``` ✅ ClickStack is set up and ingesting telemetry for service (). Complete onboarding in the console (Step 8) to auto-detect the sources and see your data. Collector ▸ New local collector via (or: configured your existing collector) ▸ Send OTLP gRPC to: localhost:4317 ▸ Send OTLP HTTP to: localhost:4318 ▸ Health check: http://localhost:13133/ (local collector only) ▸ Required header: authorization: (retrieve with: grep OTLP_AUTH_TOKEN /collector.env) ClickHouse target ▸ Endpoint: ▸ SQL user: hyperdx_ingest (password in /collector.env, mode 0600) ▸ Database: otel Finish in the ClickHouse Cloud console: ▸ Open the service, select ClickStack in the left menu, then Getting Started, and complete onboarding to auto-detect sources. ▸ https://console.clickhouse.cloud/services//clickstack ``` Then tell the user, in your own words, that: 1. All secrets live in `/collector.env` (mode `0600`). Nothing sensitive was pasted into this chat or passed on a `docker run` command line. 2. The collector keeps running until they stop it. For Compose: `cd && docker compose down` stops it, `docker compose up -d` brings it back. For individual Docker: `docker stop clickstack-otel-collector` and `docker start clickstack-otel-collector`. 3. Any application, SDK, or agent on this host can now send OTLP to `localhost:4317` (gRPC) or `localhost:4318` (HTTP) with the `authorization` header from the env file. 4. The ClickHouse Cloud service idle-suspends. If ClickStack later shows no recent data, the service may simply be asleep; sending new telemetry or running any query wakes it. --- ## Cleanup (only if the user explicitly asks) ```bash # Docker Compose deployment: ( cd "$WORKDIR" && docker compose down ) # Individual Docker deployment: docker rm -f clickstack-otel-collector docker network rm clickstack-net 2>/dev/null || true # Either deployment, remove the ingest user: clickhousectl cloud service query --id "$SERVICE_ID" --query "DROP USER IF EXISTS hyperdx_ingest" # Optionally remove the local files once they are no longer needed: # rm -f "$WORKDIR/collector.env" "$WORKDIR/svc.json" "$WORKDIR/docker-compose.yaml" ``` Do **not** drop the `otel` database: it contains telemetry the user may want to retain. --- ## 10. infra-clickhouse - **Source:** https://github.com/ClickHouse/agent-skills/blob/main/skills/infra-clickhouse/SKILL.md - **Raw:** https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/infra-clickhouse/SKILL.md - **Markdown:** https://skillsdocs.com/ClickHouse/agent-skills/infra-clickhouse.md - **Licence:** Apache-2.0 — https://spdx.org/licenses/Apache-2.0.html Bundled files (4), referenced from this skill's directory: - `metadata.json` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/infra-clickhouse/metadata.json - `README.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/infra-clickhouse/README.md - `ref/cloud.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/infra-clickhouse/ref/cloud.md - `ref/local.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/infra-clickhouse/ref/local.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. --- ## 11. infra-postgres - **Source:** https://github.com/ClickHouse/agent-skills/blob/main/skills/infra-postgres/SKILL.md - **Raw:** https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/infra-postgres/SKILL.md - **Markdown:** https://skillsdocs.com/ClickHouse/agent-skills/infra-postgres.md - **Licence:** Apache-2.0 — https://spdx.org/licenses/Apache-2.0.html Bundled files (4), referenced from this skill's directory: - `metadata.json` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/infra-postgres/metadata.json - `README.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/infra-postgres/README.md - `ref/cloud.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/infra-postgres/ref/cloud.md - `ref/local.md` — https://raw.githubusercontent.com/ClickHouse/agent-skills/main/skills/infra-postgres/ref/local.md --- name: infra-postgres description: Sets up and manages Postgres using the clickhousectl CLI — runs a local Docker-backed Postgres for development, and creates and operates managed ClickHouse Cloud Postgres services (connections, TLS, runtime config, read replicas, failover, point-in-time restore). Use when the user wants a Postgres or PostgreSQL database for their application, a local Postgres dev environment, psql access, or a managed/production Postgres in ClickHouse Cloud, or mentions moving a local Postgres to production. license: Apache-2.0 metadata: author: ClickHouse Inc version: "0.1.0" --- # Postgres with clickhousectl `clickhousectl` manages Postgres in two environments: - **Local** — named, Docker-backed Postgres instances on the user's machine, for development. - **Cloud** — managed Postgres services in ClickHouse Cloud (beta), for production: HA, read replicas, point-in-time restore. 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 | |----------------------|------| | Develop or prototype locally, run tests/CI against Postgres, no cloud account needed | [ref/local.md](ref/local.md) | | Go to production, host a managed Postgres, or use ClickHouse Cloud explicitly | [ref/cloud.md](ref/cloud.md) | | Operate an existing cloud service (passwords, TLS, config, replicas, failover, restore) | [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 Postgres 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 - To replicate Postgres data into ClickHouse for analytics, see ClickPipes (`clickhousectl cloud clickpipe --help`). - For ClickHouse itself (local development or ClickHouse Cloud services), use the `infra-clickhouse` skill.