Skip to content
dbprint

ClickHouse

Terminal window
$ pip install 'dbprint[clickhouse]'

The extra carries clickhouse-connect>=1.7. Absent driver: clickhouse-connect is not installed. Install dbprint with the [clickhouse] extra: \pip install dbprint[clickhouse]`.A failed connection:could not connect to ClickHouse at :/ as : `. There is no session-level read-only setting the adapter applies — what a connection can do is entirely the connected user's own grants.

Fully-qualified names are database.table — two segments; ClickHouse has no schema tier.

Privileges

system.tables and system.columns are named exceptions to select_from_system_db_requires_grant and need no grant at all. system.data_skipping_indices is not an exception:

GRANT SELECT ON system.data_skipping_indices TO dbprint_ro;
GRANT SELECT ON my_db.* TO dbprint_ro;


-- Only when a table is narrowed to a fraction; see "When dbprint writes".
GRANT CREATE ARBITRARY TEMPORARY TABLE ON *.* TO dbprint_ro;
PrivilegeOnNeeded for
SELECTsystem.data_skipping_indicesindex metadata; system.tables and system.columns need no grant
SELECTeach table profiledDDL and statistics — note this grant is enforced at column level, so a column-list grant makes SELECT * return nothing rather than error
CREATE ARBITRARY TEMPORARY TABLEGLOBAL levelthe sampled-table copy only, and the narrower CREATE TEMPORARY TABLE is not enough — the copy declares ENGINE = MergeTree, and that distinction is settled by the server source, not by any documentation page

Independently of every grant above, the readonly server setting gates the write: readonly = 1 refuses the sampled copy outright; readonly = 2 permits it. Two of ClickHouse's own documentation pages disagree on whether the system database is always readable — the server-settings page is the more specific, and is the one that matches what system.tables/system.columns actually do here.

What an under-privileged user does

Missing SELECT on system.data_skipping_indices fails index enumeration for that table alone; missing SELECT on the table itself fails DDL and statistics together, since both read the same grant. A readonly = 1 connection reaches every catalog read and fails only where the sampled copy is written — see "When dbprint writes" below.

Sampling

ConstructSAMPLE p against a declared SAMPLE BY key
Seededno — determinism depends on the table declaring a SAMPLE BY key at creation, which is out of dbprint's control
looks_like sub-drawfixed-size SAMPLE n, degrading to a direct scan when the table declares no sampling key

The listing reads each table's sampling_key from system.tables as it enumerates, so whether a table can be sampled is known before any statement reads it. The row-for-row guarantee PostgreSQL and duckdb give does not apply here: coherence with the rest of a sampled ClickHouse profile is population-level only.

When dbprint writes

Only one thing makes dbprint write, and only under one condition: profile.read.tables.materialize is on by default, and it fires only for a table read in sample mode — by a fraction, or by a max_rows ceiling that resolves below the table's size, at whatever level the ceiling is set. A project that narrows nothing, which is what dbprint init scaffolds, never writes. A filter is a predicate rather than a fraction and never materializes.

Where it does fire, dbprint checks that sampling key before touching the server. A table with no declared SAMPLE BY key (or that is not a MergeTree-family table) is refused outright:

table 'my_db.germination_trial': connection 'primary' (clickhouse) could not materialize its
sample of 0.25 (table 'my_db.germination_trial' declares no SAMPLE BY key (or is not a
MergeTree-family table), so ClickHouse's SAMPLE clause is not available on it at any
fraction.), and this adapter's per-statement fallback cannot be seeded into agreement across
statements. Narrow with mode: filter instead of a sample.

Where the table is samplable, the copy is created as CREATE TEMPORARY TABLE <name> ENGINE = MergeTree ORDER BY tuple() AS SELECT * FROM (SELECT * FROM <table> SAMPLE p), session-scoped, dropped as soon as the table finishes. read.tables.materialize: false never falls back on ClickHouse either — no unmaterialized SAMPLE read is guaranteed to read the same rows twice, so a sample-narrowed table with the copy switched off is refused the same way, before any statement runs:

table 'my_db.germination_trial': connection 'primary' (clickhouse) reads it
with read.tables.materialize: false, and this adapter's per-statement sampling
construct cannot be seeded into agreement across statements. Set
read.tables.materialize: true for this table's rule,
or narrow with mode: filter instead of a sample.

On ClickHouse, materialize therefore decides whether a sample-scoped table is profiled at all, not merely how faithfully.

Identifiers

Both path segments fold to lowercase for the FQN, and every statement addresses the table by the catalog's own spelling — system.* compares case-sensitively, so a folded name would address a table that does not exist. Two tables whose names differ only by case collide on one path and are refused with case-collides-with-<other>, like every other adapter. Column names fold to their lowercase map key and keep their catalog spelling in physical_name; ClickHouse holds two columns differing only by case, and such a table is refused at column grain (SPEC 1.5.2) while the rest of the run profiles.

Objects that are never listed

Some engines hold no rows a read can describe, so dbprint leaves them out of the print — no file, no manifest entry, no failed_tables mark — and an include naming one matches nothing:

  • Queue and stream engines — Kafka, RabbitMQ, NATS, FileLog, S3Queue, AzureQueue and the Redis Streams engine. A read removes what it reads from the queue, so profiling one would consume messages a pipeline is waiting for; where direct reads are off, it fails every run.
  • Null — stores nothing; a read is always empty.
  • LiveView and WindowView — deliver results through WATCH or a target table, and a window view's own .inner. storage tables hold partial aggregation states. A window view writing TO a table of yours leaves that table listed.
  • GenerateRandom — returns random rows without end, so its first count would run until the statement limit.

