AnalyticDB:Create a materialized view

Required permissions

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

• The CREATE permission on tables in the database where you want to create the materialized view.

• The SELECT permission on specific or all columns of all base tables referenced in the materialized view.

• If you want to create a materialized view that can be automatically refreshed, you must also have the following permissions:

  • The permissions to connect to AnalyticDB for MySQL from any IP address denoted by '%'.

  • The INSERT permission on the materialized view or all tables in the database where the materialized view resides. Otherwise, the data in the materialized view cannot be refreshed.

Prepare base tables

Create two base tables named customer and sales.

/*+ RC_DDL_ENGINE_REWRITE_XUANWUV2=false */ -- Set the table engine to XUANWU. 
CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255),
    is_vip Boolean
);

/*+ RC_DDL_ENGINE_REWRITE_XUANWUV2=false */ -- Set the table engine to XUANWU. 
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    price DECIMAL(10, 2),
    quantity INT,
    sale_date TIMESTAMP
);

Create a materialized view that supports complete refresh

You can create materialized views that support complete refresh (hereinafter referred to as complete materialized views) based on internal and external tables of AnalyticDB for MySQL, existing materialized views, and views.

Create a complete materialized view named join_mv based on the customer and sales tables, with the refresh trigger mechanism set to manual refresh.

CREATE MATERIALIZED VIEW join_mv
REFRESH COMPLETE ON DEMAND
AS
SELECT 
sale_id,                
SUM(price * quantity) AS price                  
FROM customer
INNER JOIN (SELECT sale_id,customer_id,price,quantity FROM sales) sales ON customer.customer_id = sales.customer_id
GROUP BY customer.customer_id;

To update the data in the materialized view, execute the following statement: REFRESH MATERIALIZED VIEW join_mv;.

Create a materialized view that supports fast refresh

You can create materialized views that support fast refresh (hereinafter referred to as fast materialized views) based on internal tables of AnalyticDB for MySQL.

The following section describes how to create a fast materialized view based on multiple joined tables.

Before you create a fast materialized view, complete the following preparations:

• Check whether the minor version of your AnalyticDB for MySQL cluster is 3.1.9.0 or later.

• Enable the binary logging feature for the cluster and the base tables.

  If an error occurs when you enable the binary logging feature for base tables, resolve this issue by referencing the section titled "Query execution error: : Can not create FAST materialized view, because demotable doesn't support getting incremental data" in this topic.

  SET ADB_CONFIG BINLOG_ENABLE=true;
  ALTER TABLE customer binlog=true;
  ALTER TABLE sales binlog=true;

Create a fast materialized view named sales_mv_incre based on the sales table.

CREATE MATERIALIZED VIEW sales_mv_incre
REFRESH FAST NEXT now() + INTERVAL 3 minute
AS
SELECT 
sale_id,                
SUM(price * quantity) AS price                  
FROM sales
GROUP BY sale_id;

If the minor version of your AnalyticDB for MySQL cluster is 3.2.1.0 or later, create a fast materialized view named join_mv_incre based on the customer and sales tables that are joined, with the refresh interval set to 3 minutes.

CREATE MATERIALIZED VIEW join_mv_incre
REFRESH FAST NEXT now() + INTERVAL 3 minute
AS
SELECT 
customer.customer_id,                
SUM(sales.price) AS price                  
FROM customer
INNER JOIN (SELECT customer_id,price FROM sales) sales ON customer.customer_id = sales.customer_id
GROUP BY customer.customer_id;

For more information about the syntax and examples of materialized views, see CREATE MATERIALIZED VIEW.

Query the materialized views that are being created

You can execute the SHOW PROCESSLIST WHERE info LIKE '%CREATE MATERIALIZED VIEW%'; statement to query the materialized views that are being created.

