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 - github.com/ClickHouse/ClickHouse/… — vladimir
– vladimir, Commented Jun 27, 2021 at 7:28
VIEW look like just SELECT alias, MATERIEALIZED VIEW it's AFTER INSERT trigger (it described in docs) — Slach
– Slach, Commented Jun 27, 2021 at 10:18

ramazan polat · Accepted Answer · 2021-06-29 18:58:34Z

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.

Collectives™ on Stack Overflow

VIEW on a table in ClickHouse

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related