When you create materialized views or manually refresh materialized views, you can specify a refresh mode.

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, the system cannot be refreshed unless you have the proper permissions. This prevents unauthorized access. If user permissions are granted on all IP addresses ('%'), unauthorized access does not exist, because all IP addresses include 127.0.0.1.
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 is automatically applied 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 and overwriting. 
# 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;

Auto-refresh

You can use the START WITH... NEXT time function to set auto-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 spent on a refresh exceeds the interval specified by auto-refresh, the system skips the period fused for a refresh and refreshes at the next specified point in time.

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