Remote engines

A table whose engine fetches its rows from another system on every read is listed with external: true (SPEC 2.2.20). It is always described from the catalog alone: no statement reaches the source, and an unreachable source does not fail the run. The engines this covers:

  • Object storage and lakes — URL, S3 (and COSN, OSS, GCS), AzureBlobStorage, HDFS, Hive, and DeltaLake, Hudi, Iceberg and Paimon in every storage variant.
  • Other databases — MySQL, PostgreSQL, ODBC, JDBC, MongoDB, Redis, SQLite, ArrowFlight, YTsaurus, ExternalDistributed.
  • Executable and ExecutablePool — their script runs on every read.

A rule writing any other read.tables.mode for it is refused before any table is read, with exit 1, because the read would reach another live database. Distributed, Remote, Merge, Dictionary and the engines that store rows locally are ordinary tables.

Secrets in DDL

ddl.sql shows every secret argument of an engine or table function as [HIDDEN]. Where your user profile turns format_display_secrets_in_show_and_select on, dbprint turns it off for its own reads; a server that refuses the override fails the table rather than publish the secret. A password or key written anywhere else in the DDL — a URL's user:password@, a default, a comment, a view body — is masked by the credential masks every adapter's DDL passes through (SPEC 2.1.3).

What it cannot deliver

  • relationships — always empty. REFERENTIAL_CONSTRAINTS is documented permanently empty, and a FOREIGN KEY clause in CREATE TABLE is accepted and silently discarded.
  • unique_keys — always empty. ClickHouse's PRIMARY KEY admits duplicate values, so it is not a declared-unique group under SPEC 2.6.7.
  • depends_on — always absent on every view. The dependency tables answer only the reverse edge, and only for materialized views.
  • Index columns — always empty for a data-skipping index. One covers an expression rather than a column list, so columns is empty rather than guessed.
  • Column collation — always the fixed constant binary. ClickHouse has no server-side collation model.

Column types

TypeProfiled asWhat to know
SimpleAggregateFunction(f, T)as Tsql_type keeps the declared spelling
AggregateFunction(f, T)unsupportedIt holds aggregation states, not values
Array(T)compositeAn array is never NULL; an empty one is the absent value, counted in empty_count
Tuple(...), Variant(...), DynamiccompositeAn unnamed tuple element is addressed by position (["1"]), a variant member by its type (.String)
Map(K, V)compositem['k'] returns 0 or '' for a missing key, never NULL; a key repeated within one map reports its first value
JSONjsonA null and an absent path are the same, so a key that is null everywhere is not listed; types keys are what JSONType returns
Point, Ring, LineString, Polygon, their Multi forms, GeometryspatialNo SRID and no validity: srids and invalid_count are absent
QBit(element, N)vectordimension is N

Merging engines

On ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree, CoalescingMergeTree, CollapsingMergeTree and VersionedCollapsingMergeTree, with or without a Replicated or Shared prefix, the print counts the stored rows, which repeat a key until a background merge combines them. row_count, cardinality and grain describe those rows; query with FINAL for the merged ones, as the table's merging block and dbprint context both say. Where your user profile sets final, every read is already merged and the block says rows: merged.

Row counts

A table's size, and a sampled table's row_count, are count(), which ClickHouse answers from part metadata and, after a lightweight DELETE, from the deletion mask, so rows a lightweight DELETE removed are never counted, before or after the merge that drops them; system.tables.total_rows counts them until that merge. count() follows the session's row policies and final setting as every statement does. An engine whose system.tables.total_rows is NULL — TinyLog, a View — has no size, and size rules do not apply to it. A sampled table publishes row_count_method: exact.

Time zone

Every DateTime and DateTime64 value — range, percentiles, a listed value — is published as its UTC instant with a trailing Z, whatever zone the column declares and whatever zone the server or session runs in. Cutting a value into days happens in a frame the DDL fixes: a column declaring a zone (DateTime('Europe/Berlin')) has its midnights counted for quantized_count and its timeline bucketed in that zone, and a column declaring none is cut in UTC. A day bucket of a declared-zone column therefore starts at that zone's midnight, written as a UTC instant, and across a daylight-saving change two consecutive day buckets start 23 or 25 hours apart.

Temporal percentiles

A temporal percentile is read from the column's non-null values sorted in one statement (arraySort(groupArray(col))), so it keeps the column's type and declared time zone and renders exactly as range does. The sort holds the column's non-null values in memory for that statement. A plain DateTime column publishes whole seconds.

Namespaces

database is optional. Omitted, the connection reads every database system.databases shows the user, less system, INFORMATION_SCHEMA and information_schema, one system.tables read each, so a remote-engine database whose listing fails is skipped with a warning. GRANT SELECT ON <db>.* is needed per database profiled.

Statement timeout

run.statement_timeout is sent with every request as the max_execution_time setting, with timeout_before_checking_execution_speed at zero so the limit is wall-clock rather than a projection of the remaining time. ClickHouse checks it at designated points only — not while merging aggregation states or during most of query analysis — so a statement can overrun it. A statement that exceeds it is cancelled by the server.

Parallelism

run.parallelism: N opens N HTTP sessions and profiles up to N tables at once. A sampled table's temporary copy belongs to its session, and every read of it runs there. Concurrent statements count against the server's max_concurrent_queries and any per-user limit.

Reference