ClickHouse Cloud & On-Prem

A server for managing ClickHouse databases and ClickHouse Cloud services.

MCP ClickHouse: Database Operations + Cloud Management

A comprehensive Model Context Protocol (MCP) server that providestwo distinct capabilities:
- Database Operations- Connect to and query any ClickHouse database (local, cloud, or self-hosted)
- Cloud Management- Complete ClickHouse Cloud infrastructure management via API

👉Complete Setup Tutorial- Transform Claude into a powerful ClickHouse data agent

For experienced users, jump to theQuick Configurationsection below.

- 🚀 Quick Start
- 📚 Table of Contents
- 🎯 Choose Your Use Case
- 🌟 Why This Server?
- ✨ Capabilities Overview
- 🔒 Safety Features
- ⚡ Quick Configuration
- 📦 Installation
- ⚙️ Configuration Guide
- 🛠️ Available Tools
- 💡 Usage Examples
- 🔧 Development
- 🐛 Troubleshooting
- 📄 License

This repository significantly improves over theoriginal ClickHouse MCP server:

[!WARNING]Security Notice:The original ClickHouse MCP server (v0.1.10) has a critical security flaw whererun_select_querycan execute ANY SQL operation including DROP, DELETE, INSERT, etc., despite its name suggesting it only runs SELECT queries. This server implements proper query filtering and safety controls.

Connect to and query any ClickHouse database:

- List databases and tableswith detailed metadata
- Execute SELECT querieswith safety guarantees (read-only mode)
- Explore schemasincluding column types, row counts, and table structures
- Works with:Local ClickHouse, self-hosted instances, ClickHouse Cloud databases, and the free SQL Playground

Complete ClickHouse Cloud API integration:

- Organizations(5 tools): Manage settings, metrics, private endpoints
- Services(12 tools): Create, scale, start/stop, configure, delete cloud services
- API Keys(5 tools): Full CRUD operations for programmatic access
- Members & Invitations(8 tools): User management and access control
- Backups(4 tools): Configure and manage automated backups
- ClickPipes(7 tools): Data ingestion pipeline management
- Monitoring(3 tools): Usage analytics, costs, and audit logs
- Network(6 tools): Private endpoints and security configuration

This MCP server includes comprehensive safety controls to prevent accidental data modification or infrastructure changes:

- Automatic Read-Only Mode: All database queries run withreadonly = 1by default
- Query Filtering: Only SELECT, SHOW, DESCRIBE, and EXPLAIN queries are allowed
- Manual Override: SetCLICKHOUSE_READONLY=falseto enable write operations when needed

- Protected Operations: Destructive cloud operations (delete, stop) can be enabled
- Safe Mode: SetCLICKHOUSE_CLOUD_READONLY=falseto allow infrastructure changes
- Audit Trail: All operations are logged for accountability

- Minimal Privileges: Create dedicated users with limited permissions
- SSL by Default: Secure connections enabled automatically
- Environment Variables: Sensitive credentials never hardcoded
- Timeout Controls: Prevent runaway queries and operations
-

Open your Claude Desktop configuration file:

- macOS:~/Library/Application Support/Claude/claude_desktop_config.json
- Windows:%APPDATA%/Claude/claude_desktop_config.json

Choose your configuration based on your use case:

{ "mcpServers": { "chmcp": { "command": "/path/to/uv", "args": ["run", "--with", "chmcp", "--python", "3.13", "chmcp"], "env": { "CLICKHOUSE_HOST": "your-server.com", "CLICKHOUSE_PORT": "8443", "CLICKHOUSE_USER": "your-username", "CLICKHOUSE_PASSWORD": "your-password", "CLICKHOUSE_SECURE": "true", "CLICKHOUSE_READONLY": "true" } } } }

{ "mcpServers": { "chmcp": { "command": "/path/to/uv", "args": ["run", "--with", "chmcp", "--python", "3.13", "chmcp"], "env": { "CLICKHOUSE_HOST": "your-instance.clickhouse.cloud", "CLICKHOUSE_USER": "default", "CLICKHOUSE_PASSWORD": "your-database-password", "CLICKHOUSE_SECURE": "true", "CLICKHOUSE_READONLY": "true" } } } }

