Skip to content
PtahDocs
edge

Graphic preview

100%

Scroll or drag to inspect details. Pinch or use + and − to zoom.

Page type: reference

ClickHouse

ClickHouse in Ptah - the capability-limited preset, the MergeTree round trip, views and materialized views, roles and grants, and the revision table's engine.

ClickHouse support is capability-limited. The preset models enums as inline Enum8/Enum16 column types: a column typed with a declared enum, or typed ENUM with its values, is written as Enum8('draft' = 1, 'live' = 2), numbered from 1 in declaration order, and as Enum16 past 127 values. Foreign keys and enforced CHECK constraints are outside the preset. Roles and grants are managed declaratively, within the boundaries Roles and grants states. Review generated SQL and the capability gates before adopting a workflow on ClickHouse.

Schema sources

Section titled “Schema sources”

Choose SQL, YAML, HCL, DBML, or Go annotations for the desired schema. These examples declare the same events table with a MergeTree engine sorted by id. Save the selected file, then run the command below it to preview SQL without connecting to a database.

schema.sql
CREATE TABLE events (
  id UInt64,
  payload String
) ENGINE = MergeTree ORDER BY id;
Terminal window
ptah schema render --schema-file schema.sql --dialect clickhouse

SQL expresses ClickHouse engine clauses directly. YAML and HCL use table platform properties for clickhouse; Go annotations use platform.clickhouse.* attributes. DBML uses the default MergeTree engine and the declared primary key for sorting. It cannot declare engine overrides, skipping-index granularity, views, roles, or grants. Use another format when the desired schema must own those settings or objects.

The supported declarations differ by format:

Format Engine overrides and index granularity Views, roles, and grants Refresh schedules Row policies
SQL Yes Yes Yes Yes
YAML Yes Yes Yes Yes
HCL Yes Yes Yes Yes
DBML No No No No
Go annotations Yes Yes Yes Yes

The same source selectors work with schema comparison and migration planning. See Work with a desired schema for the shared workflow and DBML limitations for objects a DBML source leaves outside its scope. External programs emit SQL, YAML, or HCL; they use the same syntax as the corresponding file format.

Dev-database cleanup

Section titled “Dev-database cleanup”

Dev-database replay cleanup requires ClickHouse 24.11 or newer and global SHOW DATABASES plus SHOW TABLES. Because ordinary views have no complete catalog dependency metadata, cleanup also requires that other user databases contain no view-like or dictionary objects and no Buffer, Distributed, or Merge tables.

The narrower reset used by shadow verification, by the schema apply dev rehearsal, and by schema clean removes tables, views, and materialized views. A materialized view goes as one object, with DROP VIEW, so its inner storage table leaves with it rather than being dropped out from under it. Live views and window views are left alone, matching what the ClickHouse reader reports; use the database-realm cleanup above when a database has to be emptied completely.

Tables and their engine clauses

Section titled “Tables and their engine clauses”

