ClickHouse MCP Adapter Service

This component serves as an interface bridge for interacting with ClickHouse data infrastructure within the MCP framework.

Supported Operations

Core ClickHouse Functionality

• run_select_query
• Capability to execute arbitrary SQL SELECT statements against the configured ClickHouse cluster.
• Parameter: sql (text): The SQL statement to be run.

• Security Note: All standard queries enforce readonly = 1 to prevent unintended data modifications.

• list_databases

• Retrieves a catalog of all accessible databases on the connected ClickHouse instance.

• list_tables

• Retrieves a catalog of all data collections within a specified namespace.
• Parameter: database (text): Identifier of the target database.

Embedded chDB Engine Tools

• run_chdb_select_query
• Executes SQL queries leveraging the embedded chDB engine for localized or file-based data processing.
• Parameter: sql (text): The SQL statement for chDB execution.
• This enables querying data directly from sources like local files or URLs without needing external ETL steps.

Operational Status Endpoint

When configured for network transport (HTTP or SSE), a diagnostic endpoint is exposed at /health: - Success condition: Returns HTTP 200 OK along with the running ClickHouse server version. - Failure condition: Returns HTTP 503 Service Unavailable if connectivity to the ClickHouse backend is lost.

Example Health Check: bash curl http://localhost:8000/health

Expected Output: OK - Connected to ClickHouse 24.3.1

Configuration Details

This adapter permits activation of either the external ClickHouse connection or the internal chDB instance, or both concurrently.

1. Locate Configuration File: Access the Claude Desktop configuration structure:
2. macOS path: ~/Library/Application Support/Claude/claude_desktop_config.json

3. Windows path: %APPDATA%/Claude/claude_desktop_config.json

4. Add/Modify Server Entry: Incorporate the following JSON snippet into the mcpServers section for connection settings.

Standard ClickHouse Connection Setup:

{ "mcpServers": { "mcp-clickhouse": { "command": "uv", "args": [ "run", "--with", "mcp-clickhouse", "--python", "3.10", "mcp-clickhouse" ], "env": { "CLICKHOUSE_HOST": "", "CLICKHOUSE_PORT": "", "CLICKHOUSE_USER": "", "CLICKHOUSE_PASSWORD": "", "CLICKHOUSE_SECURE": "true", "CLICKHOUSE_VERIFY": "true", "CLICKHOUSE_CONNECT_TIMEOUT": "30", "CLICKHOUSE_SEND_RECEIVE_TIMEOUT": "30" } } } }

For trial use with the public ClickHouse SQL Playground, use this configuration:

{ "mcpServers": { "mcp-clickhouse": { "command": "uv", "args": [ "run", "--with", "mcp-clickhouse", "--python", "3.10", "mcp-clickhouse" ], "env": { "CLICKHOUSE_HOST": "sql-clickhouse.clickhouse.com", "CLICKHOUSE_PORT": "8443", "CLICKHOUSE_USER": "demo", "CLICKHOUSE_PASSWORD": "", "CLICKHOUSE_SECURE": "true", "CLICKHOUSE_VERIFY": "true", "CLICKHOUSE_CONNECT_TIMEOUT": "30", "CLICKHOUSE_SEND_RECEIVE_TIMEOUT": "30" } } } }

chDB (Embedded Engine) Activation:

{ "mcpServers": { "mcp-clickhouse": { "command": "uv", "args": [ "run", "--with", "mcp-clickhouse", "--python", "3.10", "mcp-clickhouse" ], "env": { "CHDB_ENABLED": "true", "CLICKHOUSE_ENABLED": "false", "CHDB_DATA_PATH": "/path/to/chdb/storage" } } } }

Combined Mode Configuration:

