1

I have a materialized view:

CREATE MATERIALIZED VIEW reporting_device_raw_data
ENGINE = MergeTree()
PARTITION BY toYYYYMM(ts)
ORDER BY (device_id, ts)
TTL ts + INTERVAL 3 MONTH
AS SELECT
    device_id,
    ts,
    value
FROM reporting_device_raw_data_null;

I tried to:

ALTER TABLE reporting_device_raw_data MODIFY TTL ts + INTERVAL 12 MONTH;

But got erorr:

DB::Exception: Alter of type 'MODIFY TTL' is not supported by storage MaterializedView.

What are possible workarounds?

Dmitriy Dumanskiy
  • 11,657
  • 9
  • 37
  • 57

1 Answers1

4

Check show create database .... for database engine.

Ordinary database:

ALTER TABLE ".inner.reporting_device_raw_data" MODIFY TTL ts + INTERVAL 12 MONTH;

Atomic database:

select uuid from system.tables where name = 'reporting_device_raw_data';
ALTER TABLE ".inner_id.{uuid from prev. select}" MODIFY TTL ts + INTERVAL 12 MONTH;
Denny Crane
  • 11,574
  • 2
  • 19
  • 30
  • I attempted this on my db and if I `show create table my_index` I see the old value for TTL, but `show create table ".inner.my_index"` shows the new TTL value. Is there another step I need to take to sync or anything? – jeremy Oct 22 '21 at 15:47
  • https://stackoverflow.com/questions/50622488/clickhouse-altering-materialized-views-select/50624243#50624243 looks like a duplicate and has a bit more info on the need to detach and re-attach the materialized view – jeremy Oct 22 '21 at 18:53
  • 1
    @jeremy TTL in MV does not matter. Check this https://den-crane.github.io/Everything_you_should_know_about_materialized_views_commented.pdf – Denny Crane Oct 23 '21 at 15:09
  • So if I'm understanding right, the materialized view is putting data in the real table ".inner.[myindextable]" and as long as the TTL is correct on the .inner table I will be okay? How does this work with a cluster of databases? Do I need to run this on each node in the cluster or can I do "ON CLUSTER x"? – jeremy Oct 26 '21 at 13:25
  • 1
    @jeremy I dare you to use `TO` materialized view syntax notation. https://den-crane.github.io/Everything_you_should_know_about_materialized_views_commented.pdf – Denny Crane Oct 26 '21 at 13:42
  • Appreciate the help!! – jeremy Oct 26 '21 at 17:10