Planning ClickHouse Changes

Every change between two ClickHouse schemas is classified before anything runs:

Class	Means
metadata only	the server records the change and touches no existing data
background rewrite	a mutation rewrites existing parts in the background, with no rollback
rebuild	ClickHouse cannot make the change to the existing table, and the data has to be copied into a new one; a plan refuses to make it in place

Two commands report the classification, and chant lifecycle plan reports it as each update’s disruption (metadata only is in-place, a rewrite rolling, a rebuild replace):

chant sql diff base.json head.json     # two chant build outputs, offline
chant sql plan prod schema.json        # a build output against the prod server

Both exit 2 when a change needs a rebuild. chant sql diff compares a pull request’s base and head builds with no server. chant sql plan reads the server sql.profiles.<env> binds, and asks that server’s formatter about any expression the normalization rules leave different, so the server’s own rewriting (INTERVAL 1 DAY as toIntervalDay(1), a+b*2 as a + (b * 2)) is not reported as a change.

chant sql plan also exits 2 when a declared database holds an object whose definition it cannot read. It names each one under Refused (unreadable in --json) instead of leaving it out. Dictionaries are read and planned like the other objects: any change but a comment replaces one (SQLCH245).

After the plan

The plan only reports. clickhouseApply makes the metadata-only and background-rewrite changes, and refuses a rebuild the same way the plan does, sending nothing for that object. A rebuild runs as ClickHouseRebuildOp, a gated migration Op that creates the new table, backfills it, verifies it and swaps it in: see Rebuilding a Table. Both commands print the Op declaration to start from for each refused table.

chant migrate plays no part in any of this. It translates a file from one lexicon’s format into another’s, such as a GitHub Actions workflow into GitLab CI, and does not run schema migrations.

Statements for a migration file

chant sql diff base.json head.json --statements prints the statements that take the base schema to the head one, offline: each one the applier would send for the same change, after a comment naming its object, rule and class. --json prints them as one document, which diffStatements(before, after) from @intentius/chant-lexicon-sql also returns:

-- events (analytics.events): SQLCH201 metadata
ALTER TABLE `analytics`.`events` ADD COLUMN region String DEFAULT 'eu' AFTER `user_id`;

-- events (analytics.events): SQLCH210 rewrite, waits for its mutation
ALTER TABLE `analytics`.`events` MODIFY COLUMN `kind` LowCardinality(String);

-- sessions (analytics.sessions): SQLCH220 made by ClickHouseRebuildOp, not a statement:
--   export const { op } = ClickHouseRebuildOp({ name: "rebuild-analytics-sessions", env: "<env>", table: "analytics.sessions", dualWrite: { mode: "materialized-view", cutoverColumn: "ts" } });

A rebuild is never DDL: it is a step naming ClickHouseRebuildOp, or for a view or a database, which the Op does not rebuild, a manual step. A column or object drop is a statement marked destructive. Each CREATE carries the project’s ownership marker, as the applier stamps it. The command exits 2 when a step is not a statement. A migration runner’s history table (schema_migrations, goose_db_version; see Importing a Live Server) is never dropped: the report names it in a hint. Two things the applier asks the server are left out: whether a difference is formatting only, and whether a database is empty before it is dropped.

--topology renders the statements for where they will run: single (the default), cluster:<name>, replicated or cloud. On a cluster every statement carries ON CLUSTER and a MergeTree-family table becomes Replicated*MergeTree with its Keeper path; in a Replicated database the tables are Replicated*MergeTree with no path and no ON CLUSTER. diffStatements(before, after, { topology }) takes the same choice; see Applying to a Server.

Identity and renames

An object is identified by its export name between two builds, so a changed name in the SQL under the same export is a rename. Against a server, which has no export names, an object is identified by database.name; a declaration renamed since the server last saw it says so with -- previously: <old name> before its CREATE.

A column is identified by its name. A renamed column says so on its own line:

