CREATE VIEW

Creates a new view. There are two types of views: normal and materialized.

Normal

Syntax:

  1. CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] AS SELECT ...

Normal views don’t store any data. They just perform a read from another table on each access. In other words, a normal view is nothing more than a saved query. When reading from a view, this saved query is used as a subquery in the FROM clause.

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

  1. CREATE VIEW view AS SELECT ...

and written a query:

  1. SELECT a, b, c FROM view

This query is fully equivalent to using the subquery:

  1. SELECT a, b, c FROM (SELECT ...)

Materialized

  1. CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...

Materialized views store data transformed by the corresponding SELECT query.

When creating a materialized view without TO [db].[table], you must specify ENGINE – the table engine for storing data.

When creating a materialized view with TO [db].[table], you must not use POPULATE.

A materialized view is implemented as follows: when inserting data to the table specified in SELECT, part of the inserted data is converted by this SELECT query, and the result is inserted in the view.

Important

Materialized views in ClickHouse are implemented more like insert triggers. If there’s some aggregation in the view query, it’s applied only to the batch of freshly inserted data. Any changes to existing data of source table (like update, delete, drop partition, etc.) doesn’t change the materialized view.

If you specify POPULATE, the existing table data is inserted in the view when creating it, as if making a CREATE TABLE ... AS SELECT ... . Otherwise, the query contains only the data inserted in the table after creating the view. We don’t recommend using POPULATE, since data inserted in the table during the view creation will not be inserted in it.

A SELECT query can contain DISTINCT, GROUP BY, ORDER BY, LIMIT… Note that the corresponding conversions are performed independently on each block of inserted data. For example, if GROUP BY is set, data is aggregated during insertion, but only within a single packet of inserted data. The data won’t be further aggregated. The exception is when using an ENGINE that independently performs data aggregation, such as SummingMergeTree.

The execution of ALTER queries on materialized views has limitations, so they might be inconvenient. If the materialized view uses the construction TO [db.]name, you can DETACH the view, run ALTER for the target table, and then ATTACH the previously detached (DETACH) view.

Note that materialized view is influenced by optimize_on_insert setting. The data is merged before the insertion into a view.

Views look the same as normal tables. For example, they are listed in the result of the SHOW TABLES query.

There isn’t a separate query for deleting views. To delete a view, use DROP TABLE.

Live View (Experimental)

Important

This is an experimental feature that may change in backwards-incompatible ways in the future releases.
Enable usage of live views and WATCH query using set allow_experimental_live_view = 1.

  1. CREATE LIVE VIEW [IF NOT EXISTS] [db.]table_name [WITH [TIMEOUT [value_in_sec] [AND]] [REFRESH [value_in_sec]]] AS SELECT ...

Live views store result of the corresponding SELECT query and are updated any time the result of the query changes. Query result as well as partial result needed to combine with new data are stored in memory providing increased performance for repeated queries. Live views can provide push notifications when query result changes using the WATCH query.

Live views are triggered by insert into the innermost table specified in the query.

Live views work similarly to how a query in a distributed table works. But instead of combining partial results from different servers they combine partial result from current data with partial result from the new data. When a live view query includes a subquery then the cached partial result is only stored for the innermost subquery.

Limitations

  • Table function is not supported as the innermost table.
  • Tables that do not have inserts such as a dictionary, system table, a normal view, or a materialized view will not trigger a live view.
  • Only queries where one can combine partial result from the old data plus partial result from the new data will work. Live view will not work for queries that require the complete data set to compute the final result or aggregations where the state of the aggregation must be preserved.
  • Does not work with replicated or distributed tables where inserts are performed on different nodes.
  • Can’t be triggered by multiple tables.

See WITH REFRESH to force periodic updates of a live view that in some cases can be used as a workaround.

You can watch for changes in the live view query result using the WATCH query

  1. WATCH [db.]live_view

Example:

  1. CREATE TABLE mt (x Int8) Engine = MergeTree ORDER BY x;
  2. CREATE LIVE VIEW lv AS SELECT sum(x) FROM mt;