{ "mcpServers": { "mcp-clickhouse": { "command": "uv", "args": [ "run", "--with", "mcp-clickhouse", "--python", "3.10", "mcp-clickhouse" ], "env": { "CLICKHOUSE_HOST": "", "CLICKHOUSE_PORT": "", "CLICKHOUSE_USER": "", "CLICKHOUSE_PASSWORD": "", "CLICKHOUSE_SECURE": "true", "CLICKHOUSE_VERIFY": "true", "CLICKHOUSE_CONNECT_TIMEOUT": "30", "CLICKHOUSE_SEND_RECEIVE_TIMEOUT": "30", "CHDB_ENABLED": "true", "CHDB_DATA_PATH": "/path/to/chdb/data" } } } }

1. Executable Path Resolution: Verify the command entry for uv. Substitute it with the absolute filesystem path to your uv binary (e.g., determine this via which uv on Unix-like systems). This guarantees the correct runtime environment.

2. Application Restart: Relaunch Claude Desktop for the new service parameters to take effect.

Alternative: Direct System Python Execution

If avoidance of the uv dependency manager is preferred, install the package directly via pip and adjust the configuration:

1. Installation: bash python3 -m pip install mcp-clickhouse

To update: bash python3 -m pip install --upgrade mcp-clickhouse

1. Configuration for Direct Run:

Using module execution:

{ "mcpServers": { "mcp-clickhouse": { "command": "python3", "args": [ "-m", "mcp_clickhouse.main" ], "env": { / ... Connection Environment Variables ... / } } } }

Using the installed entry point script:

{ "mcpServers": { "mcp-clickhouse": { "command": "mcp-clickhouse", "env": { / ... Connection Environment Variables ... / } } } }

Path Requirement: Ensure that the system executable (python3 or mcp-clickhouse) is resolvable via your shell's PATH, or specify the full absolute path for reliability (e.g., use which python3 or which mcp-clickhouse to find locations).

Development Workflow

1. Service Setup: Navigate to the test-services directory and execute docker compose up -d to spin up the necessary ClickHouse environment.

2. Local Credentials: Create a .env file in the repository root for local development overrides.

Note: Utilizing the 'default' user here is strictly for isolated local testing environments.

bash CLICKHOUSE_HOST=localhost CLICKHOUSE_PORT=8123 CLICKHOUSE_USER=default CLICKHOUSE_PASSWORD=clickhouse

1. Dependency Installation: Run uv sync to pull in all necessary packages. If uv is absent, consult Astral's documentation for installation. Subsequently, activate the virtual environment: source .venv/bin/activate.

2. Server Launch (Inspector Mode): Initiate the MCP service for live debugging using fastmcp dev mcp_clickhouse/mcp_server.py.

3. HTTP Transport Verification: Test network connectivity and the health check endpoint: bash # Default port 8000 CLICKHOUSE_MCP_SERVER_TRANSPORT=http python -m mcp_clickhouse.main

# Custom port example CLICKHOUSE_MCP_SERVER_TRANSPORT=http CLICKHOUSE_MCP_BIND_PORT=4200 python -m mcp_clickhouse.main

# Check health in a separate terminal: curl http://localhost:8000/health # Or port 4200

Configuration Variables Reference

The behavior of the adapter is governed by specific environment settings for both external and embedded data sources.

ClickHouse Parameters

Mandatory Credentials

• CLICKHOUSE_HOST: Server address (e.g., IP or DNS name).
• CLICKHOUSE_USER: Authenticating principal identifier.
• CLICKHOUSE_PASSWORD: Secret key for authentication.

[!WARNING] Security Prudence: Access permissions assigned to the MCP user account must adhere strictly to the principle of least privilege, matching only the required operational scope. Elevated or administrative credentials must never be utilized for this service.

Optional Connection Tuning

