ALTER MATERIALIZED VIEW

The ALTER MATERIALIZED VIEW command modifies the metadata of a materialized view. To use this command, you must own the materialized view.

INFO

To modify the SQL definition of a materialized view, please refer to Alter a streaming job.

Syntax

ALTER MATERIALIZED VIEW materialized_view_name
    alter_option;

alter_option depends on the operation you want to perform on the materialized view. For all supported clauses, see the sections below.

Clause

OWNER TO

ALTER MATERIALIZED VIEW materialized_view_name
    OWNER TO new_user;

Parameter or clause	Description
OWNER TO	This clause changes the owner of the materialized view. Note that this will cascadingly change all related internal objects as well.
new_user	The new owner you want to assign to the materialized view.

-- Change the owner of the materialized view named "materialized_view1" to user "user1"
ALTER MATERIALIZED VIEW materialized_view1 OWNER TO user1;

SET SCHEMA

ALTER MATERIALIZED VIEW materialized_view_name
    SET SCHEMA schema_name;

Parameter or clause	Description
SET SCHEMA	This clause moves the materialized view to a different schema.
schema_name	The name of the schema to which the materialized view will be moved.

-- Move the materialized view named "test_materialized_view" to the schema named "test_schema"
ALTER MATERIALIZED VIEW test_materialized_view SET SCHEMA test_schema;

SET PARALLELISM

ALTER MATERIALIZED VIEW materialized_view_name
SET PARALLELISM { TO | = } parallelism_number;

Parameter or clause	Description
SET PARALLELISM	This clause controls the degree of parallelism for the targeted streaming job.
parallelism_number	Can be ADAPTIVE or a fixed number (e.g., 1, 2, 3). Setting it to ADAPTIVE expands the job’s parallelism to all available units, while a fixed number locks it at that value. Setting it to 0 is equivalent to ADAPTIVE. The maximum allowed value is determined by the max_parallelism of the job. For more information, see Configuring maximum parallelism.

-- Set the parallelism of the materialized view "m_join" to 3.
ALTER MATERIALIZED VIEW m_join SET PARALLELISM = 3;

RENAME TO

ALTER MATERIALIZED VIEW materialized_view_name
    RENAME TO new_name;

Parameter or clause	Description
RENAME TO	This clause changes the name of the materialized view.
new_name	The new name of the materialized view.

-- Change the name of the materialized view named "mv_1" to "mv_2"
ALTER MATERIALIZED VIEW mv_1 RENAME TO mv_2;

SET BACKFILL_PARALLELISM

ALTER MATERIALIZED VIEW materialized_view_name
    SET BACKFILL_PARALLELISM { TO | = } parallelism_value [ DEFERRED ];

This statement controls the degree of parallelism used during the backfill phase of a materialized view. It lets you tune resource usage for backfilling independently from the job’s steady-state parallelism.

Parameter or clause	Description
SET BACKFILL_PARALLELISM	Sets the parallelism for the backfill phase of the streaming job.
parallelism_value	Can be ADAPTIVE or a fixed number (e.g., 1, 2, 3). Setting it to ADAPTIVE or 0 expands backfill parallelism to all available units. A fixed number locks it at that value.
DEFERRED	Optional. When specified, the change takes effect after the current backfill completes, without interrupting an ongoing backfill. If omitted, the change is applied immediately.

-- Set backfill parallelism to a fixed value
ALTER MATERIALIZED VIEW mv1 SET BACKFILL_PARALLELISM = 4;

-- Set backfill parallelism to adaptive
ALTER MATERIALIZED VIEW mv1 SET BACKFILL_PARALLELISM = ADAPTIVE;

-- Defer the parallelism change until the current backfill completes
ALTER MATERIALIZED VIEW mv1 SET BACKFILL_PARALLELISM = 4 DEFERRED;

SET BACKFILL_RATE_LIMIT

ALTER MATERIALIZED VIEW mv_name
    SET BACKFILL_RATE_LIMIT { TO | = } { default | rate_limit_number };

This statement controls the rate limit of a newly created materialized view’s backfilling process from upstream materialized views and sources. For the specific value of BACKFILL_RATE_LIMIT, refer to How to view runtime parameters.

Examples

-- Pause the backfill
ALTER MATERIALIZED VIEW mv1 SET BACKFILL_RATE_LIMIT=0;

-- Set backfill rate limit to 1
ALTER MATERIALIZED VIEW mv1 SET BACKFILL_RATE_LIMIT=1;

-- Disable the backfill rate limit
ALTER MATERIALIZED VIEW mv1 SET BACKFILL_RATE_LIMIT=DEFAULT;

To modify the rate limit of the sources used in the materialized view, please refer to SET SOURCE_RATE_LIMIT.

SWAP WITH

ALTER MATERIALIZED VIEW name
SWAP WITH target_name;

Parameter	Description
name	The current name of the materialized view to swap.
target_name	The target name of the materialized view you want to swap with.

-- Swap the names of the sales_summary materialized view and the sales_archive materialized view.
ALTER MATERIALIZED VIEW sales_summary
SWAP WITH sales_archive;