Each row in the query results indicates a materialized view that is being created. The user field indicates the database account that is used to create the materialized view. The status field indicates the creation status. The Info field includes the SQL statement that is used to create the materialized view. For more information about the fields in the query results, see SHOW PROCESSLIST.

+-------+---------------------------------------+-------+-------------------+-------+-------------------+----+-------+----------------------------------------------------------------------------------------------------+
|Id     |ProcessId                              |User   |Host               |DB     |Command            |Time|State  |Info                                                                                                |
+-------+---------------------------------------+-------+-------------------+-------+-------------------+----+-------+----------------------------------------------------------------------------------------------------+
|31801  |2025012714472702101701716603151*****   |wenjun |21.17.xx.xx:49534  |demo1  |INSERT_FROM_SELECT |2   |RUNNING|/*process_id=2025012714472702101708007503151*****,access_port=62042,access_ip=59.82.xx.xx/          |
|       |                                       |       |                   |       |                   |    |       |CREATE MATERIALIZED VIEW join_mv                                                                    |
|       |                                       |       |                   |       |                   |    |       |REFRESH COMPLETE ON DEMAND                                                                          |
|       |                                       |       |                   |       |                   |    |       |AS                                                                                                  |
|       |                                       |       |                   |       |                   |    |       |SELECT                                                                                              |
|       |                                       |       |                   |       |                   |    |       |sale_id,                                                                                            |               
|       |                                       |       |                   |       |                   |    |       |SUM(price * quantity) AS price                                                                      |                  
|       |                                       |       |                   |       |                   |    |       |FROM customer                                                                                       |
|       |                                       |       |                   |       |                   |    |       |INNER JOIN (SELECT customer_id,price FROM sales) sales ON customer.customer_id = sales.customer_id  |
|       |                                       |       |                   |       |                   |    |       |GROUP BY customer.customer_id;                                                                      |
+-------+---------------------------------------+-------+-------------------+-------+-------------------+----+-------+----------------------------------------------------------------------------------------------------+                                                                                                                 

If no information is returned from the SHOW PROCESSLIST statement, the creation of the materialized view is complete. This indicates that the table schema of the materialized view is created, and initial data is loaded to the materialized view.

Base table queries for complete materialized views

Complete materialized views can be created based on internal and external tables of AnalyticDB for MySQL, existing materialized views, and views. No limits are imposed on base table queries. For information about the query syntax, see SELECT.

Base table queries for fast materialized views

Fast materialized views can be created only based on internal and external tables of AnalyticDB for MySQL. Base table queries must comply with the following rules:

General limits

General limits apply to all types of materialized views, including complete and fast materialized views.

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

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

• By default, the maximum number of materialized views that can be created for an AnalyticDB for MySQL cluster varies based on the minor version of the cluster.

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

  • Versions earlier than V3.1.4.7: up to eight materialized views.

  Note

  If the number of materialized views already reaches the upper limit, you can contact technical support to increase the maximum number of materialized views.

Limits on complete materialized views

When you adjust the number of reserved resource nodes in an AnalyticDB for MySQL cluster, asynchronous jobs are not supported. Complete refresh operations on materialized views are asynchronous jobs. As a result, complete refresh operations cannot be performed when you adjust the number of reserved resource nodes. However, fast refresh operations are not affected.

Limits on fast materialized views

• Limits on the base tables of fast materialized views:

  • You cannot use XUANWU_V2 tables as base tables of fast materialized views.

    The reason is that XUANWU_V2 tables do not support the binary logging feature.

  • You cannot use partitioned tables as base tables to create a fast materialized view in AnalyticDB for MySQL clusters earlier than V3.2.3.0.

  • You cannot execute the INSERT OVERWRITE or TRUNCATE statement on base tables of a fast materialized view in AnalyticDB for MySQL clusters earlier than V3.2.3.1. Otherwise, an error occurs.

  • If you use the MAX(), MIN(), APPROX_DISTINCT(), or COUNT(DISTINCT) function in materialized view queries, you can perform only INSERT operations on base tables. You cannot perform operations that may result in data deletion on base tables, such as DELETE, UPDATE, REPLACE, and INSERT ON DUPLICATE KEY UPDATE.

• Limits on the refresh trigger mechanisms of fast materialized views:

  Fast materialized views can only be automatically refreshed at scheduled intervals, and cannot be manually refreshed. The scheduled interval of fast refreshes ranges from 5 seconds to 5 minutes.

• Limits on the queries of fast materialized views:

  • You cannot specify expressions that may produce indefinite values. Examples: NOW() and RAND().

  • You cannot specify the ORDER BY clause.

  • You cannot specify the HAVING clause.

  • You cannot specify window functions.

  • You cannot specify set operators, such as UNION, EXCEPT, and INTERSECT.

  • You can specify only INNER JOIN for join operations. JOIN fields must be the original fields of base tables, use the same data types, and be used to create indexes. You can join up to five base tables.

    To join more base tables, contact technical support.

  • You can specify only the following aggregate functions: COUNT(), SUM(), MAX(), MIN(), AVG(), APPROX_DISTINCT(), and COUNT(DISTINCT).

  • The AVG() function does not support the DECIMAL type.

  • The COUNT(DISTINCT) function supports only the INTEGER type.

How do I retain the data of only the most recent year in a materialized view?

When you define a materialized view, you can use the date column as the partition key and specify a lifecycle to retain the data of only the most recent year.

For example, new sales data is generated in the sales table every day. When you create a materialized view based on the sales table, you can use the sale_date column as the partition key and set the lifecycle to 365. This way, the materialized view retains the data of only the most recent year.

CREATE MATERIALIZED VIEW sales_mv_lifecycle
PARTITION BY VALUE(DATE_FORMAT(sale_date, '%Y%m%d')) LIFECYCLE 365
REFRESH FAST NEXT now() + INTERVAL 100 second
AS
SELECT 
sale_date,                
SUM(price * quantity) AS price                  
FROM sales
GROUP BY sale_date;

Query execution error: : Can not create FAST materialized view, because demotable doesn't support getting incremental data

Cause: The binary logging feature is disabled for the demotable base table of the fast materialized view. If you want to create a fast materialized view, you must enable the binary logging feature for all base tables.

Solution: Execute the ALTER TABLE demotable binlog=true; statement to enable the binary logging feature for the demotable base table.

If the XUANWU_V2 engine not support ALTER_BINLOG_ENABLE now error occurs when you enable the binary logging feature for the demotable base table, it indicates that the table uses the XUANWU_V2 engine. The XUANWU_V2 engine does not support the binary logging feature. The engine of a table cannot be changed. You must disable the XUANWU_V2 engine, create a table that uses the XUANWU engine, migrate data from the XUANWU_V2 table to the XUANWU table, and then create a fast materialized view based on the XUANWU table.

Methods to disable the XUANWU_V2 engine:

• If base tables are automatically created by using Data Transmission Service (DTS), zero-ETL, or data synchronization, you can disable the XUANWU_V2 engine at the cluster level. After you disable the XUANWU_V2 engine, all new tables use the XUANWU engine.

  Disable the XUANWU_V2 engine at the cluster level: SET adb_config RC_DDL_ENGINE_REWRITE_XUANWUV2=false;

• If base tables are manually created by executing the CREATE TABLE statements, and a small number of base tables are required to create a fast materialized view, you can disable the XUANWU_V2 engine in the involved CREATE TABLE statements. This way, tables that use the XUANWU engine are created from the involved CREATE TABLE statements. Other CREATE TABLE statements can still be used to create tables that use the XUANWU_V2 engine.

  Disable the XUANWU_V2 engine at the table level: /*+ RC_DDL_ENGINE_REWRITE_XUANWUV2=false */CREATE TABLE ...

