ClickHouse in Monghoul

A ClickHouse connection lists its databases, then each database’s tables, views, materialized views and dictionaries in one list. ClickHouse has no schema level. The system and information_schema databases are hidden unless Show system databases is on.

The connection

• General: host, port, user and database, both default by default. The port is 8123 for plain HTTP and 8443 with TLS, and it follows the TLS mode until you type one. Paste URL takes a clickhouse://, http:// or https:// URL.
• Security: Verify with system CAs, Verify with a CA file, or Disable, which is plain HTTP. An SSH tunnel with a host key check.
• Advanced: the same access mode, timeouts, result cap and write protection as PostgreSQL.

The tree and Details

A column row shows its type and its marks: PK, sorting key, partition key, sampling key, materialized, alias and ephemeral. A table opens to its columns, then its skip indexes. A tooltip adds the table engine, the row count and the size when the engine keeps them.

Open Details shows Engine with the sorting, primary, partition and sampling keys, Columns, Skip indexes, Projections, Dictionary, Data flow for a materialized view, and Definition.

Drafts and Explain

Browse opens SELECT * FROM db.table LIMIT 100 and runs it. Parameters are written {name:Type}; the strip takes each field’s type from the text, and a date or time gets a calendar. A read may carry its own SETTINGS clause, except the settings Monghoul sets itself. FORMAT, INTO OUTFILE, ON CLUSTER, USE, KILL, a session SET, SYSTEM and access-control statements are refused with the reason.

Explain plans a SELECT without running it and adds the estimated granules. A read of 64 granules or more that selects every granule is graded Full read. Stop kills the query on the server; when your user may not kill it, the run is cancelled in the app only.

Writes

ClickHouse has no transaction, so a write that was sent cannot be rolled back. A mutation (ALTER UPDATE or DELETE) asks first and says ClickHouse cannot undo it. So do MODIFY COLUMN of a type, EXCHANGE TABLES, OPTIMIZE DEDUPLICATE and OPTIMIZE CLEANUP. A WHERE that names no column, such as WHERE toUInt8(1), counts as every row. An ALTER TABLE waits for its mutation, and one still running when the wait ends is reported as unknown: check system.mutations before you run it again.

A write that uses a parameter is refused, and a lightweight UPDATE or DELETE with no WHERE is refused. ClickHouse counts the rows of an INSERT only.

Grid edits

A result of one MergeTree-family table, named by its sorting key, can be edited. FINAL, SAMPLE, PREWHERE, GROUP BY, HAVING and LIMIT BY make a result read-only, and so does a row policy. Each stage commits on its own: deletes, then updates, then inserts. When a batch ends part of the way, each change says Applied, Not applied or Unknown. See Edit rows in a SQL result.

Import, Generate Data and the map

Import into Table writes batches of 5,000 rows with no transaction and has no upsert. Generate Data leaves out Array, Map, Tuple, JSON, geo and aggregate-state columns, which the server fills. The Database Map draws only Likely lines, because ClickHouse has no foreign keys, and a Scan cannot make a line Verified, because it has no unique key.

Cluster Monitor (Pro)

On a server with a cluster, the monitor reads every replica. The health strip rates Memory, Connections, Max Parts, Replicas, Replica Delay and Running Queries. Query log lists the finished and failed queries of the last hour, the first 200 by duration, rows read or memory. Kill takes two clicks and reaches the queries of the connected replica only.