跳到主要内容
跳到主要内容

如何使用 Microsoft Agent Framework 和 ClickHouse MCP Server 构建 AI Agent

在本指南中，你将学习如何使用 Microsoft Agent Framework 构建一个 AI agent，使其能够通过 ClickHouse 的 MCP Server 与 ClickHouse 的 SQL Playground 进行交互。

示例 Notebook

该示例作为 Notebook 提供在 examples 仓库 中。

前提条件​

  • 系统中需已安装 Python。
  • 系统中需已安装 pip。
  • 需要一个 OpenAI API 密钥。

你可以在 Python REPL 中或通过脚本运行以下步骤。

安装依赖库​

通过运行以下命令安装 Microsoft Agent Framework 库：

pip install -q --upgrade pip
pip install -q agent-framework --pre
pip install -q ipywidgets

设置凭据​

接下来，您需要提供 OpenAI API 密钥：

import os, getpass
os.environ["OPENAI_API_KEY"] = getpass.getpass("Enter OpenAI API Key:")

Enter OpenAI API Key: ········

接下来,定义连接到 ClickHouse SQL playground 所需的凭据:

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

初始化 MCP Server 和 Microsoft Agent Framework 代理​

现在配置 ClickHouse MCP Server 指向 ClickHouse SQL playground， 然后初始化代理并向其提问：

from agent_framework import ChatAgent, MCPStdioTool
from agent_framework.openai import OpenAIResponsesClient

clickhouse_mcp_server = MCPStdioTool(
  name="clickhouse",
  command="uv",
  args=[
      "run",
      "--with",
      "mcp-clickhouse",
      "--python",
      "3.10",
      "mcp-clickhouse"
  ],
  env=env
)


async with ChatAgent(
  chat_client=OpenAIResponsesClient(model_id="gpt-5-mini-2025-08-07"),
  name="HousePricesAgent",
  instructions="You are a helpful assistant that can help query a ClickHouse database",
  tools=clickhouse_mcp_server,
) as agent:
  query = "Tell me about UK property prices over the last five years"
  print(f"User: {query}")
  async for chunk in agent.run_stream(query):
      print(chunk.text, end="", flush=True)
  print("\n\n")

运行此脚本的输出如下：

User: Tell me about UK property prices over the last five years
I looked at monthly UK sold-price records in the uk.uk_price_paid_simple_partitioned table for the last five years (toStartOfMonth(date), from Oct 2020 → Aug 2025). Summary and key points:

What I measured
- Metrics: monthly median price, mean price, and transaction count (price paid records).
- Period covered: months starting 2020-10-01 through 2025-08-01 (last five years from today).

High-level findings
- Median price rose from £255,000 (2020-10) to £294,500 (2025-08) — an increase of about +15.4% over five years.
- Equivalent compound annual growth rate (CAGR) for the median ≈ +2.9% per year.
- Mean price fell slightly from about £376,538 (2020-10) to £364,653 (2025-08) — a decline of ≈ −3.2% over five years.
- Mean-price CAGR ≈ −0.6% per year.
- The divergence (median up, mean slightly down) suggests changes in the mix of transactions (fewer very-high-value sales or other compositional effects), since the mean is sensitive to outliers while the median is not.

Notable patterns and events in the data
- Strong rises in 2020–2021 (visible in both median and mean), consistent with the post‑pandemic / stamp‑duty / demand-driven market surge seen in that period.
- Peaks in mean prices around mid‑2022 (mean values ~£440k), then a general softening through 2022–2023 and stabilisation around 2023–2024.
- Some months show large volatility or unusual counts (e.g., June 2021 and June 2021 had very high transaction counts; March 2025 shows a high median but April–May 2025 show lower counts). Recent months (mid‑2025) have much lower transaction counts in the table — this often indicates incomplete reporting for the most recent months and means recent monthly figures should be treated cautiously.

Example datapoints (from the query)
- 2020-10: median £255,000, mean £376,538, transactions 89,125
- 2022-08: mean peak ~£441,209 (median ~£295,000)
- 2025-03: median ~£314,750 (one of the highest medians)
- 2025-08: median £294,500, mean £364,653, transactions 18,815 (low count — likely incomplete)

Caveats
- These are transaction prices (Price Paid dataset) — actual house “values” may differ.
- Mean is sensitive to composition and outliers. Changes in the types of properties sold (e.g., mix of flats vs detached houses, regional mix) will affect mean and median differently.
- Recent months can be incomplete; months with unusually low transaction counts should be treated with caution.
- This is a national aggregate — regional differences can be substantial.

If you want I can:
- Produce a chart of median and mean over time.
- Compare year-on-year or compute CAGR for a different start/end month.
- Break the analysis down by region/county/town, property type (flat, terraced, semi, detached), or by price bands.
- Show a table of top/bottom regions for price growth over the last 5 years.

Which follow-up would you like?