Setting the file. One moment.
Chapter 04 · Clickhouse Best Practices
Subchapter 4.6
rules/agent-connect-mcp.mdMarkdown4 KBView on GitHub
Impact: HIGH
Two connection methods, each with a clear use case. Pick one based on your environment.
Incorrect (prompting for credentials every time):
# Agent asks the user for host, port, user, password on every session
# Credentials are hardcoded in the prompt or conversation
response = client.query("SELECT 1",
host="???", user="???", password="???") # fragile, unsecuredCorrect (MCP or CLI with pre-configured credentials):
# MCP: credentials configured once via env vars or OAuth
claude mcp add --transport http clickhouse-cloud https://mcp.clickhouse.cloud/mcp
# CLI: credentials in a named profile or env vars
clickhouse client --host abc123.clickhouse.cloud --port 9440 --secure \
--user default --password "$CLICKHOUSE_PASSWORD" --format JSON \
--query "SELECT 1"Best for schema discovery, iterative analysis, and multi-step conversations.
ClickHouse Cloud — zero-install hosted MCP:
claude mcp add --transport http clickhouse-cloud https://mcp.clickhouse.cloud/mcpUses OAuth. Read-only. No env vars needed.
Self-hosted MCP (any ClickHouse deployment):
pip install mcp-clickhouse| Variable | Example | Notes |
|---|---|---|
CLICKHOUSE_HOST | abc123.clickhouse.cloud | Hostname |
CLICKHOUSE_USER | default | Database user |
CLICKHOUSE_PASSWORD | your-password | Database password |
CLICKHOUSE_SECURE | true | Always true for Cloud |
Enable writes: export CLICKHOUSE_ALLOW_WRITE_ACCESS=true
Limitations:
list_tables may not surface column COMMENT annotations — query system.columns directly for full schema context (see agent-discovery-schema).ClickHouse Cloud note: Services can be idle/sleeping. The first query after inactivity may take 10-20 seconds while the service wakes up. A timeout or 503 on first connection is expected — retry once before treating it as an error.
Best for scripting, automation, and queries returning >10K rows. Zero per-call overhead.
clickhouse client \
--host abc123.clickhouse.cloud --port 9440 --secure \
--user default --password 'your-password' \
--format JSON \
--max_execution_time 30 \
--query "SELECT * FROM events LIMIT 100" 2>&1If you have credentials but can’t install clickhouse-client (lambda, sandbox, web-based agent), use the HTTP interface directly:
curl -s "https://abc123.clickhouse.cloud:8443/" \
-H "X-ClickHouse-User: default" \
-H "X-ClickHouse-Key: your-password" \
--data-binary "SELECT name, engine FROM system.tables WHERE database = 'default' FORMAT JSON"Port 8443 is HTTPS. Pass query settings as URL params: ?max_execution_time=30&max_result_rows=10000.
For self-managed: check config.xml or ask your administrator.
Always specify a format. The default (TabSeparated without headers) is unparseable by agents.
| Format | Tokens (1K rows) | Best For |
|---|---|---|
JSON | ~20K | Single queries — includes column types, row count, statistics |
JSONCompact | ~10K | Same metadata as JSON but rows as arrays — good for wide tables |
JSONEachRow | ~15K | Streaming large results, piping through jq |
TabSeparatedWithNames | ~4K | Minimal tokens, simple tabular data |
Use JSON as the default for agent work. Switch to TabSeparatedWithNames when result sets are large and context window budget matters.
Reference: ClickHouse MCP Server (opens in a new tab) · clickhouse-client (opens in a new tab) · Output Formats (opens in a new tab)