CREATE VIEW

Creates a new view. Views can be normal, materialized, and refreshable materialized.

Normal View

Syntax:

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [db.]table_name [(alias1 [, alias2 ...])] [ON CLUSTER cluster_name]
[DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER | NONE }]
AS SELECT ...
[COMMENT 'comment']

Normal views do not 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:

CREATE VIEW view AS SELECT ...

and written a query:

SELECT a, b, c FROM view

This query is fully equivalent to using the subquery:

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

Parameterized View

Parameterized views are similar to normal views, but can be created with parameters which are not resolved immediately. These views can be used with table functions, which specify the name of the view as function name and the parameter values as its arguments.

CREATE VIEW view AS SELECT * FROM TABLE WHERE Column1={column1:datatype1} and Column2={column2:datatype2} ...

The above creates a view for table which can be used as table function by substituting parameters as shown below.

SELECT * FROM view(column1=value1, column2=value2 ...)

Since the parameterized view depends on the parameter values, it doesn’t have a schema when parameters are not provided. That means there’s no information about parameterized views in the system.columns table. Also, DESCRIBE queries would work only if parameters are provided.

DESCRIBE view(column1=value1, column2=value2 ...)

Materialized View

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster_name] [TO[db.]name [(columns)]] [ENGINE = engine] [POPULATE]
[REFRESH ...]
[DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | NONE }]
AS SELECT ...
[COMMENT 'comment']

CREATE OR REPLACE MATERIALIZED VIEW [db.]table_name [ON CLUSTER cluster_name] [TO[db.]name [(columns)]] [ENGINE = engine] [POPULATE]
[REFRESH ...]
[DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | NONE }]
AS SELECT ...
[COMMENT 'comment']

OR REPLACE and IF NOT EXISTS are mutually exclusive: combining them is a syntax error.

CREATE OR REPLACE MATERIALIZED VIEW

CREATE OR REPLACE MATERIALIZED VIEW atomically replaces an existing materialized view and its inner storage table (if any). The operation requires an Atomic or Replicated database engine.

CREATE OR REPLACE MATERIALIZED VIEW [db.]name [ON CLUSTER cluster]
[TO [db.]target_table]
[ENGINE = engine]
[POPULATE]
[REFRESH ...]
AS SELECT ...

Key behaviors:

Without TO clause: the old inner table is dropped and a new one is created. Existing data in the inner table is lost unless POPULATE is specified.
With TO clause: only the view definition is replaced; the target table and its data are unaffected.
Compatible with REFRESH, ON CLUSTER, and all engine options. POPULATE is supported on Atomic databases only — it is rejected on Replicated databases (see the POPULATE note below).
Requires CREATE VIEW and DROP VIEW privileges.

Examples:

-- Create a materialized view with an inner table
CREATE OR REPLACE MATERIALIZED VIEW mv
    ENGINE = MergeTree ORDER BY x
    AS SELECT x, sum(y) AS total FROM src GROUP BY x;

-- Replace with a new definition (old inner table data is lost)
CREATE OR REPLACE MATERIALIZED VIEW mv
    ENGINE = MergeTree ORDER BY x
    AS SELECT x, count() AS cnt FROM src GROUP BY x;

-- Replace with POPULATE to backfill from existing source data
CREATE OR REPLACE MATERIALIZED VIEW mv
    ENGINE = MergeTree ORDER BY x
    POPULATE
    AS SELECT x FROM src;

-- Replace an inner-table MV with a TO-table MV (target data is preserved)
CREATE OR REPLACE MATERIALIZED VIEW mv TO target
    AS SELECT x FROM src;

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 can also use POPULATE to backfill the target table from the existing source data (the target table may already contain data, in which case the backfilled rows are appended). POPULATE cannot be combined with REFRESH: a refreshable materialized view is filled by its first refresh, so POPULATE would load the initial data twice (use EMPTY to skip the first refresh instead).

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.

Note

Materialized views in ClickHouse use column names instead of column order during insertion into destination table. If some column names are not present in the SELECT query result, ClickHouse uses a default value, even if the column is not Nullable. A safe practice would be to add aliases for every column when using Materialized views.

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.) does not change the materialized view.

