Create a materialized view - AnalyticDB

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 created, 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]]]
[QUERY REWRITE]
AS 
<QUERY BODY>;

Parameters

	Description
`OR REPLACE`	The rule that is used to create a materialized view based on whether the name of the materialized view that you want to create is the same as the name of an existing materialized view. If no existing materialized view uses the same name, AnalyticDB for MySQL creates a materialized view. If an existing materialized view uses the same name, AnalyticDB for MySQL deletes the existing materialized view and creates another materialized view. Note This parameter takes effect in AnalyticDB for MySQL clusters of V3.1.4.7 or later. 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.
`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 can be used 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 can be used 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). 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.
`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 REWRITE`	Enables or disables the query rewrite feature for a materialized view. After you enable this feature for a materialized view, queries can be rewritten to the materialized view. You can use the materialized view as a cache. Valid values: `DISABLE QUERY REWRITE`: disables the query rewrite feature for the materialized view. `ENABLE QUERY REWRITE`: enables the query rewrite feature for the materialized view. Note You can enable this feature only for AnalyticDB for MySQL clusters of V3.1.4 or later. For information about how to view the version of an AnalyticDB for MySQL cluster, see How do I query the version of an AnalyticDB for MySQL cluster? To update the version of an AnalyticDB for MySQL cluster, Submit a ticket. If you do not specify this parameter, the query rewrite feature is disabled for 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 varies based on the cluster version.
- Clusters of V3.1.4.7 or earlier: up to eight materialized views.
- Clusters of V3.1.4.7 or later: up to 64 materialized views.
Note
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.

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

Refresh a materialized view: describes how to specify a refresh method for a materialized view or manually refresh a materialized view.
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.