CREATE VIEW

### Monitoring New Windows[​](#monitoring-new-windows "Direct link to Monitoring New Windows")

Window view supports the [WATCH](/docs/sql-reference/statements/watch) query to monitoring changes, or use `TO` syntax to output the results to a table.

```
WATCH [db.]window_view
[EVENTS]
[LIMIT n]
[FORMAT format]
```

A `LIMIT` can be specified to set the number of updates to receive before terminating the query. The `EVENTS` clause can be used to obtain a short form of the `WATCH` query where instead of the query result you will just get the latest query watermark.

### Settings[​](#settings-1 "Direct link to Settings")

- `window_view_clean_interval`: The clean interval of window view in seconds to free outdated data. The system will retain the windows that have not been fully triggered according to the system time or `WATERMARK` configuration, and the other data will be deleted.
- `window_view_heartbeat_interval`: The heartbeat interval in seconds to indicate the watch query is alive.
- `wait_for_window_view_fire_signal_timeout`: Timeout for waiting for window view fire signal in event time processing.

### Example[​](#example "Direct link to Example")

Suppose we need to count the number of click logs per 10 seconds in a log table called `data`, and its table structure is:

```
CREATE TABLE data ( `id` UInt64, `timestamp` DateTime) ENGINE = Memory;
```

First, we create a window view with tumble window of 10 seconds interval:

```
CREATE WINDOW VIEW wv as select count(id), tumbleStart(w_id) as window_start from data group by tumble(timestamp, INTERVAL '10' SECOND) as w_id
```

Then, we use the `WATCH` query to get the results.

```
WATCH wv
```

When logs are inserted into table `data`,

```
INSERT INTO data VALUES(1,now())
```

The `WATCH` query should print the results as follows:

```
┌─count(id)─┬────────window_start─┐
│         1 │ 2020-01-14 16:56:40 │
└───────────┴─────────────────────┘
```

Alternatively, we can attach the output to another table using `TO` syntax.

```
CREATE WINDOW VIEW wv TO dst AS SELECT count(id), tumbleStart(w_id) as window_start FROM data GROUP BY tumble(timestamp, INTERVAL '10' SECOND) as w_id
```

Additional examples can be found among stateful tests of ClickHouse (they are named `*window_view*` there).

### Window View Usage[​](#window-view-usage "Direct link to Window View Usage")

The window view is useful in the following scenarios:

- **Monitoring**: Aggregate and calculate the metrics logs by time, and output the results to a target table. The dashboard can use the target table as a source table.
- **Analyzing**: Automatically aggregate and preprocess data in the time window. This can be useful when analyzing a large number of logs. The preprocessing eliminates repeated calculations in multiple queries and reduces query latency.

## Related Content[​](#related-content "Direct link to Related Content")

- Blog: [Working with time series data in ClickHouse](https://clickhouse.com/blog/working-with-time-series-data-and-functions-ClickHouse)
- Blog: [Building an Observability Solution with ClickHouse - Part 2 - Traces](https://clickhouse.com/blog/storing-traces-and-spans-open-telemetry-in-clickhouse)

## Temporary Views[​](#temporary-views "Direct link to 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[​](#temporary-views-syntax "Direct link to 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[​](#temporary-views-examples "Direct link to 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[​](#temporary-views-limitations "Direct link to Disallowed / limitations")

- `CREATE OR REPLACE TEMPORARY VIEW ...` → **not allowed** (use `DROP` + `CREATE`).
- `CREATE TEMPORARY MATERIALIZED VIEW ...` / `WINDOW 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[​](#temporary-views-distributed-notes "Direct link to 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[​](#temporary-views-distributed-example "Direct link to 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](#normal-view)- [Parameterized View](#parameterized-view)- [Materialized View](#materialized-view)- [SQL security](#sql_security)
        - [Examples](#examples)- [Live View](#live-view)- [Refreshable Materialized View](#refreshable-materialized-view)
            - [Refresh Schedule](#refresh-schedule)- [In Replicated DB](#in-replicated-db)- [Refresh Dependencies](#refresh-dependencies)- [Refresh Settings](#refresh-settings)- [Changing Refresh Parameters](#changing-refresh-parameters)- [Other operations](#other-operations)- [Window View](#window-view)
              - [Time Window Functions](#time-window-functions)- [TIME ATTRIBUTES](#time-attributes)- [Monitoring New Windows](#monitoring-new-windows)- [Settings](#settings-1)- [Example](#example)- [Window View Usage](#window-view-usage)- [Related Content](#related-content)- [Temporary Views](#temporary-views)
                  - [Syntax](#temporary-views-syntax)- [Examples](#temporary-views-examples)- [Disallowed / limitations](#temporary-views-limitations)- [Notes on distributed queries](#temporary-views-distributed-notes)

Was this page helpful?