Views

Elena Dvoryadkina

A view is a named SQL query saved to a database. A view does not store data, but reads it from another table each time the view is referenced. Actually, when you read from a view, its underlying query is used as a subquery in the FROM clause. Views allow you to encapsulate frequently used or complex queries and reference them in SELECT statements as if they were regular tables.

A parameterized view is a view with parameters in the filter condition of its underlying query, whose values should be passed each time the view is referenced. Parameterized views can be convenient when you need to reuse complex analytical queries with variable filter conditions.

ADQM/ClickHouse also supports materialized views, which, unlike views, physically store data (aggregate states of data from another table) and are updated automatically when new data is inserted into the source table — see more details in the Materialized views article.

Brief comparison of view types
View type Data storage Parameters Query performance Update of a view

View

No

No

No impact (a query is executed each time a view is referenced)

Each time a view is referenced

Parameterized view

No

Yes

No impact (a query is executed each time a view is referenced)

Each time a view is referenced

Materialized view

Yes

No

Improved (fast reading due to pre-aggregation of data)

On data inserts into a view’s source table
Create a table for test examples

Create the sales table and populate it with test data:

CREATE TABLE sales (
    sale_id UInt32,
    product_name String,
    sale_amount Decimal(10,2),
    customer_name String,
    sale_date Date
)
ENGINE = MergeTree()
ORDER BY sale_id;
INSERT INTO sales VALUES
(1, 'Smartphone_2', 700.00, 'Mary Burton', '2026-04-15'),
(2, 'Smartphone_1', 750.00, 'Robert Gray', '2026-04-17'),
(3, 'Monitor', 200.00, 'Linda Black', '2026-04-17'),
(4, 'Laptop', 1000.00, 'Paul Brown', '2026-04-17'),
(5, 'Monitor', 200.00, 'Mark Armstrong', '2026-04-18'),
(6, 'Keyboard', 75.50, 'David Green', '2026-04-18'),
(7, 'Keyboard', 75.50, 'Daniel Gray', '2026-05-01'),
(8, 'Laptop', 1000.00, 'Robert Gray', '2026-05-02'),
(9, 'Mouse', 25.50, 'Joseph Lewis', '2026-05-02'),
(10, 'Smartphone_2', 700.00, 'Paul Brown', '2026-05-03');
    ┌─sale_id─┬─product_name─┬─sale_amount─┬─customer_name──┬──sale_date─┐
 1. │       1 │ Smartphone_2 │         700 │ Mary Burton    │ 2026-04-15 │
 2. │       2 │ Smartphone_1 │         750 │ Robert Gray    │ 2026-04-17 │
 3. │       3 │ Monitor      │         200 │ Linda Black    │ 2026-04-17 │
 4. │       4 │ Laptop       │        1000 │ Paul Brown     │ 2026-04-17 │
 5. │       5 │ Monitor      │         200 │ Mark Armstrong │ 2026-04-18 │
 6. │       6 │ Keyboard     │        75.5 │ David Green    │ 2026-04-18 │
 7. │       7 │ Keyboard     │        75.5 │ Daniel Gray    │ 2026-05-01 │
 8. │       8 │ Laptop       │        1000 │ Robert Gray    │ 2026-05-02 │
 9. │       9 │ Mouse        │        25.5 │ Joseph Lewis   │ 2026-05-02 │
10. │      10 │ Smartphone_2 │         700 │ Paul Brown     │ 2026-05-03 │
    └─────────┴──────────────┴─────────────┴────────────────┴────────────┘

Create views

Create a view

To create a view, use the CREATE VIEW query. The basic syntax is:

CREATE VIEW [IF NOT EXISTS] [<db_name>.]<view_name> [ON CLUSTER <cluster_name>]
[DEFINER = { '<user_name>' | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }]
AS SELECT ...
[COMMENT 'comment'];

where DEFINER and SQL SECURITY are parameters for data access control (see the description below).

NOTE

The view(<subquery>) table function implements the mechanism similar to a view (CREATE VIEW), but exists only within a query where it is called (no object is created in a database). It returns the result of a subquery as a table, allowing it to be used as a data source in the FROM clause. Similar to a view, the function does not store data, and the corresponding subquery is executed each time the function is called.

Example

Create a view that retrieves sales data for May:

CREATE VIEW sales_may AS
SELECT * FROM sales
WHERE toMonth(sale_date) = 5;

Query a view as a regular table using the SELECT statement:

SELECT * FROM sales_may;
   ┌─sale_id─┬─product_name─┬─sale_amount─┬─customer_name─┬──sale_date─┐
1. │       7 │ Keyboard     │        75.5 │ Daniel Gray   │ 2026-05-01 │
2. │       8 │ Laptop       │        1000 │ Robert Gray   │ 2026-05-02 │
3. │       9 │ Mouse        │        25.5 │ Joseph Lewis  │ 2026-05-02 │
4. │      10 │ Smartphone_2 │         700 │ Paul Brown    │ 2026-05-03 │
   └─────────┴──────────────┴─────────────┴───────────────┴────────────┘

