ClickHouse Database Dialect
Relevant source files
The following files were used as context for generating this wiki page:
- src/main/java/liquibase/ext/clickhouse/database/ClickHouseDatabase.java
- src/main/java/liquibase/ext/clickhouse/sqlgenerator/SqlGeneratorUtil.java
- src/main/java/liquibase/ext/clickhouse/params/ParamsLoader.java
- src/main/java/liquibase/ext/clickhouse/params/ClusterConfig.java
- src/main/java/liquibase/ext/clickhouse/lockservice/ClickHouseLockService.java
- src/main/java/liquibase/ext/clickhouse/sqlgenerator/CreateDatabaseChangeLogTableClickHouse.java
- src/main/java/liquibase/ext/clickhouse/sqlgenerator/LockDatabaseChangeLogClickHouse.java
The ClickHouse Database Dialect is a Liquibase extension designed to enable database change management for ClickHouse. It provides a specialized implementation of the Liquibase AbstractJdbcDatabase and custom SQL generators to handle ClickHouse-specific syntax, such as table engines, clustering, and asynchronous mutations.
This dialect facilitates common Liquibase operations including schema initialization, changelog tracking, and database locking within both single-instance and clustered ClickHouse environments.
Core Database Implementation
The ClickHouseDatabase class serves as the primary integration point between Liquibase and the ClickHouse JDBC driver. It defines the identity and capabilities of the ClickHouse database within the Liquibase ecosystem.
Database Identification and Connection
The dialect identifies itself using the product name "ClickHouse" and integrates with the com.clickhouse.jdbc.ClickHouseDriver. It defaults to port 8123 and handles JDBC URLs starting with jdbc:clickhouse.
Sources: src/main/java/liquibase/ext/clickhouse/database/ClickHouseDatabase.java:33-40, src/main/java/liquibase/ext/clickhouse/database/ClickHouseDatabase.java:60-63
Feature Support Matrix
ClickHouse has specific architectural constraints that differ from traditional RDBMS. The dialect explicitly disables features that ClickHouse does not support.
| Feature | Supported | Notes |
|---|---|---|
| Initially Deferrable Columns | No | Explicitly returned as false |
| Tablespaces | No | Explicitly returned as false |
| Sequences | No | Explicitly returned as false |
| DDL In Transactions | No | ClickHouse does not support transactional DDL |
Sources: src/main/java/liquibase/ext/clickhouse/database/ClickHouseDatabase.java:75-93
Cluster and Replication Configuration
The extension supports ClickHouse clusters through a configuration-driven approach. It allows Liquibase to manage schema changes across multiple nodes using the ON CLUSTER clause and replicated table engines.
Configuration Parameters
Clustering is activated by providing a liquibaseClickhouse.conf file. The ParamsLoader reads these settings into a ClusterConfig object.
| Property | Description |
|---|---|
clusterName | The name of the ClickHouse cluster for the ON CLUSTER clause. |
tableZooKeeperPathPrefix | The Zookeeper path prefix used for replicated engines. |
tableReplicaName | The replica identifier (e.g., {replica}). |
Sources: src/main/java/liquibase/ext/clickhouse/params/ParamsLoader.java:40-42, src/main/java/liquibase/ext/clickhouse/params/ClusterConfig.java:23-30
Logic Flow for Clustered SQL Generation
The dialect dynamically adjusts SQL statements based on the presence of cluster configurations.
SQL Generation and Mutations
Because ClickHouse handles updates and deletes as asynchronous mutations, the dialect's SQL generators often append SETTINGS mutations_sync = 1 to ensure Liquibase operations are synchronous and predictable.
DatabaseChangeLog Table Structure
The CreateDatabaseChangeLogTableClickHouse generator defines the schema for the Liquibase tracking table using ClickHouse-native types such as DateTime64 and UInt64.
| Column | Type |
|---|---|
| DATEEXECUTED | DateTime64 |
| ORDEREXECUTED | UInt64 |
| MD5SUM | Nullable(String) |
Database Locking Mechanism
ClickHouse does not support standard row-level locking for updates in the same way as OLTP databases. The ClickHouseLockService and LockDatabaseChangeLogClickHouse manage concurrency by performing synchronous mutations on the lock table.
Utility and Helper Components
Date and Time Functions
The dialect provides a specific implementation for the current date-time function to support high-precision ClickHouse DateTime64 columns.
public static final String CURRENT_DATE_TIME_FUNCTION =
"toDateTime64('"
+ new SimpleDateFormat("yyyy.MM.dd HH:mm:ss.SSS").format(new Date())
+ "',3)";
Sources: src/main/java/liquibase/ext/clickhouse/database/ClickHouseDatabase.java:41-45
SQL Generation Utilities
The SqlGeneratorUtil class centralizes the logic for determining which table engine to use (Replicated vs. Standard) and formatting the cluster clauses.
- Single Instance: Uses
ENGINE MergeTree() ORDER BY ID. - Clustered: Uses
ENGINE ReplicatedMergeTree('{zk_path}', '{replica}') ORDER BY ID.
Sources: src/main/java/liquibase/ext/clickhouse/sqlgenerator/SqlGeneratorUtil.java:51-57
Summary
The ClickHouse Database Dialect extends Liquibase to support the unique architecture of ClickHouse. By overriding standard JDBC database behaviors and providing specialized SQL generators, it enables reliable schema migrations across single nodes or complex clusters. Key highlights include the handling of asynchronous mutations via synchronous settings and the automated selection of replicated engines based on external configuration.