Amazon Redshift announces support for cascading refresh of nested materialized views

Amazon Redshift now supports cascading refresh of nested materialized views (MVs) that are defined on local Amazon Redshift tables and external streaming sources such as Amazon Kinesis Data Streams (KDS), Amazon Managed Streaming for Apache Kafka (MSK) or Confluent Cloud.

With this update, customers can now run cascading refresh of nested MVs with a single option to specify ‘cascade’ or ‘restrict’. The ‘restrict’ option limits the MV refresh to the single targeted MV, while the refresh with ‘cascade’ option run on the target MV will trigger a cascading refresh of all nested MVs below the target MV in a single transaction. Here’s an example:

CREATE TABLE t(a INT);
CREATE MATERIALIZED VIEW u AS SELECT * FROM t;
CREATE MATERIALIZED VIEW v AS SELECT * FROM u;
CREATE MATERIALIZED VIEW w AS SELECT * FROM v;

— w -> v -> u -> t

INSERT INTO t VALUES (1);

The following example shows an informational message when you run REFRESH MATERIALIZED VIEW on a materialized view that depends on an out-of-date materialized view.

REFRESH MATERIALIZED VIEW v;
INFO: Materialized view v is already up to date. However, it depends on another materialized view that is not up to date.

REFRESH MATERIALIZED VIEW v CASCADE;
INFO: Materialized view v was incrementally updated successfully.

In the example above with ‘cascade’ refresh option, MV ‘u’ is refreshed first and then MV ‘v’ is refreshed next in that order, while MV ‘w’ is not refreshed.

Cascading refresh greatly simplifies application development by eliminating complex logic that was previously required for coordinating manual refresh of several nested materialized views. You can start using this new capability immediately to build more complex and flexible analytics pipelines. To get started, refer to the Nested materialized views sub-section of the Refreshing a materialized view section of the documentation.

Categories:

Source: Amazon Web Services

Latest Posts