ClickHouse MCP Server for AI Assistants

Overview

This MCP (Model Context Protocol) server connects ClickHouse — and an embedded chDB ClickHouse engine — to AI assistants and other MCP-capable clients. It exposes a small set of deterministic, focused RPC-style tools that let LLMs and assistant apps run fast, read-optimized SQL, discover schema metadata, and pull fresh records in real time without building custom ETL layers.

By deploying this server you can enable your assistant to answer questions with up-to-date analytics, run ad-hoc queries, and inspect tables and databases securely. The implementation is optimized for ClickHouse’s high-throughput, low-latency reads and supports querying external data via chDB’s embedded engine for use cases where you want to query files or remote sources directly.

Features

• Execute read-optimized SQL on ClickHouse via an MCP tool.
• Inspect cluster objects: list databases and paginated table metadata.
• Optional support for chDB (embedded ClickHouse) to query local files, URLs, or external databases without ETL.
• Health check endpoint for HTTP/SSE transports that reports ClickHouse connectivity and version.
• Authentication for HTTP/SSE transports (token-based) with a development mode to disable auth locally.
• Configurable timeouts, TLS verification, and role-based connection options.

Installation / Configuration

Install the package from PyPI (or run via your preferred Python toolchain):

pip install mcp-clickhouse

Typical environment variables used to configure a ClickHouse connection:

export CLICKHOUSE_HOST="your-clickhouse-host"
export CLICKHOUSE_PORT="9440"
export CLICKHOUSE_USER="default"
export CLICKHOUSE_PASSWORD="secret"
export CLICKHOUSE_ROLE=""            # optional role
export CLICKHOUSE_SECURE="true"     # enable TLS
export CLICKHOUSE_VERIFY="true"     # verify server certs
export CLICKHOUSE_CONNECT_TIMEOUT="30"
export CLICKHOUSE_SEND_RECEIVE_TIMEOUT="30"

To enable chDB (embedded engine):

export CHDB_ENABLED="true"
export CLICKHOUSE_ENABLED="false"  # optional: disable remote ClickHouse
export CHDB_DATA_PATH="/path/to/chdb/data"

Authentication (HTTP/SSE transports):

# generate a secure token (example)
export CLICKHOUSE_MCP_AUTH_TOKEN=$(openssl rand -hex 32)

# development: disable auth only for local testing
export CLICKHOUSE_MCP_AUTH_DISABLED=true

Claude Desktop / local MCP client snippet (example JSON):

{
  "mcpServers": {
    "mcp-clickhouse": {
      "url": "http://127.0.0.1:8000",
      "headers": {
        "Authorization": "Bearer your-generated-token"
      }
    }
  }
}

Available Tools

• run_query

  • Purpose: Execute SQL against ClickHouse.
  • Input: { “query”: “” }
  • Notes: Server runs read-only by default. Writes are blocked unless explicitly allowed via configuration.

  • list_databases

    • Purpose: Return available database names.
    • Input: none

  • list_tables

    • Purpose: Paginated listing of tables and optional column metadata.
    • Inputs:
      • database (string, required)
      • like / not_like (string, optional) — filter by table name
      • page_token (string, optional) — token for next page
      • page_size (int, default 50)
      • include_detailed_columns (bool, default true)
    • Response shape:
      • tables: array of table objects (name, create_table_query, columns if requested)
      • next_page_token: string | null
      • total_tables: int

  • run_chdb_select_query

    • Purpose: Run SELECT-style SQL against chDB’s embedded engine to query external sources directly.
    • Input: { “query”: “” }
    • Use to query files, URLs, or heterogeneous data without moving data into ClickHouse.

    Health check (HTTP/SSE): GET /health — returns 200 and ClickHouse version when connected, 503 when not.

    Use Cases

    1. Real-time assistant answers from production analytics

       • An assistant can run parameterized read-only SQL to fetch the latest metrics (top customers, KPI values) and combine them with model reasoning.

    2. Ad-hoc data exploration for LLM prompts

       • Use list_tables and run_query to let an assistant discover schema and produce context-aware SQL queries, enabling dynamic diagnostics and data-driven responses.

    3. Querying external data without ETL

       • With chDB enabled, run_chdb_select_query can read CSVs, JSON hosted on URLs, or other sources directly, letting models reason over external datasets without loading them into ClickHouse.

    4. Embedding in dashboards and automation

       • Trigger automated data checks or human-in-the-loop workflows where an assistant runs a fast aggregation via run_query and returns summarized results.

    Security & Operational Notes

    • HTTP/SSE transports require token authentication by default. The stdio transport (used for local CLI integrations) does not require a token.
    • Never disable authentication in production. CLICKHOUSE_MCP_AUTH_DISABLED=true is for local development only.
    • By default the server restricts write operations; enable write access explicitly if you need mutating queries.
    • Monitor the /health endpoint from orchestration systems to assert ClickHouse connectivity.

    For source code, issues, and contributions see the project repository: https://github.com/ClickHouse/mcp-clickhouse.

    Tags:database