Create a materialized view - AnalyticDB - Alibaba Cloud Documentation Center

AnalyticDB for MySQL materialized views precompute user-defined queries and store the query results in advance. When you analyze complex queries, you can directly retrieve precomputed query results from materialized views. This accelerates query response. 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 ('%').

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

Syntax

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

Parameters

Parameter	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\|FAST]`	The refresh method of the materialized view. Valid values: COMPLETE (default): the full refresh method. For information about the full refresh method, see Configure full refresh for materialized views. FAST: the incremental refresh method. For information about the limits on the incremental refresh feature, see Configure incremental refresh for materialized views. 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. 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 \|OVERWRITE]`	The trigger mode of refreshes. Valid values: DEMAND (default): 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. OVERWRITE: The on-overwrite trigger mode of refreshes. After the base table that is involved in the 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. Note 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 information about how to refresh a materialized view, see Configure full refresh for materialized views and Configure incremental refresh for materialized views.
`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.
`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` (default): 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 minor version of an AnalyticDB for MySQL cluster, see How do I query the minor version of an AnalyticDB for MySQL cluster? 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.
`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)` indicates 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. Additional items to note for the `QUERY BODY` parameter of a materialized view that supports incremental refresh: When you create a materialized view that supports incremental refresh, you cannot specify expressions that may produce indefinite values as conditions in the query body. Examples: `NOW()` and `RAND()`. The COUNT(), SUM(), MAX(), MIN(), AVG(), APPROX_DISTINCT(), and COUNT(DISTINCT) functions are supported for aggregate operations. Note Only AnalyticDB for MySQL clusters of V3.2.2.1 or later support the MAX(), MIN(), AVG(), APPROX_DISTINCT(), and COUNT(DISTINCT) functions. If you use the MAX(), MIN(), APPROX_DISTINCT(), or COUNT(DISTINCT) function in the query body of a materialized view that supports incremental refresh, you can perform only INSERT operations on the base tables of the materialized view. You cannot perform operations that may result in data deletion, such as DELETE, UPDATE, REPLACE, and INSERT ON DUPLICATE KEY UPDATE, on the base tables. The COUNT(DISTINCT) function supports only the INTEGER type. The AVG() function does not support the DECIMAL type. Aggregate operations do not support the HAVING keyword. Aggregate operations except COUNT(DISTINCT) do not support the DISTINCT keyword. Window functions are not supported. Sorting operations are not supported. Set operations, such as UNION, EXCEPT, and INTERSECT, are not supported.
`MV_PROPERTIES`	To create an elastic materialized view, you must configure the MV_PROPERTIES parameter. When you create or refresh an elastic materialized view, the system uses a job resource group to dynamically schedule resources. This reduces costs but results in slower response than standard materialized views. Before you create an elastic materialized view, make sure that the following requirements are met: An AnalyticDB for MySQL Data Lakehouse Edition cluster is created. The minor version of the cluster is 3.1.9.3 or later. MV_PROPERTIES supports the mv_resource_group and mv_refresh_hints parameters in the JSON format. mv_resource_group: the job resource group that is used for the elastic materialized view. If the specified job resource group does not exist, an error occurs when you create an elastic materialized view. mv_refresh_hints: the parameters that are supported by the elastic materialized view. For more information, see the "Common hint parameters" section of the Config and hint configuration parameters topic.

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;

Create an elastic materialized view named myview9 that refreshes once every day by using the elastic resource group my_job_rg_1.

CREATE MATERIALIZED VIEW myview9
MV_PROPERTIES='{
  "mv_resource_group":"my_job_rg_1",
  "mv_refresh_hints":{"query_priority":"HIGH"}
}'
REFRESH COMPLETE ON DEMAND
START WITH now()
NEXT now() + INTERVAL 1 DAY
AS
SELECT * FROM base;

References

Configure full refresh for materialized views: 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.