You can specify a refresh method when you create a materialized view.
Required permissions
Before you create a materialized view, make sure that you have the following permissions:
You have the CREATE permission on databases and tables.
You have the INSERT permission on databases and tables.
You have the SELECT permission on the relevant columns in all base tables that are involved in the materialized view.
If you want to configure auto-refresh for the materialized view, make sure that you have permissions to refresh views by using the local server (
127.0.0.1
) or any IP address ('%'
).
Suggestions
If your cluster has only basic specifications, we recommend that you do not refresh all materialized views at the same time to prevent stability issues.
Specify a refresh method when you create a materialized view
CREATE [OR REPLACE] MATERIALIZED VIEW <mv_name>
[MV DEFINITION]
[REFRESH [COMPLETE|FAST] [ON [DEMAND |OVERWRITE] [START WITH date] [NEXT date]]]
AS
<QUERY BODY>;
You can use the following methods to refresh materialized views: full refresh (COMPLETE) and incremental refresh (FAST). If you do not specify a refresh method, full refresh is used. Full refresh computes the query result at the time of refresh and overwrites the original result.
NoteYou can perform incremental refresh on materialized views that are defined based on a single table only for AnalyticDB for MySQL clusters of V3.1.9.0 or later. For more information, see Configure incremental refresh for materialized views (preview).
A refresh of materialized views can be triggered based on one of the following modes: ON DEMAND and ON OVERWRITE. If you specify ON DEMAND, you can trigger a refresh for the materialized view on demand. If you specify ON OVERWRITE, when the base table that is involved in the materialized view is overwritten by executing the INSERT OVERWRITE statement, an auto-refresh is triggered for the materialized view. If you do not specify a trigger mode, on-demand is used.
Full refresh
Full refresh can be manually or automatically performed.
Scenarios
Full refresh is suitable for the following batch processing scenarios:
Data is updated once every day or every hour. In this scenario, full refresh can handle complex SQL statements.
A minute-level latency is allowed for queries. In this scenario, full refresh of specific queries can be completed within a dozen seconds.
Examples
Manually refresh a materialized view.
CREATE MATERIALIZED VIEW my_mv1 REFRESH COMPLETE AS SELECT * FROM base;
REFRESH MATERIALIZED VIEW my_mv;
Configure an auto-refresh to be triggered on demand at 02:00:00 every day.
CREATE MATERIALIZED VIEW my_mv2 REFRESH COMPLETE ON DEMAND 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 * FROM base;
NoteIf a refresh is taking longer than the interval and is not completed at the next auto-refresh time point, the system skips the second auto-refresh and refreshes the view at the next time point.
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 refresh at 00:10:00 is skipped and the next refresh starts at 00:15:00.
Configure an auto-refresh after the base table is overwritten by executing the INSERT OVERWRITE statement.
Create a materialized view.
CREATE MATERIALIZED VIEW my_mv3 REFRESH COMPLETE ON OVERWRITE AS SELECT * FROM base;
After the base table that is involved in the materialized view is overwritten by executing the
INSERT OVERWRITE
statement, an auto-refresh is triggered.INSERT OVERWRITE base SELECT * FROM t0;
Incremental refresh
Incremental refresh can be performed only automatically on demand. For more information about incremental refresh, see Configure incremental refresh for materialized views (in preview).
References
Create a materialized view: describes the permissions and the syntax that are used to create a materialized view, and provides examples.
Manage materialized views: describes how to query materialized views, auto-refresh records, and information tables, and how to delete a materialized view.
Query data from a materialized view: describes how to query data from a materialized view.