• CLICKHOUSE_PORT: Network endpoint number. Defaults to 8443 (secure) or 8123 (insecure).
• CLICKHOUSE_SECURE: Controls SSL/TLS usage. Default is "true".
• CLICKHOUSE_VERIFY: Disables SSL certificate validation if set to "false" (discouraged in production).
• CLICKHOUSE_CONNECT_TIMEOUT: Delay before connection attempts fail (seconds). Default: "30".
• CLICKHOUSE_SEND_RECEIVE_TIMEOUT: Maximum duration for query execution/response receipt (seconds). Default: "300".
• CLICKHOUSE_DATABASE: Pre-selects the working database upon connection. Default: Server default.
• CLICKHOUSE_MCP_SERVER_TRANSPORT: Defines the communication protocol for the MCP layer. Valid values: "stdio", "http", "sse". Default is "stdio".
• CLICKHOUSE_MCP_BIND_HOST: Interface address for HTTP/SSE listening. Default: "127.0.0.1". Use "0.0.0.0" for all interfaces.
• CLICKHOUSE_MCP_BIND_PORT: TCP port for HTTP/SSE services. Default: "8000".
• CLICKHOUSE_ENABLED: Toggles ClickHouse functionality scope. Default: "true".

chDB Parameters

• CHDB_ENABLED: Activates the embedded chDB tools. Default: "false".
• CHDB_DATA_PATH: Specifies the location for persistent chDB data. Default: ":memory:" (transient, volatile storage).

Configuration Scenarios (Environment Variable Examples)

Docker Local Setup: env CLICKHOUSE_HOST=localhost CLICKHOUSE_USER=default CLICKHOUSE_PASSWORD=clickhouse CLICKHOUSE_SECURE=false CLICKHOUSE_VERIFY=false

ClickHouse Cloud Instance: env CLICKHOUSE_HOST=your-instance.clickhouse.cloud CLICKHOUSE_USER=default CLICKHOUSE_PASSWORD=your-password

Secure connection is assumed by default

In-Memory chDB Only: env CHDB_ENABLED=true CLICKHOUSE_ENABLED=false

Remote Inspection via HTTP: env CLICKHOUSE_HOST=localhost CLICKHOUSE_USER=default CLICKHOUSE_PASSWORD=clickhouse CLICKHOUSE_MCP_SERVER_TRANSPORT=http CLICKHOUSE_MCP_BIND_HOST=0.0.0.0 CLICKHOUSE_MCP_BIND_PORT=4200

If utilizing HTTP binding on port 4200, the service endpoints would be: http://localhost:4200/mcp and http://localhost:4200/health.

Unit Testing

bash uv sync --all-extras --dev # Install testing dependencies uv run ruff check . # Static code analysis

docker compose up -d test_services # Start necessary test infrastructure uv run pytest -v tests uv run pytest -v tests/test_tool.py # Target ClickHouse tests uv run pytest -v tests/test_chdb_tool.py # Target chDB tests

Video Resource

== Database Systems Conceptual Background == A database represents a structured repository for persisted information, typically managed by a Database Management System (DBMS). The DBMS is the core software layer facilitating user/application interaction with the data structure, encompassing capture, analysis, and data organization management. The entire assembly—data, software, and applications—is termed a database system. Colloquially, 'database' often stands in for the DBMS itself or the entire integrated system.

Historically, before digital indexing, physical structures like card catalogs served for data management in homes, businesses, and academia. The transition to computerized systems accelerated rapidly, replacing manual indexing methods by the late 20th century.

Data storage scales from simple local filesystems to vast distributed clusters and cloud infrastructure. Database design involves complex trade-offs concerning data modeling paradigms, storage efficiency, query language effectiveness, security protocols for sensitive information, and distributed computing challenges like concurrency control and fault tolerance.

Computer science categorizes DBMS platforms based on the underlying data model they support. Relational models, employing tables (rows and columns) and standardized SQL for manipulation, dominated from the 1980s. The 2000s saw the rise of NoSQL systems, which utilize alternative data representations and query mechanisms to address scalability and flexibility limitations inherent in rigid relational structures.

Formal Definitions and Interrelation

Strictly defined, a 'database' is a collection of interconnected data accessed via a 'database management system' (DBMS). The DBMS furnishes essential functionalities for entry, storage, retrieval, and structural administration of large data volumes. Due to their symbiotic relationship, the term 'database' is frequently employed in a less formal sense to encompass both the storage structure and the managing software component.