ClickHouse

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

Connect your ClickHouse cluster to let 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

1. 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:

   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.

2. 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:

   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.

3. 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.
4. 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.

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

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.

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:

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

@tony #report the ClickHouse databases and the tables in each one

Example prompts

@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.

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.

Troubleshooting

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.

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.

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.

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.

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.

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.

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.

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

Tony Agent

Database-focused optimization agent

PostgreSQL Connection

Similar setup for PostgreSQL databases