Comment on page

CREATE MATERIALIZED VIEW

This command creates a materialized view that contains the defined key column as well as any defined aggregated columns.
Note that materialized views cannot currently be queried or previewed after they are created.
Additionally, all materialized views must be based on an aggregated query.
See also:
​ALTER MATERIALIZED VIEW, DROP MATERIALIZED VIEW​
Syntax
CREATE [SYNC] MATERIALIZED VIEW <mv_identifier> AS
​
    SELECT <column> [ [AS] <key_column_name> ] 
           [, <AGGREGATED_FUNCTION>(<column_name>) [AS] <aggregated_column_name>] 
           [,...]
        FROM <source> [ [AS] alias ]
        [ LET identifier = expression [, ...] ] 
        [ WHERE [ { $commit_time | $event_time } >= timestamp '...'] ]
            [ AND <where_filters> ]
        [ GROUP BY { <field_name> | <ordinal_position> } [, ...] ]
        [ WINDOW <integer> <time_unit> ]
​
[ STORAGE_CONNECTION = <connection_identifier>
  STORAGE_LOCATION = '<storage_location>' ] 
[ COMPUTE_CLUSTER = <cluster_identifier> ]
[ QUERY_CLUSTER = <query_cluster_identifier> ]
[ MAX_TIME_TRAVEL_DURATION = integer days ]
Jump to
​STORAGE_CONNECTION​
​STORAGE_LOCATION​
​COMPUTE_CLUSTER​
​QUERY_CLUSTER​
​MAX_TIME_TRAVEL_DURATION​
​WINDOW​
Materialized view options
STORAGE_CONNECTION
Type: identifier
Default: The storage connection of the first table in the FROM statement
(Optional) The storage connection associated with the STORAGE_LOCATION for the table's underlying files.
Only a storage type connection can be used here (e.g. S3, Blob storage, GCS, Oracle object storage), and it should match the catalog's metastore. For example, if Glue is used as the metastore, only S3 is allowed as a storage connection.
When set, STORAGE_LOCATION must be configured as well to provide a path to store the data.
STORAGE_LOCATION
Type: text
Default: The storage location of the first table in the FROM statement
(Optional) The storage location for the materialized view's underlying files. It should be provided in the format s3://bucket_name/path_to_data. This option is required when STORAGE_CONNECTION is set.
When set, STORAGE_CONNECTION must be configured as well to provide a connection with access to write to the specified storage location.
COMPUTE_CLUSTER — editable
Type: identifier
Default: The compute cluster of the first source table within the SELECT statement
(Optional) The compute cluster that processes the materialized view.
QUERY_CLUSTER — editable
Type: identifier
(Optional) The query cluster that processes the materialized view.
MAX_TIME_TRAVEL_DURATION — editable
Type: integer
Default: infinite
(Optional) The number of days that the state information maintained by the materialized view should be retained. By default, the state is maintained indefinitely, enabling you to travel to any point in time from the creation of the materialized view.
WINDOW
Type: integer
(Optional) The maximum number of days to save the materialized view data before it is deleted.
Examples
CREATE SYNC MATERIALIZED VIEW default_glue_catalog.upsolver_samples.physical_store_orders_materialized_view AS 
    SELECT orderid, 
       LAST(saleinfo.source) as source,
       LAST(saleinfo.store.location.country) as country,
       LAST(saleinfo.store.location.name) as name,
       LAST(saleinfo.store.servicedby.employeeid) as employeeid,
       LAST(saleinfo.store.servicedby.firstname) as firstname,
       LAST(saleinfo.store.servicedby.lastname) as lastname
    FROM default_glue_catalog.upsolver_samples.sales_info_raw_data
    GROUP BY orderid;
To learn how to use your materialized view, see: Join with MATERIALIZED VIEW​

MATERIALIZED VIEWS

Join with MATERIALIZED VIEW

Last modified 1mo ago

CREATE MATERIALIZED VIEW

Syntax

Jump to

Materialized view options

`STORAGE_CONNECTION`

`STORAGE_LOCATION`

`COMPUTE_CLUSTER` — editable

`QUERY_CLUSTER` — editable

`MAX_TIME_TRAVEL_DURATION` — editable

`WINDOW`

Examples