VIEW on a table in ClickHouse

Question

I'm looking into ClickHouse's VIEW, MATERIALIZED VIEW and LIVE VIEW.

MATERIALIZED VIEW and LIVE VIEW are pretty well described in the official docs.

However, VIEW on a table description is limited. From the official docs:

As an example, assume you’ve created a view:

CREATE VIEW view AS SELECT ...
and written a query:

SELECT a, b, c FROM view

But it does not specify which table engines are supported for the table we are doing SELECT from. Also, there are no examples on usage. Can you please provide a minimum usage example of VIEW and explain which table engines are supported for the initial table?

Concerning the difference between the VIEW and MATERIALIZED VIEW types I found this answer for Oracle and it looks like it can be applied to ClickHouse as well What is the difference between Views and Materialized Views in Oracle?.

Edit: Added few essential examples from tests from the link in comments:

Example 1

DROP TABLE IF EXISTS union;

create view union as select 1 as test union all select 2;

SELECT * FROM union ORDER BY test;

DETACH TABLE union;
ATTACH TABLE union;

SELECT * FROM union ORDER BY test;

DROP TABLE union;

Example 2:

DROP VIEW IF EXISTS test_view;
DROP TABLE IF EXISTS test_table;

CREATE TABLE test_table
(
    f1 Int32,
    f2 Int32,
    pk Int32
)
ENGINE = MergeTree()
ORDER BY f1
PARTITION BY pk;

CREATE VIEW test_view AS
SELECT f1, f2
FROM test_table
WHERE pk = 2;

INSERT INTO test_table (f1, f2, pk) VALUES (1,1,1), (1,1,2), (2,1,1), (2,1,2);

SELECT * FROM test_view ORDER BY f1, f2;

ALTER TABLE test_view DELETE WHERE pk = 2; --{serverError 48}

SELECT * FROM test_view ORDER BY f1, f2;

DROP VIEW IF EXISTS test_view;
DROP TABLE IF EXISTS test_table;

you can find examples of using here - https://github.com/ClickHouse/ClickHouse/search?l=TSQL&q=%22create+view%22 — vladimir, Jun 27 '21 at 07:28
VIEW look like just SELECT alias, MATERIEALIZED VIEW it's AFTER INSERT trigger (it described in docs) — Slach, Jun 27 '21 at 10:18

score 4 · Accepted Answer · answered Jun 29 '21 at 18:58

A view is just a saved query, nothing more.

Since you don't specify a table engine when you query a table, you don't need to specify the engine for a view. You can even combine multiple tables, each with a different table engine.

So instead of issuing a long query all the time, you can create a view for that query, which in turn will add an abstraction layer to help you to simplify your queries.

Suppose you have a table like this:

CREATE TABLE events(
  device_id UInt64,
  dt DateTime,
  temp Float32
) Engine = MergeTree Order By (device_id, dt);

And in your application, you use this query:

SELECT 
  device_id, 
  toDate(dt) as day,
  count() as cnt
FROM events
GROUP BY device_id, day

As you can see, you are getting event counts based on the day it is produced for each device_id.

Now, if you use this information too frequently, putting this query in another query will make it look much complicated like this:

SELECT *
FROM event_detail ed
INNER JOIN (
  SELECT 
    device_id, 
    toDate(dt) as day,
    count() as cnt
  FROM events
  GROUP BY device_id, day
) daily_event_counts
ON ed.device_id = daily_event_counts.device_id

So you create a view for the subquery like this:

CREATE VIEW daily_event_counts AS
SELECT 
  device_id, 
  toDate(dt) as day,
  count() as cnt
FROM events
GROUP BY device_id, day;

After that, writing a query will be much easier:

SELECT *
FROM event_detail ed
INNER JOIN daily_event_counts 
ON ed.device_id = daily_event_counts.device_id

As you can see, it is just a named query, nothing more.

Thanks for the examples and explanations. – vitaliis Jun 29 '21 at 19:49 — vitaliis, Jun 29 '21 at 19:49

VIEW on a table in ClickHouse

1 Answers1