ClickHouse for Microsoft Fabric

​

Overview

• AI agents: give agents governed, read-only SQL access to your data through MCP, from Copilot Studio, Azure AI Foundry, GitHub Copilot, and any MCP-compatible tool
• Interactive analytics: dashboards and data exploration over billions of rows with millisecond query latency
• Customer-facing applications: serve real-time analytical queries at petabyte scale for application consumption

​

Provisioning

1. In your Fabric workspace, select + New item
2. Search for ClickHouse and select it
3. Name the item and select Create

​

ClickHouse Cloud trial

• Trial credits valid for 30 days
• A dedicated service on the ClickHouse Scale tier
• Service idling enabled by default, so an inactive trial service does not consume compute credits while paused

​

Syncing data from OneLake

1. Open your ClickHouse item and select Sync
2. Select Sync from OneLake and pick a Lakehouse from your workspace
3. Select the tables you want to accelerate
4. Select Save and sync to start a point-in-time sync of your data

​

Powered by ClickPipes

1. The workload reads the table directly from OneLake using your authorized credentials
2. A ClickPipe streams the data into a table in your ClickHouse service, mapping types from OneLake to ClickHouse types

​

Cost

​

SQL console and saved queries

​

Saved queries

​

Go further with ClickHouse SQL

​

Connecting your data

