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

# ClickHouse

> Connect ClickHouse to CloudThinker for schema inspection, analytical query investigation, and optional write access

Connect your ClickHouse cluster to let [Tony](/guide/agents/tony) (Database Engineer) explore schemas, inspect table health, and answer questions with analytical SQL. A new connection is read-only; turn **Write access** on when you want the agent to change data, and **Allow DROP and TRUNCATE** separately when you want it to remove objects.

## Prerequisites

* A ClickHouse server reachable from CloudThinker over its **HTTP interface** (`8443` with TLS, `8123` without). ClickHouse Cloud, self-hosted, operator-run, and managed clusters all work; the native TCP port (`9000`) is not used.
* Admin access to create a dedicated user.

## Setup

<Steps>
  <Step title="Create a dedicated user and grant read access">
    Connect as an admin, create the CloudThinker user, and grant `SELECT` on the databases the agent should see plus `SHOW`:

    ```sql theme={null}
    CREATE USER cloudthinker IDENTIFIED BY 'your-secure-password';
    GRANT SELECT ON your_database.* TO cloudthinker;
    GRANT SHOW DATABASES, SHOW TABLES, SHOW COLUMNS ON *.* TO cloudthinker;
    ```

    For table sizes, part counts, and query investigation, also grant `SELECT` on `system.tables`, `system.columns`, `system.parts`, and `system.query_log`.
  </Step>

  <Step title="Pin the user to read-only (recommended)">
    The connection defaults to read-only, but a settings profile makes the restriction hold at the server, independent of any client:

    ```sql theme={null}
    CREATE SETTINGS PROFILE cloudthinker_readonly SETTINGS readonly = 1 READONLY;
    ALTER USER cloudthinker SETTINGS PROFILE cloudthinker_readonly;
    ```

    Skip this step if you plan to turn **Write access** on. The profile uses `READONLY`, so neither the user nor CloudThinker's switch can lift it.
  </Step>

  <Step title="Configure network access">
    * ClickHouse Cloud: add CloudThinker to the service **IP access list**, under the service's **Settings → Security**.
    * Self-hosted: allow inbound `8443` (or `8123`) from CloudThinker in your firewall or security group.
  </Step>

  <Step title="Add the connection in CloudThinker">
    Go to **Connections → ClickHouse** and enter the host, port, username, and password, set **Use TLS**, and leave **Write access** on `Read-only` unless the agent needs to change data.

    Click **Connect**. CloudThinker runs a single `SELECT version()` as that user, and the **Connected** message names the ClickHouse version, the username, and the default database when you set one. Anything else comes back as a specific reason — see [Troubleshooting](#troubleshooting).
  </Step>
</Steps>

## Connection details

| Field                          | Description                                                                        | Default        |
| ------------------------------ | ---------------------------------------------------------------------------------- | -------------- |
| **Host**                       | Hostname or IP, no scheme and no port                                              | —              |
| **Port**                       | HTTP interface port                                                                | `8443`         |
| **Username**                   | Dedicated user, for example `cloudthinker`                                         | —              |
| **Password**                   | User password                                                                      | —              |
| **Use TLS**                    | HTTPS instead of plain HTTP                                                        | `Yes`          |
| **Verify the TLS certificate** | Turn off only for a self-signed or internal-CA certificate; hidden when TLS is off | `Yes`          |
| **Default database**           | Database used when a query does not qualify a table                                | Server default |
| **Write access**               | Whether the agent may change data                                                  | `Read-only`    |
| **Allow DROP and TRUNCATE**    | Whether the agent may remove objects; hidden while read-only                       | `Blocked`      |

<Tip>
  `8443` and TLS is the ClickHouse Cloud pair. A self-hosted server without TLS answers on `8123`; set **Use TLS** to `No` and the port to `8123` together, because a mismatch fails at connect time.
</Tip>

## Required permissions

The grants in Setup step 1 cover read-only analysis; `system.query_log` is what turns "this dashboard is slow" into a ranked list of the queries responsible. Only if you enable write access, additionally grant:

```sql theme={null}
GRANT INSERT, ALTER, CREATE TABLE, CREATE VIEW ON your_database.* TO cloudthinker;
-- Only if the agent should remove objects:
GRANT DROP TABLE, TRUNCATE ON your_database.* TO cloudthinker;
```

Grant these on the specific databases the agent should change, never on `*.*`. A grant the user does not hold is the boundary the **Write access** switch cannot cross.

## Agent capabilities

Once connected, Tony can:

| Capability              | Description                                                                     |
| ----------------------- | ------------------------------------------------------------------------------- |
| **Schema discovery**    | List databases and tables with engine, sorting key, row count, and column types |
| **Analytical queries**  | Run SQL, including aggregates, joins, and window functions                      |
| **Table health**        | Inspect part counts, compressed and uncompressed size, and index granularity    |
| **Query investigation** | Rank slow or expensive queries from `system.query_log`                          |

### Verify the connection

```text theme={null}
@tony #report the ClickHouse databases and the tables in each one
```

### Example prompts

```text theme={null}
@tony #report which ClickHouse tables grew the most in the last week
@tony #report per-service p95 latency from the events table
@tony #recommend a better sorting key for our largest MergeTree table
```

## Write access

The connection ships read-only, and two switches open it up one step at a time.

| Write access          | Allow DROP and TRUNCATE | What the agent can do                                                                        |
| --------------------- | ----------------------- | -------------------------------------------------------------------------------------------- |
| `Read-only` (default) | hidden                  | `SELECT` only; everything else is refused by ClickHouse with error `164 READONLY`            |
| `Full access`         | `Blocked` (default)     | `INSERT`, `ALTER`, `CREATE`, and materialized views; `DROP TABLE` and `TRUNCATE` are refused |
| `Full access`         | `Allowed`               | Everything above, plus dropping and truncating tables and databases                          |

Three things to weigh before turning write access on:

* **`Blocked` protects the table, not the rows.** It does not reject `ALTER TABLE ... DELETE`, `DROP PARTITION`, or `DROP COLUMN`, each of which removes data while leaving the table in place. Treat `Full access` as "the agent can destroy data".
* **ClickHouse has no transactions.** Mutations and drops cannot be rolled back, so recovery means restoring from a backup.
* **Grants are the stronger control.** The switches only decide whether CloudThinker sends `readonly=1`; they never grant a privilege the ClickHouse user does not hold.

To turn write access on for an existing connection, open **Connections → ClickHouse → Edit**, change **Write access**, and reconnect.

<Warning>
  An agent with write access acts without a per-query confirmation prompt. Point it at an analytics or staging cluster before you point it at the one your dashboards read from.
</Warning>

## Troubleshooting

<Accordion title="ClickHouse rejected the username or password">
  Confirm the user exists (`SHOW USERS;`) and retype the password rather than pasting it — a pasted value carrying a line break is rejected before CloudThinker contacts the server. ClickHouse Cloud disables password auth for some SSO-provisioned users; create a dedicated database user instead of reusing a console login.
</Accordion>

<Accordion title="ClickHouse has no database named …">
  The name in **Default database** is not a database ClickHouse found. Names are case-sensitive — check the spelling, or leave the field blank to use the server default.
</Accordion>

<Accordion title="ClickHouse rejected the request path, or is unreachable">
  Confirm **Port** and **Use TLS** agree: `8443` with TLS, `8123` without — the native TCP port `9000` is not the HTTP interface. Check that **Host** carries no `https://` prefix and no `:port` suffix. On ClickHouse Cloud, add CloudThinker to the service IP access list; self-hosted, confirm `<listen_host>` includes the exposed interface and the firewall allows the port.
</Accordion>

<Accordion title="ClickHouse did not answer in time">
  On ClickHouse Cloud this is usually automatic idling: an inactive service suspends and connections time out until it restarts. Wake the service, then connect again. A 5xx answer means the server is running but not serving queries — check the cluster's own health.
</Accordion>

<Accordion title="Cannot execute query in readonly mode">
  The connection is read-only, which is the default. If the agent should write, set **Write access** to `Full access` and reconnect. If it still fails, the restriction is server-side: check whether the user carries a `readonly` settings profile (`SHOW CREATE USER cloudthinker;`) and holds the write grants the query needs.
</Accordion>

<Accordion title="DROP is refused even with write access on">
  `DROP` and `TRUNCATE` sit behind their own switch. Set **Allow DROP and TRUNCATE** to `Allowed`; it appears only once **Write access** is `Full access`.
</Accordion>

<Accordion title="Empty table list">
  The user needs `SHOW TABLES` and `SELECT` on the database, not only on individual tables. Grant `SELECT ON system.tables` so metadata queries return rows.
</Accordion>

## Security

* **Least privilege** — grant only the permissions the agents need for your use case; start read-only and widen later.
* **Read-only by default** — use read-only credentials unless you want agents to make changes through this connection.
* **Rotate credentials** — rotate keys and tokens on your normal schedule; CloudThinker picks up the new value when you update the connection.
* **Revoke on offboarding** — remove the credential at the provider when you delete a connection or a teammate leaves.

- **Grants over switches** — the privileges on the ClickHouse user are the durable boundary; the **Write access** switch decides whether CloudThinker asks for a write, the grant decides whether ClickHouse allows one.
- **Server-side read-only** — for a connection that must never write, add the settings profile in step 2; `READONLY` makes it un-liftable from the client side.

## Related

<CardGroup cols={2}>
  <Card title="Tony Agent" icon="database" href="/guide/agents/tony">
    Database-focused optimization agent
  </Card>

  <Card title="PostgreSQL Connection" icon="https://mintcdn.com/cloudthinker/aLd-ttc-SCW-aFky/images/icons/postgresql.svg?fit=max&auto=format&n=aLd-ttc-SCW-aFky&q=85&s=8bb2ac033d0a2ccbef51154a76e1e819" href="/guide/connections/postgresql" width="24" height="24" data-path="images/icons/postgresql.svg">
    Similar setup for PostgreSQL databases
  </Card>
</CardGroup>