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 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.