Executing Updates

Relevant source files

The following files were used as context for generating this wiki page:

• README.md
• src/main/java/liquibase/ext/clickhouse/database/ClickHouseDatabase.java
• src/main/java/liquibase/ext/clickhouse/sqlgenerator/SqlGeneratorUtil.java
• src/main/java/liquibase/ext/clickhouse/sqlgenerator/CreateDatabaseChangeLogTableClickHouse.java
• src/main/java/liquibase/ext/clickhouse/sqlgenerator/UpdateChangeSetChecksumClickHouse.java
• src/main/java/liquibase/ext/clickhouse/params/ParamsLoader.java
• src/test/java/liquibase/ClickHouseTest.java
• src/main/java/liquibase/ext/clickhouse/lockservice/ClickHouseLockService.java

The Liquibase ClickHouse extension facilitates database migrations by adapting standard Liquibase update operations to ClickHouse's specific syntax and engine requirements. This includes managing schema initialization, tracking change set execution, and supporting both single-instance and cluster-replicated modes.

Sources: README.md:1-24, ClickHouseDatabase.java:33-40

Update Lifecycle and Execution Flow

When an update is triggered, the extension manages a sequence of operations to ensure the database is in the correct state. This involves checking for existing Liquibase tracking tables, acquiring locks to prevent concurrent updates, and executing the pending change sets.

High-Level Execution Flow

The following diagram illustrates the lifecycle of a standard update operation within the extension.

Managing Tracking Tables

ClickHouse requires specific table engines for data persistence. The extension automatically generates tracking tables using syntax compatible with ClickHouse's MergeTree or ReplicatedMergeTree families.

The DATABASECHANGELOG Table

This table tracks which change sets have been applied. It uses a DateTime64 type for the DATEEXECUTED column to maintain precision and a String type for IDs and Authors.

Column	Type	Description
ID	String	Unique identifier for the changeset
AUTHOR	String	Author of the changeset
FILENAME	String	Path to the changelog file
DATEEXECUTED	DateTime64	When the change was applied
ORDEREXECUTED	UInt64	Order of execution
MD5SUM	Nullable(String)	Checksum of the changeset
DEPLOYMENT_ID	Nullable(String)	Unique ID for the specific deployment run

Sources: CreateDatabaseChangeLogTableClickHouse.java:58-75, ClickHouseDatabase.java:40-43

Cluster and Single-Instance Modes

The extension supports two operational modes determined by the presence of a configuration file (liquibaseClickhouse.conf).

Single-Instance Mode

By default, if no cluster configuration is found, the extension creates tables using the MergeTree() engine.
Sources: ParamsLoader.java:101-105, SqlGeneratorUtil.java:62

Cluster-Replicated Mode

When configured, the extension adds the ON CLUSTER clause to DDL statements and uses the ReplicatedMergeTree engine. This ensures that the Liquibase metadata itself is replicated across the ClickHouse cluster.

Lock Management

To prevent multiple instances from running updates simultaneously, the ClickHouseLockService manages a lock table. ClickHouse does not support standard DDL in transactions, so the extension utilizes ALTER TABLE ... UPDATE mutations with mutations_sync = 1 to ensure synchronous lock acquisition.

• Initialization: If the lock table is empty, a default row with ID=1, LOCKED=0 is inserted.
• Acquisition: Uses an UPDATE statement to set LOCKED=1 only if LOCKED=0.
• Release: Updates the row to set LOCKED=0 and clears lock metadata.

Sources: ClickHouseLockService.java:53-63, InitializeDatabaseChangeLogLockClickHouse.java:58-69, LockDatabaseChangeLogClickHouse.java:57-67

SQL Generation and Customizations

The extension overrides standard SQL generators to handle ClickHouse-specific limitations and syntax:

1. Checksum Updates: Uses ALTER TABLE ... UPDATE for changing MD5 sums, as ClickHouse does not support standard UPDATE statements on MergeTree tables without the mutation syntax.
   Sources: UpdateChangeSetChecksumClickHouse.java:54-65
2. No-op Operations: Certain operations like ModifyDataType are overridden to do nothing because ClickHouse uses a generic String type rather than VARCHAR(N), making size modifications unnecessary for Liquibase's internal columns.
   Sources: ModifyDataTypeClickHouse.java:34-51
3. Current Timestamp: ClickHouse uses specific functions for time. The extension uses toDateTime64 with a formatted string to provide high-precision timestamps.
   Sources: ClickHouseDatabase.java:40-43

Conclusion

Executing updates in the Liquibase ClickHouse extension is a coordinated process that adapts the standard Liquibase flow to the unique requirements of the ClickHouse OLAP engine. By managing mutations for lock and log synchronization and supporting cluster-aware table creation, the extension ensures that schema migrations are consistent across both standalone and distributed ClickHouse environments.