ClickHouse

Connect ClickHouse so CloudThinker can explore schemas, check table health, and rank slow queries with analytical SQL

Connect ClickHouse to let CloudThinker list databases and tables, check part counts and table sizes, rank slow queries from system.query_log, and answer questions with analytical SQL. CloudThinker signs in as a ClickHouse user over the HTTP interface, and a new connection is read-only.

Approvals don’t apply to ClickHouse. If you set Write access to Full access, writes run without asking anyone. See Write access.

---

Prerequisites

• A ClickHouse server reachable from CloudThinker on its HTTP interface: 8443 with TLS, 8123 without. ClickHouse Cloud and self-hosted clusters both work. The native TCP port (9000) is not used.
• An admin login on ClickHouse to create a dedicated user. On ClickHouse Cloud, run the Setup SQL in the service’s SQL console as the default user.
• The workspace Admin role in CloudThinker to add the connection. See How access works.

---

Setup

1. Create a dedicated user with read access

   Connect as an admin and run:

   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;
   GRANT SELECT ON system.tables TO cloudthinker;
   GRANT SELECT ON system.columns TO cloudthinker;
   GRANT SELECT ON system.parts TO cloudthinker;
   GRANT SELECT ON system.query_log TO cloudthinker;
   

   On ClickHouse Cloud, the password must meet the service’s complexity rules (length, mixed case, a digit, and a special character), or CREATE USER fails.

   The system.* grants power table health and slow-query answers. Skip them and those questions fail while schema questions still work.

   system.query_log holds the full text of every user’s queries, including literals in WHERE clauses, which can carry personal data. Granting it lets the agent read those queries and quote them in chat. If that’s a concern, skip that grant: slow-query answers then fail.

   On a self-hosted cluster with several nodes, a user created this way exists only on the node you ran it on, unless the cluster replicates access storage. Run this version instead, with your cluster name:

   CREATE USER cloudthinker ON CLUSTER '<cluster>' IDENTIFIED BY 'your-secure-password';
   GRANT ON CLUSTER '<cluster>' SELECT ON your_database.* TO cloudthinker;
   GRANT ON CLUSTER '<cluster>' SHOW DATABASES, SHOW TABLES, SHOW COLUMNS ON *.* TO cloudthinker;
   GRANT ON CLUSTER '<cluster>' SELECT ON system.tables TO cloudthinker;
   GRANT ON CLUSTER '<cluster>' SELECT ON system.columns TO cloudthinker;
   GRANT ON CLUSTER '<cluster>' SELECT ON system.parts TO cloudthinker;
   GRANT ON CLUSTER '<cluster>' SELECT ON system.query_log TO cloudthinker;
   

   Check the user exists on every node with SELECT name FROM clusterAllReplicas('<cluster>', system.users) WHERE name = 'cloudthinker'.

2. Lock the user to read-only at the server (recommended)

   Skip this step if you plan to turn Write access on. Otherwise, add a settings profile so ClickHouse itself refuses writes, whatever any client asks for:

   CREATE SETTINGS PROFILE cloudthinker_readonly SETTINGS readonly = 1 READONLY;
   ALTER USER cloudthinker SETTINGS PROFILE cloudthinker_readonly;
   

   The profile doesn’t break CloudThinker. Reads work as normal, and ClickHouse itself refuses every write.

3. Open network access

   These docs don’t publish CloudThinker’s egress IP list: ask your CloudThinker contact, or contact us. Then:

   • ClickHouse Cloud: add them to the service’s IP access list under the service Settings → Security.
   • Self-hosted: allow inbound 8443 (or 8123) from them in your firewall or security group.
4. Add the connection in CloudThinker

   Go to More → Workspace settings → Connections, select ClickHouse, fill in the fields from Connection details, leave Write access on Read-only (default), and click Connect.

   CloudThinker runs one SELECT version() as that user.

   Success state: ClickHouse shows a Connected status, and the message reads ClickHouse <version> reachable as cloudthinker.

---

Connection details

Field	Required	Description	Default
Host	Yes	Hostname only, no https:// and no port. ClickHouse Cloud looks like abc123.ap-southeast-1.aws.clickhouse.cloud
Port	Yes	HTTP interface port: 8443 with TLS, 8123 without	8443
Username	Yes	The dedicated user, for example cloudthinker
Password	Yes	That user’s password
Use TLS	No	HTTPS instead of plain HTTP. Options: Yes, No	Yes
Verify the TLS certificate	No	Set to No only for a self-signed or internal-CA certificate. Shown only when TLS is on. Options: Yes (default), No	Yes (default)
Default database	No	Database used when a query doesn’t name one. Blank uses the server default	Blank
Write access	No	Whether CloudThinker may write. Options: Read-only (default), Full access	Read-only (default)
Allow DROP and TRUNCATE	No	Shown only with Full access. Options: Blocked (default), Allowed	Blocked (default)

Change Port and Use TLS together. 8443 goes with TLS on, 8123 with TLS off. A mismatch fails at connect time.

---

Required permissions

The Setup grants cover everything read-only. Add write grants only if you set Write access to Full access, and only on the databases the agent should change:

-- ALTER includes ALTER ... DELETE, DROP PARTITION, and DROP COLUMN, which remove data.
-- See "Blocked protects the table, not the rows" under Write access.
GRANT INSERT, ALTER, CREATE TABLE, CREATE VIEW ON your_database.* TO cloudthinker;
-- Only if the agent should remove tables:
GRANT DROP TABLE, TRUNCATE ON your_database.* TO cloudthinker;

