Create, query, and maintain asynchronous materialized views

Create a materialized view

Permissions

Creating a materialized view requires the same permissions as creating a table, plus the SELECT permission on the query that defines the view.

Syntax

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <mv_name>
    [ (<columns_definition>) ]
    [ BUILD <build_mode> ]
    [ REFRESH <refresh_method> [refresh_trigger] ]
    [ [DUPLICATE] KEY (<key_cols>) ]
    [ COMMENT '<table_comment>' ]
    [ PARTITION BY (
        { <partition_col>
            | DATE_TRUNC(<partition_col>, <partition_unit>) }
    ) ]
    [ DISTRIBUTED BY { HASH (<distribute_cols>) | RANDOM }
        [ BUCKETS { <bucket_count> | AUTO } ]
    ]
    [ PROPERTIES (
        <table_property>
        [ , ... ])
    ]
    AS <query>

Refresh configuration

build_mode: Build timing

Controls whether the materialized view is populated with data upon creation.

• IMMEDIATE: Populates the view immediately upon creation. This is the default.

• DEFERRED: Skips initial data population. You must refresh the view manually later.

refresh_method: Refresh method

• COMPLETE: Recalculates all data from the defining query and overwrites the existing contents.

• AUTO: Refreshes only partitions with changed data. Falls back to a full refresh if the system cannot detect which partitions changed.

refresh_trigger: Trigger method

• ON MANUAL (manual trigger): Triggers a refresh manually using a SQL statement.

  -- Perform an incremental refresh (only refreshes changed partitions)
  REFRESH MATERIALIZED VIEW mvName AUTO;
  -- Perform a full refresh
  REFRESH MATERIALIZED VIEW mvName COMPLETE;
  -- Refresh specific partitions
  REFRESH MATERIALIZED VIEW mvName partitions(partitionName1, partitionName2);

  Note

  You can get partition names by running SHOW PARTITIONS FROM mvName. If the materialized view is based on a JDBC external table, the system cannot detect data changes. In this case, you must specify COMPLETE for the refresh. Using AUTO mode may result in an empty materialized view.

• ON SCHEDULE (scheduled trigger): Automatically triggers a refresh at a specified interval. Supported units include minute, hour, day, and week. You can use STARTS to specify the time of the first refresh.

  -- Perform a full refresh every 10 hours
  CREATE MATERIALIZED VIEW mv_1
  REFRESH COMPLETE ON SCHEDULE EVERY 10 hour
  AS SELECT * FROM lineitem;
  
  -- Starting from a specific time, perform an incremental refresh once a day
  CREATE MATERIALIZED VIEW mv_2
  BUILD DEFERRED REFRESH AUTO
  ON SCHEDULE EVERY 1 DAY STARTS '2024-12-01 20:30:00'
  PROPERTIES ('replication_num' = '1')
  AS SELECT l_linestatus, to_date(o_orderdate) as date_alias
     FROM orders LEFT JOIN lineitem ON l_orderkey = o_orderkey;

• ON COMMIT (automatic trigger, supported since version 4.0): Automatically triggers a refresh after the data in a base table changes.

  CREATE MATERIALIZED VIEW mv_3
  BUILD IMMEDIATE REFRESH AUTO ON COMMIT
  AS SELECT l_linestatus, to_date(o_orderdate) as date_alias
     FROM orders LEFT JOIN lineitem ON l_orderkey = o_orderkey;

  Important

  If data in the base tables changes frequently, avoid using the ON COMMIT trigger. Frequent refreshes can consume excessive system resources.

Partition configuration

Specify a partition key in the PARTITION BY clause to enable incremental refreshes, which refresh only partitions with changed data and significantly reduce overhead. The partition key expression must be a date_trunc function or a direct column reference.

Basic partitioning example

Correct example — The partition key uses only the date_trunc function:

-- Create a materialized view partitioned by month
CREATE MATERIALIZED VIEW mv_partition
BUILD IMMEDIATE REFRESH AUTO ON MANUAL
PARTITION BY (order_date_month)
DISTRIBUTED BY RANDOM BUCKETS 2
AS SELECT
    l_linestatus,
    date_trunc(o_orderdate, 'month') AS order_date_month,
    o_shippriority
FROM orders LEFT JOIN lineitem ON l_orderkey = o_orderkey;

Incorrect example — The partition key order_date_month uses the date_add() function, which causes the error because column to check use invalid implicit expression:

-- Error: The partition key does not support functions like date_add
CREATE MATERIALIZED VIEW mv_wrong
BUILD IMMEDIATE REFRESH AUTO ON MANUAL
PARTITION BY (order_date_month)
DISTRIBUTED BY RANDOM BUCKETS 2
AS SELECT
    l_linestatus,
    date_trunc(date_add(o_orderdate, INTERVAL 2 DAY), 'month') AS order_date_month,
    o_shippriority
FROM orders LEFT JOIN lineitem ON l_orderkey = o_orderkey;

Partition rollup

When aggregation reduces the data volume per partition, you can use the date_trunc function to roll up partitions to a coarser granularity, reducing the total number of partitions.

Assume the base table is partitioned by day:

CREATE TABLE t1 (
    k1 LARGEINT NOT NULL,
    k2 DATE NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(k1)
PARTITION BY range(k2)
(
    PARTITION p_20200101 VALUES [("2020-01-01"),("2020-01-02")),
    PARTITION p_20200102 VALUES [("2020-01-02"),("2020-01-03")),
    PARTITION p_20200201 VALUES [("2020-02-01"),("2020-02-02"))
)
DISTRIBUTED BY HASH(k1) BUCKETS 2;

Monthly rollup — The materialized view will have two partitions: [("2020-01-01","2020-02-01")] and [("2020-02-01","2020-03-01")]:

CREATE MATERIALIZED VIEW mv_monthly_agg
BUILD DEFERRED REFRESH AUTO ON MANUAL
partition by (date_trunc(k2, 'month'))
DISTRIBUTED BY RANDOM BUCKETS 2
AS SELECT * FROM t1;

Yearly rollup — The materialized view will have only one partition: [("2020-01-01","2021-01-01")]:

CREATE MATERIALIZED VIEW mv_yearly_agg
BUILD DEFERRED REFRESH AUTO ON MANUAL
partition by (date_trunc(k2, 'year'))
DISTRIBUTED BY RANDOM BUCKETS 2
AS SELECT * FROM t1;

Retain only recent partitions

Use the partition_sync_limit and partition_sync_time_unit properties to retain only the most recent N time units of data, preventing the materialized view from growing indefinitely.

Assume the base table is partitioned by day:

CREATE TABLE t1 (
    k1 INT,
    k2 DATE NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(k1)
PARTITION BY range(k2)
(
    PARTITION p26 VALUES [("2024-03-26"),("2024-03-27")),
    PARTITION p27 VALUES [("2024-03-27"),("2024-03-28")),
    PARTITION p28 VALUES [("2024-03-28"),("2024-03-29"))
)
DISTRIBUTED BY HASH(k1) BUCKETS 2;

This materialized view retains data from the most recent day. If the current date is 2024-03-28, the materialized view will contain only one partition: [("2024-03-28"),("2024-03-29")].

CREATE MATERIALIZED VIEW mv_recent
BUILD DEFERRED REFRESH AUTO ON MANUAL
PARTITION BY (k2)
DISTRIBUTED BY RANDOM BUCKETS 2
PROPERTIES (
    'partition_sync_limit' = '1',
    'partition_sync_time_unit' = 'DAY'
)
AS SELECT * FROM t1;

Rolling behavior: If a day passes (the current date is 2024-03-29) and a new partition [("2024-03-29"),("2024-03-30")] is added to the base table, when the materialized view is refreshed, it will then contain only the new partition [("2024-03-29"),("2024-03-30")]. The old partition is automatically dropped.

Multi-column partitions (Hive tables)

Only Hive external tables support multiple partition columns. A Hive table often has multiple partition levels, such as date at the first level and region at the second. You can choose any partition column from the Hive table as the partition key for the materialized view.

Example of a CREATE TABLE statement for a Hive table:

CREATE TABLE hive1 (
    k1 int)
PARTITIONED BY (
    year int,
    region string)
STORED AS ORC;

ALTER TABLE hive1 ADD IF NOT EXISTS
PARTITION(year=2020,region="bj")
PARTITION(year=2020,region="sh")
PARTITION(year=2021,region="bj")
PARTITION(year=2021,region="sh")
PARTITION(year=2022,region="bj")
PARTITION(year=2022,region="sh")

If you partition by year, the materialized view will have three partitions: ('2020'), ('2021'), ('2022').

CREATE MATERIALIZED VIEW mv_hive
BUILD DEFERRED REFRESH AUTO ON MANUAL
partition by(year)
DISTRIBUTED BY RANDOM BUCKETS 2
AS SELECT k1,year,region FROM hive1;

If you partition by region, the materialized view will have two partitions: ('bj'), ('sh').

CREATE MATERIALIZED VIEW mv_hive2
BUILD DEFERRED REFRESH AUTO ON MANUAL
partition by(region)
DISTRIBUTED BY RANDOM BUCKETS 2
AS SELECT k1,year,region FROM hive1;

Direct queries

A materialized view is a physical table that you can query directly like any regular table:

SELECT * FROM mv_name WHERE ...;

Direct queries are not affected by the view status (NORMAL or SCHEMA_CHANGE). Even if the view is in an abnormal state, a direct query still returns existing data, though the data may not be current.

Transparent query rewrite

The Nereids query optimizer supports transparent query rewrite based on the SELECT-PROJECT-JOIN-GROUP-BY (SPJG) pattern. When you run a query, the optimizer checks whether an existing materialized view can satisfy it. If a match is found, the query is automatically rewritten to read from the view instead of the base tables, accelerating performance without changes to your SQL.

Prerequisites

• The materialized view status is NORMAL (it has been successfully refreshed and there are no schema changes in the base tables).

• The new optimizer is enabled: SET enable_nereids_planner = true (enabled by default).

• Materialized view rewrite is enabled: SET enable_materialized_view_rewrite = true (enabled by default).

Supported rewrite types

The following rewrite scenarios are supported:

• Predicate compensation: If a query has stricter filters than the materialized view, the optimizer applies the extra filters on the view automatically.

• Join rewrite: If a query and a materialized view share the same join relationship, the optimizer matches and uses the view.

• Join derivation: If a query requires an additional join not present in the view, the optimizer can still use the view and apply the extra join, provided the required join keys exist in the view's output.

• Aggregation rewrite: If the view has pre-calculated the aggregation results that a query needs, the optimizer reads them directly.

• Aggregation rollup: If the view is aggregated at a finer granularity than the query requires, the optimizer performs further aggregation on the view's results. For example, a view aggregated by day can serve a query that aggregates by month.

• Partition compensation rewrite: If some partitions in a partitioned view become stale, the optimizer combines up-to-date view partitions with fresh data from the base tables.

• Nested materialized view rewrite: Rewrites queries that involve nested materialized views (views built on top of other views).

Verify the rewrite status

Run the EXPLAIN statement to check whether a query was rewritten to use a materialized view:

EXPLAIN SELECT l_linestatus, sum(l_extendedprice) FROM orders
LEFT JOIN lineitem ON l_orderkey = o_orderkey
GROUP BY l_linestatus;

In the EXPLAIN output, look for these markers:

• MaterializedViewRewriteSuccessAndChose: Rewrite succeeded and this view was chosen.

• MaterializedViewRewriteSuccessButNotChose: Rewrite succeeded but this view was not chosen based on the cost model.

• MaterializedViewRewriteFail: Rewrite failed, followed by a summary of the reason.

Maintain a materialized view

Materialized view information

-- View basic information and status of a materialized view
SELECT * FROM mv_infos('database'='db_name') WHERE Name = 'mv_name' \G

-- View the partition status of a materialized view, including whether each partition is synchronized with the base tables
SHOW PARTITIONS FROM mv_name;

-- View the CREATE statement for a materialized view
SHOW CREATE MATERIALIZED VIEW mv_name;

-- View the table structure of a materialized view
DESC mv_name;

Refresh tasks

-- View the refresh task history for a materialized view
SELECT * FROM tasks('type'='mv') WHERE MvName = 'mv_name';

-- View the scheduled job for a materialized view
SELECT * FROM jobs('type'='mv') WHERE MvName = 'mv_name';

Pause and resume refresh tasks

-- Pause the refresh job for a materialized view
PAUSE MATERIALIZED VIEW JOB ON mv_name;

-- Resume the refresh job for a materialized view
RESUME MATERIALIZED VIEW JOB ON mv_name;

-- Cancel a running refresh task
CANCEL MATERIALIZED VIEW TASK taskId ON mv_name;

Modifying materialized views

-- Modify the refresh interval of a materialized view (a property specific to materialized views)
ALTER MATERIALIZED VIEW mv_name SET (
    'refresh_interval' = '2 HOUR'
);

-- Modify common properties, such as the replication number
ALTER TABLE mv_name SET (
    'replication_num' = '2'
);

-- Rename a materialized view
ALTER MATERIALIZED VIEW mv_name RENAME new_mv_name;

-- Atomically replace a materialized view
ALTER MATERIALIZED VIEW mv_old REPLACE WITH MATERIALIZED VIEW mv_new;

Dropping a materialized view

DROP MATERIALIZED VIEW IF EXISTS mv_name;

Related configurations