Skip to main content

ClickHouse

SnoutData connects to ClickHouse over its HTTP interface and knows what a ClickHouse user actually deals with: parts and merges, the sort key, what a query read, mutations, replicas, and ClickHouse Cloud's routing of every request to any replica. It works with self-managed servers, replicated clusters and ClickHouse Cloud.

ClickHouse Operations: health cards, and each replica's disks read in one view

Connect​

  1. Open the connections sidebar and choose New connection.
  2. Pick the ClickHouse driver.
  3. Enter the host, the port (8123, or 8443 with SSL, as on ClickHouse Cloud), a username and a password. A database is optional.
  4. Click Test, then Save.

SSL and SSH tunnels work as they do for every other driver. Read-only users (readonly = 1, the usual production analyst account) can connect: SnoutData never sends a setting such a user would be refused.

Explore​

Each ClickHouse database shows up as a schema, with its tables, views and materialized views. For each table you see:

  • the engine line: engine, PARTITION BY, PRIMARY KEY (when it is shorter than the sort order), ORDER BY and SAMPLE BY;
  • row count and size on disk, and the table's data-skipping indexes;
  • the server's own CREATE TABLE statement in the DDL view, so TTL and SETTINGS are there as written.

Columns that are part of the sort key have their own icon. Hover a column to see whether it is in the primary index or only in the sort order, its compression codec, and how much it takes on disk compressed and raw, with the compression ratio. The editor's hover shows the same, plus the table's engine line.

Run queries​

  • Rows and bytes read are shown under every result. On a columnar database that is what a query cost: a query that returns four rows can still have read a billion.
  • Live progress while a query runs: rows read (with a percentage when the server knows the total), bytes read and the memory the query holds, next to the elapsed time.
  • Query parameters: write {name:Type} placeholders (WHERE id = {id:UInt32}) and SnoutData asks for the values before the query runs, starting from what you typed last. The values are bound on the server with their types, so there is no quoting to get right, and the next page, EXPLAIN and a re-run use the same values. A SET param_id = 42 you type yourself works the same way, as in clickhouse-client.
  • SET and USE carry over to the statements after them, even though every HTTP request is a new session.
  • Your own FORMAT (FORMAT CSV, FORMAT Pretty, ...) shows the server's output as it came, one line per row.
  • The automatic row limit goes before a trailing SETTINGS or FORMAT, and a LIMIT n BY is not mistaken for a row cap.
  • Stop ends the query wherever it runs, including on ClickHouse Cloud, where the stop request can land on a different replica from the query.
  • Integers past 2^53 (UInt64, Int64) keep every digit.

Tables are read-only in the results grid on purpose: a ClickHouse sort key is not unique, so it cannot address a single row. Change data with SQL.

Query advice​

After a slow query (and before a large one, as a warning), SnoutData reads the server's own plan and tells you:

  • when a query read every granule of a table instead of skipping by its sort key or partition key, with the table's real row count and what to filter on instead;
  • when a query was answered from a projection, so you know the projection is working;
  • when FINAL is used on a large table, which merges its parts while the query reads them, and what to write instead.

When a query fails, the error is classified by ClickHouse's own error code. A query that hit a memory limit, a quota, too many parts or a timeout is described as that, not as wrong SQL, so Fix with AI makes the query read and hold less instead of renaming columns that were never wrong.

Operations​

Every ClickHouse connection has an Operations view, under the connection in the sidebar or from the command palette ("ClickHouse Operations..."). It refreshes every few seconds and opens on a set of health cards, each of which opens the rows behind it:

  • Parts, against the server's own "too many parts" limits;
  • Mutations pending or failing;
  • Replication: read-only replicas and replica lag;
  • Disk use and detached parts;
  • Dictionaries that failed to load, and refreshable views whose last refresh failed;
  • Failed queries in the last hour, and what is running now.

The views are grouped in five tabs:

  • Queries: running queries (with Stop), failed queries grouped by error.
  • Storage: parts per partition, merges, mutations (with Cancel), disks, detached parts, column sizes and compression.
  • Objects: materialized views with what each reads from and writes into, refreshable views (with Refresh now), dictionaries (with Reload).
  • Cluster: replicas, clusters with per-node error counts, and the ON CLUSTER DDL queue.
  • Server: settings that differ from the defaults, backups and restores.

Stop, Cancel, Refresh now and Reload ask before they run, and go into your query history. The view's own reads are kept out of your history and out of its own lists.

ClickHouse Cloud and replicated clusters​

ClickHouse Cloud sends each request to any replica, and a server's system tables describe only that one node. SnoutData finds the replicas of the default cluster and reads what is per node (running and failed queries, merges, disks, dictionaries, replication, backups) from every replica at once, with a replica column saying which node each row is from. The header says All N replicas. Stop and Reload act on every replica.

Reading across replicas needs the READ ON REMOTE grant. Without it the header says 1 of N replicas and why, and the view shows the replica that answered.

Query Performance​

Query Performance (command palette) ranks the heaviest statements of the last day from ClickHouse's query log, across every replica, by total time, data read or peak memory, with calls, mean time, rows, megabytes read and peak memory for each. SnoutData's own reads of the server are left out. Hand any statement to the assistant to explain it or suggest an optimization.

Production safety​

On a connection flagged production, a ClickHouse mutation (ALTER TABLE ... DELETE or ALTER TABLE ... UPDATE) is confirmed as the DELETE or UPDATE it is, and WHERE 1 counts as every row. The confirmation, and a warning in the editor, explain that a mutation rewrites every part holding a matching row in the background and cannot be rolled back, and that a lightweight DELETE FROM ... WHERE ... is usually what was meant. See Security.

Schema sync, design and data flows​

Schema sync, the table designer and data flows write ClickHouse DDL: tables are copied from the server's own CREATE TABLE, so engine clauses survive, and new tables are created as a MergeTree ordered by their key columns.

Not supported yet​

User and role management, a ClickHouse function catalog in completion, a tree viewer for Array/Map/Tuple/JSON values, and the native TCP protocol (9000/9440).