> ## Documentation Index
> Fetch the complete documentation index at: https://docs.rootly.com/llms.txt
> Use this file to discover all available pages before exploring further.

# ClickHouse AI Connector

> Connect Rootly AI to a ClickHouse database for bounded, read-only investigation queries across logs, metrics, traces, and operational data.

## Overview

The **ClickHouse AI connector** gives Rootly AI direct, read-only access to the ClickHouse data your responders use during investigations. Rootly AI can discover databases and tables, inspect column schemas, and run bounded SQL queries against logs, metrics, traces, or other operational data.

Rootly calls ClickHouse's HTTPS interface directly. You don't need to deploy an MCP server or copy ClickHouse data into Rootly. Queries run on demand, and ClickHouse remains the source of truth.

<Note>
  The ClickHouse account is the primary authorization boundary. Rootly adds query validation and resource limits, but it can read every database and table granted to the configured account. Create a dedicated account with access only to the data Rootly AI should investigate.
</Note>

***

## Before You Start

You'll need:

* A public ClickHouse HTTPS endpoint that Rootly can reach.
* A dedicated ClickHouse username and password.
* `SELECT` access on only the databases and tables Rootly AI should query.
* Permission in Rootly to manage AI connectors.

For ClickHouse Cloud, copy the **HTTPS endpoint** from your service's connection details. It normally uses port `8443` and looks like `https://example.us-east-1.aws.clickhouse.cloud:8443`.

Self-hosted deployments must expose a trusted HTTPS endpoint to Rootly. Plain HTTP endpoints, private network addresses, embedded credentials, URL paths, query strings, and fragments are rejected.

<Warning>
  Don't connect the `default` user or an administrative account. Application-level query checks don't replace ClickHouse access control. The configured account should be unable to insert, alter, delete, or administer data.
</Warning>

***

## Create a Read-Only ClickHouse Account

Run the following statements as a ClickHouse administrator. Replace the database name, username, and password before running them:

```sql Create a read-only account theme={null}
CREATE ROLE rootly_ai_role;

GRANT SELECT ON observability.* TO rootly_ai_role;

ALTER ROLE rootly_ai_role SETTINGS
  readonly = 1,
  max_execution_time = 15 MAX 15,
  max_rows_to_read = 100000000 MAX 100000000,
  max_bytes_to_read = 5000000000 MAX 5000000000,
  max_memory_usage = 2000000000 MAX 2000000000,
  max_threads = 4 MAX 4;

CREATE USER rootly_readonly
IDENTIFIED WITH sha256_password BY 'REPLACE_WITH_A_STRONG_PASSWORD';

GRANT rootly_ai_role TO rootly_readonly;
ALTER USER rootly_readonly DEFAULT ROLE rootly_ai_role;
```

Grant individual databases rather than `*.*` when possible. Repeat the `GRANT SELECT` statement for each database Rootly AI needs.

The role mirrors Rootly's per-request read, time, memory, and thread limits. The `readonly = 1` setting blocks data-definition and data-modification queries at the ClickHouse layer. Rootly also sends `readonly=1` with every request and rejects mutations, administration statements, settings changes, multiple statements, output formats, and external-network table functions before sending a query.

<Tip>
  ClickHouse Cloud also lets you create users and assign roles from its SQL console. Keep the same least-privilege shape: a dedicated identity, `SELECT` on selected databases, and no administrative grants.
</Tip>

### Verify the account

Test the endpoint and credentials before connecting them to Rootly:

```bash Verify with curl theme={null}
curl --fail-with-body \
  --user 'rootly_readonly' \
  --data-binary 'SELECT 1' \
  'https://YOUR_CLICKHOUSE_HOST:8443/?readonly=1'
```

`curl` prompts for the password without placing it in shell history or the process arguments. The command should return `1`. Then verify that the account can read an intended table and can't create one; enter the password at each prompt:

```bash Verify read-only access theme={null}
curl --fail-with-body \
  --user 'rootly_readonly' \
  --data-binary 'SELECT 1 FROM YOUR_DATABASE.YOUR_TABLE LIMIT 1' \
  'https://YOUR_CLICKHOUSE_HOST:8443/?readonly=1'

curl --fail-with-body \
  --user 'rootly_readonly' \
  --data-binary "SELECT name FROM system.tables WHERE database = 'YOUR_DATABASE' LIMIT 1" \
  'https://YOUR_CLICKHOUSE_HOST:8443/?readonly=1'

curl --fail-with-body \
  --user 'rootly_readonly' \
  --data-binary 'CREATE TABLE YOUR_DATABASE.rootly_permission_test (id UInt8) ENGINE = Memory' \
  'https://YOUR_CLICKHOUSE_HOST:8443/?readonly=1'
```

Both `SELECT` statements should succeed. The `CREATE TABLE` statement should fail with a read-only error.

***

## Connect ClickHouse