Query execution error: : PRIMARY KEY id must output to MV.

Cause: The query of a fast materialized view is a non-aggregate query without a GROUP BY clause. In this case, the primary key of the materialized view must be the same as the primary key of the base table. However, the primary key of the base table is not contained in the SELECT clause.

Incorrect sample statement:

CREATE MATERIALIZED VIEW wrong_example1
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT product_id,price -- The primary key of the sales base table is sale_id. However, sale_id is not contained in the SELECT clause. 
FROM sales;

Solution: Add the primary key of the base table to the SELECT clause of the materialized view query.

Correct sample statement:

CREATE MATERIALIZED VIEW correct_example1
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT sale_id,product_id, price -- Add the primary key of the base table, sale_id, to the SELECT clause. 
FROM sales;

Query execution error: : MV PRIMARY KEY must be equal to base table PRIMARY KEY.

Cause: The query of a fast materialized view is a non-aggregate query without a GROUP BY clause. In this case, the primary key of the materialized view must be the same as the primary key of the base table. However, the primary key and a non-primary-key column of the base table are specified as the primary key of the materialized view in the materialized view definition.

Incorrect sample statement:

CREATE MATERIALIZED VIEW wrong_example2
(PRIMARY KEY(sale_id,product_id)) -- product_id is a non-primary-key column of the sales base table. 
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT sale_id,product_id,price
FROM sales;

