clickhouse-mcp

clickhouse-mcp


中文 README

A read-only Model Context Protocol (MCP) server that queries a USA-market ClickHouse warehouse — 1.49 billion rows of 1-minute bars across 1,388 symbols (1991–2026) plus L1 aggregates and L2 materialised technical indicators — and exposes it to MCP-aware agents for large-scale, cross-sectional quant analysis that single-symbol REST MCP servers cannot do.

It is the 6th read-only server in the kevinkda MCP ecosystem (schwab-marketdata / schwab-positions / sec-edgar / polygon-news / yfinance), and deliberately stays independent because its authentication (ClickHouse host/user/password) and data shape (columnar deep history) differ from the REST-based siblings.

Tools (7, all read-only)

| Tool | Purpose | | --- | --- | | get_ohlcv | OHLCV bars for one symbol over a date range at 1m/5m/15m/1h/1d/1w | | get_indicators | One materialised / runtime indicator series (ma20, rsi14, macd_hist, …) | | screen_stocks | Full-market technical-indicator scan (e.g. "today's RSI<30 oversold list") | | get_correlation_matrix | Pairwise return-correlation matrix for 2–50 symbols (Pearson / Spearman) | | run_safe_sql | Disabled by default raw-SQL escape hatch (single read-only SELECT only) | | health_check | Local probe + optional ClickHouse reachability check | | get_server_info | Version + tool list (offline) |

Quickstart

git clone https://github.com/kevinkda/clickhouse-mcp
cd clickhouse-mcp
uv sync --extra dev
cp .env.example .env   # then fill in your read-only ClickHouse connection

Configure the connection in .env (see Security):

CLICKHOUSE_MCP_HOST=your-clickhouse-host
CLICKHOUSE_MCP_HTTP_PORT=8123
CLICKHOUSE_MCP_USER=mcp_readonly
CLICKHOUSE_MCP_PASSWORD=...
CLICKHOUSE_MCP_DATABASE=usa

Run it:

uv run clickhouse-mcp

Register with an MCP client

{
  "mcpServers": {
    "clickhouse": {
      "command": "uv",
      "args": ["run", "clickhouse-mcp"],
      "cwd": "/absolute/path/to/clickhouse-mcp",
      "env": {
        "CLICKHOUSE_MCP_HOST": "your-clickhouse-host",
        "CLICKHOUSE_MCP_HTTP_PORT": "8123",
        "CLICKHOUSE_MCP_USER": "mcp_readonly",
        "CLICKHOUSE_MCP_PASSWORD": "...",
        "CLICKHOUSE_MCP_DATABASE": "usa"
      }
    }
  }
}

Security

This server touches financial data, so it is hardened accordingly. See docs/SECURITY.md and docs/THREAT_MODEL.md.

• Dedicated read-only ClickHouse account — point CLICKHOUSE_MCP_USER at an

account created with readonly = 1 and GRANT SELECT only, never an admin user. Defence in depth: every query is also issued with the ClickHouse session setting readonly=1 plus max_execution_time / max_result_rows guardrails.

• Parameterised, never concatenated — get_ohlcv / get_indicators /

screen_stocks / get_correlation_matrix validate every symbol / indicator / date through anchored regexes + allow-lists and bind them as ClickHouse query parameters. User input never lands in the SQL string.

• run_safe_sql disabled by default — gated behind

CLICKHOUSE_MCP_ALLOW_RAW_SQL=true; even when enabled it enforces SELECT-only single statements, rejects DDL/DML + comments, forces a LIMIT, and inherits readonly=1 + the resource guardrails.

• SSRF-safe — the ClickHouse host is read from env at startup and is never

derived from tool input; no argument can redirect the outbound connection.

• Credentials redacted — every exception runs its text through

redact_secrets, so a password / DSN can never leak via repr(exc) or logs.

Quant use cases unlocked

CH is the "full-market + deep-history + batch" engine; the REST MCP siblings are "real-time + fundamentals + news". Together they close the loop:

• Full-market technical scan — RSI/MACD/Bollinger/MA signals across all 1,388

symbols (single-symbol MCP cannot scan the market).

• Cross-sectional factor ranking — value/momentum/quality scoring across the

universe on a columnar store.

• Multi-symbol correlation matrix — portfolio / pairs prerequisite; CH batch

read beats fetching symbols one at a time.

• Cross-source quant playbook — CH historical signal → schwab real-time price

→ sec-edgar fundamentals → polygon news.

Development

bash scripts/local-ci.sh   # ruff + mypy + bandit + pip-audit + pytest (100% cov)

Tests never touch a live ClickHouse — clickhouse_connect is mocked.

License

MIT — see LICENSE.