Materialized views in ClickHouse do not have deterministic behaviour in case of errors. This means that blocks that had been already written will be preserved in the destination table, but all blocks after error will not.

By default, if pushing to one of the views throws, the INSERT query fails. Whether the block has already reached the source table by that point is not guaranteed — it depends on insert pipeline timing, not on the view error. Retry the failed INSERT with insert deduplication (insert_deduplicate, deduplicate_blocks_in_dependent_materialized_views) to get exactly-once delivery to the source table and all dependent views.

Setting materialized_views_ignore_errors=true on the INSERT query only changes error reporting: each view error is logged as a warning and the INSERT query succeeds. Delivery to the failing view’s destination is partial — blocks processed before the exception are kept, and the failing block plus any subsequent blocks are dropped from that view. Views downstream of that destination see only the blocks that did arrive, so their delivery is partial too. Sibling views (and their downstream chains) that did not throw are written to in full, and the source table is written to as usual. Because the INSERT reports success, the client gets no failure signal and no automatic retry is triggered; use this setting only when source-table writes must not be blocked by view-side problems (for example, system.*_log tables).

materialized_views_ignore_errors is true by default for system.*_log tables.

If you specify POPULATE, the existing source table data is inserted into the view when creating it. Otherwise, the view contains only the data inserted into the source table after the view is created.

For a plain CREATE MATERIALIZED VIEW, POPULATE is atomic by default (setting materialized_views_populate_atomically = 1): the view is subscribed to new inserts of the source table and a snapshot of the existing data is taken together, under a brief exclusive lock on the source table, so that every row inserted concurrently with the population is delivered to the view exactly once — neither missed nor duplicated. The (possibly long-running) population then reads the pinned snapshot without holding any lock.

This is local insert-path atomicity: the exclusive lock only serializes with inserts that acquire this source table’s storage lock on the same server, so the exactly-once guarantee covers inserts arriving through this server. It is not a cluster-wide guarantee — rows inserted on another replica of a ReplicatedMergeTree source, or through a distributed write path (for example, into a Distributed table or via ON CLUSTER), concurrently with the population are outside this cut and can still be missed or duplicated.

If the population fails — for example, the exclusive lock on a busy source table cannot be acquired within lock_acquire_timeout, or the view’s SELECT throws while running — the just-created view is dropped and the CREATE query fails, leaving behind nothing of what it created, so it can simply be retried. For the TO [db].[table] form this rollback drops only the view, never the pre-existing target table — but rows the failed population already inserted into the target stay there, exactly as after a failed INSERT ... SELECT into that table, so retrying the CREATE inserts them again. If the backfill must be exact, retry into a truncated or fresh target table, or use a deduplicating engine such as ReplacingMergeTree.

Note

Atomicity requires the source table to support reading a pinned point-in-time snapshot — the MergeTree family and Memory. For any other source (a view, Distributed, Merge, Buffer, the Log family, or a table not in an Atomic database), the population falls back to the legacy, non-atomic behavior (recorded in the server log): the existing data is read with a separate, uncoordinated snapshot, so rows inserted during the population can be missed or duplicated. In that case create the view and run a separate INSERT ... SELECT if you need exact data. Setting materialized_views_populate_atomically = 0 forces this legacy behavior for all sources.

Atomic population applies to plain CREATE MATERIALIZED VIEW only. CREATE OR REPLACE / REPLACE MATERIALIZED VIEW ... POPULATE always use the legacy, non-atomic population.

POPULATE is not supported with Replicated databases (use database_replicated_allow_heavy_create to override) and is not supported in ClickHouse Cloud. When it is enabled through that override, the population is always the legacy, non-atomic one — a failed population could not be rolled back consistently on all replicas.

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.

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.

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

To delete a view, use DROP VIEW. Although DROP TABLE works for VIEWs as well.

SQL security

DEFINER and SQL SECURITY allow you to specify which ClickHouse user to use when executing the view’s underlying query. SQL SECURITY has three legal values: DEFINER, INVOKER, or NONE. You can specify any existing user or CURRENT_USER in the DEFINER clause.

The following table will explain which rights are required for which user in order to select from view. Note that regardless of the SQL security option, in every case it is still required to have GRANT SELECT ON <view> in order to read from it.

