clickhouse:在启动时创建物化视图(docker)

Question

我正在尝试在启动时设置一个具有物化视图的本地开发docker。所以有了这个Dockerfile:

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

在default中，我们有两个定义：

a_table.sql:

CREATE TABLE default.a_table (
`startTimestamp` DateTime,
`fieldNumber` UInt32,
`clientCountry` UInt16,
`packets` UInt64,
`bytes` UInt64
)
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(startTimestamp)
ORDER BY (startTimestamp, clientCountry)
SETTINGS index_granularity = 8192

和视图，v_by_country_15m.sql:：

CREATE MATERIALIZED VIEW v_by_country_15m 
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(startTimestamp)
PRIMARY KEY (startTimestamp, clientCountry)
ORDER BY (startTimestamp, clientCountry)
AS (
SELECT startTimestamp,
clientCountry,
sum(bytes) as bytes
FROM a_table
GROUP BY startTimestamp, clientCountry
)

如果我只在default文件夹中包含a_table.sql文件，则容器正常启动，但如果我在default文件夹中包含了v_by_country_15m.sql文件，则无法启动。如果我只从表开始，然后从exec和clickhouse-client开始，只创建物化视图，它是有效的，所以我不认为问题是物化视图本身。

我们可以在启动时实现视图吗？

Answer 1

它需要将sql脚本复制到/doker-entrypoint-initdb.d/folder:

FROM yandex/clickhouse-server:20.6.4.44
COPY default/* /docker-entrypoint-initdb.d/

检查：

docker build -t test_ch:latest  .
docker run test_ch
docker exec -it {container-id} bash
> clickhouse client
> USE default
> SHOW TABLES
> ┌─name────────────────────┐
> │ .inner.v_by_country_15m │
> │ a_table                 │
> │ v_by_country_15m        │
> └─────────────────────────┘