clickhouse: create materialized view on startup (docker)

Question

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

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

And the view, 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
)

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?

Hi @DennyCrane But it seems to make no difference when it's a table (the table is created). What's the difference in practice? — scc
The difference is huge, I cannot explain it in the comment. You should use attach. — Denny Crane
Ok, thanks. Changed everything to ATTACH but now have another issue.. Can you help stackoverflow.com/questions/67348100/…? — scc

vladimir vladimir · Accepted Answer · 2021-04-30T21:45:55

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/

Check it:

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: create materialized view on startup (docker)

1 Answers