SQL security option	View	Materialized View
`DEFINER alice`	`alice` must have a `SELECT` grant for the view’s source table.	`alice` must have a `SELECT` grant for the view’s source table and an `INSERT` grant for the view’s target table.
`INVOKER`	User must have a `SELECT` grant for the view’s source table.	`SQL SECURITY INVOKER` can’t be specified for materialized views.
`NONE`	-	-

If DEFINER/SQL SECURITY aren’t specified, the result depends on the ignore_empty_sql_security_in_create_view_query server setting.

With its default value of true, the query is stored as written and the view gets an empty SQL security type. A normal view then runs with the permissions of the invoker, and for a materialized view with an explicitly specified target table, the access checks on that target table are skipped: inserting into the source table does not require the INSERT privilege on the target table, and reading from the view does not require the SELECT privilege on it.

With false, the following defaults are written into the view definition at creation time:

SQL SECURITY: INVOKER for normal views (configurable by default_normal_view_sql_security) and DEFINER for materialized views (configurable by default_materialized_view_sql_security)
DEFINER: CURRENT_USER (configurable by default_view_definer)

Refreshable materialized views always receive these defaults, regardless of the setting.

A view keeps the SQL security type from its stored definition when it is attached or reloaded at server startup, so a view stored without DEFINER/SQL SECURITY keeps the empty SQL security type.

To change SQL security for an existing view, use

ALTER TABLE MODIFY SQL SECURITY { DEFINER | INVOKER | NONE } [DEFINER = { user | CURRENT_USER }]

Examples

CREATE VIEW test_view
DEFINER = alice SQL SECURITY DEFINER
AS SELECT ...

CREATE VIEW test_view
SQL SECURITY INVOKER
AS SELECT ...

Live View

Deprecated feature

This feature is deprecated and will be removed in the future.

For your convenience, the old documentation is located here

Refreshable Materialized View

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
REFRESH [EVERY|AFTER interval [OFFSET interval]]
[RANDOMIZE FOR interval]
[DEPENDS ON [db.]name [, [db.]name [, ...]]]
[SETTINGS name = value [, name = value [, ...]]]
[APPEND]
[TO[db.]name] [(columns)] [ENGINE = engine]
[EMPTY]
[DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | NONE }]
AS SELECT ...
[COMMENT 'comment']

where interval is a sequence of simple intervals:

number SECOND|MINUTE|HOUR|DAY|WEEK|MONTH|YEAR

The REFRESH clause must specify at least one of EVERY, AFTER, or DEPENDS ON. Bare REFRESH (with none of these) is rejected. REFRESH DEPENDS ON ... without EVERY/AFTER is shorthand for REFRESH AFTER 0 SECOND DEPENDS ON ...; see Refresh Dependencies below.

Periodically runs the corresponding query and stores its result into a table.

If APPEND is specified, each refresh inserts rows into the table without deleting existing rows. The insert is not atomic, just like a regular INSERT INTO ... SELECT query.
Otherwise, each refresh atomically replaces the table’s previous contents.

Differences from regular non-refreshable materialized views:

No insert trigger. When new data is inserted into the table specified in SELECT, it’s not automatically pushed to the refreshable materialized view. Instead, data insertion only takes place during the periodic or manual refresh runs.
No restrictions on the SELECT query. Table functions (e.g. url()), views, UNION, JOIN, are all allowed.

Refresh Schedule

Example refresh schedules:

REFRESH EVERY 1 DAY -- every day, at midnight (UTC)
REFRESH EVERY 1 MONTH -- on 1st day of every month, at midnight
REFRESH EVERY 1 MONTH OFFSET 5 DAY 2 HOUR -- on 6th day of every month, at 2:00 am
REFRESH EVERY 2 WEEK OFFSET 5 DAY 15 HOUR 10 MINUTE -- every other Saturday, at 3:10 pm
REFRESH EVERY 30 MINUTE -- at 00:00, 00:30, 01:00, 01:30, etc
REFRESH AFTER 30 MINUTE -- 30 minutes after the previous refresh completes, no alignment with time of day
-- REFRESH AFTER 1 HOUR OFFSET 1 MINUTE -- syntax error, OFFSET is not allowed with AFTER
REFRESH EVERY 1 WEEK 2 DAYS -- every 9 days, not on any particular day of the week or month;
                            -- specifically, when day number (since 1969-12-29) is divisible by 9
