All Products
Search

This Product

    :Configure full refresh for materialized views

    Document Center

    :Configure full refresh for materialized views

    Last Updated:Sep 12, 2024

    You can specify a refresh method when you create a materialized view.

    Required permissions

    • To create a view, you must have the CREATE permission on databases or tables.

    • To refresh a materialized view, you must have the INSERT permission on databases or tables.

    • You must have the SELECT permission on the relevant table columns or all tables that are involved in a materialized view.

    • If you want to configure auto-refresh for a materialized view that you created, you must have permissions to refresh views by using the on-premises server (127.0.0.1) or an 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]]]
[QUERY REWRITE]
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

      For AnalyticDB for MySQL clusters of V3.1.9.0 or later, you can perform incremental refresh on materialized views that are defined based on a single table. For AnalyticDB for MySQL clusters of V3.2.0.0 or later, you can perform incremental refresh on materialized views that are defined based on multiple tables. For more information, see Configure incremental refresh for materialized views (preview).

      • To query the minor version of an AnalyticDB for MySQL Data Lakehouse Edition cluster, execute the SELECT adb_version(); statement. To update the minor version of a cluster, contact technical support.

      • For information about how to view and update the minor version of an AnalyticDB for MySQL Data Warehouse Edition cluster, see Update the minor version of a cluster.

    • 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 (preview).

    References