clickhouse:在启动时创建实例化视图(docker) [英] clickhouse: create materialized view on startup (docker)

问题描述

我正在尝试使用启动时的实例化视图来设置本地开发docker.因此，有了这个Dockerfile:

I'm trying to set up a local development docker with a materialized view on startup. So having this Dockerfile:

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

在 default 中，我们有以下两个定义:

And in default we have these 2 definitions:

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 文件，则容器将正常启动，但是如果我包含 v_by_country_15m.sql default 文件夹中的>文件，则无法启动.如果我仅从表开始，然后先 exec 和 clickhouse-client 并仅创建实例化视图，则它可以工作，因此我认为问题不在于该实例化查看自己.

If I only include the a_table.sql file in the default folder, the container starts up normally but if I include the v_by_country_15m.sql file in the default folder, it fails to startup. If I start with only the table, and then exec and clickhouse-client and just create the materialized view, it works, so I don't think the issue is the materialized view itself.

我们可以在启动时实现实例化视图吗?

Can we have materialized views on startup?

推荐答案

它需要将sql脚本复制到

It needs to copy sql-scripts to /docker-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        │
  > └─────────────────────────┘

这篇关于clickhouse:在启动时创建实例化视图(docker)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！