REFRESH EVERY 5 MONTHS -- every 5 months, different months each year (as 12 is not divisible by 5);
                       -- specifically, when month number (since 1970-01) is divisible by 5

RANDOMIZE FOR randomly adjusts the time of each refresh, e.g.:

REFRESH EVERY 1 DAY OFFSET 2 HOUR RANDOMIZE FOR 1 HOUR -- every day at random time between 01:30 and 02:30

At most one refresh may be running at a time, for a given view. E.g. if a view with REFRESH EVERY 1 MINUTE takes 2 minutes to refresh, it’ll just be refreshing every 2 minutes. If it then becomes faster and starts refreshing in 10 seconds, it’ll go back to refreshing every minute. (In particular, it won’t refresh every 10 seconds to catch up with a backlog of missed refreshes - there’s no such backlog.)

Typically the first refresh is started immediately after the materialized view is created: time since last refresh is infinity, so any schedule says it’s time to refresh now. If EMPTY is specified, this initial refresh is skipped, and the first refresh happens at the next scheduled time; e.g. for EVERY 1 HOUR the first refresh will happen at the end of current hour.

In Replicated DB

If the refreshable materialized view is in a Replicated database, the replicas coordinate with each other such that only one replica performs the refresh at each scheduled time. ReplicatedMergeTree table engine is required, so that all replicas see the data produced by the refresh.

In APPEND mode, coordination can be disabled using SETTINGS all_replicas = 1. This makes replicas do refreshes independently of each other. In this case ReplicatedMergeTree is not required.

In non-APPEND mode, only coordinated refreshing is supported. For uncoordinated, use Atomic database and CREATE ... ON CLUSTER query to create refreshable materialized views on all replicas.

The coordination is done through Keeper. The znode path is determined by default_replica_path server setting.

Refresh Dependencies

DEPENDS ON synchronizes refreshes of different tables:

CREATE MATERIALIZED VIEW dependent REFRESH EVERY 1 HOUR DEPENDS ON dependency [...]

Dependent view’s refresh will start only after all dependency views’ refreshes complete.

To refresh immediately after another view’s refresh:

CREATE MATERIALIZED VIEW dependent REFRESH AFTER 0 SECOND DEPENDS ON dependency [...]

Or equivalently:

CREATE MATERIALIZED VIEW dependent REFRESH DEPENDS ON dependency [...]

Using DEPENDS ON for consistent propagation latency

If both views use REFRESH EVERY with the same period, the dependency applies in each timeslot.

E.g. suppose views X and Y both use REFRESH EVERY 1 HOUR, and Y reads from X’s output table. Without dependencies, Y would usually see X’s data from previous hour’s refresh. With DEPENDS ON X, Y’s 11:00 refresh will start only after the X’s 11:00 refresh completes.

           10:00            11:00            12:00
           │                │                │
  X:        [run]┐           [run]┐           [run]┐
                 │                │                │
  Y:             └►[run]          └►[run]          └►[run]

Both dependency and dependent may independently skip timeslots if refreshes run for longer than the refresh period. There’s no guarantee that the dependent refreshes exactly once for each dependency refresh.

           10:00          11:00          12:00          13:00
           │              │              │              |
  X:        [run]┐         [run]┐         [run]┐         [run]┐
                 │              └────┐    (Y skips 12:00)     └───┐
  Y:             └►[10:00 ru------un]└►[11:00 ru---------------un]└►[13:00 run]

Using DEPENDS ON for batched stream processing

If REFRESH EVERY is not used, the dependent view X refreshes if all its dependencies refreshed at least once since X’s last refresh. REFRESH AFTER T adds a delay: the dependent will start refresh T time after the dependency completes a refresh.

Circular dependencies are allowed and useful. Consider this graph of refreshable materialized views:

X takes a batch of rows from some stream and puts them in a table.
Then Y and Z both read from that table, do different aggregation, and append results to other tables.
After the batch is fully processed, X takes the next batch, and the cycle repeats.

            source
               │
               ▼
          ┌─────────┐
     ┌───►│    X    │◄───┐
     │    └──┬───┬──┘    │
  DEPENDS    │   │    DEPENDS
    ON       ▼   ▼      ON
     │      ┌─┐ ┌─┐      │
     └──────┤Y│ │Z├──────┘
            └─┘ └─┘

