This topic describes how to create materialized views.

Required permissions

  • You must have CREATE permissions on databases and tables.
  • You must have INSERT permissions on databases and tables.
  • You must have SELECT permissions on the relevant columns in all tables that are involved in materialized views.
  • If you want to enable the auto-refresh feature, you must have the permissions to refresh views by using the 127.0.0.1 localhost address or a regular IP address ('%').

Syntax

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

Keyword description

Keyword Description
MV DEFINITION Specifies table-related properties in a materialized view. A materialized view uses a standard table schema to store data. You can use this keyword to define the primary keys, partition keys, and indexes of materialized views.
Note The syntax used to create a materialized view is the same as that used to create a table. When you create a materialized view, you cannot define columns that are not returned in the query result.
REFRESH COMPLETE Specifies the refresh mode of a materialized view. Only the COMPLETE mode is supported.
ON Specifies the method to trigger a full refresh. Valid values:
  • ON DEMAND: A full refresh is triggered on demand. When you want to refresh a materialized view, you can manually trigger the refresh or configure auto-refresh.
  • ON OVERWRITE: A full refresh is triggered when the base table that is referenced by a materialized view is overwritten by executing the INSERT OVERWRITE statement. This method is suitable for scenarios where data is batch inserted by using the batchload method.
Note
  • This parameter takes effect only when the REFRESH parameter is set to COMPLETE.
  • If this parameter is not specified, the ON DEMAND trigger method is used.
START WITH and NEXT START WITH specifies the date and time of the first auto-refresh, and NEXT specifies the interval between subsequent refresh operations.
Note
  • START WITH is an optional parameter. If this parameter is not specified, auto-refresh starts from the current time.
  • If you want to enable auto-refresh, the NEXT parameter is required.
  • 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 materialized views.

QUERY BODY Specifies the query body of a materialized view.
  • You must define aliases for the expression columns in the query result. We recommend that you set a descriptive alias that makes it easy to identify. For example, (sum(price) AS total_price) indicates that the alias of the sum(price) expression column is total_price.
  • The base tables involved in the query of materialized views cannot be deleted. The columns of these base tables cannot be deleted or modified.
  • You can use the WITH clause to query data based on logical or materialized views.

Limits

  • You cannot perform INSERT, DELETE, or UPDATE operations on materialized views.
  • Base tables referenced by materialized views or columns in these base tables cannot be deleted or renamed. To modify the base tables, you must first delete the materialized views.
  • The following section lists the maximum number of materialized views that can be created for an AnalyticDB for MySQL cluster by default:
    • Clusters earlier than V3.1.4.7: up to eight materialized views.
    • Clusters of V3.1.4.7 or later: up to 64 materialized views.
    Note If you want to create more materialized views, submit a ticket.

Examples

# Refresh every 5 minutes.
CREATE MATERIALIZED VIEW myview
REFRESH NEXT now() + interval 5 minute
AS
SELECT count(*) as cnt FROM base;

# 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 day.
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;

# Do not use auto-refresh. You must manually refresh a materialized view.
CREATE MATERIALIZED VIEW myview (
  PRIMARY KEY (id)
) DISTRIBUTED BY HASH (id)
AS
SELECT id, name FROM base;

# Create indexes on specified columns. By default, all columns are indexed.
CREATE MATERIALIZED VIEW myview (
  INDEX (name),
  PRIMARY KEY (id)
) DISTRIBUTED BY HASH (id)
AS
SELECT id, name, age FROM base;

# Specify the partition key and the comment.
CREATE MATERIALIZED VIEW c (
  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;

Recommendations

  • When you create materialized views, we recommend that you use all the parameters required to create standard tables, such as partition keys, list partition keys, and storage policies for hot and cold data. When you create materialized views, we recommend that you specify partition keys and primary keys to improve the performance of subsequent queries. For more information about the parameters required to create standard tables, see CREATE TABLE.
  • To ensure the stability of clusters that have basic specifications, we recommend that you do not refresh all materialized views for such clusters.
  • By default, materialized views are indexed across all columns in the same manner as standard tables. To reduce storage space and I/O writes, you can specify the INDEX keyword to index specific columns if materialized views do not need to be indexed across all columns. The syntax used to create indexes in materialized views is the same as that used to create indexes in standard tables. For more information, see CREATE TABLE.