CREATE MATERIALIZED VIEW#

Synopsis#

CREATE [ OR REPLACE ] MATERIALIZED VIEW
[ IF NOT EXISTS ] view_name
[ COMMENT string ]
[ WITH properties ]
AS query

Description#

Create and validate the definition of a new materialized view view_name of a SELECT query. You need to run the REFRESH MATERIALIZED VIEW statement after the creation to populate the materialized view with data. This materialized view is a physical manifestation of the query results at time of refresh. The data is stored, and can be referenced by future queries.

Queries accessing materialized views are typically faster than retrieving data from a view created with the same query. Any computation, aggregation, and other operation to create the data is performed once during refresh of the materialized views, as compared to each time of accessing the view. Multiple reads of view data over time, or by multiple users, all trigger repeated processing. This is avoided for materialized views.

When the underlying data changes, the materialized view becomes out of sync with the source tables. Update the data in the materialized view with the REFRESH MATERIALIZED VIEW statement.

The optional OR REPLACE clause causes the materialized view to be replaced if it already exists rather than raising an error.

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

The optional COMMENT clause causes a string comment to be stored with the metadata about the materialized view. The comment is displayed with the SHOW CREATE MATERIALIZED VIEW statement and is available in the table system.metadata.materialized_view_properties.

The optional WITH clause is used to define properties for the materialized view creation. Separate multiple property/value pairs by commas. The connector uses the properties as input parameters for the materialized view refresh operation. The supported properties are different for each connector and detailed in the SQL support section of the specific connector’s documentation.

After successful creation, all metadata about the materialized view is available in the table system.metadata.materialized_views in the following columns:

Metadata for materialized views#

Column

Description

catalog_name

Name of the catalog containing the materialized view.

schema_name

Name of the schema in catalog_name containing the materialized view.

name

Name of the materialized view.

storage_catalog

Name of the catalog used for the storage table backing the materialized view.

storage_schema

Name of the schema in storage_catalog used for the storage table backing the materialized view.

storage_table

Name of the storage table backing the materialized view.

is_fresh

Flag to signal if data in the storage table is up to date. Queries on the materialized view access the storage table if true, otherwise the definition is used to access the underlying data in the source tables.

owner

Username of the creator and owner of the materialized view.

comment

User supplied text about the materialized view.

definition

SQL query that defines the data provided by the materialized view.

Examples#

Create a simple materialized view cancelled_orders over the orders table that only includes cancelled orders. Note that orderstatus is a numeric value that is potentially meaningless to a consumer, yet the name of the view clarifies the content:

CREATE MATERIALIZED VIEW cancelled_orders
AS
    SELECT orderkey, totalprice
    FROM orders
    WHERE orderstatus = 3;

Create or replace a materialized view order_totals_by_date that summarizes orders across all orders from all customers:

CREATE OR REPLACE MATERIALIZED VIEW order_totals_by_date
AS
    SELECT orderdate, sum(totalprice) AS price
    FROM orders
    GROUP BY orderdate;

Create a materialized view with a comment and partitioning on two fields in the storage:

CREATE MATERIALIZED VIEW orders_nation_mkgsegment
COMMENT 'Orders with nation and market segment data'
WITH ( partitioning = ARRAY['mktsegment', 'nationkey'] )
AS
    SELECT o.*, c.nationkey, c.mktsegment
    FROM orders AS o
    JOIN customer AS c
    ON o.custkey = c.custkey;

Set multiple properties:

WITH ( format = 'ORC', partitioning = ARRAY['_date'] )

Show defined materialized view properties for all catalogs:

SELECT * FROM system.metadata.materialized_view_properties;

Show metadata about the materialized views in all catalogs:

SELECT * FROM system.metadata.materialized_views;

See also#