Complete example:

CREATE TABLE current_batch (t UInt64, v Int64) ENGINE ReplicatedMergeTree ORDER BY t;
CREATE TABLE batch_log (max_t UInt64, n Int64, v_sum Int64, processed_at DateTime64) ENGINE ReplicatedMergeTree ORDER BY max_t;
CREATE TABLE stats (h UInt64, n UInt64) ENGINE ReplicatedSummingMergeTree ORDER BY h;

-- (system.numbers stands in for a data source with monotonically increasing timestamps or sequence numbers)
CREATE MATERIALIZED VIEW current_batch_v REFRESH EVERY 10 SECOND DEPENDS ON batch_log_v, stats_v TO current_batch AS SELECT number as t, number * 10 as v FROM system.numbers WHERE number > (SELECT max(max_t) FROM batch_log) LIMIT 100;

CREATE MATERIALIZED VIEW batch_log_v REFRESH DEPENDS ON current_batch_v APPEND TO batch_log AS SELECT max(t) as max_t, count() as n, sum(v) as v_sum, now64() as processed_at FROM current_batch;

CREATE MATERIALIZED VIEW stats_v REFRESH DEPENDS ON current_batch_v APPEND TO stats AS SELECT cityHash64(v) % 20 as h, count() as n FROM current_batch GROUP BY h;

-- Must trigger initial refresh manually.
SYSTEM REFRESH VIEW current_batch_v;

Longer chains work as well.

This only works well when refresh coordination is enabled, i.e. the views are in Replicated or Shared database. Without coordination, server restart breaks the cycle, requiring a manual SYSTEM REFRESH VIEW after each restart rather than once after creating the views.

Refresh Settings

Available refresh settings:

refresh_retries - How many times to retry if refresh query fails with an exception. If all retries fail, skip to the next scheduled refresh time. 0 means no retries, -1 means infinite retries. Default: 2.
refresh_retry_initial_backoff_ms - Delay before the first retry, if refresh_retries is not zero. Each subsequent retry doubles the delay, up to refresh_retry_max_backoff_ms. Default: 100 ms.
refresh_retry_max_backoff_ms - Limit on the exponential growth of delay between refresh attempts. Default: 60000 ms (1 minute).
all_replicas - In a Replicated database with APPEND, controls whether all replicas refresh independently or only one replica refreshes at each scheduled time. Cannot be changed after the view is created. Default: false.

Changing Refresh Parameters

Refresh parameters of an existing refreshable materialized view are changed with ALTER TABLE ... MODIFY REFRESH:

ALTER TABLE [db.]name MODIFY REFRESH EVERY|AFTER ... [RANDOMIZE FOR ...] [DEPENDS ON ...] [SETTINGS ...]

The schedule (EVERY or AFTER) is mandatory: the statement always replaces all refresh parameters — schedule, RANDOMIZE FOR, DEPENDS ON, and refresh settings — with what is specified. Anything omitted is reset to its default (settings) or removed (dependencies, randomization).

Note

To change only refresh settings (e.g. refresh_retries), repeat the existing schedule:

ALTER TABLE rmv MODIFY REFRESH EVERY 1 HOUR SETTINGS refresh_retries = 5;

ALTER TABLE ... MODIFY SETTING refresh_retries = ... is not supported on materialized views; you must go through MODIFY REFRESH.
Adding or removing APPEND is not supported.
The all_replicas setting cannot be changed after creation.

Examples:

-- Change the schedule, drop existing settings and dependencies.
ALTER TABLE rmv MODIFY REFRESH EVERY 30 MINUTE;

-- Change the schedule and tune retry behavior.
ALTER TABLE rmv MODIFY REFRESH EVERY 30 MINUTE
SETTINGS refresh_retries = 5,
         refresh_retry_initial_backoff_ms = 500,
         refresh_retry_max_backoff_ms = 60000;

-- Keep the dependency while changing the period.
ALTER TABLE rmv MODIFY REFRESH EVERY 6 HOUR DEPENDS ON other_rmv;

-- Drop the dependency by omitting `DEPENDS ON`.
ALTER TABLE rmv MODIFY REFRESH EVERY 6 HOUR;

