Use the CREATE VIEW statement to create a normal view - ApsaraDB for ClickHouse

Use CREATE VIEW to create a normal view in ApsaraDB for ClickHouse. ApsaraDB for ClickHouse supports two view types: normal view (this topic) and materialized view.

Syntax

CREATE VIEW [IF NOT EXISTS] [db.]view_name [ON CLUSTER cluster] AS SELECT ...

Parameters

Parameter	Description
`IF NOT EXISTS`	Skip view creation if a view with the same name already exists.
`db`	Database name. Defaults to the current database.
`view_name`	Name of the view.
`ON CLUSTER cluster`	Create the view on every node in the cluster.
`SELECT ...`	The query that defines the view. Supports `DISTINCT`, `GROUP BY`, `ORDER BY`, and `LIMIT`.

In ApsaraDB for ClickHouse, set ON CLUSTER to default. For example: ON CLUSTER default.

Create a view

The following example creates a source table, creates a view over it, inserts data, and then queries the view.

Create the source table.

CREATE TABLE test ON CLUSTER default (
  id   Int32,
  name String
) ENGINE = MergeTree()
ORDER BY (id);

Create a view based on the source table.

CREATE VIEW test_view ON CLUSTER default AS SELECT * FROM test;

Insert data into the source table.

INSERT INTO test VALUES (1, 'a'), (2, 'b'), (3, 'c');

Query the view.

SELECT * FROM test_view;

Expected output:

id│name
─│──
 1│ a
 2│ b
 3│ c

References

CREATE VIEW — ClickHouse documentation