<Steps>
  <Step title="Open The ClickHouse Card">
    Go to **AI & Agents → Connectors**, find **ClickHouse**, and click **Connect**.
  </Step>

  <Step title="Enter The Endpoint">
    Enter the public HTTPS origin, including its port when required. Use only the origin, such as `https://example.clickhouse.cloud:8443`; don't include a database path or query parameters.
  </Step>

  <Step title="Choose The Default Database">
    Enter the database Rootly AI should use for unqualified table names. Leave this field blank to use the ClickHouse account's default database.
  </Step>

  <Step title="Enter The Credentials">
    Enter the dedicated read-only username and password, then click **Connect**.
  </Step>

  <Step title="Verify The Connection">
    Rootly runs `SELECT 1` with the connector's read-only settings. The card shows **Connected** only after ClickHouse authenticates the account and completes the query.
  </Step>
</Steps>

<ParamField path="Name" type="string" required>
  A label that identifies this connection in investigation citations, such as `Production Observability`.
</ParamField>

<ParamField path="Endpoint" type="URL" required>
  The public HTTPS origin for ClickHouse. Include a non-default port, but don't include credentials, a path, query parameters, or a fragment.
</ParamField>

<ParamField path="Database" type="string">
  The optional default database for queries. Database discovery still shows every database visible to the configured account.
</ParamField>

<ParamField path="Username" type="string" required>
  The dedicated ClickHouse account with narrowly scoped `SELECT` grants.
</ParamField>

<ParamField path="Password" type="password" required>
  The password for the dedicated account. Rootly encrypts it at rest and doesn't show it again.
</ParamField>

***

## What Rootly AI Can Query

The connector exposes four read-only tools:

| Operation      | What it does                                                                                             |
| -------------- | -------------------------------------------------------------------------------------------------------- |
| List databases | Lists databases visible to the configured ClickHouse account.                                            |
| List tables    | Lists tables in one database with engine, row-count, and byte-size metadata when ClickHouse provides it. |
| Describe table | Lists a table's columns, types, defaults, expressions, and comments.                                     |
| Query          | Runs one `SELECT`, `WITH`, or `EXPLAIN` statement and returns a bounded JSON result.                     |

Rootly AI discovers the database, table, and column names before composing SQL. Ask questions that identify a useful time window and service, environment, or trace identifier. For example:

* *"Which services produced the most error spans in the 15 minutes before this incident?"*
* *"Compare checkout latency by deployment version for the last hour."*
* *"Find traces containing this request ID and summarize the failing dependency."*
* *"Did log volume or error rate change after the deployment at 14:05 UTC?"*

ClickHouse SQL remains authoritative for query semantics. Rootly AI can query standard tables, views, and the `system` catalog when the account has access.

***

## Query and Resource Limits

Every query is subject to application and ClickHouse request limits:

| Limit           | Behavior                                                                                |
| --------------- | --------------------------------------------------------------------------------------- |
| Statement type  | One `SELECT`, `WITH`, or `EXPLAIN` statement                                            |
| Returned rows   | 200 rows by default; 1,000 rows maximum                                                 |
| Response size   | 1 MB maximum                                                                            |
| Execution time  | 15 seconds in ClickHouse; 20-second HTTP timeout                                        |
| Rows read       | 100 million maximum                                                                     |
| Bytes read      | 5 GB maximum                                                                            |
| Memory          | 2 GB maximum                                                                            |
| Threads         | 4 maximum                                                                               |
| Result overflow | ClickHouse stops the result at the configured boundary and Rootly marks it as truncated |

Rootly rejects data-definition language (DDL), mutations, administrative commands, `SET` and `SETTINGS` changes, multiple statements, custom `FORMAT` clauses, and external-network table functions such as `url`, `s3`, `http`, `remote`, `mysql`, and `postgresql`.

These limits protect investigation context and shared ClickHouse capacity. They aren't a substitute for ClickHouse quotas or workload controls. Use `MAX` or `CONST` [settings-profile constraints](https://clickhouse.com/docs/reference/statements/create/settings-profile) when a server-side cap must remain authoritative; ClickHouse rejects a request that exceeds the constraint. Use [quotas](https://clickhouse.com/docs/concepts/features/configuration/server-config/quotas) to bound cumulative use across queries. Test the Rootly connection after tightening either control.

<Tip>
  Aggregate and filter before returning raw rows. Narrow time windows, select only useful columns, and group by service or error attribute so the result stays below the row and response limits.
</Tip>

***

## Data Handling and Permissions

* **On-demand queries.** Rootly AI queries ClickHouse only when an investigation or direct question needs the data. The connector doesn't run a background ingestion job.
* **ClickHouse permissions apply.** Rootly can only read objects granted to the configured account.
* **Defense in depth.** Rootly validates query shape and sends read-only, execution, result, memory, and thread settings with every request.
* **Encrypted credentials.** Rootly encrypts the username and password at rest and excludes them from audit-version payloads.
* **Credential cleanup.** Disconnecting the AI connector scrubs the stored username and password before soft-deleting the connection.
* **AI traces may contain results.** Connector responses can appear in Rootly AI's model and observability traces. See [Data Privacy for Rootly AI](/ai/data-privacy-for-rootly-ai) for retention details.

