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;

alteroption 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 clauseDescription
OWNER TOThis clause changes the owner of the materialized view. Note that this will cascadingly change all related internal objects as well.
new_userThe 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 clauseDescription
SET SCHEMAThis clause moves the materialized view to a different schema.
schema_nameThe 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 = parallelism_number;
Parameter or clauseDescription
SET PARALLELISMThis clause controls the degree of parallelism for the targeted streaming job.
parallelism_numberThis parameter can be ADAPTIVE or a fixed number, like 1, 2, 3, etc. Altering the parameter to ADAPTIVE will expand the streaming job’s degree of parallelism to encompass all available units, whereas setting it to a fixed number will lock the job’s parallelism at that specific figure. Setting it to 0 is equivalent to ADAPTIVE.
-- 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 clauseDescription
RENAME TOThis clause changes the name of the materialized view.
new_nameThe 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_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;
ParameterDescription
nameThe current name of the materialized view to swap.
target_nameThe 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;

Was this page helpful?

ALTER INDEXALTER SCHEMA