REFRESH MATERIALIZED VIEW - Amazon Redshift
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

REFRESH MATERIALIZED VIEW

Refreshes a materialized view.

When you create a materialized view, its contents reflect the state of the underlying database table or tables at that time. The data in the materialized view remains unchanged, even when applications make changes to the data in the underlying tables. To update the data in a materialized view, you can use the REFRESH MATERIALIZED VIEW statement at any time. When you use this statement, Amazon Redshift identifies changes that have taken place in the base table or tables, and then applies those changes to the materialized view.

For more information about materialized views, see Creating materialized views in Amazon Redshift.

Syntax

REFRESH MATERIALIZED VIEW mv_name

Parameters

mv_name

The name of the materialized view to be refreshed.

Usage notes

Only the owner of a materialized view can perform a REFRESH MATERIALIZED VIEW operation on that materialized view. Furthermore, the owner must have SELECT privilege on the underlying base tables to successfully run REFRESH MATERIALIZED VIEW.

The REFRESH MATERIALIZED VIEW command runs as a transaction of its own. Amazon Redshift transaction semantics are followed to determine what data from base tables is visible to the REFRESH command, or when the changes made by the REFRESH command are made visible to other transactions running in Amazon Redshift.

  • For incremental materialized views, REFRESH MATERIALIZED VIEW uses only those base table rows that are already committed. Therefore, if the refresh operation runs after a data manipulation language (DML) statement in the same transaction, then changes of that DML statement aren't visible to refresh.

  • For a full refresh of a materialized view, REFRESH MATERIALIZED VIEW sees all base table rows visible to the refresh transaction, according to usual Amazon Redshift transaction semantics.

  • Depending on the input argument type, Amazon Redshift still supports incremental refresh for materialized views for the following functions with specific input argument types: DATE (timestamp), DATE_PART (date, time, interval, time-tz), DATE_TRUNC (timestamp, interval).

  • Incremental refresh is supported on a materialized view where the base table is in a datashare.

Some operations in Amazon Redshift interact with materialized views. Some of these operations might force a REFRESH MATERIALIZED VIEW operation to fully recompute the materialized view even though the query defining the materialized view only uses the SQL features eligible for incremental refresh. For example:

  • Background vacuum operations might be blocked if materialized views aren't refreshed. After an internally defined threshold period, a vacuum operation is allowed to run. When this vacuum operation happens, any dependent materialized views are marked for recomputation upon the next refresh (even if they are incremental). For information about VACUUM, see VACUUM. For more information about events and state changes, see STL_MV_STATE.

  • Some user-initiated operations on base tables force a materialized view to be fully recomputed next time that a REFRESH operation is run. Examples of such operations are a manually invoked VACUUM, a classic resize, an ALTER DISTKEY operation, an ALTER SORTKEY operation, and a truncate operation. For more information about events and state changes, see STL_MV_STATE.

Incremental refresh for materialized views in a datashare

Amazon Redshift supports automatic and incremental refresh for materialized views in a consumer datashare when the base tables are shared. Incremental refresh is an operation where Amazon Redshift identifies changes in the base table or tables that happened after the previous refresh and updates only the corresponding records in the materialized view. For more information about this behavior, see CREATE MATERIALIZED VIEW.

Limitations for incremental refresh

Amazon Redshift currently doesn't support incremental refresh for materialized views that are defined with a query using any of the following SQL elements:

  • OUTER JOIN (RIGHT, LEFT, or FULL).

  • Set operations: UNION, INTERSECT, EXCEPT, MINUS.

  • UNION ALL when it occurs in a subquery and an aggregate function or a GROUP BY clause is present in the query.

  • Aggregate functions: MEDIAN, PERCENTILE_CONT, LISTAGG, STDDEV_SAMP, STDDEV_POP, APPROXIMATE COUNT, APPROXIMATE PERCENTILE, and bitwise aggregate functions.

    Note

    The COUNT, SUM, MIN, MAX, and AVG aggregate functions are supported.

  • DISTINCT aggregate functions, such as DISTINCT COUNT, DISTINCT SUM, and so on.

  • Window functions.

  • A query that uses temporary tables for query optimization, such as optimizing common subexpressions.

  • Subqueries

  • External tables referencing the following formats in the query that defines the materialized view.

    • Delta Lake

    • Hudi

    Incremental refresh is supported for materialized views defined using external tables referencing other formats on the preview track. For more information about setting up Preview clusters, see Creating a preview cluster in the Amazon Redshift Management Guide. For information about setting up Preview workgroups, see Creating a preview workgroup in the Amazon Redshift Management Guide.

  • Mutable functions, such as date-time functions, RANDOM and non-STABLE user-defined functions.

  • For limitations regarding incremental refresh for zero-ETL integrations, see Considerations when using zero-ETL integrations with Amazon Redshift.

For more information about materialized-view limitations, including the effect of background operations like VACUUM on materialized-view refresh operations, see Usage notes.

Examples

The following example refreshes the tickets_mv materialized view.

REFRESH MATERIALIZED VIEW tickets_mv;