CREATE MATERIALIZED VIEW

Use the CREATE MATERIALIZED VIEW command to create a materialized view. A materialized view can be created based on sources, tables, materialized views, or indexes.

Syntax

CREATE MATERIALIZED VIEW [IF NOT EXISTS] mv_name
[ WITH ( parameter = value [, ... ] ) ]
AS select_query;

Parameters

Parameter or clause	Description
mv_name	The name of the materialized view to be created.
select_query	A SELECT query that retrieves data for the materialized view. See SELECT for the syntax and examples of the SELECT command.
WITH clause	Specifies optional parameters for the materialized view. For example, source_rate_limit to set the ingestion rate, and backfill_order to control the backfill order.

Names and unquoted identifiers are case-insensitive. Therefore, you must double-quote any of these fields for them to be case-sensitive. See also Identifiers.

The ORDER BY clause in the CREATE MATERIALIZED VIEW statement is allowed but not considered as part of the definition of the materialized view. It’s only used in the initial creation of the materialized view and not during refreshes.

Backfill behavior and controls

1. CREATE MATERIALIZED VIEW will first backfill historical data from the referenced relations, and completion time varies based on the volume of data to be backfilled.

2. To perform the operations in the background, you can execute SET BACKGROUND_DDL=true; before running the CREATE MATERIALIZED VIEW statement. See details in SET BACKGROUND_DDL.

3. You can specify backfill_order in the WITH clause to control the backfill order for different upstream relations. Use the -> operator to define dependencies between tables. The left relation will be fully backfilled before the right relation begins.

   CREATE MATERIALIZED VIEW m1
   WITH (backfill_order = FIXED(t1 -> t2, t2 -> t3))
   AS
   SELECT v1 FROM t1
   UNION
   SELECT v1 FROM t2
   UNION
   SELECT v1 FROM t3;
   
   -- The backfill will follow this order:
   -- 1. t1
   -- 2. t2
   -- 3. t3
   

   To view progress of each backfill fragment:

   SELECT * FROM rw_catalog.rw_fragment_backfill_progress;
   

   To inspect the structure of the running streaming job:

   DESCRIBE FRAGMENTS <mv name>;
   

   To dump a dot formatted graph of your backfill orders:

   EXPLAIN (backfill, format dot) CREATE MATERIALIZED VIEW ...;
   

   • The backfill_order feature is currently in technical preview stage.
   • It is supported only for MATERIALIZED VIEW.
   • Backfill order recovery is not supported for background ddl. If a backfilling job restarts, the specified order will be lost, and all tables will backfill concurrently.
   • Cross-database scans inside the backfill_order clause are not supported.

See also

Overview of data processing