• Analytics and BI: Power BI desktop and service, Fabric notebooks with the Python client and more
• Agents and AI: the ClickHouse MCP server for Copilot Studio, Azure AI Foundry, and GitHub Copilot
• Build and interfaces: HTTPS, JDBC/ODBC, and official ClickHouse clients (Python, Node.js, Java, Go, C#, Rust and more)

​

Administration and management

​

Open in ClickHouse Cloud

​

Scaling

​

Idling

• Keep idling on for development and intermittent workloads to minimize cost
• Turn idling off for production dashboards, applications, and agents that need consistent millisecond latency

​

Monitoring the service

​

Monitoring ClickPipes

​

Mapping to ClickHouse Cloud

​

Support

​

Azure Marketplace

• When your trial ends, the upgrade flow in the workload takes you through the marketplace purchase for your organization. Usage is then billed pay-as-you-go through your Azure account. See Azure Marketplace PAYG billing for details.
• Completing a marketplace purchase requires the billing permission of the ClickHouse organization, which workspace administrators hold. An organization administrator can grant it to other users in the Cloud console.

​

Getting started tutorial

​

Load the NYC taxi data

1. Create a Lakehouse in your workspace, or use an existing one
2. Select + New item and create a Notebook
3. Attach the Lakehouse to the notebook. It must be the notebook’s default Lakehouse
4. Paste the two cells below into the notebook and run them in order

import os, requests

YEARS = range(2024, 2025)   # one year (~40M rows). Use range(2013, 2027) for the full ~1.1B rows
BASE = "https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_{y}-{m:02d}.parquet"
DEST = "/lakehouse/default/Files/yellow_tripdata"
os.makedirs(DEST, exist_ok=True)

for y in YEARS:
    for m in range(1, 13):
        url = BASE.format(y=y, m=m)
        out = f"{DEST}/{os.path.basename(url)}"
        if os.path.exists(out):
            continue
        r = requests.get(url, timeout=300)
        if r.status_code == 200:
            open(out, "wb").write(r.content)
            print("saved", out)

from pyspark.sql import functions as F

COLS = {
    "VendorID": "int", "tpep_pickup_datetime": "timestamp", "tpep_dropoff_datetime": "timestamp",
    "passenger_count": "int", "trip_distance": "double", "RatecodeID": "int",
    "store_and_fwd_flag": "string", "PULocationID": "int", "DOLocationID": "int",
    "payment_type": "int", "fare_amount": "double", "extra": "double", "mta_tax": "double",
    "tip_amount": "double", "tolls_amount": "double", "improvement_surcharge": "double",
    "total_amount": "double", "congestion_surcharge": "double", "airport_fee": "double",
}

def normalize(df):
    lower = {c.lower(): c for c in df.columns}
    return df.select([
        (F.col(lower[c.lower()]) if c.lower() in lower else F.lit(None)).cast(t).alias(c)
        for c, t in COLS.items()
    ])

ROOT = next(m.source for m in notebookutils.fs.mounts() if m.mountPoint == "/default") + "/Files/yellow_tripdata"

files = sorted(f for f in os.listdir(DEST) if f.endswith(".parquet"))
for i, name in enumerate(files):
    normalize(spark.read.parquet(f"{ROOT}/{name}")) \
        .write.mode("overwrite" if i == 0 else "append").format("delta").saveAsTable("yellow_tripdata")

print(spark.table("yellow_tripdata").count(), "rows")

​

Create a ClickHouse item and sync

1. Create a ClickHouse item in the same workspace, as described in provisioning
2. Open Sync, pick your Lakehouse, and select the yellow_tripdata table
3. Set the sorting key to PULocationID, tpep_pickup_datetime
4. Start the sync. One year finishes in a few minutes, and the table is queryable as soon as its status shows complete

​

Query the data

-- 1. Row count
SELECT count() FROM yellow_tripdata;

-- 2. Hourly fares: pickup location 186 on June 12, 2024
SELECT
    toHour(tpep_pickup_datetime) AS hr,
    count() AS trip_count,
    round(avg(fare_amount), 2) AS avg_fare
FROM yellow_tripdata
WHERE PULocationID = 186
  AND tpep_pickup_datetime >= '2024-06-12'
  AND tpep_pickup_datetime <  '2024-06-13'
GROUP BY hr
ORDER BY hr;

-- 3. Daily trend: route 132 → 161 during June 2024
SELECT
    toDate(tpep_pickup_datetime) AS d,
    count() AS trip_count,
    round(avg(total_amount), 2) AS avg_total
FROM yellow_tripdata
WHERE PULocationID = 132
  AND DOLocationID = 161
  AND tpep_pickup_datetime >= '2024-06-01'
  AND tpep_pickup_datetime <  '2024-07-01'
GROUP BY d
ORDER BY d;

​

Connect to MCP

1. On the Connect screen, enable MCP for the service
2. Register the ClickHouse MCP server with the MCP client of your choice, as described in getting started with the remote MCP server
3. Ask: “Which pickup zone had the most trips in June 2024, and what was the average fare?”

​

Known limitations

​

Data movement

• Copies are point-in-time snapshots. There is no scheduled or continuous replication yet. To refresh a table with the latest OneLake data, re-run the sync. Updates and deletes in the source table are not propagated to the ClickHouse copy.
• Lakehouse tables only. Sync reads tables from a Lakehouse in the same workspace.
• No write-back to OneLake. Data flows one way, from OneLake into ClickHouse. Writing query results back to OneLake is planned.

​

Service management

• One service per item, fixed at creation. You cannot attach an existing ClickHouse Cloud service to a Fabric item, and you cannot connect the workload to an existing ClickHouse Cloud organization.
• Service deletion from the ClickHouse Cloud console leaves the Fabric item orphaned and unrecoverable. Deleting a Fabric item deletes its service, but the reverse path is currently unsupported.
• Service configuration lives in the Cloud console. Scaling, idling, and backups are managed from ClickHouse Cloud, not from Fabric item settings.

​

Access and identity

• Role changes apply on next open. Your ClickHouse organization role is mapped from your Fabric workspace role each time you open a ClickHouse item. Fabric cannot notify ClickHouse of role changes, so a changed workspace role does not take effect immediately.
• Removing or downgrading a user in Fabric does not revoke their ClickHouse access. Because roles are only applied when a user opens a ClickHouse item, a user who is removed from the workspace or downgraded may retain their existing access on the ClickHouse Cloud side, and if they never open the item again that change never propagates at all. To revoke access immediately and reliably, remove the user from the organization in the ClickHouse Cloud console.
• Workspace-level access. Everyone in the workspace can use its ClickHouse items. Granular, per-table permission sync from OneLake is currently not supported.
• Console SSO requires an email on your Entra profile. ClickHouse Cloud users need an email address; if your Microsoft Entra profile has none set, signing in to the Cloud console will fail.

​

Troubleshooting

​

Email verification fails when opening a ClickHouse item

• email_not_found — your guest profile in this tenant has no email address set.
• email_domain_unverified — your email belongs to your home tenant, which is not a verified domain in the tenant you are using Fabric in.

​

Cannot sign in to the ClickHouse Cloud console

​

Access denied when opening an item

​

Reference

• Vendor attestation
• ClickHouse Cloud on Microsoft Marketplace
• Azure Marketplace PAYG billing
• Supported regions
• ClickPipes pricing dimensions
• Support program
• Service status
• Terms of service