{ "mcpServers": { "chmcp": { "command": "/path/to/uv", "args": ["run", "--with", "chmcp", "--python", "3.13", "chmcp"], "env": { "CLICKHOUSE_HOST": "sql-clickhouse.clickhouse.com", "CLICKHOUSE_PORT": "8443", "CLICKHOUSE_USER": "demo", "CLICKHOUSE_PASSWORD": "", "CLICKHOUSE_SECURE": "true", "CLICKHOUSE_READONLY": "true" } } } }

{ "mcpServers": { "chmcp": { "command": "/path/to/uv", "args": ["run", "--with", "chmcp", "--python", "3.13", "chmcp"], "env": { "CLICKHOUSE_CLOUD_KEY_ID": "your-cloud-key-id", "CLICKHOUSE_CLOUD_KEY_SECRET": "your-cloud-key-secret" } } } }

Note:CLICKHOUSE_CLOUD_READONLYdefaults totrue(monitoring-only mode). Add"CLICKHOUSE_CLOUD_READONLY": "false"for full access.

{ "mcpServers": { "chmcp": { "command": "/path/to/uv", "args": ["run", "--with", "chmcp", "--python", "3.13", "chmcp"], "env": { "CLICKHOUSE_HOST": "your-instance.clickhouse.cloud", "CLICKHOUSE_USER": "default", "CLICKHOUSE_PASSWORD": "your-database-password", "CLICKHOUSE_SECURE": "true", "CLICKHOUSE_READONLY": "true", "CLICKHOUSE_CLOUD_KEY_ID": "your-cloud-key-id", "CLICKHOUSE_CLOUD_KEY_SECRET": "your-cloud-key-secret" } } } }

Note:This enables database analysis (readonly) + full cloud management. Add"CLICKHOUSE_CLOUD_READONLY": "true"for monitoring-only mode.
-

Important:Replace/path/to/uvwith the absolute path to youruvexecutable (find it withwhich uvon macOS/Linux)

Restart Claude Desktopto apply the changes

# Install via uv (used by Claude Desktop) uv add chmcp

# Clone the repository git clone https://github.com/oualib/chmcp.git cd chmcp # Install core dependencies pip install . # Install with development dependencies pip install ".[dev]" # Install with test dependencies pip install ".[test]" # Install with documentation dependencies pip install ".[docs]" # Install with all optional dependencies pip install ".[dev,test,docs]" # Set up environment variables cp .env.example .env # Edit .env with your configuration

Set these environment variables to enable database operations:

CLICKHOUSE_HOST=your-clickhouse-host.com # ClickHouse server hostname CLICKHOUSE_USER=your-username # Username for authentication CLICKHOUSE_PASSWORD=your-password # Password for authentication

CLICKHOUSE_READONLY=true # Enable read-only mode (recommended) # true: Only SELECT/SHOW/DESCRIBE queries allowed # false: All SQL operations permitted

CLICKHOUSE_PORT=8443 # 8443 for HTTPS, 8123 for HTTP CLICKHOUSE_SECURE=true # Enable HTTPS connection CLICKHOUSE_VERIFY=true # Verify SSL certificates CLICKHOUSE_CONNECT_TIMEOUT=30 # Connection timeout in seconds CLICKHOUSE_SEND_RECEIVE_TIMEOUT=300 # Query timeout in seconds CLICKHOUSE_DATABASE=default # Default database to use

[!CAUTION]Security Best Practice:Always useCLICKHOUSE_READONLY=truein production environments. Create a dedicated database user with minimal privileges for MCP connections. Avoid using administrative accounts.

Set these environment variables to enable cloud management:

CLICKHOUSE_CLOUD_KEY_ID=your-cloud-key-id # From ClickHouse Cloud Console CLICKHOUSE_CLOUD_KEY_SECRET=your-cloud-key-secret # From ClickHouse Cloud Console

CLICKHOUSE_CLOUD_READONLY=false # Cloud operation mode (default: false) # true: Only read operations (list, get, metrics) # false: All cloud operations permitted (create, update, delete)

CLICKHOUSE_CLOUD_API_URL=https://api.clickhouse.cloud # API endpoint CLICKHOUSE_CLOUD_TIMEOUT=30 # Request timeout CLICKHOUSE_CLOUD_SSL_VERIFY=true # SSL verification

