CODEWITHSUNDEEP
clickhousematerialized-views
Sean Goldfarb
Sean Goldfarb

Reputation: 400

Clickhouse materialized view with or without a table

I started using clickhouse and I noticed there are 2 "types" of ways of using materialized views.

the first one is firstly creating a table with MergeTree engine and "adding" the mv to the table:

CREATE MATERIALIZED VIEW my_view TO my_target_table AS
SELECT toStartOfDay(_timestamp)::Date AS Day, ...
FROM my_source_table

But I also can just use the view without a target_table:

CREATE MATERIALIZED VIEW my_view AS
SELECT toStartOfDay(_timestamp)::Date AS Day, ...
FROM my_source_table

Fox context my source table is currently just a table listening to a RabbitMQ queue. So maybe there are more significant considerations between the 2 methods in more complex cases, but I don't see any differences in my case and I would like to know if I am missing something.

Upvotes: 1

Views: 357

Answers (2)

Slach
Slach

Reputation: 2473

When you don't use TO db.table, then will create .inner.* table

from my perspective TO db.table more flexible which allow more understable sql command when you need change data structure

  • DETACH MATERIALIZED VIEW mv
  • ALTER TABLE db.table ...
  • CREATE OR REPLACE MATERIALIZED VIEW ...

Upvotes: 2

gingerwizard
gingerwizard

Reputation: 522

Generally we prefer the former, its more explicit and de-couples the target table from the view. This has management advantages e.g. you can drop the MV without dropping the target table. Additionally, querying the de-coupled target table ensures all optimizations are used - historically (need to test if case still) the latter implicit approach wouldn't use some.

Generally go explicit, create the target table first.

Upvotes: 2

Related Questions