CODEWITHSUNDEEP
clickhouse
MAYANK BHARTI
MAYANK BHARTI

Reputation: 83

Why we can't make materialized view on top of another view in clickhouse?

A is a collapsingMergeTree engine table

CREATE VIEW A AS SELECT * FROM A final;

CREATE MATERIALIZED VIEW a_mview1 TO B AS select id, name from A;

This is not working it seems we can't make mview on view..but why?

Upvotes: 0

Views: 2507

Answers (2)

Denny Crane
Denny Crane

Reputation: 13350

CREATE VIEW A AS SELECT * FROM A final;

It's impossible. Because MV never reads a source table. MV gets inserted blocks from INSERT command.

Upvotes: 1

vladimir
vladimir

Reputation: 15226

Normal View doesn't store any data (see doc) so that it is wrong to use it as a source of data for Materialized View.

It needs to create Materialized View based on the origin table:

CREATE TABLE A (
  ..
) ENGINE = CollapsingMergeTree
.. ;

CREATE MATERIALIZED VIEW a_mview1 TO B 
AS 
SELECT ..
FROM A
.. ;

Look at the article ClickHouse Materialized Views Illuminated for details.

Upvotes: 1

Related Questions