All Products
Search
Document Center

AnalyticDB for MySQL:Refresh a materialized view

Last Updated:Dec 26, 2023

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.

    Note

    You 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;
    Note

    If 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