A MergeTree table’s sorting key is carried through the read, so a table Ptah creates reads back as itself: the primary-key flag comes from system.columns.is_in_primary_key, and the renderer derives the ORDER BY a MergeTree engine requires from it. Where a table declares both a narrower PRIMARY KEY and a wider ORDER BY, the sorting key is carried separately, so a description does not silently sort by fewer columns than the table it describes. Before this the read dropped the key entirely: a declaration carrying primary="true" differed from its own table on every comparison, so ALTER TABLE ... MODIFY COLUMN was re-planned forever, and ptah db read exited 2 against any ClickHouse database Ptah could create because the description it produced could not be rendered (stokaro/ptah#1603).

A declaration states the key on the engine, as PRIMARY KEY or ORDER BY, and the comparison reads the key columns from those clauses as the server does: the PRIMARY KEY clause, or ORDER BY without one, and every column either names, inside an expression too. A table identical to its SQL or YAML declaration therefore compares as identical.

A declaration whose key moves a column into or out of the table’s primary key is refused when the plan is made. ClickHouse fixes a MergeTree table’s primary key when the table is created: MODIFY PRIMARY KEY is not a statement, and MODIFY ORDER BY has to keep the primary key as a prefix of the sorting key. Create the table again with the new key and copy its rows.

Every other clause of the engine is carried with it, so a table read and replayed is the table that was read rather than a MergeTree that happens to hold the same columns. The engine is taken with its parameters — a ReplacingMergeTree(ver) stays one, and does not fall back to the default MergeTree, which would merge on nothing — and so are the PARTITION BY, PRIMARY KEY, ORDER BY, SAMPLE BY, TTL and SETTINGS clauses. The TTL is the one that changes what the data does rather than how fast it is read: a table replayed without it keeps rows it was configured to delete.

A MySQL-family storage engine is refused rather than rendered. A table’s engine reaches the renderer through the same key both families fill, so engine = "InnoDB" on a struct, and ENGINE=InnoDB in a SQL source, arrive indistinguishable from a value written for ClickHouse. Rendering one produces statements the server rejects with Code: 56 ... Unknown table engine, and because the whole CREATE TABLE is lost the author gets no table at all rather than a weaker one. Ptah names the engine instead. Declare a ClickHouse engine with SQL’s ENGINE clause, YAML or HCL table platform properties, or platform.clickhouse.engine in Go annotations. Removing the engine takes the MergeTree default.

Only names that belong to the MySQL and MariaDB families are refused, and only those they do not share with ClickHouse — Memory, Merge and S3 are real ClickHouse engines and render as written. ClickHouse’s engine set grows with every release, so nothing here decides whether a name is a ClickHouse engine: an engine Ptah has never heard of renders unchanged.

Before adopting the round trip, know that both of these come from the server’s answer rather than from the declaration’s text:

  • The settings are the server’s resolved values, not the ones a CREATE named. system.tables reports index_granularity = 8192 for a table that never mentioned it, so a description carries that value and a replay pins it. It is the value the source table is actually running with. A declaration that leaves index_granularity out does not manage it, so it does not differ from a table that carries it.
  • A clause keyword is a legal column name, and the two are told apart by position. A table sorted by a column named settings or ttl is read as what it is; the clause is only recognized where a clause can start.

Storage changes

Section titled “Storage changes”

Ptah changes the storage clauses of an existing MergeTree table in place where ClickHouse has an ALTER TABLE for the change, and refuses the others by name. It does not create the table again and copy its rows, because that moves data and rows written during the copy can be lost. To change a refused clause, create a new table with it and copy the rows yourself. Omitting a clause from the declaration keeps the inspected one, and the plan needs a complete storage observation of the table.

Clause Planned as Rule
ORDER BY MODIFY ORDER BY Drops expressions from the end, or adds expressions over columns the same ALTER TABLE adds.
SAMPLE BY MODIFY SAMPLE BY, REMOVE SAMPLE BY The sampling key is one of the primary key’s expressions.
TTL MODIFY TTL, REMOVE TTL An explicitly empty TTL removes the rules.
SETTINGS MODIFY SETTING, RESET SETTING Settings ClickHouse fixes at creation are refused.
ENGINE, PARTITION BY, PRIMARY KEY Refused ClickHouse has no ALTER TABLE for them.

ClickHouse keeps the parts already on disk sorted by the old sorting key. A new key may therefore drop expressions from its end, and may add an expression only over columns the same ALTER TABLE adds. Such a column cannot be Nullable or have a DEFAULT, MATERIALIZED or ALIAS value. When the key gains an expression, the plan adds every new column of the table in the statement that changes the key:

ALTER TABLE events ADD COLUMN region String, MODIFY ORDER BY (id, region);

The primary key stays a prefix of the sorting key. A table created without PRIMARY KEY keeps its old sorting key as its primary key after the change, which is what ClickHouse records and what a declaration that omits the primary key asks for. A new key that reorders the old one, drops an expression that is not last, or adds a column the table already has is refused by name. On an engine that merges rows by its sorting key, such as ReplacingMergeTree, dropping an expression lets rows that differed only in it merge into one, so the plan marks that change destructive.

ClickHouse refuses a sampling key that is not one of the primary key’s expressions, and allow_sampling_expression_not_in_primary_key is not a table setting on the supported lines, so Ptah refuses such a key when the plan is made.

Table settings compare as a set of name = value entries, so their order does not count. A setting the declaration adds or changes is set with MODIFY SETTING, and one it leaves out is reset with RESET SETTING. ClickHouse fixes some settings when the table is created, among them index_granularity, index_granularity_bytes, enable_mixed_granularity_parts, table_disk and the add_minmax_index_for_* family. A change to one of them is refused by name. A setting named twice in one SETTINGS clause is refused too, because the plan cannot know which value the table runs with.

Each changed clause is one statement, outside a transaction. Settings the change sets come first, then the sorting key, the sampling key and TTL, and settings it resets come last. Column additions the new clauses need run before them, and removals of columns only the old clauses use run after them. A column the table’s storage still uses after the change cannot be removed or modified by the same plan. When a TTL rule moves to a new column, for example, the plan adds that column, changes TTL, then removes the old column.

The reverse migration restores the captured clauses in place. It cannot restore rows or values TTL already expired or aggregated, undo TTL data movement or the merges and moves done under changed settings, or separate rows a merging engine combined while its key lacked a dropped expression. A reverse ClickHouse cannot run in place is refused when the migration is generated. Dropping the last sorting-key expression while its column stays has no reverse, because the reverse would add a column the table already has to the key; ptah schema apply, which plans no reverse, plans that change.

Data-skipping indexes

Section titled “Data-skipping indexes”

A data-skipping index declares its type and granularity as ClickHouse settings of the index. SQL uses TYPE and GRANULARITY; YAML and HCL use the index’s platform group for clickhouse. Go annotations use platform.clickhouse.type and platform.clickhouse.granularity. On ClickHouse the common type attribute names the skipping-index type too, and declaring the type both ways is refused. The type keeps its parameters, such as set(100) or bloom_filter(0.01).

Add the index to the events declaration above. SQL, YAML, HCL, and Go declare bloom_filter(0.01) with granularity 64. The DBML variant uses the creation defaults because DBML cannot set granularity:

Place the index inside the CREATE TABLE column list:

INDEX idx_e_payload payload TYPE bloom_filter(0.01) GRANULARITY 64

DBML cannot declare the granularity in this example. Use SQL, YAML, HCL, or Go annotations to manage both skipping-index settings.

A new index that declares neither setting is a minmax index with GRANULARITY 1. Index granularity counts table granules per index block; it is separate from the table’s index_granularity setting, which controls rows per granule. In Go annotations, YAML and HCL, a setting left out of the declaration of an existing index keeps the value the server reports. A .state suffix with the value default, such as platform.clickhouse.granularity.state="default", asks for the creation default instead. A SQL source is read as ClickHouse reads it: an INDEX ... TYPE ... clause without GRANULARITY means one granule, so comparing it with an index that has another granularity plans a change.

ClickHouse changes neither setting in place. A changed type or granularity is planned as ALTER TABLE ... DROP INDEX, then ADD INDEX with the declared key expression and settings. A key with several parts is written as a tuple, including one the server reports as a bare list, a, b, for INDEX i (a, b). The pair runs around a change to a column either expression reads. When the plan already rebuilds the index for another reason, that rebuild carries the new settings and no second pair is planned. ADD INDEX indexes data written afterwards; existing parts have no index data until a separate MATERIALIZE INDEX runs. The reverse migration restores the previous key and settings the same way and reports that it cannot restore materialized index data.

A declared key that names a column the table does not declare, such as a misspelled field, is refused before anything runs, for a new index and for a changed one: ClickHouse refuses the ADD INDEX, and a changed index is gone before the new one is added. A settings change is refused the same way when the plan also removes a column the declared key reads. Only a name that can be nothing but a column is checked: a bare key part, or a bare argument of a call, such as payload in lower(payload).

The comparison matches skipping indexes by name, and compares the key expression as well as the settings. A changed key is planned the same way, as DROP INDEX then ADD INDEX. ClickHouse stores a key in its own formatting: b * 2 + 1 reads back as (b * 2) + 1, LOWER(a) as lower(a), and tuple(a, b) as a, b. So spacing, case outside string literals, and parentheses that only group are not read as a change; a change that only regroups an expression or only changes the case of a name plans nothing.

Database inspection reads the type with its parameters, from the type_full column, and the granularity from system.data_skipping_indices. A server whose catalog has no type_full column is refused with a message that names the column; every release line Ptah tests has it. HCL and Go output write the settings as ClickHouse platform properties. A ClickHouse ADD INDEX or DROP INDEX operation sent to another target is refused. See the ClickHouse skipping-index reference.

A secondary index declaring a PostgreSQL or MySQL access method is refused the same way as a MySQL engine, and for the same reason: an index’s type reaches ClickHouse through the field those families fill, so USING GIN in a SQL source arrives here looking like a data-skipping type. Rendering it produces an ALTER TABLE the server rejects with Code: 80 ... Unknown Index type, and the index is not added. The ClickHouse owner refuses the type wherever it reads the settings, so comparison and planning refuse it too, before a settings change could drop the old index. Ptah names the type and points at the fix: declare a ClickHouse data-skipping type such as minmax, or drop the type to take the minmax default. As with the engine, the refusal reads the closed PostgreSQL and MySQL sets rather than deciding what a ClickHouse type is, so a type from a future release renders unchanged.

Column changes

Section titled “Column changes”

A changed column is planned as one ALTER TABLE ... MODIFY COLUMN that states the column’s type and, when the column declares one, its default. The default is in the statement because a MODIFY COLUMN that names only a type keeps the default the column already had.

A column’s type is compared as the server stores it, width included. A live Int32 declared Int64, UInt8 declared UInt16, Float32 declared Float64, Decimal(9, 2) declared Decimal(18, 2) and DateTime declared DateTime64(3) are each planned as a MODIFY COLUMN, and so is a change inside Nullable(...), LowCardinality(...), Array(...) or Map(...). A spelling the server stores under another name is the same type: Decimal32(2) is Decimal(9, 2), Boolean is Bool, SMALLINT UNSIGNED is UInt16, and Enum('a', 'b') is Enum8('a' = 1, 'b' = 2).

A portable type name is compared as the type Ptah writes for it, which is not always the type ClickHouse makes from the same word. A declared DATETIME is written as DateTime64(3), so a live DateTime column declared DATETIME is planned as a change. A ClickHouse type name, spelled the way ClickHouse spells it, is written as it stands: Int8 is an 8-bit column and DateTime a second-precision one, while the portable INT8 is Int64.

A narrowing is planned as well. Measured on 24.10 and 26.9, the server accepts MODIFY COLUMN d Int32 over an Int64 column and stores a value that does not fit wrapped: 30000000000 becomes -64771072. A decimal narrowing whose values do not fit fails the conversion and leaves the column unreadable. The plan’s safety report marks an integer narrowing between ClickHouse type names, and a decimal narrowing, as destructive; check the values before applying either.

A type that already admits NULL, such as LowCardinality(Nullable(String)), is not wrapped in a second Nullable, which ClickHouse refuses. A Nullable inside an array, a map or a tuple makes the elements nullable and not the column, so Array(Nullable(Int32)) is a column that is never NULL itself.

A default the declaration takes away is removed with its own statement, written before the one that states the type:

ALTER TABLE asn MODIFY COLUMN n REMOVE DEFAULT;
ALTER TABLE asn MODIFY COLUMN n Int32;

REMOVE DEFAULT comes first. A type the old default cannot take is refused while the default is still there. In a single ALTER, ClickHouse 24.10 keeps the default and reports no error. A down migration that takes a default away is planned the same way.

Making a nullable column NOT NULL needs a default for the rows that hold NULL. The server lines handle those rows differently. ClickHouse 26.3 and later refuse MODIFY COLUMN n Int32 without a DEFAULT, even on an empty table, and fill every NULL row from the DEFAULT when there is one. ClickHouse 24.10 and 25.8 accept the statement with or without a DEFAULT, change the column’s type, and then fail on a row that holds NULL, which leaves the table unreadable.

So a column that declares a default has its NULL rows filled first, on every line, by an update that waits for its mutation:

ALTER TABLE asn UPDATE n = '7' WHERE n IS NULL SETTINGS mutations_sync = 2;
ALTER TABLE asn MODIFY COLUMN n Int32 DEFAULT '7';

A column that declares no default is refused when the plan is made, on every line. Ptah does not invent a value such as 0 for the NULL rows. Give the column a default, which the NULL rows take.

The same rule applies to a down migration. ptah migrations generate refuses to make a NOT NULL column without a default nullable, because the down migration would have to make it NOT NULL again. To make such a column nullable, give it a default in one migration, then make it nullable in the next.

A nullable MATERIALIZED column made NOT NULL is dropped and added back as declared:

ALTER TABLE asn DROP COLUMN x;
ALTER TABLE asn ADD COLUMN x Int32 MATERIALIZED id + 1;

On 26.3 and later, MODIFY COLUMN fails the conversion for such a column and leaves it unreadable. The column holds only what its expression computes, so dropping it loses nothing, and the server computes the values again for the existing rows. An expression that can yield NULL, such as s + 1 over a nullable s, cannot back a non-nullable column on any line: reading a row where it yields NULL fails. An ALIAS column stores nothing, and MODIFY COLUMN changes its type on every line.

Views and materialized views

Section titled “Views and materialized views”

Plain views participate in the complete render, plan, and introspection cycle. Ptah emits CREATE VIEW, CREATE OR REPLACE VIEW, and DROP VIEW, preserving qualified names and query bodies, and reads ordinary views from system.tables. An empty query body, WITH CHECK OPTION, or DROP VIEW ... CASCADE fails instead of being ignored.

Add these declarations to the selected schema source from the table example:

CREATE VIEW event_count AS SELECT count() AS c FROM events;
CREATE MATERIALIZED VIEW event_stats
ENGINE = MergeTree ORDER BY tuple() AS SELECT count() AS c FROM events;

DBML has no view or materialized-view declaration. Use another format for these objects.

Materialized views do too. Ptah emits CREATE MATERIALIZED VIEW <name> ENGINE = MergeTree ORDER BY tuple() AS <query>, reads the object back from system.tables, and plans a changed query as a drop followed by a create. Several ClickHouse-specific points are worth knowing before adopting them:

  • A materialized view maintains itself from inserts into its source. Ptah declares no refresh strategy and emits no refresh: a declaration that carries refresh_strategy is refused when it is parsed, on every dialect.

  • ClickHouse’s own scheduled materialized views are managed. A declaration carries the schedule as ClickHouse spells it, and Ptah renders it, reads it back, and reconciles it:

    SQL, Go annotations, YAML, and HCL can declare a refresh schedule. For example:

    CREATE MATERIALIZED VIEW event_stats REFRESH EVERY 1 HOUR
    ENGINE = MergeTree ORDER BY tuple() AS SELECT count() AS c FROM events;

    Go annotations:

    //ptah:schema:matview name="event_stats" body="SELECT count() AS c FROM events" refresh="every 1 hour"
    type EventStats struct{}

    YAML uses the same clause in a matviews entry:

    matviews:
      event_stats:
        body: SELECT count() AS c FROM events
        refresh: EVERY 1 HOUR

    HCL uses a string on a materialized block:

    materialized "event_stats" {
      as      = "SELECT count() AS c FROM events"
      refresh = "EVERY 1 HOUR"
    }

    The server rewrites what it stores — EVERY 60 MINUTE becomes EVERY 1 HOUR, AFTER 90 SECOND becomes AFTER 1 MINUTE 30 SECOND — so a declaration is normalized to the stored spelling before anything compares it. Any spelling of the same schedule therefore converges instead of re-planning forever.

    A changed schedule is applied with ALTER TABLE <view> MODIFY REFRESH, which keeps the rows the view accumulated. A view gaining its first schedule or losing its last is a drop and a create instead, because the server refuses that transition in place: Alter of type 'MODIFY_REFRESH' is not supported by storage MaterializedView. Adding or removing APPEND is a drop and a create for the same reason: 24.10 answers Adding or removing APPEND is not supported and 26.9 answers Changing APPEND or INCREMENTAL is not supported. That drop empties the view, and the plan reports it as destructive.

    OFFSET, RANDOMIZE FOR, DEPENDS ON and APPEND are carried too, each at most once and in that order, as the server takes them. OFFSET belongs to EVERY alone, and an interval mixing calendar units with clock ones is refused where it is declared, both matching the server. 24.10 also refuses DEPENDS ON with AFTER. A refresh SETTINGS list is not modeled: a declaration cannot state one, and a view the server stores with one has a schedule Ptah could not read. A view created elsewhere with TO <table> has its schedule read up to the target table.

    The schedule is a ClickHouse setting of the view, not part of the shared materialized view. A SQL file states it between the view name and AS, as shown above.

    The storage clause may be left out. When it is written, it has to be ENGINE = MergeTree ORDER BY tuple(), the storage Ptah creates every view with. Another storage is refused, because Ptah does not model it and would create the view without it. All four sources state every view’s schedule. Omitting refresh, or writing an empty string in Go, YAML, or HCL, declares a plain view. Removing an existing schedule replaces the view and discards its stored rows. HCL export preserves a supported schedule.

    A schedule Ptah could not read is never read as no schedule. A source that declares one, or that declares the view plain, leaves the view undecided; a change to the view’s query from any source is refused, because the replacement would recreate the view without the schedule; ptah db read and other renders of the database write the view with a comment saying its schedule was not captured; and exporting the database to Go annotations or HCL is refused, because the export would declare the view plain. An account that may not read system.view_refreshes reads the database all the same, with every view’s schedule uninspected: a source that declares a schedule is then undecided, and one that declares plain views plans nothing for them.

    Other targets do not carry the schedule. A PostgreSQL or Oracle view rendered from the same declaration has none, and ptah schema validate --no-skipped does not report it, because the declaration was made for ClickHouse alone.

  • The storage clause is written explicitly rather than left to the server. ClickHouse 25.x and later accept a materialized view with no storage clause and supply MergeTree ORDER BY tuple() themselves; 24.x rejects it with ORDER BY or PRIMARY KEY clause is missing.

  • The drop is spelled DROP VIEW. DROP MATERIALIZED VIEW is a syntax error on ClickHouse, and DROP VIEW removes the view together with the inner table that stores its result.

  • POPULATE is never emitted, so a materialized view starts empty and fills from inserts into its source rather than from the rows already there. POPULATE is a one-shot argument that leaves no trace in the catalog, so nothing Ptah reads back could diff it. Backfill existing rows yourself if you need them.

  • A query written without a database qualifier is read back carrying one. ClickHouse resolves the query when the view is created and records what it resolved, so SELECT count() AS c FROM users comes back from system.tables.as_select as SELECT count() AS c FROM <database>.users. Comparison removes the qualifier the object’s own database added, the same way it does for an ordinary view, so an unchanged declaration is not reported as drift and is never planned as a drop and a create. A qualifier naming some other database is a real difference and is still reported.

  • A changed query is applied destructively. The drop takes the inner storage table and every row the view had accumulated, and the replacement omits POPULATE, so the view starts empty again and refills only from new inserts. ClickHouse does have ALTER TABLE <view> MODIFY QUERY, which keeps the stored rows, but it refuses any query whose output columns differ from the ones the storage table already has, and a Ptah declaration carries a query body with no column list to compare, so the planner cannot tell the two cases apart before the statement runs. Treat a materialized-view body change as a change that empties the view.

The TO <target table> form is not emitted: the shared schema model carries a name and a query, so it cannot name a separate target table.

Ptah models no storage for a materialized view but the ENGINE = MergeTree ORDER BY tuple() it creates every view with. A view the server stores any other way is read with its storage marked as not described: another engine, a sorting key, a PARTITION BY, SAMPLE BY or TTL clause, a setting other than the index_granularity = 8192 the server adds, or a TO target. The read takes the clause from system.tables.create_table_query, because system.tables reports every form with the same engine and the same as_select, and the reason names the clause:

-- materialized view analytics.totals: ptah.run/clickhouse/view-storage was not captured (the view stores its rows with ENGINE = SummingMergeTree ORDER BY c SETTINGS index_granularity = 8192, which Ptah does not model), and its statement does not carry it

schema inspect --format sql and ptah db read write that comment beside the view, whose statement still carries Ptah’s storage. A comparison reports the view undecided rather than synchronized, so schema drift against such a description fails instead of reporting no drift. A change to the view’s query is refused, because the drop and create it plans would recreate the view with Ptah’s storage, and for a TO view inserts would stop reaching the target table. HCL, YAML and Go annotations state no storage for a view, so an export to them carries none. Manage such a view’s storage outside Ptah.

Roles and grants

Section titled “Roles and grants”

Roles and grants complete the render, plan, apply, introspect, and diff cycle. A declared role and a database- or table-scoped grant are applied, read back from system.roles and system.grants, and compared to zero difference on the next run. SQL, YAML, HCL, and Go annotations declare them, with the grant scope written as database.table. These examples grant read access to the events table in the ptah_test database:

CREATE ROLE ptah_reader;
GRANT SELECT ON ptah_test.events TO ptah_reader;

DBML cannot declare roles or grants. Rendering the selected source emits:

CREATE ROLE IF NOT EXISTS `ptah_reader`;
GRANT SELECT ON `ptah_test`.`events` TO `ptah_reader`;

Roles are always planned before grants, because ClickHouse refuses a grant to a role it does not know. GRANT ... WITH GRANT OPTION is emitted when the declaration asks for it, and taking that option away again is the single statement REVOKE GRANT OPTION FOR ..., which leaves the privilege itself in place. Removing a grant emits REVOKE <privileges> ON <scope> FROM <role>. DROP ROLE IF EXISTS has a rendering for a schema source that spells one out, but comparison never plans it — see “Roles are never dropped” below.

What Ptah manages here is roles and grants, and nothing else in ClickHouse’s access control:

Not managed What that means for you
Users No user is created, altered, or read, so no credential enters a description, a plan, or a log. Provision users outside Ptah and grant them a managed role.
Role membership GRANT <role> TO <role> is not modeled. Ptah reads and writes privilege grants only.
Quotas, settings profiles Outside the schema model entirely; a declaration cannot express them and a read does not report them.
Column-scoped grants GRANT SELECT(id) ON db.t is refused when declared and excluded when read. Grants are managed at database and table scope.
Wildcard and global scopes *.* and a wildcard database are refused. Such a grant reaches objects no declared schema describes.
Privilege names the server rewrites ALL, CREATE, DROP, SYSTEM, SYSTEM FLUSH, ACCESS MANAGEMENT, SHOW ACCESS, SHOW FILESYSTEM CACHES, and — at table scope only — SHOW and ALTER. See below.

Five refusals arrive before anything on the server changes, and the reason for each is worth knowing in advance:

  • A ClickHouse role carries no attributes. system.roles is (name, id, storage), so a declared password, login, superuser, createdb, createrole, or replication is refused rather than dropped. Dropping a password would leave you believing a credential was set on an object that cannot hold one. ALTER ROLE is refused for the same reason: there is nothing to alter.
  • The server absorbs a narrower grant into a broader one. Granting SELECT on db.* and on db.t leaves one row for db.*, in either order, and the table-level grant is recorded nowhere. Declaring both is refused, because the pair could never converge: the absorbed grant would read as missing on every inspection and the plan would re-issue it forever. Declare the broader scope.
  • A grant scope must be qualified as database.table. An unqualified table name is refused. Rendering is offline and has no current database to resolve it against, and resolving an access-control decision against whichever database a session happens to have selected is not a formatting mistake. A trailing dot such as shop. is refused too, rather than read as the whole database: a typo must not widen one table’s privilege to every table.
  • A grant must name a role the same schema declares. ClickHouse resolves a grantee by name across users AND roles, with no syntax to say which is meant. If nothing of that name exists the GRANT fails partway through a migration with UNKNOWN_ROLE; if a user of that name exists it succeeds and lands on the user, where the reader never sees it again — the plan re-issues it forever and a real account quietly holds a privilege nobody declared for it. Declaring the role removes both outcomes, because Ptah creates it in the same plan. One case this cannot cover: if a live ClickHouse user shares a name with a declared role, resolution still prefers the user. Do not name a role after an account.
  • Some privilege names are groups the server rewrites. GRANT CREATE ON db.* records four rows — CREATE DATABASE, CREATE TABLE, CREATE VIEW, CREATE DICTIONARY — and never reads back as CREATE, so a schema declaring it can never converge. GRANT ALL records 45 individual rows on 26.7 and 39 on 24.10. GRANT SHOW ACCESS is stored as SHOW ROW POLICIES. GRANT SHOW FILESYSTEM CACHES is accepted and records nothing at all, which would tell you a grant applied while the role held no privilege. Declare the individual privileges instead. Group names that do read back as written stay declarable — ALTER TABLE, ALTER VIEW, ALTER COLUMN, ALTER INDEX, ALTER STATISTICS, ALTER PROJECTION, ALTER CONSTRAINT, SYSTEM SENDS, and SHOW and ALTER on a database scope. A name the server itself refuses, such as INTROSPECTION or SYSTEM RELOAD, needs no gate here: ClickHouse answers Code: 509 ... cannot be granted on the database level, which names the problem more precisely than Ptah could.

Three more boundaries shape what a run does:

  • Roles are never dropped. A role that exists on the server and not in the schema is named in the plan and left alone. A ClickHouse role is server-wide and may carry grants outside the managed schema, so removing it is not Ptah’s decision to make. A read reports only the roles the described grants name, and leaves out roles defined in the server’s configuration files, whose storage is users_xml, because SQL does not own those.
  • A partial revoke fails the comparison. GRANT SELECT ON db.* TO r followed by REVOKE SELECT ON db.t FROM r leaves two rows in system.grants, and the role’s effective privileges are the first minus the second. A declaration cannot express an exception, so on a managed role Ptah refuses instead of comparing equal and reporting convergence. Remove the partial revoke, or stop declaring the role.
  • An account that may not read the access catalog still gets a read. Reading system.roles and system.grants needs a privilege reading a table does not, and an account holding only SELECT, SHOW TABLES and SHOW COLUMNS is answered Code: 497 ... (ACCESS_DENIED) by both. Rather than failing the whole read — which would break reading a schema that declares no role at all — Ptah describes everything else and records that it did not look. Comparison then withholds every declared role instead of planning a CREATE ROLE it could not verify, and nothing destructive can follow, because removal is decided from live rows and there are none. ptah-compat schema inspect says so on stderr.

Two operational notes. ClickHouse RBAC statements carry no ON CLUSTER clause, so on a cluster they affect the connected replica only; Ptah does not model cluster propagation, and a multi-replica deployment needs its own arrangement for that. And a ClickHouse role is server-wide rather than database-scoped, so a role created against a dev or throwaway database outlives the database that workflow drops.

The connected account needs the privileges these statements require — ROLE ADMIN plus GRANT OPTION on what it grants. The docker-compose.yaml in this repository configures CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1, which is what gives its ptah_user that authority.

Row policies

Section titled “Row policies”

A row policy filters the rows of one table that the users and roles it names may read. SQL, HCL, YAML, and Go annotations can declare one:

schema.sql
CREATE TABLE orders (
  id UInt64,
  tenant_id UInt64
) ENGINE = MergeTree ORDER BY id;


CREATE ROW POLICY tenant_rows ON orders
FOR SELECT USING tenant_id = 1
AS RESTRICTIVE TO ALL EXCEPT admin;

DBML cannot declare ClickHouse row policies.

Render the SQL example:

Terminal window
ptah schema render --schema-file schema.sql --dialect clickhouse

Policy and table names may be quoted; a table may name its database. ALTER ROW POLICY updates a policy declared earlier in the same file or an earlier file of the schema load. It changes only the clauses it states: USING NONE clears the filter, and TO NONE clears the recipients. DROP ROW POLICY is accepted as migration syntax and refused in a desired schema; omit the declaration to request removal during comparison.

SQL sources refuse IF NOT EXISTS, replacement declarations, ON CLUSTER, access-storage clauses, renames, multiple policies or tables in one statement, and backslash escapes in names. Use doubled quotes for embedded quote characters. Unsupported clauses are reported before planning.

  • using is the filter. Without one, the policy admits every row to the users it names.
  • to names users and roles, ALL, ALL EXCEPT names, or NONE. Without one, the policy applies to nobody, as ClickHouse’s own default does.
  • as is PERMISSIVE, the default, or RESTRICTIVE. A user reads the rows that pass at least one permissive policy and every restrictive one.

HCL uses a row_policy block with on pointing to its table. The using attribute accepts a string or sql(...); to is a string with the same grammar as YAML. Quote a literal role named ALL with backticks inside that string. HCL export preserves the policy and refuses an incomplete read of policies. The PostgreSQL policy block does not declare a ClickHouse row policy. The compatibility CLI refuses row_policy in strict CE mode, even when the dev database uses another dialect.

The directive and HCL block bind the policy to ClickHouse, so another target leaves it out. In YAML, an entry of the row_policies key declares the same policy with the same attributes, keyed by its name. Row-level security scoped to ClickHouse is refused in both formats, since it declares PostgreSQL’s row-level security, and so is an enablement: ClickHouse has no table switch.

A plan creates, alters in place or drops each policy, and a read reports every policy on a table of the database, restrictive ones included. These are refused rather than dropped:

  • A write check. ClickHouse parses WITH CHECK and discards it, so the policy would filter reads and leave writes open.
  • A command other than SELECT. A row policy filters reads only.
  • A comment, which a row policy cannot hold.

A policy written ON db.* applies to every table of a database. Ptah does not read or plan it.

The revision table’s storage engine

Section titled “The revision table’s storage engine”

ClickHouse gives a table no engine unless the statement names one, and whether an unnamed one is legal at all is decided by the server’s default_table_engine — whose own default value is None. Against such a server the first migrations up stopped before the first migration:

code: 119, message: Table engine is not specified in CREATE query

Ptah names ENGINE = MergeTree in both revision layouts, which is what a server whose default is already MergeTree was producing, so an existing deployment sees the table it had.

A replicated deployment has to say so. With the default, the migration history is a node-local MergeTree on whichever node ran the migration, and every replica then reports itself consistent. Name the engine instead:

Terminal window
$ ptah migrations up --db-url 'clickhouse://…' \
    --migrations-engine "ReplicatedMergeTree('/clickhouse/tables/{shard}/schema_migrations', '{replica}')"

PTAH_MIGRATIONS_ENGINE carries the same value, and is the only spelling on the compatibility surface — ptah-compat registers no flag for it, because the community binary has none and the conformance cli-surface tier asserts flag parity against it. ptah-compat migrate apply, status and down read the variable; the native verbs take either.

Whichever command creates the table decides its engine, so both of these hold:

  • Every command that can create the table has to be given the same value. migrations up, down, status, tag, baseline, repair, and the maintenance verbs edit, rm and rebase all initialize metadata when they are the first to run. CREATE TABLE IF NOT EXISTS does not re-engine a table that already exists, so whichever command ran first decided it.
  • An engine the revision table cannot be is refused before any statement runs. On MySQL and MariaDB it must be InnoDB, which is the only engine that records an applied migration when the statement around it fails; SQL Server’s revision table has no engine clause at all. The refusal happens first because MySQL DDL commits: rejecting the table afterwards would leave one Ptah will neither use nor recreate.

Atlas revision metadata

Section titled “Atlas revision metadata”

Ptah creates the Atlas revision table with partial_hashes declared as text. ClickHouse reads a trailing NULL on a column definition as Nullable(T), so declaring that column JSON asks for Nullable(JSON), and no ClickHouse server handles that the way the rest of the Atlas revision layout needs:

  • Servers that reject Nullable(JSON) refuse the CREATE during type analysis (code: 43, Nested type JSON cannot be inside Nullable type). The rejection happens before the IF NOT EXISTS existence check, so an already-provisioned database does not escape it, and Atlas-format apply fails before any user migration SQL runs.
  • Servers that accept Nullable(JSON) coerce the JSON null Ptah writes into the JSON type and store {} instead. The value the author wrote is replaced, and the column can no longer be scanned into a string by an Atlas-compatible consumer.

Declaring the column text resolves to Nullable(String) on both, which stores the same JSON null every other dialect stores. This is stated by behavior rather than by a version cut-off on purpose: the release that changed Nullable(JSON) from rejected to accepted was not measured, and the column type is correct on either side of it.

CREATE TABLE IF NOT EXISTS does not alter a table that already exists, so a revision table created by an earlier Ptah against a server that accepted Nullable(JSON) keeps that column type and keeps storing {}. Ptah ignores a legacy {} on clean rows, but reads partial_hashes on dirty Atlas-format rows with committed progress. Malformed metadata, including {} where a digest array is required, fails closed during status or retry. Ptah does not rewrite the column automatically; alter it to Nullable(String) or recreate the table before recovering such a dirty row.

Setting a revision directly (SetAtlasRevision) is still unsupported on ClickHouse, because revision history cannot be updated atomically there.

Next steps

Section titled “Next steps”