Clickhouse

test

中文 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)

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.