Create a parameterized view

A parameterized view is created like a regular view, but the WHERE condition contains parameters in the {<param_name>:<param_type>} form, for example:

CREATE VIEW [IF NOT EXISTS] [<db_name>.]<view_name> [ON CLUSTER <cluster_name>]
[DEFINER = { '<user_name>' | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }]
AS SELECT ...
WHERE <column_name1> = {<param_name1>:<data_type1>} AND <column_name2> != {<param_name2>:<data_type2>} ...
[COMMENT 'comment'];

where <param_name1>, <param_name2> are parameter names, and <data_type1>, <data_type2> are parameter data types.

Parameter values should be passed each time a view is referenced as follows:

SELECT <list_of_columns> FROM <view_name>(<param_name1>=<value1>, <param_name2>=<value2>, ...);

Thus, you can access a parameterized view as a table function, specifying a view name as a function name and parameter values as the function’s named arguments.

Example

Create a parameterized view that retrieves information about sales of a specified product for a specified month:

CREATE VIEW sales_product_by_month AS
SELECT * FROM sales
WHERE product_name = {product:String} AND toMonth(sale_date) = {month:UInt8};

When referencing the view, pass the product name and month number as values ​​of the product and month parameters, respectively:

SELECT * FROM sales_product_by_month(product='Monitor', month=4);
   ┌─sale_id─┬─product_name─┬─sale_amount─┬─customer_name──┬──sale_date─┐
1. │       3 │ Monitor      │         200 │ Linda Black    │ 2026-04-17 │
2. │       5 │ Monitor      │         200 │ Mark Armstrong │ 2026-04-18 │
   └─────────┴──────────────┴─────────────┴────────────────┴────────────┘

SQL SECURITY and DEFINER

The SQL SECURITY and DEFINER options, which you can specify when creating views, allow flexible configuration of data access. For example, you can hide source tables (from which views read data) from users, while giving them access to aggregated or filtered data through views.

Parameter Description Possible values

SQL SECURITY

Specifies which user’s permissions to use when executing a query that defines a view

  • DEFINER — a query will be executed on behalf of a user specified via the DEFINER parameter. This user should have the SELECT permission on a table from which a view reads data.

  • INVOKER (by default) — a query will be executed on behalf of a user referencing a view. This user should have the SELECT permission on a table from which the view reads data.

DEFINER

Specifies which user’s privileges to use when executing a view’s underlying query if SQL SECURITY DEFINER is set.

To set the parameter value, a user creating a view should have the SET DEFINER privilege

  • '<user_name>' — name of an existing ADQM user.

  • CURRENT_USER (by default) — the parameter value is set to a name of a user creating a view.

Regardless of how access control settings are configured, a user requires the SELECT ON <view_name> permission to read data from a view.

Example

Assume that only the administrator (for example, the admin user who created the table) can access to all the data of the sales table, while other users can retrieve sales data, but not customer names. To implement this scenario, follow the steps below:

  1. Create a new user:

    CREATE USER john IDENTIFIED WITH sha256_password BY 'qwerty';

  2. On behalf of the administrator, create a view that reads sales data from the sales table except for the names of customers:

    CREATE VIEW sales_without_customers
DEFINER = admin SQL SECURITY DEFINER
AS SELECT sale_id, product_name, sale_amount, sale_date
FROM sales;

  3. Grant the john user access to this view:

    GRANT SELECT ON sales_without_customers TO john;

  4. Query the view on behalf of john:

    SELECT * FROM sales_without_customers;

    As a result, the john user has access to sales data without information about customers through the sales_without_customers view:

        ┌─sale_id─┬─product_name─┬─sale_amount─┬──sale_date─┐
 1. │       1 │ Smartphone_2 │         700 │ 2026-04-15 │
 2. │       2 │ Smartphone_1 │         750 │ 2026-04-17 │
 3. │       3 │ Monitor      │         200 │ 2026-04-17 │
 4. │       4 │ Laptop       │        1000 │ 2026-04-17 │
 5. │       5 │ Monitor      │         200 │ 2026-04-18 │
 6. │       6 │ Keyboard     │        75.5 │ 2026-04-18 │
 7. │       7 │ Keyboard     │        75.5 │ 2026-05-01 │
 8. │       8 │ Laptop       │        1000 │ 2026-05-02 │
 9. │       9 │ Mouse        │        25.5 │ 2026-05-02 │
10. │      10 │ Smartphone_2 │         700 │ 2026-05-03 │
    └─────────┴──────────────┴─────────────┴────────────┘

    However, the john user does not have direct access to the sales table. If john tries to read data from the sales table, the following message will be displayed:

    Exception: Received from localhost:9000. DB::Exception: john: Not enough privileges.
