Unleash the Power of ClickHouse with the Model Context Protocol (MCP)

Seamlessly integrate your ClickHouse data warehouse with Large Language Models (LLMs) using the mcp-clickhouse server. This open-source tool bridges the gap between AI applications and your ClickHouse cluster, enabling context-aware AI workflows.

Key Features

The mcp-clickhouse server exposes powerful ClickHouse functionalities to LLMs through the Model Context Protocol:

run_select_query: Execute arbitrary SQL SELECT queries against your ClickHouse data. This allows LLMs to retrieve specific information based on user prompts or internal logic. All queries are executed with readonly = 1 for enhanced security, preventing unintended data modifications.
- Input: sql (string) - The SQL SELECT statement to be executed.
- Use Case: An AI-powered business intelligence tool can use this to answer complex analytical questions posed in natural language.
list_databases: Discover the available databases within your ClickHouse instance. This enables dynamic exploration of the data landscape.
- Use Case: An LLM-powered data catalog can use this to provide users with a list of available data sources.
list_tables: Retrieve a list of tables within a specified database. This allows LLMs to understand the structure and content of individual databases.
- Input: database (string) - The name of the database to list tables from.
- Use Case: An AI-driven data exploration tool can use this to suggest relevant tables based on the user's query.

Configuration: Connecting Your LLM to ClickHouse

To integrate mcp-clickhouse with your LLM application (e.g., Claude Desktop), you need to configure the MCP server connection. Follow these steps:

Locate the Claude Desktop Configuration File:
- macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
- Windows: %APPDATA%/Claude/claude_desktop_config.json

Add the mcp-clickhouse Server Configuration: Insert the following JSON snippet into the mcpServers section of the configuration file.

{
  "mcpServers": {
    "mcp-clickhouse": {
      "command": "uv",
      "args": [
        "run",
        "--with",
        "mcp-clickhouse",
        "--python",
        "3.13",
        "mcp-clickhouse"
      ],
      "env": {
        "CLICKHOUSE_HOST": "<clickhouse-host>",
        "CLICKHOUSE_PORT": "<clickhouse-port>",
        "CLICKHOUSE_USER": "<clickhouse-user>",
        "CLICKHOUSE_PASSWORD": "<clickhouse-password>",
        "CLICKHOUSE_SECURE": "true",
        "CLICKHOUSE_VERIFY": "true",
        "CLICKHOUSE_CONNECT_TIMEOUT": "30",
        "CLICKHOUSE_SEND_RECEIVE_TIMEOUT": "30"
      }
    }
  }
}

Configure ClickHouse Connection Details: Replace the placeholder values in the env section with your ClickHouse connection parameters. See the "Environment Variables" section below for detailed explanations.

Example: Connecting to the ClickHouse SQL Playground:

{
  "mcpServers": {
    "mcp-clickhouse": {
      "command": "uv",
      "args": [
        "run",
        "--with",
        "mcp-clickhouse",
        "--python",
        "3.13",
        "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"
      }
    }
  }
}

Specify the Absolute Path to the uv Executable: Locate the command entry for uv and replace it with the full path to the uv executable. This ensures the correct version of uv is used. You can find the path using the which uv command in your terminal.
Restart Claude Desktop: Restart the application to apply the configuration changes.

Development Setup

To contribute to the mcp-clickhouse project or run it locally for testing:

Start the ClickHouse Cluster (using Docker): Navigate to the test-services directory and execute docker compose up -d to launch a ClickHouse instance.
Configure Environment Variables: Create a .env file in the root directory of the repository and add the following variables, adjusting them to match your local ClickHouse setup:
```
CLICKHOUSE_HOST=localhost
CLICKHOUSE_PORT=8123
CLICKHOUSE_USER=default
CLICKHOUSE_PASSWORD=clickhouse
```
Install Dependencies: Run uv sync to install the required Python packages. If you don't have uv installed, follow the instructions here. Then, activate the virtual environment: source .venv/bin/activate.
Run the MCP Server for Development: Use the command mcp dev mcp_clickhouse/mcp_server.py to start the server in development mode.

Environment Variables: Fine-Tuning Your ClickHouse Connection

The following environment variables control the connection to your ClickHouse server. Understanding these variables is crucial for a successful integration.

Required Variables

CLICKHOUSE_HOST: The hostname or IP address of your ClickHouse server. This is the network address where your ClickHouse instance is accessible.
CLICKHOUSE_USER: The username used for authenticating with the ClickHouse server. Ensure this user has the necessary permissions to access the desired databases and tables.
CLICKHOUSE_PASSWORD: The password associated with the specified CLICKHOUSE_USER.

Optional Variables

CLICKHOUSE_PORT: The port number on which your ClickHouse server is listening for connections.
- Default: 8443 (if CLICKHOUSE_SECURE is set to "true"), 8123 (if CLICKHOUSE_SECURE is set to "false").
- Note: You typically only need to set this if your ClickHouse server is using a non-standard port.
CLICKHOUSE_SECURE: Enables or disables HTTPS for the connection to ClickHouse.
- Default: "true" (enables HTTPS).
- Set to "false" for non-secure connections (typically used for local development).
CLICKHOUSE_VERIFY: Enables or disables SSL certificate verification.
- Default: "true" (enables certificate verification).
- Set to "false" to disable certificate verification. Warning: Disabling certificate verification is not recommended for production environments as it can expose your data to security risks.
CLICKHOUSE_CONNECT_TIMEOUT: The maximum time (in seconds) to wait for a connection to be established with the ClickHouse server.
- Default: "30" seconds.
- Increase this value if you are experiencing connection timeouts, especially when connecting to a remote server over a slow network.
CLICKHOUSE_SEND_RECEIVE_TIMEOUT: The maximum time (in seconds) to wait for data to be sent to or received from the ClickHouse server. This applies to individual queries.
- Default: "300" seconds.
- Increase this value if you are running long-running queries that may exceed the default timeout.
CLICKHOUSE_DATABASE: The default database to use when connecting to the ClickHouse server.
- Default: None (uses the server's default database).
- Set this value to automatically connect to a specific database upon connection.

Example Configurations

Local Development with Docker:

# Required variables
CLICKHOUSE_HOST=localhost
CLICKHOUSE_USER=default
CLICKHOUSE_PASSWORD=clickhouse

# Optional: Override defaults for local development
CLICKHOUSE_SECURE=false  # Uses port 8123 automatically
CLICKHOUSE_VERIFY=false

ClickHouse Cloud:

# Required variables
CLICKHOUSE_HOST=your-instance.clickhouse.cloud
CLICKHOUSE_USER=default
CLICKHOUSE_PASSWORD=your-password

# Optional: These use secure defaults
# CLICKHOUSE_SECURE=true  # Uses port 8443 automatically
# CLICKHOUSE_DATABASE=your_database

ClickHouse SQL Playground:

CLICKHOUSE_HOST=sql-clickhouse.clickhouse.com
CLICKHOUSE_USER=demo
CLICKHOUSE_PASSWORD=
# Uses secure defaults (HTTPS on port 8443)

You can set these environment variables in your system environment, within a .env file, or directly within the Claude Desktop configuration file (as shown in the "Configuration" section).