CODEWITHSUNDEEP
clickhouse
frosty
frosty

Reputation: 2852

Clickhouse materialized view with TO clause doesn't support partition by

I use TO syntax to move the data from a materialized view to a table. Since, I have a need to periodically cleanup old partitions from materialized view, I tried below syntax without success:

CREATE MATERIALIZED VIEW counter_daily_mv
 TO counter_daily
 ENGINE = MergeTree()
 PARTITION BY toYYYYMMDD(day) ORDER BY (device, count)
AS SELECT
    when as day,
    device,
    value as count
FROM counter

But PARTITION BY works without TO (see below). How can I work around so that I can use TO clause and still cleanup materialized view efficiently?

CREATE MATERIALIZED VIEW counter_daily_mv
ENGINE = MergeTree()
 PARTITION BY toYYYYMMDD(day) ORDER BY (device, count)
AS SELECT
    when as day,
    device,
    value as count
FROM counter

Upvotes: 0

Views: 3218

Answers (1)

vladimir
vladimir

Reputation: 15226

This is expected behavior: for the existing table that defined in TO-expression cannot be redefined table's properties which already been defined in its CREATE-clause.

MV with TO does not store any data, it just inserts it to table defined in TO.

It needs to either define the TTL-clause for a table defined in TO to automate removing extra data:

CREATE TABLE test_source_data
(
    `id` Int32,
    `ts` DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(ts)
ORDER BY id

CREATE TABLE test_mv_storage
(
    `id` Int32,
    `ts` DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(ts)
ORDER BY id
TTL ts + INTERVAL 1 MONTH DELETE

CREATE MATERIALIZED VIEW test_mv TO test_mv_storage
AS
SELECT *
FROM test_source_data
WHERE id > 10

or create 'storable' MV with TTL (i would prefer this way as more compact):

CREATE TABLE test_source_data
(
    `id` Int32,
    `ts` DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(ts)
ORDER BY id

CREATE MATERIALIZED VIEW test_mv_2
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(ts)
ORDER BY id 
TTL ts + INTERVAL 1 MONTH DELETE
AS
SELECT *
FROM test_source_data
WHERE id > 10

Under the hood for the second case, CH creates the hidden table for MV .inner.test_mv_2 (call show tables from db to see it).

see for details Putting Things Where They Belong Using New TTL Moves.

Upvotes: 1

Related Questions