To execute this query, it's necessary to have the grant SELECT(sale_id, product_name, sale_amount, customer_name, sale_date) ON default.sales. (ACCESS_DENIED)

Modify views

Change a view’s query

If you need to change a query that defines a view, use the CREATE OR REPLACE VIEW command:

CREATE OR REPLACE VIEW [<db_name>.]<view_name> AS SELECT ...

If a view with this name exists, it will be updated according to the new definition of its query. If the view does not exist, it will be created.

Change SQL SECURITY and DEFINER

To change the SQL SECURITY and DEFINER settings for an existing view, use the ALTER TABLE command:

ALTER TABLE <view_name> MODIFY SQL SECURITY { DEFINER | INVOKER } [DEFINER = { '<user_name>' | CURRENT_USER }];

Get information on views

There are several ways to obtain information about views located in an ADQM/ClickHouse database.

  • Get all tables and views (see the engine column — the View table engine is used to implement views):

    SHOW FULL TABLES;
       ┌─name────────────────────┬─engine────┐
1. │ sales                   │ MergeTree │
2. │ sales_may               │ View      │
3. │ sales_product_by_month  │ View      │
4. │ sales_without_customers │ View      │
   └─────────────────────────┴───────────┘

  • Get detailed information about views from system.tables. For example, the following columns may be useful:

    • as_select — a query that defines a view;

    • parameterized_view_parameters — parameters of a parameterized view;

    • definer — a user whose rights are used to execute a view’s underlying query.

    SELECT
    name,
    as_select,
    parameterized_view_parameters,
    definer
FROM system.tables
WHERE engine = 'View' AND database = '<database_name>';\G
    Row 1:
──────
name:                          sales_may
as_select:                     SELECT * FROM default.sales WHERE toMonth(sale_date) = 5
parameterized_view_parameters: []
definer:

Row 2:
──────
name:                          sales_product_by_month
as_select:                     SELECT * FROM default.sales WHERE (product_name = {product:String}) AND (toMonth(sale_date) = {month:UInt8})
parameterized_view_parameters: [('product','String'),('month','UInt8')]
definer:

Row 3:
──────
name:                          sales_without_customers
as_select:                     SELECT sale_id, product_name, sale_amount, sale_date FROM default.sales
parameterized_view_parameters: []
definer:                       admin

  • Get the CREATE VIEW statement that was used to create a specified view:

    SHOW CREATE VIEW <view_name>;

    For example, the output for the sales_without_customers view looks like:

       ┌─statement───────────────────────────────────┐
1. │ CREATE VIEW default.sales_without_customers │
   │ (                                           │
   │     `sale_id` UInt32,                       │
   │     `product_name` String,                  │
   │     `sale_amount` Decimal(10, 2),           │
   │     `sale_date` Date                        │
   │ )                                           │
   │ DEFINER = admin SQL SECURITY DEFINER        │
   │ AS SELECT                                   │
   │     sale_id,                                │
   │     product_name,                           │
   │     sale_amount,                            │
   │     sale_date                               │
   │ FROM default.sales                          │
   └─────────────────────────────────────────────┘

  • You can also find information about views in the VIEWS table of the INFORMATION_SCHEMA system database:

    SELECT table_name, view_definition
FROM information_schema.views
WHERE table_schema = '<your_database_name>';
       ┌─table_name──────────────┬─view_definition──────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ sales_may               │ SELECT * FROM default.sales WHERE toMonth(sale_date) = 5                                                     │
2. │ sales_product_by_month  │ SELECT * FROM default.sales WHERE (product_name = {product:String}) AND (toMonth(sale_date) = {month:UInt8}) │
3. │ sales_without_customers │ SELECT sale_id, product_name, sale_amount, sale_date FROM default.sales                                      │
   └─────────────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Detach and attach views

You can detach a view with the DETACH TABLE query:

DETACH VIEW [IF EXISTS] [<db_name.]<view_name> [ON CLUSTER <cluster_name>] [PERMANENTLY];

When this query is executed, the view becomes "invisible" to the server (no longer available for queries), but its metadata is not deleted. If the view was not detached permanently (the PERMANENTLY clause was not used in a query), the next time the server starts, it will read the view metadata and "see" the view again. If a view has been detached permanently, the server will not automatically re-enable it.

You can re-attach a view with the ATTACH TABLE query regardless of whether it was detached permanently or not:

ATTACH TABLE [IF NOT EXISTS] [<db_name.]<view_name> [ON CLUSTER <cluster_name>];

Delete views

To remove a view, use the DROP VIEW command:

DROP VIEW [IF EXISTS] [<db_name.]<view_name> [ON CLUSTER <cluster_name>];

You can also delete a view using the DROP TABLE query:

DROP TABLE [IF EXISTS] [<db_name.]<view_name> [ON CLUSTER <cluster_name>];
Found a mistake? Seleсt text and press Ctrl+Enter to report it