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

This document details the mcp-clickhouse server, a crucial component for integrating ClickHouse, a high-performance columnar database, with Large Language Model (LLM) applications via the Model Context Protocol (MCP). By leveraging MCP, you can seamlessly connect your LLMs to ClickHouse, enabling them to access, analyze, and utilize vast datasets for enhanced AI-powered applications.

Core Functionalities

The mcp-clickhouse server exposes the following key functionalities as tools accessible through the MCP:

run_select_query: Executes arbitrary SQL SELECT queries against your ClickHouse cluster. This tool is fundamental for retrieving data to inform LLM decision-making. All queries are executed with readonly = 1 to enforce data safety and prevent unintended modifications.
- Input: sql (string): The SQL query to be executed.
- Output: The result set of the SQL query.
list_databases: Retrieves a list of all databases available within your ClickHouse instance. This allows the LLM to dynamically discover available data sources.
- Output: A list of database names.
list_tables: Lists all tables residing within a specified database. This enables the LLM to understand the schema and available datasets within a particular database.
- Input: database (string): The name of the database to inspect.
- Output: A list of table names within the specified database.

Configuration and Deployment

To integrate mcp-clickhouse with your LLM application, you need to configure it within your Claude Desktop environment (or equivalent MCP-compatible environment).

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 your configuration file. Crucially, replace the placeholder values with your actual ClickHouse connection details.

{
  "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"
      }
    }
  }
}

For quick experimentation with the ClickHouse SQL Playground, use the following configuration:

{
  "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 uv command entry in your configuration and replace it with the full path to the uv executable. This ensures that the correct version of uv is used. You can determine the path using the command which uv in your terminal.
Restart Claude Desktop:

Restart your Claude Desktop application to apply the new configuration.

Development Environment Setup

For local development and testing, follow these steps:

Start the ClickHouse Cluster (using Docker):

Navigate to the test-services directory and execute docker compose up -d to launch a ClickHouse cluster within Docker.
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 necessary dependencies. If you don't have uv installed, follow the instructions here. After installation, activate the virtual environment using source .venv/bin/activate.
Run the MCP Server for Testing:

Execute mcp dev mcp_clickhouse/mcp_server.py to start the MCP server in development mode. This allows you to interact with the server and test its functionality.

Environment Variable Reference

The mcp-clickhouse server relies on environment variables for configuring the connection to your ClickHouse instance.

Required Variables

CLICKHOUSE_HOST: The hostname or IP address of your ClickHouse server.
CLICKHOUSE_USER: The username for authenticating with ClickHouse.
CLICKHOUSE_PASSWORD: The password for the specified ClickHouse user.

Optional Variables

CLICKHOUSE_PORT: The port number of your ClickHouse server.
- Default: 8443 if HTTPS is enabled (CLICKHOUSE_SECURE="true"), 8123 if HTTPS is disabled (CLICKHOUSE_SECURE="false").
- Only needs to be set if you are using a non-standard port.
CLICKHOUSE_SECURE: Enables or disables HTTPS for the connection.
- Default: "true" (HTTPS enabled).
- Set to "false" for non-secure connections (not recommended for production).
CLICKHOUSE_VERIFY: Enables or disables SSL certificate verification.
- Default: "true" (certificate verification enabled).
- Set to "false" to disable certificate verification (strongly discouraged for production environments).
CLICKHOUSE_CONNECT_TIMEOUT: The connection timeout in seconds.
- Default: "30" seconds.
- Increase this value if you experience connection timeouts.
CLICKHOUSE_SEND_RECEIVE_TIMEOUT: The send/receive timeout in seconds for data transfer.
- Default: "300" seconds.
- Increase this value for long-running queries to prevent timeouts during data retrieval.
CLICKHOUSE_DATABASE: The default database to use.
- Default: None (uses the server's default database).
- Setting this variable automatically connects to the specified 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)

These environment variables can be set directly in your system environment, within a .env file, or within the env section of the Claude Desktop configuration.

{
  "mcpServers": {
    "mcp-clickhouse": {
      "command": "uv",
      "args": [
        "run",
        "--with",
        "mcp-clickhouse",
        "--python",
        "3.13",
        "mcp-clickhouse"
      ],
      "env": {
        "CLICKHOUSE_HOST": "<clickhouse-host>",
        "CLICKHOUSE_USER": "<clickhouse-user>",
        "CLICKHOUSE_PASSWORD": "<clickhouse-password>",
        "CLICKHOUSE_DATABASE": "<optional-database>"
      }
    }
  }
}