AnalyticDB:Create a materialized view

Required permissions

To create a materialized view, you must 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 columns, or all columns, of all base tables referenced in the materialized view.

• If you want to create an automatically refreshed materialized view, you must also have the following permissions:

  • The permission to connect to '%' (any IP address) in AnalyticDB for MySQL.

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

Prepare base tables

This section uses the customer and sales tables as examples to help you quickly create a materialized view.

/*+ 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, also known as complete materialized views. These can be based on internal and external tables of AnalyticDB for MySQL, existing materialized views, and views.

This topic demonstrates how to create a full-refresh materialized view named join_mv using the customer and sales base tables, and then configures the materialized view for 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 REFRESH MATERIALIZED VIEW join_mv;.

Create a materialized view that supports fast refresh

You can create an incremental materialized view, a type of materialized view that supports incremental refresh, based on internal tables in AnalyticDB for MySQL.

This section shows how to create a fast materialized view based on multiple joined tables.

Before you create an incremental materialized view, complete the following preparations:

• Check whether your cluster runs AnalyticDB for MySQL V3.1.9.0 or later.

• Enable binary logging at the cluster level and for each base table.

  If enabling binary logging for a base table fails, see Query execution error: : Can not create FAST materialized view, because demotable doesn't support getting incremental data.

  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. This view computes data only from 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 your cluster runs AnalyticDB for MySQL V3.2.1.0 or later, you can also create a multi-table fast materialized view named join_mv_incre, with automatic refresh every 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 more examples, see CREATE MATERIALIZED VIEW.

View materialized views that are being created

Run SHOW PROCESSLIST WHERE info LIKE '%CREATE MATERIALIZED VIEW%'; to list materialized views that are currently being created.

Each row in the result represents a materialized view that is being created. The user field shows the database account that is used to create the view. The status field shows the current status. The Info field contains the full CREATE statement. For more information about these fields, 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 SHOW PROCESSLIST returns no rows, the materialized view has been created, including its table schema and initial data load.

Base table queries for complete materialized views

Complete materialized views can be created from internal and external tables of AnalyticDB for MySQL, existing materialized views, and views. No restrictions apply to base table queries. For more information about the query syntax, see SELECT.

Base Table Query of Incremental Materialized View

For a materialized view with incremental refresh, the base table must be an internal table in AnalyticDB for MySQL. Queries on the base table must comply with the following rules:

General limits

These limits apply to all materialized views, including complete and fast materialized views.

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

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

• By default, the maximum number of materialized views per AnalyticDB for MySQL cluster depends on the minor version:

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

  • Earlier than V3.1.4.7: up to eight materialized views.

  Note

  If you reach the limit, you can contact technical support to request a higher quota.

Limits on complete materialized views

When you add or remove reserved nodes, asynchronous jobs are disabled. Because a complete refresh is an asynchronous job, it cannot run during node scaling. The fast refresh feature is not affected.

Limitations of Incremental Materialized Views

• Limitations of incremental materialized views on base tables:

  • Clusters that are earlier than V3.2.6.0 do not support XUANWU_V2 tables as base tables for fast materialized views.

    XUANWU_V2 tables do not support binary logging.

  • Clusters that are earlier than V3.2.3.0 do not support partitioned tables as base tables for fast materialized views.

  • Clusters that are earlier than V3.2.3.1 do not support INSERT OVERWRITE or TRUNCATE on base tables for fast materialized views. These statements return errors.

  • If your query uses MAX(), MIN(), APPROX_DISTINCT(), or COUNT(DISTINCT), base tables support only INSERT. Operations that delete data, such as DELETE, UPDATE, REPLACE, and INSERT ON DUPLICATE KEY UPDATE, are not allowed.

  • Clusters that run V3.2.5.0 or later support binary logging on materialized views. Therefore, materialized views can serve as base tables, which enables you to nest fast materialized views.

• Limits on refresh triggers:

  Only scheduled auto-refresh is supported, not manual refresh. The interval must be between 5 seconds and 5 minutes.

• Limits on query body (query_body):

  • Nondeterministic expressions, such as NOW() or RAND(), are not supported.

  • The ORDER BY clause is not supported.

  • The HAVING clause is not supported.

  • Window functions are not supported.

  • Set operators such as UNION, EXCEPT, and INTERSECT are not supported. UNION ALL is supported in V3.2.5.0 and later.

  • Only INNER JOIN is supported. Join columns must meet all the following criteria: they must be original columns of base tables, have identical data types, and be indexed. You can join up to five base tables.

    To associate more tables, please to technical support.

  • Only the following aggregate functions are supported: COUNT, SUM, MAX, MIN, AVG, APPROX_DISTINCT, and COUNT(DISTINCT).

  • AVG does not support the DECIMAL type.

  • COUNT(DISTINCT) supports only the INTEGER type.

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

When you define a materialized view, use a date column as the partition key (PARTITION BY) and set a lifecycle (LIFECYCLE) to keep only the most recent year of data.

For example, if the sales table receives new records daily, you can use the sale_date column as the partition key and set LIFECYCLE to 365. This keeps only the most recent 365 partitions, which is effectively one year of data.

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: Binary logging is not enabled for the base table demotable, which is used by the incrementally refreshed materialized view. Incrementally refreshed materialized views require binary logging to be enabled for all involved base tables.

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

If you receive the error XUANWU_V2 engine not support ALTER_BINLOG_ENABLE now, the base table demotable uses the XUANWU_V2 engine. XUANWU_V2 does not support binary logging. Because you cannot change the engine of a table, you must disable XUANWU_V2, recreate the table using the XUANWU engine, migrate data from the XUANWU_V2 table to the XUANWU table, and then create the fast materialized view on the XUANWU table.

To disable XUANWU_V2:

• If base tables are automatically created by DTS, zero-ETL, or console data synchronization, disable XUANWU_V2 at the cluster level. After you disable XUANWU_V2, all new tables use the XUANWU engine.

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

• If base tables are manually created with CREATE TABLE and only a few are needed for fast materialized views, you can disable XUANWU_V2 in those specific CREATE TABLE statements. Only those statements will create XUANWU tables. Other CREATE TABLE statements still create XUANWU_V2 tables.

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

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

Cause: Your fast materialized view query is a non-aggregate query that does not have a GROUP BY clause. In this case, the primary key of the materialized view must match the primary key of the base table, but the SELECT list omits the primary key column of the base table.

Incorrect example:

CREATE MATERIALIZED VIEW wrong_example1
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT product_id,price -- Base table sales has primary key sale_id, but sale_id is missing from SELECT.
FROM sales;

Solution: Add the primary key column of the base table to the SELECT list.

Correct example:

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

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

Cause: Your fast materialized view query is a non-aggregate query that does not have a GROUP BY clause. In this case, the primary key of the materialized view must match the primary key of the base table, but the definition includes both base table primary key columns and non-primary-key columns.

Incorrect example:

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

Solution: Remove non-primary-key columns from the primary key definition of the materialized view.

Correct example:

CREATE MATERIALIZED VIEW correct_example2
(PRIMARY KEY(sale_id)) -- Remove product_id from the primary key.
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

Cause: This error has two possible causes:

• The fast materialized view does not define a valid primary key.

• The query applies a function to a base table primary key column.

Solution: Choose the appropriate fix:

• No valid primary key defined: Update the materialized view definition (mv_definition) to meet the following rules:

  • For grouped aggregate queries (with GROUP BY), the primary key must be the GROUP BY columns. For example, if GROUP BY a,b, the primary key must be columns a and b.

  • For non-grouped aggregate queries (without GROUP BY), the primary key must be a constant.

  • For non-aggregate queries, the primary key must exactly match the primary key of the base table. For example, if the base table primary key is PRIMARY KEY(sale_id,sale_date), the materialized view primary key must also be PRIMARY KEY(sale_id,sale_date).

• Function applied to a primary key column: Modify the query to remove the function from the base table primary key column.

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

Cause: Columns that are used in multi-table joins have mismatched data types. Example: sales INNER JOIN customer ON customer.id=sales.id. If customer.id and sales.id have different data types, this error occurs.

Solution: Run ALTER TABLE tablename MODIFY COLUMN columnname newtype; to align the data types. For more information, see Change the data type of a column.

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

Cause: Join columns lack indexes. Example: sales INNER JOIN customer ON customer.id=sales.id. If customer.id or sales.id does not have an index, this error occurs.

Solution: Run ALTER TABLE tablename ADD KEY idx_name(columnname); to add indexes on join columns. For more information, see Create an index.

Query execution error: : Query exceeded reserved memory limit

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

Solution: SQL statements that are memory-intensive typically use Aggregation, TopN, Window, or Join operators. You can use the SQL diagnostics feature to identify high-memory stages and operators. Then, you can optimize those operators. For more information, see Memory metrics and Use stage and task details to analyze queries.