Working with ClickHouse from a desktop SQL client

ClickHouse is the database people reach for when a table has billions of rows and the question is "how many, grouped by what, over which week?". Its built-in web SQL console is fine for a quick query, and clickhouse-client is excellent in a terminal. But when you are exploring a schema you did not design, comparing environments, or trying to work out why yesterday's dashboard query got slow, a desktop SQL client with an explorer, completion and a real result grid earns its place.

This post covers the practical side of using ClickHouse from a desktop client: which port and protocol to use, the handful of system tables worth knowing, how to check whether a query actually used the primary key, and what a client can and cannot sensibly do on a database with no transactions.

Ports and protocols

ClickHouse exposes several interfaces. The two that matter for clients are the native TCP protocol (port 9000, or 9440 with TLS) used by clickhouse-client, and the HTTP interface (port 8123, or 8443 with TLS). ClickHouse Cloud services expose HTTPS on 8443.

SQLly connects to ClickHouse over the HTTP interface. In the connection editor, pick ClickHouse, enter the host, port 8123 (or 8443 with a TLS mode selected), a user and a password, and optionally a default database. You can also paste a clickhouse:// URL and let the editor fill in the fields:

clickhouse://analyst@ch.example.com:8443/analytics

Credentials are sent as ClickHouse's X-ClickHouse-User and X-ClickHouse-Key headers rather than in the request URL, and the password is kept in your operating system's keychain. A server that is only reachable from inside a network works through SSH tunnels, proxies and Kubernetes port-forwards like any other connection.

One statement at a time, streamed

The HTTP interface accepts one statement per request, so SQLly splits a script on ; (ignoring semicolons inside strings and comments) and runs each statement in turn. All statements in a script share one ClickHouse session, so USE, SET and temporary tables carry from one statement to the next:

SET max_execution_time = 30;

CREATE TEMPORARY TABLE top_paths AS
SELECT path, count() AS hits
FROM web.requests
WHERE event_date = today() - 1
GROUP BY path
ORDER BY hits DESC
LIMIT 100;

SELECT * FROM top_paths;

Results stream: SQLly asks for a line-per-row format and reads the response as it arrives, so the grid paints progressively and a large result is spilled to disk rather than held in memory. Cancel does what you would hope on a shared analytics cluster: it stops reading and sends KILL QUERY for the running query's id, so the work stops on the server too.

ClickHouse types come through faithfully. Integers are numbers (values wider than 64 bits stay exact as text), decimals, dates, UUIDs, enums and IP addresses show as their text form, and Array, Tuple and Map values render as compact JSON in the grid.

The system tables worth knowing

Much of what you want to know about a ClickHouse server is one query away in the system database. Three examples you will reuse.

How big is each table, really?

SELECT
    database,
    table,
    sum(rows)                                         AS total_rows,
    formatReadableSize(sum(data_compressed_bytes))   AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
    count()                                           AS parts
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY sum(data_compressed_bytes) DESC
LIMIT 20;

The WHERE active matters: inactive parts are ones already merged away and waiting for cleanup. A table with a very high part count is a hint that inserts are arriving in too many small batches.

What ran, and what hurt?

SELECT
    event_time,
    query_duration_ms,
    read_rows,
    formatReadableSize(memory_usage) AS memory,
    substring(query, 1, 120) AS query_start
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time > now() - INTERVAL 1 DAY
ORDER BY query_duration_ms DESC
LIMIT 20;

Swap 'QueryFinish' for 'ExceptionWhileProcessing' to see recent failures instead. SQLly's Query Insights pane is built on the same table for ClickHouse: top queries by duration, executions or rows read over a time window, plus a recent-failures view, without writing the query each time.

What is running right now?

SELECT query_id, user, elapsed, read_rows, substring(query, 1, 120) AS query_start
FROM system.processes
ORDER BY elapsed DESC;

In SQLly, the Activity Monitor shows this live for ClickHouse, and terminating a query is scripted as a KILL QUERY statement for you to review rather than fired from the pane.

Did the query use the primary key?

ClickHouse's primary key is sparse: it does not find individual rows, it lets the server skip whole granules of data. The question for a slow query is therefore "how much was skipped?", and EXPLAIN indexes = 1 answers it:

EXPLAIN indexes = 1
SELECT count()
FROM web.requests
WHERE event_date = '2026-09-25' AND status = 500;

Look at the PrimaryKey and Partition sections of the read step. They show how many parts and granules were selected out of the total. If the filter does not start with the leading columns of the table's ORDER BY key, you will typically see nearly every granule selected, which is a full scan by another name.

SQLly captures this as EXPLAIN json = 1, indexes = 1 and parses it into the same plan views it uses for other engines, including the index analysis of how many parts and granules were pruned. ClickHouse plans in SQLly are estimated only; there is no actual-plan capture for ClickHouse.

A database without transactions

ClickHouse is not a transactional database in the way PostgreSQL or SQL Server is, and a client should not pretend otherwise. SQLly's rollback wrap safety setting, which runs a script inside a transaction and rolls it back, is refused up front on ClickHouse with a clear reason, because there would be nothing to roll back. That makes the other guardrails more important:

• Make exploratory connections read-only, so SQLly refuses anything that is not a read before it is sent.
• Back that with a ClickHouse user whose grants or readonly setting prevent writes, because a client-side check is a lexical gate, not a permission.
• Give production an environment and a color, so you always know which cluster you are looking at.

For the same reason, some familiar tools do not have a ClickHouse counterpart in SQLly: editing rows in the grid, actual plans, and the Index Analyzer are marked unavailable with a reason that names ClickHouse.

What else works

The explorer and IntelliSense read system.tables, system.columns and friends, so completion knows your tables and columns. Beyond querying, the ClickHouse-aware tools include Disk Usage from system.parts and system.disks, a Server Dashboard from system.metrics and system.events, OPTIMIZE TABLE ... FINAL in the Maintenance menu, Schema Compare and Data Compare between two ClickHouse connections, a test data generator that understands Nullable, LowCardinality, arrays and maps, Data Transfer into ClickHouse that creates MergeTree tables, and a read-only view of users, roles and grants that scripts CREATE USER and GRANT statements for review.

A word on OPTIMIZE TABLE ... FINAL, since it is one click away: it forces a merge of all parts, which rewrites data and can be expensive on a large table. It is useful for collapsing a ReplacingMergeTree before a one-off check, not as routine maintenance. For deduplicated reads, SELECT ... FINAL is usually the better tool.

If ClickHouse is part of a mixed estate, the practical benefit of a desktop client is having it next to your PostgreSQL and SQL Server connections in one explorer, with the same shortcuts and the same result grid. Download SQLly, add a ClickHouse connection on port 8123, and start with the system.parts query above.