SQL Generation Utilities

Relevant source files

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

• 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/CreateDatabaseChangeLogLockTableClickHouse.java
• src/main/java/liquibase/ext/clickhouse/sqlgenerator/TagDatabaseGeneratorClickhouse.java
• src/main/java/liquibase/ext/clickhouse/sqlgenerator/UpdateChangeSetChecksumClickHouse.java
• src/main/java/liquibase/ext/clickhouse/sqlgenerator/InitializeDatabaseChangeLogLockClickHouse.java
• src/main/java/liquibase/ext/clickhouse/params/ParamsLoader.java
• src/main/java/liquibase/ext/clickhouse/params/ClusterConfig.java

The SQL Generation Utilities within the Liquibase ClickHouse extension provide specialized logic for translating Liquibase statements into ClickHouse-compatible SQL. Given ClickHouse's unique architecture—specifically its handling of mutations, engines, and cluster-wide operations—these utilities ensure that standard Liquibase operations like table creation, tagging, and checksum updates are executed correctly across both standalone instances and distributed clusters.

The core of this functionality resides in the SqlGeneratorUtil class, which abstracts the repetitive task of appending ON CLUSTER clauses and defining table engines based on the presence of cluster configurations.

Core SQL Utility Logic

The SqlGeneratorUtil class acts as a central helper for various SqlGenerator implementations. It provides static methods to handle the differences between standard ClickHouse MergeTree engines and ReplicatedMergeTree engines used in cluster environments.

Cluster and Engine Clauses

The utility determines whether to include cluster-specific syntax based on the ClusterConfig provided by the ParamsLoader.

• ON CLUSTER Clause: Appends ON CLUSTER '{clusterName}' to queries if a cluster configuration is detected.
• Table Engine Clause: Returns ENGINE MergeTree() ORDER BY ID for single instances or ENGINE ReplicatedMergeTree('{zkPath}', '{replicaName}') ORDER BY ID for clusters.

Sources: SqlGeneratorUtil.java:45-59

SQL Generation Flow

The following diagram illustrates how the SqlGeneratorUtil is utilized by specific ClickHouse generators to produce SQL statements.

The flow shows the dependency on ParamsLoader to determine the environment (Single vs. Cluster) before generating the final SQL syntax.
Sources: SqlGeneratorUtil.java:34-43, CreateDatabaseChangeLogTableClickHouse.java:54-78

Implementation in Statement Generators

Several generators leverage the utilities to handle Liquibase internal tables (DATABASECHANGELOG and DATABASECHANGELOGLOCK) and operational metadata.

Schema Management

The extension overrides standard generators to inject ClickHouse-specific data types (e.g., DateTime64, UInt64) and engines.

Feature	Generator Class	Key SQL Specifics
ChangeLog Table	CreateDatabaseChangeLogTableClickHouse	Uses String for IDs and DateTime64 for execution dates.
Lock Table	CreateDatabaseChangeLogLockTableClickHouse	Uses Int64 for IDs and UInt8 for boolean LOCKED flags.
Checksum Updates	UpdateChangeSetChecksumClickHouse	Uses ALTER TABLE ... UPDATE with mutations_sync = 1.
Database Tagging	TagDatabaseGeneratorClickhouse	Uses ALTER TABLE ... UPDATE with subqueries to find the latest record.

Sources: CreateDatabaseChangeLogTableClickHouse.java:60-76, CreateDatabaseChangeLogLockTableClickHouse.java:57-68, UpdateChangeSetChecksumClickHouse.java:54-65, TagDatabaseGeneratorClickhouse.java:58-85

Mutation Handling

ClickHouse handles updates and deletes as "mutations." The utilities ensure these are synchronous where required by appending SETTINGS mutations_sync = 1.

// Example from UpdateChangeSetChecksumClickHouse
String updateChecksumQuery =
    String.format(
        "ALTER TABLE `%s`.%s "
            + SqlGeneratorUtil.generateSqlOnClusterClause(properties)
            + "UPDATE MD5SUM = '%s' WHERE ID = '%s' AND AUTHOR = '%s' AND FILENAME = '%s' SETTINGS mutations_sync = 1",
        database.getDefaultSchemaName(),
        database.getDatabaseChangeLogTableName(),
        changeSet.generateCheckSum().toString(),
        changeSet.getId(),
        changeSet.getAuthor(),
        changeSet.getFilePath());

Sources: UpdateChangeSetChecksumClickHouse.java:55-66, RemoveChangeSetRanStatusClickHouse.java:54-63

Configuration and Cluster Parameters

The SQL generation behavior is heavily influenced by the ClusterConfig object, which is populated by the ParamsLoader.

Cluster Configuration Properties

These properties are typically loaded from a liquibaseClickhouse.conf file.

Property	Type	Description
clusterName	String	The name of the ClickHouse cluster for ON CLUSTER clauses.
tableZooKeeperPathPrefix	String	The Zookeeper path prefix used for ReplicatedMergeTree.
tableReplicaName	String	The replica identifier within the cluster.

Sources: ClusterConfig.java:23-28, ParamsLoader.java:37-41

Parameter Loading Logic

The ParamsLoader validates that if any cluster property is defined, all three mandatory properties must be present.

Conclusion

The SQL Generation Utilities serve as the architectural bridge between Liquibase's generic database operations and ClickHouse's specialized storage and distribution model. By centralizing cluster clause and engine logic in SqlGeneratorUtil, the extension maintains consistency across various management tasks, ensuring that schema changes and lock mechanisms function reliably in both standalone and distributed ClickHouse environments.