Other operations

The status of all refreshable materialized views is available in table system.view_refreshes. In particular, it contains refresh progress (if running), last and next refresh time, exception message if a refresh failed.

To manually stop, start, trigger, or cancel refreshes, use SYSTEM STOP|START|REFRESH|WAIT|CANCEL VIEW.

To wait for a refresh to complete, use SYSTEM WAIT VIEW. In particular, useful for waiting for initial refresh after creating a view.

Temporary Views

ClickHouse supports temporary views with the following characteristics (matching temporary tables where applicable):

Session-lifetime A temporary view exists only for the duration of the current session. It is dropped automatically when the session ends.
No database You cannot qualify a temporary view with a database name. It lives outside databases (session namespace).
Not replicated / no ON CLUSTER Temporary objects are local to the session and cannot be created with ON CLUSTER.
Name resolution If a temporary object (table or view) has the same name as a persistent object and a query references the name without a database, the temporary object is used.
Logical object (no storage) A temporary view stores only its SELECT text (uses the View storage internally). It does not persist data and cannot accept INSERT.
Engine clause You do not need to specify ENGINE; if provided as ENGINE = View, it’s ignored/treated as the same logical view.
Security / privileges Creating a temporary view requires the privilege CREATE TEMPORARY VIEW which is implicitly granted by CREATE VIEW.
SHOW CREATE Use SHOW CREATE TEMPORARY VIEW view_name; to print the DDL of a temporary view.

Syntax

CREATE TEMPORARY VIEW [IF NOT EXISTS] view_name AS <select_query>

OR REPLACE is not supported for temporary views (to match temporary tables). If you need to “replace” a temporary view, drop it and create it again.

Examples

Create a temporary source table and a temporary view on top:

CREATE TEMPORARY TABLE t_src (id UInt32, val String);
INSERT INTO t_src VALUES (1, 'a'), (2, 'b');

CREATE TEMPORARY VIEW tview AS
SELECT id, upper(val) AS u
FROM t_src
WHERE id <= 2;

SELECT * FROM tview ORDER BY id;

Show its DDL:

SHOW CREATE TEMPORARY VIEW tview;

Drop it:

DROP TEMPORARY VIEW IF EXISTS tview;  -- temporary views are dropped with TEMPORARY TABLE syntax

Disallowed / limitations

CREATE OR REPLACE TEMPORARY VIEW ... → not allowed (use DROP + CREATE).
CREATE TEMPORARY MATERIALIZED VIEW ... → not allowed.
CREATE TEMPORARY VIEW db.view AS ... → not allowed (no database qualifier).
CREATE TEMPORARY VIEW view ON CLUSTER 'name' AS ... → not allowed (temporary objects are session-local).
POPULATE, REFRESH, TO [db.table], inner engines, and all MV-specific clauses → not applicable to temporary views.

Notes on distributed queries

A temporary view is just a definition; there’s no data to pass around. If your temporary view references temporary tables (e.g., Memory), their data can be shipped to remote servers during distributed query execution the same way temporary tables work.

Example

-- A session-scoped, in-memory table
CREATE TEMPORARY TABLE temp_ids (id UInt64) ENGINE = Memory;

INSERT INTO temp_ids VALUES (1), (5), (42);

-- A session-scoped view over the temp table (purely logical)
CREATE TEMPORARY VIEW v_ids AS
SELECT id FROM temp_ids;

-- Replace 'test' with your cluster name.
-- GLOBAL JOIN forces ClickHouse to *ship* the small join-side (temp_ids via v_ids)
-- to every remote server that executes the left side.
SELECT count()
FROM cluster('test', system.numbers) AS n
GLOBAL ANY INNER JOIN v_ids USING (id)
WHERE n.number < 100;

Normal View

Parameterized View

Materialized View

CREATE OR REPLACE MATERIALIZED VIEW

SQL security

Examples

Live View

Refreshable Materialized View

Refresh Schedule

In Replicated DB

Refresh Dependencies

Using DEPENDS ON for consistent propagation latency

Using DEPENDS ON for batched stream processing

Refresh Settings

Changing Refresh Parameters

Other operations

Related Content

Temporary Views

Syntax

Examples

Disallowed / limitations

Notes on distributed queries

Example