All Products
Search
Document Center

AnalyticDB for MySQL:Create a materialized view

Last Updated:Mar 01, 2024

This topic describes how to 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 create, you must have permissions to refresh views by using the on-premises server (127.0.0.1) or an IP address ('%').

Syntax

CREATE [OR REPLACE] MATERIALIZED VIEW <mv_name>
[MV DEFINITION]
[REFRESH [COMPLETE|FAST] [ON [DEMAND |OVERWRITE] [START WITH date] [NEXT date]]]
AS 
<QUERY BODY>;

Parameters

Description

mv_name

The name of the materialized view.

MV DEFINITION

The table-related properties in the materialized view.

  • A materialized view uses a standard table schema to store data. When you create a materialized view, you can use all parameters that are required to create a standard table, such as the partition key, distribution key, index, and storage policy for hot and cold data. When you create a materialized view, we recommend that you specify a partition key and a primary key to improve the performance of subsequent queries. For more information about the parameters that are required to create a standard table, see CREATE TABLE.

  • By default, a materialized view is indexed across all columns in the same manner as standard tables. To reduce storage and I/O writes, you can configure the INDEX parameter to index specific columns when a materialized view does not need to be indexed across all columns. The syntax that is used to create indexes in a materialized view is the same as the syntax that is used to create indexes in a standard table. For more information, see CREATE TABLE.

  • When you create a materialized view, you cannot specify columns that are not included in the query result. This rule is the same as the rule that is used when you create a table.

REFRESH COMPLETE

The full refresh method.

If you do not specify a refresh method when you create a materialized view, full refresh is used.

REFRESH FAST

The incremental refresh method.

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

ON DEMAND

The on-demand trigger mode of refreshes. To configure the next refresh on demand, you can manually trigger a refresh or specify the NEXT parameter.

By default, a full refresh is triggered in on-demand mode.

ON OVERWRITE

The on-overwrite trigger mode of refreshes. When the base table that is involved in a materialized view is overwritten by executing the INSERT OVERWRITE statement, a full refresh is triggered. This mode is suitable for scenarios in which data is batch imported by using the BATCH LOAD statement.

If you configure the on-overwrite mode, you cannot specify the START WITH or NEXT parameter.

START WITH

The first point in time when you want the materialized view to be automatically and fully refreshed. If you do not specify this parameter, the current point in time is used.

Note

Time functions are supported and must be accurate to the second. Milliseconds are truncated.

For more information about how to refresh a materialized view, see Refresh a materialized view.

NEXT

The next point in time when you want the materialized view to be automatically and fully refreshed. If you want to enable auto-refresh, you must specify the NEXT parameter.

Note

Time functions are supported and must be accurate to the second. Milliseconds are truncated.

For more information about how to refresh a materialized view, see Refresh a materialized view.

QUERY BODY

The query body of the materialized view, which can be a table, logical view, or materialized view. Take note of the following items:

  • You must specify aliases for the expression columns in the query result. We recommend that you specify a descriptive alias that makes it easy to identify. For example, (SUM(price) AS total_price) specifies that the alias of the SUM(price) expression column is total_price.

  • You cannot delete the base tables that are involved in the materialized view, or delete or modify the columns in the base tables.

  • You can use the WITH clause to query the materialized view.

Limits

  • You cannot perform INSERT, DELETE, or UPDATE operations on materialized views.

  • You cannot delete or rename base tables that are involved in a materialized view or columns in the base tables. Before you modify the base tables, you must delete the materialized view.

  • The maximum number of materialized views that can be created for an AnalyticDB for MySQL cluster is as follows:

    • Clusters of V3.1.4.6 or earlier: up to eight materialized views.

    • Clusters of V3.1.4.7 or later: up to 64 materialized views.

    Note

    For information about how to query the minor version of a cluster, see How do I query the version of an AnalyticDB for MySQL cluster? To update the minor version of a cluster, contact technical support.

Examples

  • Create a materialized view named myview1 that refreshes every 5 minutes.

    CREATE MATERIALIZED VIEW myview1
    REFRESH NEXT now() + interval 5 minute
    AS
    SELECT count(*) as cnt FROM base;
  • Create a materialized view named myview2 that refreshes at 02:00:00 every Monday.

    CREATE MATERIALIZED VIEW myview2
    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;
  • Create a materialized view named myview3 that refreshes at 02:00:00 every day.

    CREATE MATERIALIZED VIEW myview3
    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;
  • Create a materialized view named myview4 that refreshes at 02:00:00 on the first day of every month.

    CREATE MATERIALIZED VIEW myview4
    REFRESH NEXT DATE_FORMAT(last_day(now()) + interval 1 day, '%Y-%m-%d 02:00:00')
    AS
    SELECT count(*) as cnt FROM base;
  • Create a materialized view named myview5 that refreshes only once.

    CREATE MATERIALIZED VIEW myview5
    REFRESH START WITH now() + interval 1 day
    AS 
    SELECT count(*) as cnt FROM base;
  • Create a materialized view named myview6 that does not use auto-refresh.

    CREATE MATERIALIZED VIEW myview6 (
      PRIMARY KEY (id)
    ) DISTRIBUTED BY HASH (id)
    AS
    SELECT id, name FROM base;
  • Create a materialized view named myview7 for which specified columns are indexed. By default, all columns are indexed.

    CREATE MATERIALIZED VIEW myview7 (
      INDEX (name),
      PRIMARY KEY (id)
    ) DISTRIBUTED BY HASH (id)
    AS
    SELECT id, name, age FROM base;
  • Create a materialized view named myview8 for which a partition key and a comment are specified.

    CREATE MATERIALIZED VIEW myview8 (
      name varchar(10),
      value double,
      KEY INDEX_ID(id) COMMENT 'id',
      CLUSTERED KEY INDEX(name, value),
      PRIMARY KEY(id)
    ) 
    DISTRIBUTED BY hash(id)
    PARTITION BY value(date_format(dat, "%Y%m%d")) LIFECYCLE 30
    COMMENT 'MATERIALIZED VIEW c'
    AS 
    SELECT * FROM base;

References