[!WARNING]Cloud Safety:By default,CLICKHOUSE_CLOUD_READONLY=falseallows all infrastructure operations. Set totruein production to prevent accidental infrastructure changes. When disabled, Claude can create, modify, and delete cloud services, which may incur costs or cause service disruptions.
- Log intoClickHouse Cloud Console
- Navigate toSettings→API Keys
- ClickCreate API Key
- Select appropriate permissions:

# Database - read-only queries only CLICKHOUSE_HOST=your-instance.clickhouse.cloud CLICKHOUSE_USER=readonly_user CLICKHOUSE_PASSWORD=secure-password CLICKHOUSE_SECURE=true CLICKHOUSE_READONLY=true # Cloud - monitoring and inspection only (explicitly set to true) CLICKHOUSE_CLOUD_KEY_ID=your-cloud-key-id CLICKHOUSE_CLOUD_KEY_SECRET=your-cloud-key-secret CLICKHOUSE_CLOUD_READONLY=true

# Database - all operations allowed CLICKHOUSE_HOST=localhost CLICKHOUSE_USER=default CLICKHOUSE_PASSWORD=clickhouse CLICKHOUSE_SECURE=false CLICKHOUSE_READONLY=false # Cloud - full infrastructure management CLICKHOUSE_CLOUD_KEY_ID=dev-key-id CLICKHOUSE_CLOUD_KEY_SECRET=dev-key-secret CLICKHOUSE_CLOUD_READONLY=false

# Database - read-only for data analysis CLICKHOUSE_HOST=analytics.company.com CLICKHOUSE_USER=analyst CLICKHOUSE_PASSWORD=analyst-password CLICKHOUSE_SECURE=true CLICKHOUSE_READONLY=true # Cloud - monitoring only, no infrastructure changes CLICKHOUSE_CLOUD_KEY_ID=monitoring-key-id CLICKHOUSE_CLOUD_KEY_SECRET=monitoring-key-secret CLICKHOUSE_CLOUD_READONLY=true

# Database only - full access for development CLICKHOUSE_HOST=localhost CLICKHOUSE_USER=default CLICKHOUSE_PASSWORD=clickhouse CLICKHOUSE_SECURE=false CLICKHOUSE_PORT=8123 CLICKHOUSE_READONLY=false

# Database connection - read-only CLICKHOUSE_HOST=your-instance.clickhouse.cloud CLICKHOUSE_USER=default CLICKHOUSE_PASSWORD=your-database-password CLICKHOUSE_SECURE=true CLICKHOUSE_READONLY=true # Cloud management - monitoring only (explicitly set to true) CLICKHOUSE_CLOUD_KEY_ID=your-cloud-key-id CLICKHOUSE_CLOUD_KEY_SECRET=your-cloud-key-secret CLICKHOUSE_CLOUD_READONLY=true

# Disable SSL verification for database CLICKHOUSE_VERIFY=false CLICKHOUSE_SECURE=false # Use HTTP instead of HTTPS CLICKHOUSE_PORT=8123 # HTTP port instead of 8443 # Disable SSL verification for cloud API CLICKHOUSE_CLOUD_SSL_VERIFY=false

[!NOTE]Query Safety:WhenCLICKHOUSE_READONLY=true, all queries automatically run withreadonly = 1setting and are filtered to prevent data modification operations.

These tools work with ClickHouse Cloud when API credentials are provided. Tool availability depends on theCLICKHOUSE_CLOUD_READONLYsetting:

🔍 Read-Only Operations (Available whenCLICKHOUSE_CLOUD_READONLY=true)

- cloud_list_organizations()- List available organizations
- cloud_get_organization(organization_id)- Get organization details
- cloud_get_organization_metrics(organization_id, filtered_metrics?)- Get Prometheus metrics

- cloud_list_services(organization_id)- List all services in organization
- cloud_get_service(organization_id, service_id)- Get detailed service information
- cloud_get_service_metrics(organization_id, service_id, filtered_metrics?)- Get service performance metrics

- cloud_list_api_keys(organization_id)- List all API keys (metadata only)
- cloud_get_api_key(organization_id, key_id)- Get API key details
- cloud_list_members(organization_id)- List organization members
- cloud_get_member(organization_id, user_id)- Get member details
- cloud_list_invitations(organization_id)- List pending invitations
- cloud_get_invitation(organization_id, invitation_id)- Get invitation details
- cloud_list_backups(organization_id, service_id)- List service backups
- cloud_get_backup(organization_id, service_id, backup_id)- Get backup details