Never grant writes on *.*. The ClickHouse grants are the real boundary: the Write access field only decides whether CloudThinker sends readonly=1, and it can’t give the user a privilege ClickHouse didn’t grant.

---

Agent capabilities

Capability	Description
Schema discovery	List databases and tables with engine, sorting key, row count, and column types
Analytical queries	Run SQL with aggregates, joins, and window functions
Table health	Check part counts, compressed and uncompressed size, and index granularity from system.parts
Query investigation	Rank slow or expensive queries from system.query_log
Changes	With Full access only: insert rows, alter tables, and create tables and views

system.query_log and system.parts are kept per node. On a cluster with several replicas, answers cover only the node that took the query. To cover every node, ask for it in the prompt:

• Self-hosted: “use clusterAllReplicas('<cluster>', system.query_log)”
• ClickHouse Cloud: “use clusterAllReplicas('default', system.query_log)”

Verify the connection

List the ClickHouse databases and the tables in each one

Success state: CloudThinker returns your databases with their tables, engines, and row counts.

Example prompts

Which ClickHouse tables are the largest by compressed size, and how many parts does each have?
Show the 10 slowest queries from system.query_log in the last 24 hours, with the user and table for each
Suggest a better sorting key for our largest MergeTree table, based on the queries that hit it

---

Write access

Write access	Allow DROP and TRUNCATE	What the agent can do
Read-only (default)	Hidden	SELECT only. ClickHouse refuses anything else with error 164 (READONLY)
Full access	Blocked (default)	INSERT, ALTER, and CREATE, with no approval step. DROP TABLE and TRUNCATE are refused
Full access	Allowed	Everything above, plus DROP TABLE and TRUNCATE. DROP DATABASE also runs if the user holds that grant, which Required permissions leaves out

Before you choose Full access:

• Blocked protects the table, not the rows. ALTER TABLE ... DELETE, DROP PARTITION, and DROP COLUMN still run and still remove data. Treat Full access as “the agent can destroy data”.
• Nothing rolls back. ClickHouse has no transactions, so recovery means restoring from a backup.

Write access is locked after you connect. In Edit Instance, Replace… opens the saved fields for editing, but Write access stays greyed out with “To change this, disconnect and connect again.” To change it, disconnect the ClickHouse connection, then add it again with the new Write access.

With Full access on, you can still switch Allow DROP and TRUNCATE in place: open More → Workspace settings → Connections, select ClickHouse, click the pencil icon on the instance’s row, then in Edit Instance click Replace…, pick the option, leave Password blank to keep the saved one, and click Save and test. If the test fails, the connection keeps its previous settings.

If you applied the READONLY profile from Setup step 2, lift it first or ClickHouse keeps refusing writes:

ALTER USER cloudthinker SETTINGS PROFILE default;
DROP SETTINGS PROFILE cloudthinker_readonly;

Why approvals don’t apply: ClickHouse runs every query through one tool, so CloudThinker can’t ask about a write separately from a read. Rules and Approval mode never see ClickHouse writes. Try Full access on a staging cluster before the one your dashboards read from.

---

Rotate the password

1. Set a new password on the ClickHouse user:
   ALTER USER cloudthinker IDENTIFIED BY 'new-secure-password';
   
   The old password stops working at once, so CloudThinker’s queries fail until you finish step 2. Do both steps together.
2. Open More → Workspace settings → Connections, select ClickHouse, click the pencil icon on the instance’s row, and in Edit Instance click Replace…. Enter the new Password and click Save and test.

Success state: the test passes and the connection stays Connected. If the test fails, CloudThinker keeps the previous settings and shows the error.

---

Troubleshooting

The first four titles match the connection test’s messages, so you can search this page for yours. The fifth is ClickHouse’s own error text, and the sixth is a symptom you see in chat.

ClickHouse rejected the username or password

Check the user exists with SHOW USERS;, then retype the password instead of pasting it. A value with a line break is rejected before CloudThinker contacts the server. On ClickHouse Cloud, create a database user rather than reusing a console sign-in.

ClickHouse is unreachable, or rejected the request path

• Port and Use TLS must agree: 8443 with TLS, 8123 without. 9000 is the native TCP port, not the HTTP interface.
• Host must have no https:// prefix and no :port suffix.
• On ClickHouse Cloud, add CloudThinker’s egress IPs to the service IP access list. Self-hosted, check the firewall and that <listen_host> covers the exposed interface.

ClickHouse did not answer in time

On ClickHouse Cloud, an idle service suspends and connections time out until it wakes. Wake the service in the ClickHouse Cloud console, then connect again.

ClickHouse has no database named ...

The Default database doesn’t exist. Names are case-sensitive. Fix the spelling or leave the field blank.

Cannot execute query in readonly mode

The connection is read-only, which is the default. If the agent should write, reconnect with Write access set to Full access (see Write access). If it still fails, the limit is on the server: run SHOW CREATE USER cloudthinker; and look for a readonly settings profile or missing write grants.

The table list is empty

The user needs SHOW TABLES and SELECT on the database, not only on single tables, plus SELECT on system.tables.

---

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 decide what’s possible. Write access only decides what CloudThinker asks for.
• Server-side read-only: for a connection that must never write, keep the READONLY settings profile from Setup step 2. No client can lift it.

---

Related

PostgreSQL Connection

Connect a PostgreSQL database the same way

Connections overview

How credentials and write controls work across connections

Approvals

How approvals work on connections that support them