如何用Liquibase管理ClickHouse？创建DATABASECHANGELOG表报错求助

问题 #

之前用Liquibase管理PostgreSQL数据库迁移，现在想迁移到正在搭建的ClickHouse服务器。已经下载了JDBC连接JAR包，在liquibase.properties里配置了driver=com.clickhouse.jdbc.ClickHouseDriver，但执行liquibase status时报错，提示创建DATABASECHANGELOG表失败，原因是ClickHouse的MergeTree引擎必须指定ORDER BY或PRIMARY KEY子句。

报错详情 #

Unexpected error running Liquibase: Code: 42. DB::Exception: ORDER BY or PRIMARY KEY clause is missing. Consider using extended storage definition syntax with ORDER BY or PRIMARY KEY clause. With deprecated old syntax (highly not recommended) storage MergeTree requires 3 to 4 parameters: 
name of column with date,
[sampling element of primary key],
primary key expression,
index granularity

Syntax for the MergeTree table engine:

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) [GRANULARITY value1],
    INDEX index_name2 expr2 TYPE type2(...) [GRANULARITY value2]
) ENGINE = MergeTree()
        at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$2(LiquibaseCommandLine.java:404)
        at liquibase.Scope.child(Scope.java:200)
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]
[COMMENT 'comment']

See details in documentation: https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/. Other engines of the family support different syntax, see details in the corresponding documentation topics.

If you use the Replicated version of engines, see https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/replication/.
. (NUMBER_OF_ARGUMENTS_DOESNT_MATCH) (version 24.10.1.1301 (official build))
 [Failed SQL: (42) CREATE TABLE DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED datetime NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10))]

解决方案 #

Liquibase默认生成的DATABASECHANGELOG表语句没有适配ClickHouse的MergeTree引擎要求，需要手动干预表的创建，以下是两种可行方案：

方案1：提前手动创建符合要求的DATABASECHANGELOG表 #

在执行Liquibase命令前，直接在ClickHouse中运行以下SQL创建表，指定MergeTree引擎和排序键：

CREATE TABLE IF NOT EXISTS DATABASECHANGELOG (
    ID VARCHAR(255) NOT NULL,
    AUTHOR VARCHAR(255) NOT NULL,
    FILENAME VARCHAR(255) NOT NULL,
    DATEEXECUTED DateTime NOT NULL,
    ORDEREXECUTED INT NOT NULL,
    EXECTYPE VARCHAR(10) NOT NULL,
    MD5SUM VARCHAR(35),
    DESCRIPTION VARCHAR(255),
    COMMENTS VARCHAR(255),
    TAG VARCHAR(255),
    LIQUIBASE VARCHAR(20),
    CONTEXTS VARCHAR(255),
    LABELS VARCHAR(255),
    DEPLOYMENT_ID VARCHAR(10)
) ENGINE = MergeTree()
ORDER BY (DATEEXECUTED, ORDEREXECUTED);

这里选择DATEEXECUTED和ORDEREXECUTED作为排序键，既满足ClickHouse引擎要求，也符合Liquibase查询迁移历史的逻辑。

方案2：用自定义变更集初始化记录表 #

创建一个初始变更集文件，手动定义DATABASECHANGELOG表的创建语句，再让Liquibase执行这个变更集完成初始化：

1. 创建init_changelog.xml文件：

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                   http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.19.xsd">

    <changeSet id="init-clickhouse-changelog" author="your-name">
        <sql>
            CREATE TABLE IF NOT EXISTS DATABASECHANGELOG (
                ID VARCHAR(255) NOT NULL,
                AUTHOR VARCHAR(255) NOT NULL,
                FILENAME VARCHAR(255) NOT NULL,
                DATEEXECUTED DateTime NOT NULL,
                ORDEREXECUTED INT NOT NULL,
                EXECTYPE VARCHAR(10) NOT NULL,
                MD5SUM VARCHAR(35),
                DESCRIPTION VARCHAR(255),
                COMMENTS VARCHAR(255),
                TAG VARCHAR(255),
                LIQUIBASE VARCHAR(20),
                CONTEXTS VARCHAR(255),
                LABELS VARCHAR(255),
                DEPLOYMENT_ID VARCHAR(10)
            ) ENGINE = MergeTree()
            ORDER BY (DATEEXECUTED, ORDEREXECUTED);
        </sql>
    </changeSet>

</databaseChangeLog>

2. 执行初始化命令：

liquibase --changeLogFile=init_changelog.xml update

完成后即可使用常规变更集进行后续迁移操作。

额外注意事项 #

• 确保ClickHouse JDBC驱动版本与服务器版本兼容（比如24.10.x服务器匹配同大版本的驱动）
• Liquibase对ClickHouse的支持属于社区级别，部分高级特性建议直接写原生SQL变更，避免依赖自动生成语句
• 如果使用ClickHouse集群，需要在CREATE TABLE语句中添加ON CLUSTER子句，并将引擎替换为ReplicatedMergeTree

内容的提问来源于stack exchange，提问作者m_squared