CREATE TABLE events (
  event_kind LowCardinality(String), -- previously: kind
  ...

Without the hint the change is a drop and an add, and the report points out a drop and an add with the same type in the same place.

The rules

Id	Class	Change	Restriction
SQLCH200	create	Create an object	A new database, table, view, dictionary, function, user, role or row policy is created, or a grantee gets its first grants; nothing existing changes. ClickHouse docs
SQLCH201	metadata only	Add a column	ADD COLUMN only changes metadata: parts written before it read the column’s default until they are merged or the column is materialized. ClickHouse docs
SQLCH202	metadata only	Drop a column	DROP COLUMN removes the column’s data from every part; it is not undone. A column in a key cannot be dropped (SQLCH213 covers that case). ClickHouse docs
SQLCH203	metadata only	Change a comment	COMMENT COLUMN and MODIFY COMMENT change metadata only. ClickHouse docs
SQLCH204	metadata only	Add, drop or change a skip index	ADD INDEX and DROP INDEX change metadata; a new index covers parts written after it until MATERIALIZE INDEX rebuilds it for older parts. ClickHouse docs
SQLCH205	background rewrite	Change a TTL	MODIFY TTL is recorded in metadata, and with materialize_ttl_after_modify (on by default) the server recalculates the TTL over existing data as a mutation. ClickHouse docs
SQLCH206	metadata only	Change a table setting	MODIFY SETTING and RESET SETTING change metadata, for settings the server lets change after creation. ClickHouse docs
SQLCH207	metadata only	Change a column’s default	MODIFY COLUMN with a new DEFAULT, MATERIALIZED or ALIAS expression changes metadata; values already written keep the old default until the column is materialized. ClickHouse docs
SQLCH208	metadata only	Change a column’s codec	MODIFY COLUMN … CODEC applies to parts written afterwards; existing parts keep their codec until they are merged or rewritten. ClickHouse docs
SQLCH209	metadata only	Move a column	MODIFY COLUMN … FIRST | AFTER changes the column order in metadata only. ClickHouse docs
SQLCH210	background rewrite	Change a column’s type	Changing the type of a column outside the primary key is a mutation that rewrites the column in every part in the background, with no rollback. ClickHouse docs
SQLCH211	rebuild	Change the type of a key column	The type of a primary key column can change only when the change does not modify the data (for example adding values to an Enum); anything else needs a new table. ClickHouse docs
SQLCH212	metadata only	Rename a column	RENAME COLUMN changes metadata, for a column that is not in a key. ClickHouse docs
SQLCH213	rebuild	Rename or drop a key column	A column in the sorting key, the primary key or the partition key cannot be renamed or dropped. ClickHouse docs
SQLCH214	metadata only	Add, drop or change a projection	ADD PROJECTION and DROP PROJECTION change metadata; a new projection covers new parts until MATERIALIZE PROJECTION builds it for older ones. ClickHouse docs
SQLCH215	metadata only	Add or drop a constraint	ADD CONSTRAINT and DROP CONSTRAINT change metadata; existing rows are not checked. ClickHouse docs
SQLCH216	metadata only	Append new columns to the sorting key	MODIFY ORDER BY can only append columns added by ADD COLUMN in the same ALTER, and leaves the primary key as it was; the declaration must keep the old key as PRIMARY KEY. ClickHouse docs
SQLCH217	metadata only	Change the sampling key	MODIFY SAMPLE BY changes metadata; the new sampling expression must be part of the primary key, and the server refuses it otherwise. ClickHouse docs
SQLCH218	rebuild	Change a setting fixed at creation	A table setting the server marks read-only (such as index_granularity) is fixed when the table is created and needs a new table to change. ClickHouse docs
SQLCH220	rebuild	Change the sorting key	The sorting key is the on-disk order of every part (and in ReplacingMergeTree the deduplication identity); beyond appending new columns (SQLCH216) it cannot change in place. ClickHouse docs
SQLCH221	rebuild	Change the primary key	The primary key cannot be changed by ALTER; MODIFY ORDER BY leaves it as it was. ClickHouse docs
SQLCH222	rebuild	Change the partition key	There is no ALTER for the partition key; parts are laid out by it when they are written. ClickHouse docs
SQLCH223	rebuild	Change the table engine or its arguments	There is no ALTER for a table’s engine or its arguments (a ReplacingMergeTree version column, a Distributed sharding key); a different engine is a different table. ClickHouse docs
SQLCH224	rebuild	Change what kind of object it is	A table cannot become a view or a view a table; the old object is dropped and the new one created. ClickHouse docs
SQLCH230	metadata only	Rename or move a table or view	RENAME TABLE changes the name, or moves the object to another database, in metadata on an Atomic database. ClickHouse docs
SQLCH231	metadata only	Rename a database	RENAME DATABASE is metadata only, for an Atomic database. ClickHouse docs
SQLCH232	rebuild	Change a database’s engine	A database’s engine is fixed at creation; there is no ALTER for it. ClickHouse docs
SQLCH240	metadata only	Change a view’s query	A plain view stores no data; CREATE OR REPLACE VIEW replaces its query. ClickHouse docs
SQLCH241	metadata only	Change a materialized view’s query	ALTER TABLE … MODIFY QUERY replaces the query without stopping inserts; rows already written are not recomputed. ClickHouse docs
SQLCH242	rebuild	Change a materialized view’s target	A materialized view’s TO target is fixed at creation; the view is dropped and created again, and inserts into its source in between are not seen. ClickHouse docs
SQLCH243	rebuild	Change a materialized view’s own storage	A materialized view without TO keeps its rows in an inner table whose engine and keys are fixed like any table’s. ClickHouse docs
SQLCH244	metadata only	Change a refreshable view’s schedule	ALTER TABLE … MODIFY REFRESH changes the schedule of a refreshable materialized view. ClickHouse docs
SQLCH245	metadata only	Change a dictionary	A dictionary stores no data of its own: CREATE OR REPLACE DICTIONARY replaces its attributes, key, source, layout, lifetime or range, and it loads again from its source. ClickHouse docs
SQLCH260	metadata only	Change a function	A SQL user-defined function stores no data: CREATE OR REPLACE FUNCTION replaces its parameters and expression, and queries that call it use the new one. ClickHouse docs
SQLCH270	metadata only	Change a role	ALTER ROLE … SETTINGS replaces a role’s settings; its grants are untouched. ClickHouse docs
SQLCH271	metadata only	Change a user	ALTER USER sets one clause (HOST, DEFAULT ROLE, DEFAULT DATABASE, GRANTEES, SETTINGS, VALID UNTIL, IDENTIFIED) and leaves the rest, the password and the grants untouched. Sessions already open keep the old settings. ClickHouse docs
SQLCH272	metadata only	Change a row policy	CREATE ROW POLICY OR REPLACE replaces the policy’s condition, kind and roles; queries started after it see the new one. ClickHouse docs
SQLCH273	metadata only	Grant a privilege or a role	GRANT adds a privilege or a role to a user or role; it takes effect for new queries. ClickHouse docs
SQLCH274	metadata only	Revoke a privilege or a role	REVOKE takes a privilege or a role away, including one granted by hand to a grantee the build declares grants for; queries that relied on it fail from then on. ClickHouse docs
SQLCH250	drop	Drop an object	DROP removes the object and, for a table, its data; it is not undone. ClickHouse docs