One active ClickHouse connection can be configured per Rootly team. Use a ClickHouse view or a dedicated database when responders need a curated schema rather than broad table access.

***

## Managing the Connection

Open the ClickHouse card to update or disconnect it. When editing the connection, leave both credential fields blank to keep the current username and password. If you enter replacement credentials, Rootly verifies them before saving the change.

Disconnecting removes the stored credentials and prevents future queries. It doesn't change the ClickHouse account or delete ClickHouse data. Revoke or delete the ClickHouse account separately if it is no longer needed.

If your firewall restricts inbound traffic, add [Rootly's published IP ranges](/integrations/ip-whitelist) before connecting ClickHouse.

***

## Troubleshooting

<AccordionGroup>
  <Accordion title="Rootly can't reach the endpoint" icon="network-wired">
    Confirm that the value is a public HTTPS origin and that DNS and the certificate are valid. Include port `8443` for ClickHouse Cloud when it appears in the connection details. Rootly rejects HTTP, private and loopback destinations, embedded credentials, URL paths, query parameters, and fragments. If you restrict inbound traffic, add Rootly's published IP ranges.
  </Accordion>

  <Accordion title="ClickHouse rejects the credentials" icon="key">
    Repeat the `curl` check in [Verify the account](#verify-the-account) with the same HTTPS origin, username, and password. Confirm that the account uses password authentication and hasn't been disabled or rotated. When updating Rootly after a rotation, enter both the username and password.
  </Accordion>

  <Accordion title="Verification succeeds, but table discovery fails" icon="table">
    `SELECT 1` confirms connectivity and authentication but doesn't prove the account can read your data. Grant `SELECT` on the intended database and its tables. The discovery tools also read `system.databases`, `system.tables`, and `system.columns`; confirm that your ClickHouse policy permits those catalog queries.
  </Accordion>

  <Accordion title="A query is rejected before it reaches ClickHouse" icon="shield">
    Use one `SELECT`, `WITH`, or `EXPLAIN` statement without a trailing semicolon or `FORMAT` clause. Remove settings changes, mutations, DDL, administration commands, and external-network table functions. Rootly rejects these query shapes even if the ClickHouse account could run them.
  </Accordion>

  <Accordion title="A query times out or returns a truncated result" icon="gauge-high">
    Filter to a shorter incident window, select fewer columns, aggregate before sorting, or use a lower-cardinality grouping. Rootly limits execution time, rows, bytes, memory, threads, and response size. A truncated result is partial evidence, not a complete count.
  </Accordion>

  <Accordion title="Rootly AI can't find recent telemetry" icon="magnifying-glass">
    Confirm that the expected exporter is writing to the database and table granted to the Rootly account. Check the event timestamp column and query the same time range directly in ClickHouse. Empty results can mean the wrong database, table, time zone, or filter; they don't prove that the service is healthy.
  </Accordion>
</AccordionGroup>

***

## Frequently Asked Questions

<AccordionGroup>
  <Accordion title="Do I need to run a ClickHouse MCP server?" icon="server">
    No. Rootly calls ClickHouse's HTTPS query interface directly and provides its own bounded AI tools for database discovery, schema inspection, and read-only SQL.
  </Accordion>

  <Accordion title="Can Rootly AI modify ClickHouse data?" icon="shield">
    No. Rootly exposes only read operations, rejects mutations and administrative statements, and sends `readonly=1` with each request. Keep the dedicated ClickHouse account read-only as the authoritative server-side control.
  </Accordion>

  <Accordion title="Can I connect a private ClickHouse cluster?" icon="lock">
    The connector requires a public HTTPS endpoint. Place a trusted HTTPS proxy or load balancer in front of the cluster, restrict it to Rootly's published IP ranges, and keep ClickHouse authentication enabled.
  </Accordion>

  <Accordion title="Can I connect multiple ClickHouse services?" icon="database">
    One active ClickHouse connection can be configured per Rootly team. To expose data from several clusters, consolidate the required investigation data behind one approved ClickHouse endpoint or use separate Rootly teams.
  </Accordion>
</AccordionGroup>

***

## Related Pages

<CardGroup cols={3}>
  <Card title="Connectors Overview" icon="sparkles" href="/ai/connectors/overview">
    Compare Rootly AI connectors and setup flows.
  </Card>

  <Card title="Data Privacy for Rootly AI" icon="shield" href="/ai/data-privacy-for-rootly-ai">
    Review connector data handling, traces, and retention.
  </Card>

  <Card title="ClickHouse Access Control" icon="arrow-up-right-from-square" href="https://clickhouse.com/docs/operations/access-rights">
    Configure users, roles, and grants in ClickHouse.
  </Card>
</CardGroup>