Solution: Remove the non-primary-key column of the base table to modify the primary key of the materialized view.

Correct sample statement:

CREATE MATERIALIZED VIEW correct_example2
(PRIMARY KEY(sale_id)) -- Remove the product_id column from the primary key of the materialized view. 
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT sale_id,product_id,price
FROM sales;

Query execution error: : FAST materialized view must define PRIMARY KEY

Causes: This error may be caused by the following reasons:

• No correct primary key is specified for a fast materialized view.

• A function is invoked on the primary key of the base table in the materialized view query.

Solution: Select optimal solutions based on the reasons.

• If no correct primary key is specified for the materialized view, modify the definition of the materialized view to ensure that the primary key meets the following requirements:

  • If you specify a GROUP BY clause, the primary key must be the columns referenced in the GROUP BY clause. For example, if GROUP BY a,b is specified, the primary key must be columns a and b.

  • If you specify an aggregate function without GROUP BY, the primary key must be a globally unique constant.

  • If you specify a non-aggregate query without GROUP BY, the primary key must be the same as the primary key of the base table.

• If a function is invoked on the primary key of the base table, modify the query of the materialized view to remove the function.

Query execution error: : The join graph is not supported.

Cause: The columns that are used to join base tables in the query of a fast materialized view use different data types. Example: sales INNER JOIN customer ON customer.id=sales.id. If the customer.id and sales.id columns use different data types, this error occurs.

Solution: Execute the ALTER TABLE tablename MODIFY COLUMN columnname newtype; statement to change the data types of the columns. For more information, see the "Change the data type of a column" section of the ALTER TABLE topic.

Query execution error: : Unable to use index join to refresh this fast MV.

Cause: No indexes are created on columns that are used to join base tables in the query of a fast materialized view. Example: sales INNER JOIN customer ON customer.id=sales.id. If no index is created on the customer.id or sales.id column, this error occurs.

Solution: Execute the ALTER TABLE tablename ADD KEY idx_name(columnname); statement to create indexes on the columns that are used to join base tables. For more information, see the "Create an index" section of the ALTER TABLE topic.

Query execution error: : Query exceeded reserved memory limit

Cause: The memory usage of the query on a single node exceeds the upper limit.

Solution: In most cases, memory-intensive SQL statements include the Aggregation, TopN, Window, and Join operators. You can use the SQL diagnostics feature to identify memory-intensive stages and operators and perform SQL optimization on the operators. For more information, see the "Memory metrics" section of the Cluster performance optimization topic and Use stage and task details to analyze queries.