Clickhouse附加实体化视图错误 [英] clickhouse attach materialized view error

问题描述

给出此dockerfile

Given this dockerfile

FROM yandex/clickhouse-server:20.6.4.44
COPY default /var/lib/clickhouse/metadata/default

并将这些sql文件放在 default 目录中:

And having these sql files in the default directory:

ATTACH TABLE IF NOT EXISTS default.a_table (
    `startTimestamp` DateTime,
    `fieldNumber` UInt32,
    `clientCountry` UInt16,
    `packets` UInt64,
    `bytes` UInt64
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(startTimestamp)
ORDER BY (startTimestamp, clientCountry)
SETTINGS index_granularity = 8192

ATTACH TABLE IF NOT EXISTS default.net_by_country_15m (
    `startTimestamp` DateTime,
    `clientCountry` UInt16,
    `bytes` UInt64
)
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(startTimestamp)
ORDER BY (startTimestamp, clientCountry)
SETTINGS index_granularity = 8192;

此视图:

ATTACH MATERIALIZED VIEW v_by_country_15m TO default.net_by_country_15m
AS (
       SELECT startTimestamp,
              clientCountry,
              sum(bytes) as bytes
       FROM default.a_table
       GROUP BY startTimestamp, clientCountry
)

容器无法启动，并且我手动运行语句

the container doesn't start and running the statement manually I get

Received from localhost:9000. DB::Exception: Table `v_by_country_15m` doesn't exist.

(这是对 clickhouse的跟踪:在启动时创建实例化视图(docker)，但我想继续使用 metadata 目录，而不是使用 docker-entrypoint-initdb.d 目录，因为这是我继承的项目，在我更好地了解Clickhouse之前，不要想做太多更改.

(this is a follow up to clickhouse: create materialized view on startup (docker) but instead of using the docker-entrypoint-initdb.d directory, I want to keep using the metadata directory since this is a project I inherited and don't wanna change too much until I understand clickhouse better).

(更新的表定义)

推荐答案

应为 TO default.a_table

ATTACH MATERIALIZED VIEW v_by_country_15m  to default.a_table
AS (
       SELECT startTimestamp,
              clientCountry,
              sum(bytes) as bytes
       FROM another_table
       GROUP BY startTimestamp, clientCountry
)

这篇关于Clickhouse附加实体化视图错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！