> For the complete documentation index, see [llms.txt](https://upsolver.gitbook.io/content/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://upsolver.gitbook.io/content/reference-1/sql-commands/materialized-views/create-materialized-view.md).

# CREATE MATERIALIZED VIEW

This command creates a materialized view that contains the defined key column as well as any defined aggregated columns.

## Syntax

```sql
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> ]
        
[ COMPUTE_CLUSTER = <cluster_identifier> ]
[ MAX_TIME_TRAVEL_DURATION = integer days ]
[ QUERY_CLUSTER = <query_cluster_identifier> ]
[ STORAGE_CONNECTION = <connection_identifier>
  STORAGE_LOCATION = '<storage_location>' ]
```

#### Jump to

* [`COMPUTE_CLUSTER`](#compute_cluster-editable)
* [`QUERY_CLUSTER`](#query_cluster-editable)
* [`MAX_TIME_TRAVEL_DURATION`](#max_time_travel_duration-editable)
* [`STORAGE_CONNECTION`](#storage_connection)
* [`STORAGE_LOCATION`](#storage_location)
* [`WINDOW`](#window)

## Materialized view options

#### `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) How long, in days, the state information maintained by the materialized view should be retained. By default, the state is maintained indefinitely, allowing you to time travel to any point in time from the creation of the MV.

#### `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`](#storage_location) for the table's underlying files.&#x20;

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`](#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`](#storage_connection) is set.&#x20;

When set, [`STORAGE_CONNECTION`](#storage_connection) must be configured as well to provide a connection with access to write to the specified storage location.

#### `WINDOW`

Type: `integer { MINUTE[S] | HOUR[S] | DAY[S] }`

(Optional) The time span over which the aggregation should be applied, e.g. 1 HOUR.

## Example

{% code overflow="wrap" %}

```sql
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
    WINDOW 1 HOUR;
```

{% endcode %}

{% hint style="success" %}
To learn how to use your materialized view, see [Join with a Materialized View](/content/reference-1/sql-commands/materialized-views/join-with-a-materialized-view.md).
{% endhint %}


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://upsolver.gitbook.io/content/reference-1/sql-commands/materialized-views/create-materialized-view.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.