Watch a live view while doing a parallel insert into the source table.

  1. WATCH lv
  1. ┌─sum(x)─┬─_version─┐
  2. │      1 │        1 │
  3. └────────┴──────────┘
  4. ┌─sum(x)─┬─_version─┐
  5. │      2 │        2 │
  6. └────────┴──────────┘
  7. ┌─sum(x)─┬─_version─┐
  8. │      6 │        3 │
  9. └────────┴──────────┘
  10. ...
  1. INSERT INTO mt VALUES (1);
  2. INSERT INTO mt VALUES (2);
  3. INSERT INTO mt VALUES (3);

or add EVENTS clause to just get change events.

  1. WATCH [db.]live_view EVENTS

Example:

  1. WATCH lv EVENTS
  1. ┌─version─┐
  2. │       1 │
  3. └─────────┘
  4. ┌─version─┐
  5. │       2 │
  6. └─────────┘
  7. ┌─version─┐
  8. │       3 │
  9. └─────────┘
  10. ...

You can execute SELECT query on a live view in the same way as for any regular view or a table. If the query result is cached it will return the result immediately without running the stored query on the underlying tables.

  1. SELECT * FROM [db.]live_view WHERE ...

Force Refresh

You can force live view refresh using the ALTER LIVE VIEW [db.]table_name REFRESH statement.

With Timeout

When a live view is create with a WITH TIMEOUT clause then the live view will be dropped automatically after the specified number of seconds elapse since the end of the last WATCH query that was watching the live view.

  1. CREATE LIVE VIEW [db.]table_name WITH TIMEOUT [value_in_sec] AS SELECT ...

If the timeout value is not specified then the value specified by the temporary_live_view_timeout setting is used.

Example:

  1. CREATE TABLE mt (x Int8) Engine = MergeTree ORDER BY x;
  2. CREATE LIVE VIEW lv WITH TIMEOUT 15 AS SELECT sum(x) FROM mt;

With Refresh

When a live view is created with a WITH REFRESH clause then it will be automatically refreshed after the specified number of seconds elapse since the last refresh or trigger.

  1. CREATE LIVE VIEW [db.]table_name WITH REFRESH [value_in_sec] AS SELECT ...

If the refresh value is not specified then the value specified by the periodic_live_view_refresh setting is used.

Example:

  1. CREATE LIVE VIEW lv WITH REFRESH 5 AS SELECT now();
  2. WATCH lv
  1. ┌───────────────now()─┬─_version─┐
  2. │ 2021-02-21 08:47:05 │        1 │
  3. └─────────────────────┴──────────┘
  4. ┌───────────────now()─┬─_version─┐
  5. │ 2021-02-21 08:47:10 │        2 │
  6. └─────────────────────┴──────────┘
  7. ┌───────────────now()─┬─_version─┐
  8. │ 2021-02-21 08:47:15 │        3 │
  9. └─────────────────────┴──────────┘

You can combine WITH TIMEOUT and WITH REFRESH clauses using an AND clause.

  1. CREATE LIVE VIEW [db.]table_name WITH TIMEOUT [value_in_sec] AND REFRESH [value_in_sec] AS SELECT ...

Example:

  1. CREATE LIVE VIEW lv WITH TIMEOUT 15 AND REFRESH 5 AS SELECT now();

After 15 sec the live view will be automatically dropped if there are no active WATCH queries.

  1. WATCH lv
  1. Code: 60. DB::Exception: Received from localhost:9000. DB::Exception: Table default.lv doesn't exist..

Usage

Most common uses of live view tables include:

  • Providing push notifications for query result changes to avoid polling.
  • Caching results of most frequent queries to provide immediate query results.
  • Watching for table changes and triggering a follow-up select queries.
  • Watching metrics from system tables using periodic refresh.

Settings

You can use the following settings to control the behaviour of live views.

  • allow_experimental_live_view - enable live views. Default is 0.
  • live_view_heartbeat_interval - the heartbeat interval in seconds to indicate live query is alive. Default is 15 seconds.
  • max_live_view_insert_blocks_before_refresh - maximum number of inserted blocks after which
    mergeable blocks are dropped and query is re-executed. Default is 64 inserts.
  • temporary_live_view_timeout - interval after which live view with timeout is deleted. Default is 5 seconds.
  • periodic_live_view_refresh - interval after which periodically refreshed live view is forced to refresh. Default is 60 seconds.

Original article