ClickHouse MCP Server

A comprehensive Model Context Protocol (MCP) server for interacting with ClickHouse databases. This server provides agent-friendly tools for database exploration, query execution, schema management, and performance monitoring.

Features

Database Management

• List Databases: Explore all available databases with metadata
• Database Statistics: Get comprehensive stats about database usage
• System Metrics: Monitor ClickHouse performance and activity

Table Operations

• List Tables: Browse tables with row counts and size information
• Search Tables: Find tables using pattern matching across databases
• Get Schema: Retrieve detailed table structure with columns, types, and indexes

Query Execution

• Execute Query: Run SELECT, INSERT, UPDATE, DELETE, and DDL queries
• Parameterized Queries: Secure query execution with parameter binding
• Response Formats: Get results in JSON or Markdown format
• Automatic Pagination: Handle large result sets efficiently

Advanced Features

• Character Limit Management: Automatic truncation of large responses
• Error Handling: Helpful error messages for common issues
• Connection Pooling: Efficient resource management
• Multiple Response Formats: JSON for machines, Markdown for humans

Installation

Prerequisites

• Python 3.10 or higher
• ClickHouse server (local or cloud, version 20.0+, tested with 25.7+)
• MCP-compatible client (Claude Desktop, Cursor, etc.)

Quick Start with Virtual Environment (Recommended)

# Clone the repository
git clone https://github.com/akworob/clickhouse-mcp-server.git
cd clickhouse-mcp-server

# Create and activate virtual environment
python -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate

# Install dependencies
pip install -r requirements.txt

Install Dependencies (Alternative)

pip install -r requirements.txt

Or install packages individually:

pip install mcp clickhouse-connect httpx pydantic

Configuration

Environment Variables

Configure your ClickHouse connection using environment variables:

# Required
export CLICKHOUSE_HOST="localhost"           # ClickHouse server hostname
export CLICKHOUSE_USER="default"            # Username
export CLICKHOUSE_PASSWORD="your-password"  # Password

# Optional
export CLICKHOUSE_PORT="8443"              # Port (8443 for HTTPS, 9000 for native)
export CLICKHOUSE_DATABASE="default"       # Default database
export CLICKHOUSE_SECURE="true"           # Use secure connection (HTTPS/TLS)

Configuration File (.env)

Create a .env file in the same directory:

CLICKHOUSE_HOST=my-clickhouse.cloud
CLICKHOUSE_PORT=8443
CLICKHOUSE_USER=default
CLICKHOUSE_PASSWORD=SecurePassword123
CLICKHOUSE_DATABASE=analytics
CLICKHOUSE_SECURE=true

Usage

Running the Server

Start the MCP server:

python clickhouse_mcp.py

Integrating with Claude Desktop

Add to your Claude Desktop configuration (claude_desktop_config.json):

Using Virtual Environment (Recommended):

{
  "mcpServers": {
    "clickhouse": {
      "command": "/absolute/path/to/mcp_clickhouse/venv/bin/python",
      "args": ["/absolute/path/to/mcp_clickhouse/clickhouse_mcp.py"],
      "env": {
        "CLICKHOUSE_HOST": "localhost",
        "CLICKHOUSE_PORT": "8123",
        "CLICKHOUSE_USER": "default",
        "CLICKHOUSE_PASSWORD": "your-password",
        "CLICKHOUSE_SECURE": "false"
      }
    }
  }
}

Using System Python:

{
  "mcpServers": {
    "clickhouse": {
      "command": "python",
      "args": ["/absolute/path/to/clickhouse_mcp.py"],
      "env": {
        "CLICKHOUSE_HOST": "localhost",
        "CLICKHOUSE_PORT": "8123",
        "CLICKHOUSE_USER": "default",
        "CLICKHOUSE_PASSWORD": "your-password",
        "CLICKHOUSE_SECURE": "false"
      }
    }
  }
}

Note: Replace /absolute/path/to/ with the actual path to your installation. Use absolute paths for reliability.

Integrating with Cursor

Add to your Cursor MCP settings:

{
  "mcp": {
    "servers": {
      "clickhouse": {
        "command": "python",
        "args": ["clickhouse_mcp.py"],
        "cwd": "/path/to/server",
        "env": {
          "CLICKHOUSE_HOST": "localhost",
          "CLICKHOUSE_USER": "default",
          "CLICKHOUSE_PASSWORD": "your-password"
        }
      }
    }
  }
}

Available Tools

1. clickhouse_list_databases

List all databases with metadata.

Example Request:

List all ClickHouse databases

2. clickhouse_list_tables

List tables in a specific database.

Example Request:

Show all tables in the analytics database

3. clickhouse_get_table_schema

Get detailed schema information for a table.

Example Request:

Get the schema for the events table in analytics database

4. clickhouse_execute_query

Execute SQL queries with optional parameters.

Example Requests:

Run query: SELECT count(*) FROM analytics.events WHERE date >= '2024-01-01'

Execute parameterized query:
SELECT * FROM users WHERE age > {min_age:Int32}
with parameters: {"min_age": 18}

5. clickhouse_get_database_stats

Get comprehensive statistics about a database.

Example Request:

Get statistics for the analytics database

6. clickhouse_search_tables

Search for tables matching a pattern.

Example Request:

Find all tables with "user" in the name

7. clickhouse_get_system_metrics

Get current system metrics and performance indicators.

Example Request:

Show ClickHouse system metrics

Docker Setup for Testing

Create a local ClickHouse instance for testing:

docker-compose.yml

version: '3.8'

services:
  clickhouse:
    image: clickhouse/clickhouse-server:latest
    container_name: clickhouse-mcp
    ports:
      - "8123:8123"  # HTTP interface
      - "9000:9000"  # Native protocol
      - "8443:8443"  # HTTPS interface
    environment:
      CLICKHOUSE_DB: default
      CLICKHOUSE_USER: default
      CLICKHOUSE_PASSWORD: changeme
      CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT: 1
    volumes:
      - clickhouse-data:/var/lib/clickhouse
      - clickhouse-logs:/var/log/clickhouse-server
    ulimits:
      nofile:
        soft: 262144
        hard: 262144

volumes:
  clickhouse-data:
  clickhouse-logs:

Start the container:

docker-compose up -d

Test the connection:

curl 'http://localhost:8123/' --data 'SELECT version()'

Example Workflows

Data Exploration Workflow

1. List all databases:

   Agent: "Show me all databases"
   

2. Explore a specific database:

   Agent: "List tables in the analytics database"
   

3. Examine table structure:

   Agent: "Get schema for the events table in analytics"
   

4. Query data:

   Agent: "Select top 10 rows from analytics.events ordered by timestamp"
   

Data Analysis Workflow

1. Check database statistics:

   Agent: "Get statistics for the production database"
   

2. Find related tables:

   Agent: "Search for tables containing 'user' in any database"
   

3. Run analytical query:

   Agent: "Calculate daily active users for the last 30 days"
   

4. Monitor performance:

   Agent: "Show current system metrics"
   

Security Considerations

1. Authentication: Always use strong passwords and consider using certificate-based authentication for production.

2. Network Security: Use HTTPS/TLS connections (port 8443) for encrypted communication.

3. Query Parameters: Always use parameterized queries to prevent SQL injection:

   # Good - parameterized
   query = "SELECT * FROM users WHERE id = {user_id:Int32}"
   params = {"user_id": 123}
   
   # Bad - string concatenation
   query = f"SELECT * FROM users WHERE id = {user_id}"

4. Access Control: Configure ClickHouse user permissions appropriately:

   • Use read-only users for analytics
   • Restrict access to system tables
   • Implement row-level security where needed

Performance Tips

1. Use LIMIT: Always include LIMIT clauses for exploratory queries
2. Add Indexes: Create appropriate indexes for frequently queried columns
3. Partition Tables: Use partitioning for time-series data
4. Materialized Views: Pre-compute expensive aggregations
5. Connection Pooling: The MCP server maintains a persistent connection

Troubleshooting

Connection Issues

Error: "Failed to initialize ClickHouse connection"

• Check hostname and port are correct
• Verify ClickHouse server is running
• Test with clickhouse-client or curl

Error: "Access denied"

• Verify username and password
• Check user permissions in ClickHouse
• Ensure user has access to specified database

Query Issues

Error: "SQL syntax error"

• Check query syntax matches ClickHouse SQL dialect
• Verify table and column names exist
• Use backticks for reserved words

Error: "Query timeout"

• Add appropriate WHERE clauses to limit data
• Use sampling for large tables
• Consider creating materialized views

MCP Integration Issues

Server not appearing in client:

• Check MCP configuration file syntax
• Verify Python path is correct
• Check server starts without errors: python clickhouse_mcp.py --help

Contributing

Contributions are welcome! Areas for improvement:

1. Additional Tools:

   • Backup and restore operations
   • User management tools
   • Cluster management
   • Query optimization suggestions

2. Enhanced Features:

   • Query result caching
   • Query history tracking
   • Cost estimation for queries
   • Visual query builder

3. Integrations:

   • Support for ClickHouse Cloud API
   • Integration with monitoring tools
   • Export to various formats (CSV, Parquet)

License

MIT License - See LICENSE file for details.

Support

For issues, questions, or suggestions:

1. Check the troubleshooting section
2. Review ClickHouse documentation: https://clickhouse.com/docs
3. Check MCP documentation: https://modelcontextprotocol.io
4. Open an issue on GitHub

Changelog

Version 1.0.0 (2024-10)

• Initial release
• Core database management tools
• Query execution with parameterization
• Multiple response formats
• System metrics monitoring
• Comprehensive error handling