The mcp-clickhouse solution is an MCP server designed to seamlessly connect AI models with ClickHouse, a high-performance columnar database. It empowers AI models with the ability to directly query and retrieve data from ClickHouse clusters using SQL, enabling data-driven insights and decision-making. Key features include the run_select_query tool for executing SQL queries (with read-only protection) and tools for listing databases and tables.

This server eliminates the complexities of manual data extraction and transformation, providing a direct and secure channel for AI models to access real-time data. By configuring the server with your ClickHouse credentials, AI models can leverage the speed and efficiency of ClickHouse for data analysis and feature engineering. The mcp-clickhouse server enhances AI model capabilities by providing a reliable and efficient data access layer, streamlining the development process and accelerating time-to-insight. It integrates via standard environment variables for easy setup and deployment.

Execute SQL Queries Safely

The run_select_query tool allows AI models to execute SQL queries on a ClickHouse cluster. This functionality is crucial for AI models that need to retrieve specific data for analysis, reporting, or decision-making. The tool accepts a SQL query string as input and returns the query results. To ensure security, all queries are executed with the readonly = 1 setting, preventing any data modification operations. This safeguard is vital in preventing unintended data corruption or malicious attacks through the AI model. For example, an AI-powered financial analyst could use this tool to fetch the latest stock prices or transaction data from a ClickHouse database to generate real-time market insights. The implementation involves using the ClickHouse Python client to connect to the database, execute the query, and return the results in a structured format suitable for the AI model.

List Databases and Tables

The list_databases and list_tables tools provide AI models with the ability to discover the schema of a ClickHouse cluster. list_databases retrieves a list of all databases within the cluster, while list_tables lists all tables within a specified database. This is particularly useful for AI models that need to dynamically adapt to different data structures or explore available datasets. For instance, an AI data exploration tool could use these tools to allow users to browse and select specific tables for analysis without needing to know the exact database and table names beforehand. The list_tables tool requires the database name as input. These tools enhance the AI model's ability to interact with and understand the data stored in ClickHouse. The implementation involves using ClickHouse's system tables to query the database schema and return the results as a list of strings.

Secure ClickHouse Connection

The mcp-clickhouse server prioritizes secure connections to ClickHouse instances. It supports both HTTPS and SSL certificate verification, configurable through environment variables. By default, CLICKHOUSE_SECURE is set to "true", enforcing HTTPS connections, and CLICKHOUSE_VERIFY is also set to "true", ensuring that the SSL certificate is verified. This is crucial for protecting sensitive data transmitted between the AI model and the ClickHouse database, especially in production environments. For development or testing against local instances, these settings can be relaxed, but it's strongly recommended to maintain secure connections in production. The server uses the ClickHouse Python client, which leverages these environment variables to establish a secure connection.