CREATE MATERIALIZED VIEW¶

Synopsis¶

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] view_name
[ COMMENT 'string' ]
[ SECURITY { DEFINER | INVOKER } ]
[ WITH ( property_name = expression [, ...] ) ]
AS query

Description¶

Create a new materialized view of a SELECT query. The materialized view physically stores the query results, unlike regular views which are virtual. Queries can read pre-computed results instead of re-executing the underlying query.

The optional IF NOT EXISTS clause causes the materialized view to be created only if it does not already exist.

The optional COMMENT clause stores a description of the materialized view in the metastore.

The optional SECURITY clause specifies the security mode for the materialized view. When legacy_materialized_views=false:

• SECURITY DEFINER: The view executes with the permissions of the user who created it. This is the default mode if SECURITY is not specified and matches the behavior of most SQL systems.

• SECURITY INVOKER: The view executes with the permissions of the user querying it. Each user must have appropriate permissions on the underlying base tables.

When legacy_materialized_views=true, the SECURITY clause is not supported and will cause an error if used.

The optional WITH clause specifies connector-specific properties. Connector properties vary by connector implementation. Consult connector documentation for supported properties.

Examples¶

Create a materialized view with daily aggregations:

CREATE MATERIALIZED VIEW daily_sales AS
SELECT date_trunc('day', order_date) AS day,
       region,
       SUM(amount) AS total_sales,
       COUNT(*) AS order_count
FROM orders
GROUP BY date_trunc('day', order_date), region

Create a materialized view with DEFINER security mode:

CREATE MATERIALIZED VIEW daily_sales
SECURITY DEFINER
AS
SELECT date_trunc('day', order_date) AS day,
       region,
       SUM(amount) AS total_sales
FROM orders
GROUP BY date_trunc('day', order_date), region

Create a materialized view with INVOKER security mode:

CREATE MATERIALIZED VIEW user_specific_sales
SECURITY INVOKER
AS
SELECT date_trunc('day', order_date) AS day,
       SUM(amount) AS total_sales
FROM orders
GROUP BY date_trunc('day', order_date)

Create a materialized view with connector properties:

CREATE MATERIALIZED VIEW partitioned_sales
WITH (
    partitioned_by = ARRAY['year', 'month']
)
AS
SELECT year(order_date) AS year,
       month(order_date) AS month,
       SUM(amount) AS total_sales
FROM orders
GROUP BY year(order_date), month(order_date)

See Also¶

DROP MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW, SHOW CREATE MATERIALIZED VIEW, Materialized Views