- cloud_get_backup_configuration(organization_id, service_id)- Get backup configuration
- cloud_get_private_endpoint_config(organization_id, service_id)- Get private endpoint configuration
- cloud_list_clickpipes(organization_id, service_id)- List ClickPipes
- cloud_get_clickpipe(organization_id, service_id, clickpipe_id)- Get ClickPipe details
- cloud_get_available_regions()- Get supported regions

- cloud_list_activities(organization_id, from_date?, to_date?)- Get audit logs
- cloud_get_activity(organization_id, activity_id)- Get activity details
- cloud_get_usage_cost(organization_id, from_date, to_date)- Get usage analytics

⚠️ Write Operations (Available only whenCLICKHOUSE_CLOUD_READONLY=false)

- cloud_update_organization(organization_id, name?, private_endpoints?)- Update organization settings
- cloud_get_organization_private_endpoint_info(organization_id, cloud_provider, region)- Get private endpoint info

- cloud_create_service(organization_id, name, provider, region, ...)- Create new service
- cloud_update_service(organization_id, service_id, ...)- Update service settings
- cloud_update_service_state(organization_id, service_id, command)- Start/stop services
- cloud_update_service_scaling(organization_id, service_id, ...)- Configure scaling (legacy)
- cloud_update_service_replica_scaling(organization_id, service_id, ...)- Configure replica scaling
- cloud_update_service_password(organization_id, service_id, ...)- Update service password
- cloud_create_service_private_endpoint(organization_id, service_id, id, description)- Create private endpoint
- cloud_delete_service(organization_id, service_id)- Delete service

- cloud_create_api_key(organization_id, name, roles, ...)- Create new API key
- cloud_update_api_key(organization_id, key_id, ...)- Update API key properties
- cloud_delete_api_key(organization_id, key_id)- Delete API key

- cloud_update_member_role(organization_id, user_id, role)- Update member role
- cloud_remove_member(organization_id, user_id)- Remove member
- cloud_create_invitation(organization_id, email, role)- Send invitation
- cloud_delete_invitation(organization_id, invitation_id)- Cancel invitation

- cloud_update_backup_configuration(organization_id, service_id, ...)- Update backup settings
- cloud_create_clickpipe(organization_id, service_id, name, description, source, destination, field_mappings?)- Create ClickPipe
- cloud_update_clickpipe(organization_id, service_id, clickpipe_id, ...)- Update ClickPipe
- cloud_update_clickpipe_scaling(organization_id, service_id, clickpipe_id, replicas?)- Scale ClickPipe
- cloud_update_clickpipe_state(organization_id, service_id, clickpipe_id, command)- Control ClickPipe state
- cloud_delete_clickpipe(organization_id, service_id, clickpipe_id)- Delete ClickPipe
- cloud_list_reverse_private_endpoints(organization_id, service_id)- List reverse private endpoints
- cloud_create_reverse_private_endpoint(organization_id, service_id, ...)- Create reverse private endpoint
- cloud_get_reverse_private_endpoint(organization_id, service_id, reverse_private_endpoint_id)- Get details
- cloud_delete_reverse_private_endpoint(organization_id, service_id, reverse_private_endpoint_id)- Delete endpoint
- cloud_create_query_endpoint_config(organization_id, service_id, roles, open_api_keys, allowed_origins)- Create query config
- cloud_delete_query_endpoint_config(organization_id, service_id)- Delete query config

[!CAUTION]Production Warning:Write operations can create billable resources, modify running services, or delete infrastructure. Always useCLICKHOUSE_CLOUD_READONLY=truein production unless infrastructure changes are specifically required.

# With CLICKHOUSE_READONLY=true (recommended for production) # Only analytical queries are allowed # Explore database structure databases = list_databases() print(f"Available databases: {[db['name'] for db in databases]}") # Get detailed table information tables = list_tables("my_database") for table in tables: print(f"Table: {table['name']}, Rows: {table['total_rows']}") # Execute analytical queries safely result = run_query(""" SELECT date_trunc('day', timestamp) as day, count(*) as events, avg(value) as avg_value FROM my_table WHERE timestamp >= '2024-01-01' GROUP BY day ORDER BY day """) # These queries would be blocked in readonly mode: # run_query("DROP TABLE my_table") # ❌ Blocked # run_query("INSERT INTO my_table VALUES (1)") # ❌ Blocked # run_query("UPDATE my_table SET value = 0") # ❌ Blocked

