# 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. Source: https://docs.ptah.run/edge/databases/clickhouse/ import { Tabs, TabItem } from '@astrojs/starlight/components'; 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](#roles-and-grants) states. Review generated SQL and the [capability gates](../../reference/capabilities/) before adopting a workflow on ClickHouse. ## 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. ```sql title="schema.sql" CREATE TABLE events ( id UInt64, payload String ) ENGINE = MergeTree ORDER BY id; ``` ```console ptah schema render --schema-file schema.sql --dialect clickhouse ``` ```yaml title="schema.yaml" tables: events: columns: id: type: UInt64 primary: true not_null: true payload: type: String not_null: true ``` ```console ptah schema render --schema-file schema.yaml --dialect clickhouse ``` ```hcl title="schema.hcl" table "events" { column "id" { type = "UInt64" } column "payload" { type = "String" } primary_key { columns = [column.id] } } ``` ```console ptah schema render --schema-file schema.hcl --dialect clickhouse ``` ```text title="schema.dbml" Table events { id UInt64 [pk, not null] payload String [not null] } ``` ```console ptah schema render --schema-file schema.dbml --dialect clickhouse ``` ```go title="models/events.go" package models //ptah:schema:table name="events" type Event struct { //ptah:schema:field name="id" type="UInt64" primary="true" not_null="true" ID uint64 //ptah:schema:field name="payload" type="String" not_null="true" Payload string } ``` ```console ptah schema render --root-dir ./models --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](../../schema/work-with-a-source/) for the shared workflow and [DBML limitations](../../schema/dbml/#what-it-cannot) for objects a DBML source leaves outside its scope. External programs emit [SQL, YAML, or HCL](../../schema/orm-and-external/); they use the same syntax as the corresponding file format. ## 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 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 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: ```sql 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 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: ```sql INDEX idx_e_payload payload TYPE bloom_filter(0.01) GRANULARITY 64 ``` Add this top-level `indexes` entry: ```yaml indexes: idx_e_payload: table: events fields: [payload] type: bloom_filter(0.01) platform: clickhouse: granularity: 64 ``` Add this block inside `table "events"`: ```hcl index "idx_e_payload" { columns = [column.payload] type = "bloom_filter(0.01)" platform "clickhouse" { override "granularity" { value = "64" } } } ``` Add this block inside `Table events`. It creates a `minmax` index with granularity 1: ```text title="DBML index" Indexes { payload [name: 'idx_e_payload'] } ``` Place the annotation on the `Payload` field, beside its field annotation: ```go //ptah:schema:index name="idx_e_payload" fields="payload" type="bloom_filter(0.01)" platform.clickhouse.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](https://clickhouse.com/docs/reference/statements/alter/skipping-index). 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 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: ```sql 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: ```sql 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: ```sql 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 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: ```sql 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; ``` ```yaml views: event_count: body: SELECT count() AS c FROM events matviews: event_stats: body: SELECT count() AS c FROM events ``` ```hcl view "event_count" { as = "SELECT count() AS c FROM events" } materialized "event_stats" { as = "SELECT count() AS c FROM events" } ``` ```go //ptah:schema:view name="event_count" body="SELECT count() AS c FROM events" type EventCount struct{} //ptah:schema:matview name="event_stats" body="SELECT count() AS c FROM events" type EventStats struct{} ``` DBML has no view or materialized-view declaration. Use another format for these objects. Materialized views do too. Ptah emits `CREATE MATERIALIZED VIEW ENGINE = MergeTree ORDER BY tuple() AS `, 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: ```sql CREATE MATERIALIZED VIEW event_stats REFRESH EVERY 1 HOUR ENGINE = MergeTree ORDER BY tuple() AS SELECT count() AS c FROM events; ``` Go annotations: ```go //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: ```yaml matviews: event_stats: body: SELECT count() AS c FROM events refresh: EVERY 1 HOUR ``` HCL uses a string on a `materialized` block: ```hcl 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 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 ` 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`](../../schema/validate-and-format/) 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 .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 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 ` 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: ```sql -- 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 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: ```sql CREATE ROLE ptah_reader; GRANT SELECT ON ptah_test.events TO ptah_reader; ``` ```yaml roles: ptah_reader: {} grants: ptah_reader_events: role: ptah_reader privileges: [SELECT] on_table: ptah_test.events ``` ```hcl role "ptah_reader" {} permission { to = role.ptah_reader for = "ptah_test.events" privileges = [SELECT] } ``` ```go //ptah:schema:role name="ptah_reader" type PtahReaderRole struct{} //ptah:schema:grant role="ptah_reader" privileges="SELECT" on_table="ptah_test.events" type PtahReaderEventsGrant struct{} ``` DBML cannot declare roles or grants. Rendering the selected source emits: ```sql 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 ON FROM `. `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 TO ` 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 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: ```sql title="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; ``` ```hcl title="schema.hcl" table "orders" { column "id" { type = UInt64 } column "tenant_id" { type = UInt64 } primary_key { columns = [column.id] } } row_policy "tenant_rows" { on = table.orders using = sql("tenant_id = 1") to = "ALL EXCEPT admin" as = "RESTRICTIVE" } ``` ```yaml title="schema.yaml" tables: orders: columns: id: type: UInt64 primary: true not_null: true tenant_id: type: UInt64 not_null: true row_policies: tenant_rows: table: orders using: tenant_id = 1 to: ALL EXCEPT admin as: RESTRICTIVE ``` ```go package models //ptah:schema:table name="orders" //ptah:schema:rowpolicy name="tenant_rows" table="orders" using="tenant_id = 1" to="ALL EXCEPT admin" as="RESTRICTIVE" type Order struct { //ptah:schema:field name="id" type="UInt64" primary="true" not_null="true" ID uint64 //ptah:schema:field name="tenant_id" type="UInt64" not_null="true" TenantID uint64 } ``` DBML cannot declare ClickHouse row policies. Render the SQL example: ```console 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 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: ```text 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: ```console $ 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 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 - Which release lines are declared and at what support level: [Database support matrix](../support-matrix/). - Capability keys per dialect: [Capabilities](../../reference/capabilities/). - Choosing an input format and source selector: [Work with a desired schema](../../schema/work-with-a-source/).