ClickHouse集群环境下非默认数据库创建视图失败问题排查

我基于clickhouse/clickhouse-server:22.8.13.20镜像用Docker部署了ClickHouse集群，通过Go客户端连接到非默认数据库X，成功创建了分布式表statuses及其副本，建表语句如下：

CREATE TABLE IF NOT EXISTS statuses ON CLUSTER '{cluster}'
(
    id Nullable(UUID),
    team__id UUID,
    event_id UUID,
    domain__id Int64,
    type String,
    user_name String,
    status String,
    comment String,
    time_utc DateTime
) 
Engine = ReplicatedMergeTree('/clickhouse/tables/statuses', '{replica}')
PARTITION BY (toYYYYMM(toDate(time_utc)))
ORDER BY (status, time_utc)

但创建引用该表的视图时，报错提示default.statuses不存在（实际表存在于X.statuses，且客户端连接URL已指定该数据库），建视图语句如下：

CREATE OR REPLACE VIEW status_view ON CLUSTER '{cluster}' AS SELECT 
    statuses.status as status_status, 
    statuses.comment as status_comment, 
    statuses.event_id as status_event_id, 
    statuses.time_utc as status_time_utc
FROM 
    statuses 
    JOIN (
    SELECT 
        statuses.event_id, 
        max(statuses.time_utc) AS time 
    FROM 
        statuses 
    GROUP BY 
        statuses.event_id
    ) AS last_status ON (
        statuses.event_id = last_status.event_id
        AND statuses.time_utc = last_status.time
    );

Go客户端结构体定义：

type Client struct {
    conn     *sql.DB
    replicas []string
    mode     string
}

创建表和视图均通过调用c.conn.Exec(stmt)执行，为何同一客户端能成功在正确数据库创建表，却无法创建视图？

问题原因 #

ClickHouse在执行带ON CLUSTER的DDL语句时，会将语句分发到集群所有节点执行，但节点执行该语句时不会继承客户端连接时指定的默认数据库，而是使用节点自身的默认数据库（即default）。

创建表时，CREATE TABLE IF NOT EXISTS statuses未指定数据库，但由于ON CLUSTER的表创建逻辑特性，ClickHouse会自动将客户端连接的默认库X附加到表名前，分发到节点后会在X库下创建表。但创建视图时，视图定义中的FROM statuses没有显式指定数据库，节点执行时会默认去default库查找该表，因此出现找不到的错误。

解决办法 #

方法一：显式指定表的数据库前缀 #

修改视图创建语句，在所有引用statuses表的位置加上数据库名X的前缀，确保节点执行时能定位到正确的表：

CREATE OR REPLACE VIEW status_view ON CLUSTER '{cluster}' AS SELECT 
    X.statuses.status as status_status, 
    X.statuses.comment as status_comment, 
    X.statuses.event_id as status_event_id, 
    X.statuses.time_utc as status_time_utc
FROM 
    X.statuses 
    JOIN (
    SELECT 
        X.statuses.event_id, 
        max(X.statuses.time_utc) AS time 
    FROM 
        X.statuses 
    GROUP BY 
        X.statuses.event_id
    ) AS last_status ON (
        X.statuses.event_id = last_status.event_id
        AND X.statuses.time_utc = last_status.time
    );

方法二：先切换到目标数据库再创建视图 #

在视图创建语句前添加USE X;，确保节点执行时先切换到目标数据库，再处理视图创建逻辑：

USE X;
CREATE OR REPLACE VIEW status_view ON CLUSTER '{cluster}' AS SELECT 
    statuses.status as status_status, 
    statuses.comment as status_comment, 
    statuses.event_id as status_event_id, 
    statuses.time_utc as status_time_utc
FROM 
    statuses 
    JOIN (
    SELECT 
        statuses.event_id, 
        max(statuses.time_utc) AS time 
    FROM 
        statuses 
    GROUP BY 
        statuses.event_id
    ) AS last_status ON (
        statuses.event_id = last_status.event_id
        AND statuses.time_utc = last_status.time
    );

注意：使用该方法时，需要确保Go客户端将两条语句作为一个批量操作执行（部分ClickHouse Go驱动支持分号分隔的单条Exec调用）。

内容的提问来源于stack exchange，提问作者V.Cunichin