You can specify a refresh mode when you create materialized views or manually refresh materialized views.

Required permissions
  • You must have INSERT permissions on materialized views.
  • You must have SELECT permissions on the relevant columns in all base tables that are involved in materialized views.
  • If you want to enable the auto-refresh feature, you must have the permissions to refresh views on the server whose IP address is 127.0.0.1. The system acts as a proxy creator and logs on from the local server (127.0.0.1) to perform auto-refresh. This way, if you do not have the permissions, the system cannot refresh. This helps prevent unauthorized access. If user permissions are granted on all IP addresses ('%'), this helps prevent the issue of unauthorized access because all IP addresses include the 127.0.0.1 IP address.
Specify the refresh mode when you create materialized views

Materialized views can be refreshed in full (COMPLETE) or incremental (FAST) refresh mode. If you do not specify the refresh mode, full refresh is performed.

Each refresh mode applies to different trigger methods.

CREATE MATERIALIZED VIEW mv_name
[REFRESH [FAST|COMPLETE] [ON <DEMAND|OVERWRITE>] [START WITH date] [NEXT date]]
Note Only full refresh is supported.
Manual refresh
# Manually trigger a refresh.
REFRESH MATERIALIZED VIEW <mv_name>

Full refresh mode

By default, the full refresh mode applies when a materialized view is created. Full refresh calculates the query result at the time of refresh and replaces the original result by using the overwrite method.

Trigger mechanism for full refresh
ON DEMAND: A refresh is triggered on demand. You can manually trigger the refresh or configure auto-refresh. By default, ON-DEMAND is used for full refresh.
# Manually refresh a materialized view.
CREATE MATERIALIZED VIEW my_mv
REFRESH ON DEMAND
AS
SELECT * FROM base

# Configure auto-refresh to start after 5 minutes and once per minute after the first refresh.
CREATE MATERIALIZED VIEW my_mv
REFRESH ON DEMAND START WITH now() + interval 5 minute NEXT now() + interval 1 minute
AS
SELECT * FROM base
ON OVERWRITE: A refresh is triggered when the base table is overwritten. This method is suitable for T+1 scenarios in which materialized views are automatically updated after data cleansing overwrites materialized views.
# Create a materialized view.
CREATE MATERIALIZED VIEW my_mv
REFRESH ON OVERWRITE
AS
SELECT * FROM base

# Trigger the refresh of the my_mv view.
INSERT OVERWRITE base SELECT * FROM t0; 
Atomicity of full refresh

Materialized views use the same storage schema as common tables. AnalyticDB for MySQL uses the distributed storage to store table data in multiple partitions that are distributed on multiple machines. Data is partitioned by using the HASH keyword. The DISTRIBUTED BY HASH keyword specifies the partition key column.

Full refresh ensures that each partition is atomically replaced. The data in partitions is before or after a refresh, not in intermediate state. Multiple partitions are replaced at the same time. As a result, some partitions have the refreshed data but other partitions still have the original data at a time.

If you want to make sure that the data of all partitions in a materialized view is replaced during an atomic operation, you can submit a ticket and separately configure the atomic refresh mode. By default, this mode is not enabled because this mode has a high overhead.

Auto-refresh

Auto-refresh supports the START WITH... NEXT time function to set refresh rules. START WITH is optional. By default, auto-refresh starts from the current time.

# Refresh at 02:00:00 every Monday.
CREATE MATERIALIZED VIEW myview
REFRESH 
 START WITH DATE_FORMAT(now() + interval 7 - weekday(now()) day, '%Y-%m-%d 02:00:00') 
 NEXT DATE_FORMAT(now() + interval 7 - weekday(now()) day, '%Y-%m-%d 02:00:00')
AS
SELECT count(*) as cnt FROM base

# Refresh at 02:00:00 every night.
CREATE MATERIALIZED VIEW myview
REFRESH 
 START WITH DATE_FORMAT(now() + interval 1 day, '%Y-%m-%d 02:00:00')
 NEXT DATE_FORMAT(now() + interval 1 day, '%Y-%m-%d 02:00:00')
AS
SELECT count(*) as cnt FROM base

# Refresh at 02:00:00 on the first day of every month.
CREATE MATERIALIZED VIEW myview
REFRESH NEXT DATE_FORMAT(last_day(now()) + interval 1 day, '%Y-%m-%d 02:00:00')
AS
SELECT count(*) as cnt FROM base

# Refresh only once.
CREATE MATERIALIZED VIEW myview
REFRESH START WITH now() + interval 1 day
AS 
SELECT count(*) as cnt FROM base

If the time used for a refresh exceeds the interval specified by auto-refresh, the system automatically skips the period fused for a refresh and refreshes at the next specified time point.

For example, assume that auto-refresh is configured to start at the following time points: 00:05:00, 00:10:00, and 00:15:00. The interval is 5 minutes. If a refresh starts at 00:05:00 and does not end until 00:12:00, the refresh that is configured to start at 00:10:00 is skipped and the next refresh starts at 00:15:00.