ClickHouse MCP Server Project Details

Overview

The ClickHouse MCP Server is a Model Context Protocol (MCP) server implementation developed by ClickHouse officially. It is specifically designed to provide AI assistants (such as Claude) with secure connectivity and interaction capabilities with ClickHouse databases. This project, through the standardized MCP protocol, enables AI assistants to execute SQL queries, manage database structures, and perform real-time data analysis.

MCP (Model Context Protocol) is an open standard aimed at providing AI applications with a secure and standardized way to integrate with external services. Through this server, users can allow AI assistants to directly access their ClickHouse databases, enabling intelligent data querying and analysis.

Core Features

1. SQL Query Execution (run_select_query)

• Description: Executes SQL queries on the ClickHouse cluster.
• Security Mechanism: All queries are run in readonly = 1 mode to ensure data security.
• Input Parameters:
  • sql (string): The SQL query statement to execute.
• Use Cases: Data retrieval, statistical analysis, report generation.

2. Database Management (list_databases)

• Description: Lists all databases in the ClickHouse cluster.
• Purpose: Database structure exploration, permission verification.
• Return Content: A list of accessible databases.

3. Table Management (list_tables)

• Description: Lists all tables in the specified database.
• Input Parameters:
  • database (string): The database name.
• Purpose: Table structure exploration, data model understanding.

Configuration and Deployment

Claude Desktop Integration

macOS Configuration Path

~/Library/Application Support/Claude/claude_desktop_config.json

Windows Configuration Path

%APPDATA%/Claude/claude_desktop_config.json

Basic Configuration Example

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

Environment Variable Configuration

Required Variables

• CLICKHOUSE_HOST: ClickHouse server hostname.
• CLICKHOUSE_USER: Authentication username.
• CLICKHOUSE_PASSWORD: Authentication password.

Optional Variables

• CLICKHOUSE_PORT: Port number.
  • Default: 8443 when HTTPS is enabled, 8123 when disabled.
• CLICKHOUSE_SECURE: Enable/disable HTTPS connection.
  • Default: "true"
• CLICKHOUSE_VERIFY: Enable/disable SSL certificate verification.
  • Default: "true"
• CLICKHOUSE_CONNECT_TIMEOUT: Connection timeout (seconds).
  • Default: "30"
• CLICKHOUSE_SEND_RECEIVE_TIMEOUT: Send/receive timeout (seconds).
  • Default: "300"
• CLICKHOUSE_DATABASE: Default database to connect to.
  • Default: None (uses the server default).

Use Cases

1. ClickHouse SQL Playground Configuration

{
  "env": {
    "CLICKHOUSE_HOST": "sql-clickhouse.clickhouse.com",
    "CLICKHOUSE_PORT": "8443",
    "CLICKHOUSE_USER": "demo",
    "CLICKHOUSE_PASSWORD": "",
    "CLICKHOUSE_SECURE": "true",
    "CLICKHOUSE_VERIFY": "true"
  }
}

2. Local Development Environment Configuration

# .env file configuration
CLICKHOUSE_HOST=localhost
CLICKHOUSE_USER=default
CLICKHOUSE_PASSWORD=clickhouse
CLICKHOUSE_SECURE=false
CLICKHOUSE_VERIFY=false

3. ClickHouse Cloud Configuration

CLICKHOUSE_HOST=your-instance.clickhouse.cloud
CLICKHOUSE_USER=default
CLICKHOUSE_PASSWORD=your-password
# Use secure default settings

Development and Testing

Environment Setup

# Install dependencies
uv sync

# Activate virtual environment
source .venv/bin/activate

# Start development server
mcp dev mcp_clickhouse/mcp_server.py

Testing Process

# Install development dependencies
uv sync --all-extras --dev

# Code check
uv run ruff check .

# Start test services
docker compose up -d test_services

# Run tests
uv run pytest tests

Security Considerations

Database Permission Management

• Principle of Least Privilege: MCP database users should only have the necessary minimum permissions.
• Avoid Administrator Accounts: Strictly prohibit the use of default or administrator users.
• Read-Only Mode: All queries are executed in read-only mode to prevent data modification.

Network Security

• HTTPS Connection: It is recommended to enable HTTPS in production environments.
• Certificate Verification: Enable SSL certificate verification to ensure connection security.
• Timeout Settings: Set reasonable connection and query timeouts to prevent resource occupation.

Technical Features

Compatibility

• Python Version: Supports Python 3.13.
• Dependency Management: Uses uv for package management.
• Containerization: Supports Docker deployment.
• Cross-Platform: Supports macOS, Windows, Linux.

Performance Optimization

• Connection Pool: Efficient database connection management.
• Timeout Control: Configurable connection and query timeouts.
• Asynchronous Support: Supports asynchronous query execution.

Summary

The ClickHouse MCP Server project builds a secure and efficient bridge between AI assistants and ClickHouse databases. Through the standardized MCP protocol, it enables AI assistants to understand and operate complex database structures, execute sophisticated SQL queries, and provide real-time data insights.

Key Advantages

1. Official Support: Maintained by ClickHouse officially, ensuring compatibility and stability.
2. Secure Design: Built-in read-only mode and permission control to protect data security.
3. Easy Integration: Standardized MCP protocol simplifies AI application integration.
4. Flexible Configuration: Supports multiple deployment scenarios, from local development to cloud production.
5. Developer-Friendly: Complete development toolchain and testing framework.

Applicable Scenarios

• Data Analysis: Enable AI assistants to assist with complex data analysis tasks.
• Business Intelligence: Automate report generation and data insights.
• Real-Time Monitoring: Combine AI for system and business metric monitoring.
• Data Exploration: Explore database structure and content through natural language queries.

This project represents a new direction in AI and database integration, providing developers with a powerful and secure tool that allows AI assistants to truly understand and operate enterprise-grade data resources.