Elastic materialized views - AnalyticDB for MySQL - Alibaba Cloud Documentation Center

Materialized views can be used to accelerate analysis and simplify extract-transform-load (ETL) jobs in various scenarios, such as report analysis, screen display, and business intelligence (BI) tool-based queries. AnalyticDB for MySQL Data Lakehouse Edition (V3.0) allows you to specify a job resource group when you create a materialized view and use the elasticity of the resource group to refresh the materialized view. This topic describes how to create, modify, and query an elastic materialized view.

Prerequisites

Your AnalyticDB for MySQL cluster must meet the following requirements:

The cluster is of Data Lakehouse Edition (V3.0).
The minor version of the cluster is 3.1.9.3 or later.
Note
For information about how to view 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.
A job resource group is created in the AnalyticDB for MySQL Data Lakehouse Edition (V3.0) cluster. For more information, see Create a resource group.

Create an elastic materialized view

Before you create a materialized view, make sure that you have the following permissions:

You have the CREATE permission on databases and tables.
You have the INSERT permission on databases and tables.
You have the SELECT permission on the relevant columns in all base tables that are involved in the materialized view.
If you want to configure auto-refresh for the materialized view, make sure that you 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]
[MV_PROPERTIES=<MV_PROPERTIES>]
[REFRESH [COMPLETE|FAST] [ON [DEMAND |OVERWRITE] [START WITH date] [NEXT date]]]
AS 
<QUERY BODY>;

Parameters

MV_PROPERTIES supports only the mv_resource_group and mv_refresh_hints parameters in the JSON format.

mv_resource_group: the elastic resource group that is used for the elastic materialized view. The resource group is used for creating and refreshing the elastic materialized view. If the specified elastic resource group does not exist, an error is reported when you create an elastic materialized view.
mv_refresh_hints: the parameters that are supported by the elastic materialized view in the JSON format. Example: query_priority.

For information about other parameters, see the "Parameters" section of the Create a materialized view topic.

Examples

Create an elastic materialized view named mv0 that refreshes every day based on the elastic resource group my_job_rg_1.

CREATE MATERIALIZED VIEW mv0
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 base0;

Modify an elastic materialized view

Syntax

ALTER MATERIALIZED VIEW <mv_name> MV_PROPERTIES=<MV_PROPERTIES>;

Important

The value of <MV_PROPERTIES> that is configured in the ALTER statement overwrites the value of <MV_PROPERTIES> that is configured when you create the elastic materialized view.

For example, you configure the mv_resource_group and mv_refresh_hints parameters to create an elastic materialized view, and configure only the mv_resource_group parameter to modify the materialized view. When you execute the ALTER statement, the mv_resource_group parameter is modified and the mv_refresh_hints parameter is deleted.

Parameters

<MV_PROPERTIES>: the properties of the elastic materialized view. Only the mv_resource_group and mv_refresh_hints parameters are supported. For more information, see the "Parameters" section of this topic.

Examples

Change the resource group that is used for an elastic materialized view.

Configure the mv_resource_group and mv_refresh_hints parameters.
```
ALTER MATERIALIZED VIEW mv0 
MV_PROPERTIES='{
  "mv_resource_group":"my_job_rg_2",
  "mv_refresh_hints":{"query_priority":"HIGH"}
}'
```
After you execute the preceding statement, only the elastic resource group that is used for the elastic materialized view is changed. Other parameters remain unchanged.
Configure only the mv_resource_group parameter.
```
ALTER MATERIALIZED VIEW mv0 
MV_PROPERTIES='{
  "mv_resource_group":"my_job_rg_2"
}'
```
After you execute the preceding statement, the elastic resource group that is used for the elastic materialized view is changed and the mv_refresh_hints parameter is deleted.

Query the definition of an elastic materialized view

Important

To query the definition of an elastic materialized view, you must have the SELECT permission on the elastic materialized view.

Syntax

SHOW CREATE MATERIALIZED VIEW <mv_name>;

Parameters

<mv_name>: the name of the elastic materialized view.

Examples

Query the definition of the elastic materialized view mv0.

SHOW CREATE MATERIALIZED VIEW mv0;

The information about the DDL statement that is used to create the elastic materialized view is returned.