mcp-clickhousex
MCP ClickHouse 工具
一个用于 ClickHouse 的只读 Model Context Protocol (MCP) 服务器,支持元数据发现、资源、参数化 SELECT 查询、SHOW 内省、查询分析,以及针对大型结果集的快照模式,并带有基于配置文件的配置和严格的禁止 DML/DDL 执行。
要求: Python 3.13+、正在运行的 ClickHouse 实例,以及通过环境变量或配置文件提供的连接信息。
快速开始
设置 DSN 并使用 MCP Inspector 运行服务器:
# Option 1: Run directly with uvx (no clone needed)
export MCP_CLICKHOUSE_DSN="http://default:@localhost:8123/default"
npx -y @modelcontextprotocol/inspector uvx mcp-clickhousex# Option 2: Run from source (clone repo, then)
export MCP_CLICKHOUSE_DSN="http://default:@localhost:8123/default"
npx -y @modelcontextprotocol/inspector uv run main.pyRelated MCP server: db-connect-mcp
配置
所有设置均使用 MCP_CLICKHOUSE 前缀。扁平环境变量(例如 MCP_CLICKHOUSE_DSN)是在拥有单个连接时配置默认配置文件的直接方式。对于多个配置文件,建议使用用户级 config.json 文件。
单个连接: 通过环境变量进行配置。
# Connection DSN (required).
export MCP_CLICKHOUSE_DSN="http://user:password@host:8123/database"
# Optional description for the default profile (tooling/AI discovery).
export MCP_CLICKHOUSE_DESCRIPTION="Primary cluster"
# Optional max rows per interactive query (default 500; hard ceiling 1000).
export MCP_CLICKHOUSE_QUERY_MAX_ROWS="500"
# Optional interactive query timeout in seconds (default 30; hard ceiling 300).
export MCP_CLICKHOUSE_QUERY_COMMAND_TIMEOUT_SECONDS="30"
# Optional max rows for snapshot queries (default 10000; hard ceiling 50000).
export MCP_CLICKHOUSE_SNAPSHOT_MAX_ROWS="10000"
# Optional snapshot query timeout in seconds (default 120; hard ceiling 300).
export MCP_CLICKHOUSE_SNAPSHOT_COMMAND_TIMEOUT_SECONDS="120"多个连接: 使用用户级 config.json 文件(推荐)。环境变量也可以通过 MCP_CLICKHOUSE_PROFILES_<NAME>_ 前缀(例如 MCP_CLICKHOUSE_PROFILES_WAREHOUSE_DSN)生效。
Unix 类系统:
~/.config/mcp-clickhousex/config.jsonWindows:
%USERPROFILE%\.config\mcp-clickhousex\config.json
示例(config.json):
{
"profiles": {
"default": {
"dsn": "http://default:@localhost:8123/default",
"description": "Primary",
"query_max_rows": 500,
"query_command_timeout_seconds": 60,
"snapshot_max_rows": 10000,
"snapshot_command_timeout_seconds": 120
},
"warehouse": {
"dsn": "http://user:pass@warehouse:8123/analytics",
"description": "Warehouse"
}
}
}凭据中的特殊字符: 如果用户名或密码包含 URL 保留字符,请在 DSN 中对它们进行百分号编码:
字符 | 编码 |
|
|
|
|
|
|
|
|
|
|
例如,用户名 admin@org 和密码 p#ss? 在 DSN 中变为 admin%40org:p%23ss%3F:http://admin%40org:p%23ss%3F@host:8123/database。
工具
工具描述与 server.py 工具文档字符串一致,只是此处省略了 [ClickHouse] 前缀(该前缀仍保留在 MCP 暴露的元数据中)。参数文本与同一工具上的每个 Field(description=…) 一致。
工具 | 描述 | 关键参数 |
| 列出已配置的配置文件。每个条目包含名称和可选描述。 | — |
| 获取集群属性和执行限制。返回 ClickHouse 服务器版本以及该配置文件的强制限制(最大行数、超时)。 |
|
| 执行只读 SELECT 或 WITH … SELECT。每次调用一条语句;拒绝 DML、DDL、SET、SYSTEM 及类似语句。返回 |
|
| 执行 SHOW 内省语句。每次调用一条语句;拒绝 INTO OUTFILE。适用交互式行限制(默认 500,硬上限 1 000)。与 run_query 相同的超时。 |
|
| 解释只读 SELECT 或 WITH … SELECT。返回 plan、pipeline 和/或 syntax 文本。默认类型为 plan 和 pipeline。使用查询超时和可选数据库;与 run_query 不同,没有最大行数上限。 |
|
| 列出数据库。返回连接可见的 system.databases 中的行。 |
|
| 列出数据库中的表和视图。返回 system.tables 中的行:name、engine、primary_key、sorting_key、partition_key、total_rows、total_bytes,用于查询规划。 |
|
| 列出表或视图的列。返回已解析的数据库和表的 system.columns 中的行。 |
|
资源
服务器通过 URI 可寻址资源(profile 优先的层级结构)暴露与上述工具相同的发现和元数据。每个资源的 description 与对应工具(list_profiles、get_cluster_properties、list_databases、list_tables、list_columns)相匹配,并带有用于 URI 路径参数的 Src: 标签。所有资源内容均为 JSON(application/json),但快照除外,快照返回 CSV(text/csv)。对于默认 profile 或数据库,请使用路径段 default。
资源描述与 server.py 中 @mcp.resource 上的 description=… 一致(与上述相同的省略前缀规则)。
URI | 描述 |
| 列出已配置的 profile。每个条目包含名称和可选的描述。 |
| 获取集群属性和执行限制。返回该 profile 的 ClickHouse 服务器版本以及强制限制(最大行数、超时时间)。Src: profiles。 |
| 列出数据库。返回连接可见的 system.databases 中的行。Src: profiles。 |
| 列出数据库中的表和视图。返回 system.tables 中的行:name、engine、primary_key、sorting_key、partition_key、total_rows、total_bytes,用于查询规划。Src: profiles, dbs。 |
| 列出表或视图的列。返回解析后的数据库和表的 system.columns 中的行。Src: profiles, dbs, tables。 |
| 按 ID 获取查询结果快照。以 CSV 字符串(表头行 + 数据行)形式返回完整结果。条目在 7 天后过期。Src: run_query with snapshot=true。 |
安全性
仅允许只读 SQL:run_query 允许 SELECT / WITH … SELECT;run_show 每次调用仅允许一条 SHOW 语句。run_show 不允许使用 INTO OUTFILE。交互式查询强制执行严格的行数上限(默认 500,硬上限 1 000);对于更大的数据提取,请使用 snapshot=true(默认 10 000,硬上限 50 000)。在驱动允许的情况下支持参数化查询(%(name)s 或 {name:Type} 语法)。使用环境变量存储连接凭据——切勿提交密钥。
MCP 主机示例
使用 uvx mcp-clickhousex 的常见 MCP 客户端代码片段(无需克隆;确保 uv 在您的 PATH 中)。根据需要替换连接信息。
Cursor
{
"mcpServers": {
"clickhouse": {
"command": "uvx",
"args": ["mcp-clickhousex"],
"env": {
"MCP_CLICKHOUSE_DSN": "http://default:@localhost:8123/default"
}
}
}
}Codex
[mcp_servers.clickhouse]
command = "uvx"
args = ["mcp-clickhousex"]
[mcp_servers.clickhouse.env]
MCP_CLICKHOUSE_DSN = "http://default:@localhost:8123/default"OpenCode
{
"$schema": "https://opencode.ai/config.json",
"mcp": {
"clickhouse": {
"type": "local",
"enabled": true,
"command": ["uvx", "mcp-clickhousex"],
"environment": {
"MCP_CLICKHOUSE_DSN": "http://default:@localhost:8123/default"
}
}
}
}Claude Code
{
"mcpServers": {
"clickhouse": {
"command": "uvx",
"args": ["mcp-clickhousex"],
"env": {
"MCP_CLICKHOUSE_DSN": "http://default:@localhost:8123/default"
}
}
}
}Copilot
{
"inputs": [],
"servers": {
"clickhouse": {
"type": "stdio",
"command": "uvx",
"args": ["mcp-clickhousex"],
"env": {
"MCP_CLICKHOUSE_DSN": "http://default:@localhost:8123/default"
}
}
}
}配置文件位置: Cursor .cursor/mcp.json,Codex/Copilot/OpenCode 因客户端而异;请参阅您所用客户端的 MCP 文档。
测试
测试需要运行中的 ClickHouse 实例。测试套件会在默认数据库中创建示例表、填充数据,并在测试结束后删除该表。
# Run all tests (unit + functional + e2e)
uv run pytest tests/ -v测试框架使用 MCP_TEST_CLICKHOUSE_DSN 来定位 ClickHouse 实例。如果未设置,则回退到 http://admin:password123@localhost:8123/default。设置该变量可将测试指向其他服务器,而不会影响您的生产环境 MCP_CLICKHOUSE_DSN:
export MCP_TEST_CLICKHOUSE_DSN="http://user:pass@testhost:8123/default"
uv run pytest tests/ -v路线图
目前没有计划中的功能。如有改进建议,请提交 issue。
贡献
欢迎提交 issue 或 PR;请遵循现有风格,并在适当的地方添加测试。
许可证
MIT。参见 LICENSE。
Available Tools
8 toolsanalyze_queryARead-only
[ClickHouse] Explain read-only SELECT or WITH … SELECT.
Returns plan, pipeline, and/or syntax text. Default types plan and pipeline. Uses query timeout and optional database; no max-rows cap unlike run_query.
Inputs: sql (required), types, profile, database, +1 more. Returns: Structured EXPLAIN text output keyed by explain type.
get_cluster_propertiesARead-only
[ClickHouse] Get cluster properties and execution limits.
Returns ClickHouse server version plus enforced limits (max rows, timeouts) for the profile.
Inputs: profile. Returns: ClickHouse cluster metadata safe to surface to MCP clients.
list_columnsARead-only
[ClickHouse] List columns for a table or view.
Rows from system.columns for the resolved database and table.
Inputs: table (required), profile, database. Returns: Generic tabular result with ordered columns and aligned row values.
list_databasesBRead-only
[ClickHouse] List databases.
Rows from system.databases visible to the connection.
Inputs: profile. Returns: Generic tabular result with ordered columns and aligned row values.
list_profilesARead-only
[ClickHouse] List configured profiles.
Each entry includes name and optional description.
Inputs: no named parameters. Returns: result
list_tablesARead-only
[ClickHouse] List tables and views in a database.
Rows from system.tables: name, engine, primary_key, sorting_key, partition_key, total_rows, total_bytes for query planning.
Inputs: profile, database. Returns: Generic tabular result with ordered columns and aligned row values.
run_queryARead-only
[ClickHouse] Execute read-only SELECT or WITH … SELECT.
One statement; DML, DDL, SET, SYSTEM, and similar are rejected. Max-rows cap; overflow sets truncated and row_limit. Same SQL validation as analyze_query.
Returns {data, row_count} where data is an RFC 4180 CSV string.
Pass snapshot=true to persist the result to disk and receive a
{snapshot_uri, row_count} instead; fetch the CSV via the snapshot
resource URI.
Inputs: sql (required), profile, database, snapshot, +1 more. Returns: result
run_showARead-only
[ClickHouse] Execute SHOW introspection statement.
One statement per call; INTO OUTFILE rejected. Interactive row limits apply (default 500, hard ceiling 1 000). Same timeout as run_query.
Inputs: sql (required), profile, database, parameters. Returns: Result of a SHOW introspection statement (columns + rows + optional truncation).
Tool Schema Changelog
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
Loading tool schema changelog…
TDQS
Scored across 8 tools
Each tool has a distinct purpose: listing profiles, cluster properties, running SELECT queries, running SHOW statements, analyzing queries, and listing databases, tables, and columns. No overlap in functionality.
Uses snake_case consistently, but mixes verb prefixes: 'list_', 'get_', 'run_', 'analyze_'. The pattern is somewhat predictable within categories (metadata listing uses 'list_', execution uses 'run_'), but not fully uniform.
8 tools is well-scoped for a read-only ClickHouse client. Covers metadata discovery, query execution, and analysis without unnecessary tools.
Covers essential read-only operations: metadata listing, SELECT, SHOW, and EXPLAIN. Lacks DDL/DML support, but that is intentional. Minor gap: no tool to retrieve table DDL or status.
Maintenance
Related MCP Connectors
MCP server for querying and analyzing data from ad platforms, analytics tools, and spreadsheets
Read-only MCP server for The Quiet Protocol's engines, benchmarks, proof, and business data.
Read-only MCP server for ClassQuill, a tutoring-business-management platform.
Hosted BigQuery MCP server for Claude, ChatGPT and Cursor, with each action switched per account.
Related MCP Servers
- AlicenseAqualityDmaintenanceA DataOps-focused MCP server for ClickHouse that provides query optimization advice, pipeline latency analysis, and data quality monitoring, with read-only safety.832 PyPIMIT
- AlicenseAqualityBmaintenanceA read-only MCP server for exploratory data analysis across PostgreSQL, MySQL, and ClickHouse databases, providing safe, read-only access with comprehensive analysis capabilities.1084 PyPI6MIT
- FlicenseAqualityDmaintenanceRead-only MCP server for ClickHouse that allows listing databases and tables, describing schemas, and running SELECT queries.4-
- AlicenseNot gradedqualityBmaintenanceA read-only-by-default Model Context Protocol (MCP) server for Microsoft SQL Server with schema discovery, SELECT-only queries, execution-plan analysis, and opt-in writes per profile. Profile-based configuration serves multiple databases and servers from one toolset deployment.11MIT