# With CLICKHOUSE_READONLY=false (development only) # All SQL operations are allowed # Data modification operations run_query(""" CREATE TABLE test_table ( id UInt32, name String, created_at DateTime ) ENGINE = MergeTree() ORDER BY id """) run_query("INSERT INTO test_table VALUES (1, 'test', now())") run_query("UPDATE test_table SET name = 'updated' WHERE id = 1")

# With CLICKHOUSE_CLOUD_READONLY=true (recommended for production) # Only monitoring and inspection operations # Monitor organization resources orgs = cloud_list_organizations() for org in orgs: services = cloud_list_services(org['id']) print(f"Organization: {org['name']}, Services: {len(services)}") # Get service metrics for service in services: metrics = cloud_get_service_metrics(org['id'], service['id']) print(f"Service {service['name']} metrics: {metrics}") # Monitor costs and usage usage = cloud_get_usage_cost( organization_id="org-123", from_date="2024-01-01", to_date="2024-01-31" ) print(f"Monthly cost: ${usage['total_cost']}") # Audit recent activities activities = cloud_list_activities( organization_id="org-123", from_date="2024-01-01T00:00:00Z" ) print(f"Recent activities: {len(activities)} events") # These operations would be blocked in readonly mode: # cloud_create_service(...) # ❌ Blocked # cloud_delete_service(...) # ❌ Blocked # cloud_update_service_state(...) # ❌ Blocked

# With CLICKHOUSE_CLOUD_READONLY=false (use with caution) # All infrastructure operations allowed # Create a production service with full configuration service = cloud_create_service( organization_id="org-123", name="analytics-prod", provider="aws", region="us-east-1", tier="production", min_replica_memory_gb=32, max_replica_memory_gb=256, num_replicas=3, idle_scaling=True, idle_timeout_minutes=10, ip_access_list=[ {"source": "10.0.0.0/8", "description": "Internal network"}, {"source": "203.0.113.0/24", "description": "Office network"} ] ) # Start the service and monitor status cloud_update_service_state( organization_id="org-123", service_id=service['id'], command="start" ) # Set up automated backups cloud_update_backup_configuration( organization_id="org-123", service_id=service['id'], backup_period_in_hours=24, backup_retention_period_in_hours=168, # 7 days backup_start_time="02:00" )

# Production-safe configuration for monitoring and analysis # CLICKHOUSE_READONLY=true + CLICKHOUSE_CLOUD_READONLY=true # 1. Monitor existing cloud infrastructure orgs = cloud_list_organizations() org_id = orgs[0]['id'] services = cloud_list_services(org_id) active_services = [s for s in services if s['state'] == 'running'] print(f"Active services: {len(active_services)}") # 2. Analyze data from running services for service in active_services: # Check service health metrics = cloud_get_service_metrics(org_id, service['id']) # Analyze data (read-only queries) if service['endpoints']: # Connect to database (would use service endpoint) result = run_query(""" SELECT database, table, sum(rows) as total_rows, sum(bytes_on_disk) as disk_usage FROM system.parts WHERE active = 1 GROUP BY database, table ORDER BY total_rows DESC LIMIT 10 """) print(f"Top tables in {service['name']}: {result}") # 3. Generate usage report usage = cloud_get_usage_cost( organization_id=org_id, from_date="2024-01-01", to_date="2024-01-31" ) activities = cloud_list_activities(org_id) recent_changes = [a for a in activities if 'create' in a.get('action', '').lower()] print(f""" Monthly Report: - Total Cost: ${usage.get('total_cost', 'N/A')} - Active Services: {len(active_services)} - Recent Infrastructure Changes: {len(recent_changes)} """)

cat > .env << EOF # Database configuration (development mode) CLICKHOUSE_HOST=localhost CLICKHOUSE_PORT=8123 CLICKHOUSE_USER=default CLICKHOUSE_PASSWORD=clickhouse CLICKHOUSE_SECURE=false CLICKHOUSE_READONLY=false # Cloud configuration (optional, safe mode) CLICKHOUSE_CLOUD_KEY_ID=your-key-id CLICKHOUSE_CLOUD_KEY_SECRET=your-key-secret CLICKHOUSE_CLOUD_READONLY=true EOF