CREATE MATERIALIZED VIEW syntax for materialized views - AnalyticDB

This topic describes the CREATE MATERIALIZED VIEW statement, which you can use to create materialized views that support complete or fast refresh and configure their refresh schedule.

Syntax

CREATE [OR REPLACE] MATERIALIZED VIEW mv_name
[mv_definition]
[mv_properties]
[REFRESH {COMPLETE|FAST}]
[ON {DEMAND|OVERWRITE}]
[START WITH date] [NEXT date]
[{DISABLE|ENABLE} QUERY REWRITE]
[COMMENT 'view_comment']
AS 
query_body

mv_definition:
  ({column_name column_type [column_attributes] [ column_constraints ] [COMMENT 'column_comment']
  | table_constraints}
  [, ... ])
  [table_attribute]
  [partition_options]
  [index_all]
  [storage_policy]
  [block_size]
  [engine]
  [table_properties]

Parameters

OR REPLACE

Optional

Changeable after creation: no

Only AnalyticDB for MySQL clusters of V3.1.4.7 or later support this parameter.

If no existing materialized view uses the same name, AnalyticDB for MySQL creates a new materialized view.
If an existing materialized view uses the same name, AnalyticDB for MySQL creates a temporary view to write new data and replaces the original materialized view after the data is written.

mv_definition

Optional

Changeable after creation: no

You can define the structure of the materialized view.

You can choose whether to explicitly define the schema of a materialized view. If you do not explicitly define the schema, AnalyticDB for MySQL uses the columns from the query_body results as the materialized view columns, defines a primary key, creates indexes on all columns, configures hot data storage, and sets the engine to XUANWU.

To manually define the schema—including distribution keys, partition keys, primary keys, indexes, and tiered storage policies for hot and cold data—use the same method as for the CREATE TABLE statement. For example, if you do not need to index all columns, use the INDEX keyword to specify which columns to index. To reduce storage costs, set the storage policy to mixed hot-and-cold storage or retain only the last year of data.

Primary key rules

Complete refresh: If you do not explicitly define a primary key, AnalyticDB for MySQL automatically generates a column named __adb_auto_id__ and uses it as the primary key. To explicitly define a primary key, select any column from the query_body results.
Fast refresh: Whether explicitly defined or auto-generated, the primary key must follow these 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).

Recommendations

To optimize query performance, define a primary key, a distribution key, and a partition key when you create a materialized view.

mv_properties

Optional

Changeable after creation: yes (using the ALTER MATERIALIZED VIEW statement)

Only AnalyticDB for MySQL Enterprise Edition, Basic Edition, or Data Lakehouse Edition clusters of V3.1.9.3 or later support this parameter.

The resource policy for the materialized view, including the resource group used (mv_resource_group) and configuration hints for refresh tasks (mv_refresh_hints). This parameter uses JSON format. Example:

MV_PROPERTIES='{
  "mv_resource_group":"<resource_group_name>",
  "mv_refresh_hints":{"<hint_name>":"<hint_value>"}
}'

mv_resource_group

The resource group used to create and refresh the materialized view. If you do not specify one, the default resource group user_default is used.

You can set this to an interactive resource group or a job resource group powered by the XIHE engine. Job resource groups require seconds or minutes to provision resources. If you tolerate higher refresh latency, use a job resource group. Materialized views that use job resource groups are called elastic materialized views. To improve the refresh speed of elastic materialized views, configure elastic_job_max_acu in mv_refresh_hints to increase the maximum resource limit. See the elastic materialized view example.

You can view available resource groups on the Resource Groups page in the console or call the DescribeDBResourceGroup operation.

If the specified resource group does not exist, an error occurs when you create the materialized view.

mv_refresh_hints

Specifies the configuration parameters for materialized views. For a list of supported parameters and their usage, see Common Hints.

REFRESH [COMPLETE | FAST]

Optional

Default value: COMPLETE

Changeable after creation: no

The refresh policy for the materialized view. For differences between policies and applicable scenarios, see How to choose a refresh policy.

COMPLETE

Complete refresh runs the original query SQL each time, scans all target partitions of the base table, and overwrites old data with newly computed data.

Complete refresh supports these trigger mechanisms: on-demand manual refresh, scheduled automatic refresh (ON DEMAND [START WITH date] [NEXT date]), and automatic refresh when base tables are overwritten (ON OVERWRITE).

FAST

Only AnalyticDB for MySQL clusters of V3.1.9.0 or later support this parameter. V3.1.9.0 supports only single-table materialized views with fast refresh. V3.2.0.0 or later supports both single-table and multi-table materialized views with fast refresh.

Fast refresh rewrites the materialized view query (query_body) to scan only changed data in the base table (INSERT, DELETE, UPDATE), process it, and write it to the materialized view. This avoids scanning all base table data and reduces per-refresh compute overhead.

Before creating a materialized view with fast refresh, enable binary logging for the cluster and base tables. Otherwise, creation fails. See Enable binary logging.

Materialized views with fast refresh must use scheduled automatic refresh. Define the next refresh time using ON DEMAND {NEXT date}.

Materialized views with fast refresh have specific limits. If the query_body does not support fast refresh, creation fails.

ON [DEMAND | OVERWRITE]

Optional

Default value: DEMAND

Changeable after creation: no

The refresh trigger mechanism for the materialized view. For differences between mechanisms and applicable scenarios, see How to choose a refresh trigger mechanism.

DEMAND

On-demand refresh. Manually trigger a refresh or use the NEXT clause to schedule automatic refresh.

Materialized views with fast refresh support only ON DEMAND.

OVERWRITE

Automatic refresh when the base table is overwritten by an INSERT OVERWRITE statement.

If you set the trigger mechanism to ON OVERWRITE, do not specify START WITH or NEXT.

[START WITH date] [NEXT date]

Optional

Changeable after creation: no

Define the refresh time only when the trigger mechanism is ON DEMAND. If you do not define a time, no scheduled refresh occurs.

START WITH

The first refresh time. If omitted, the first refresh occurs at creation time.

The next refresh time.

For materialized views with fast refresh, you must specify NEXT. The interval must be between 5 seconds and 5 minutes.
For materialized views with complete refresh, NEXT is optional. If specified, the interval must be at least 60 seconds.

date

Time functions are supported but must be accurate to the second. Milliseconds are truncated.

[DISABLE | ENABLE] QUERY REWRITE

Optional

Default value: DISABLE

Changeable after creation: yes (using the ALTER MATERIALIZED VIEW statement)

This parameter is supported only by versions 3.1.4 and later.

Enables or disables query rewrite for the materialized view. For details, see Query rewrite for materialized views.

DISABLE

Disables query rewrite for the materialized view.

ENABLE

Enables query rewrite for the materialized view. After enabling, the optimizer rewrites full or partial queries based on SQL patterns and routes them to the materialized view. This avoids computing on base tables and improves query performance.

query_body

Required

Changeable after creation: no

The base table query for the materialized view.

For a materialized view with full refresh, the base tables can be internal tables, external tables, existing materialized views, and views in AnalyticDB for MySQL. There are no restrictions on the base table query. For more information about the query syntax, see SELECT.

For a materialized view with incremental refresh, the base tables can only be internal tables in AnalyticDB for MySQL. The base table query must follow these rules:

SELECT output columns

For a grouping and aggregation query (an aggregate query with a GROUP BY clause), the output columns in the SELECT list must include all grouping columns from the GROUP BY clause.

Click to view examples

Correct example

Incorrect example

CREATE MATERIALIZED VIEW demo_mv1
REFRESH FAST ON DEMAND
 NEXT now() + INTERVAL 3 minute
AS
SELECT 
  sale_id, -- Output the grouping column.
  sale_date, -- Output the grouping column.
  max(quantity) AS max, -- An alias must be set for the expression column.
  sum(price)AS sum
FROM sales
GROUP BY sale_id,sale_date;

CREATE MATERIALIZED VIEW false_mv1
REFRESH FAST ON DEMAND
 NEXT now() + INTERVAL 3 minute
AS
SELECT 
  sale_id, -- The grouping column sale_date is not in the output.
  max(quantity) AS max,
  sum(price)AS sum
FROM sales
GROUP BY sale_id,sale_date;

For a non-grouping aggregate query (an aggregate query without a GROUP BY clause), the SELECT list can output only constant columns and aggregated columns, or only aggregated columns.

Click to view examples

Correct example

Incorrect example

Outputs only the aggregated columns `max` and `sum`.

CREATE MATERIALIZED VIEW demo_mv2
REFRESH FAST ON DEMAND
 NEXT now() + INTERVAL 3 minute
AS
SELECT 
  max(quantity) AS max, -- An alias must be set for the expression column.
  sum(price)AS sum
FROM sales;

Outputs columns other than constant columns and aggregated columns.

CREATE MATERIALIZED VIEW false_mv2
REFRESH FAST ON DEMAND
 NEXT now() + INTERVAL 3 minute
AS
SELECT
  sale_id, 
  max(quantity) AS max,
  sum(price) AS sum
FROM sales;

Outputs the constant column `pk` and the aggregated columns `max` and `sum`.

CREATE MATERIALIZED VIEW demo_mv3
REFRESH FAST ON DEMAND
 NEXT now() + INTERVAL 3 minute
AS
SELECT 
  1 AS pk,  -- In a non-grouping aggregation, the constant column is used as the primary key of the materialized view.
  max(quantity) AS max,
  sum(price) AS sum
FROM sales;

For a non-aggregate query, the SELECT list must output all primary key columns of the base table.

Click to view examples

Correct example

Incorrect example

CREATE MATERIALIZED VIEW demo_mv4
REFRESH FAST ON DEMAND
 NEXT now() + INTERVAL 3 minute
AS
SELECT 
  sale_id, -- Output the primary key column of the base table.
  quantity
FROM sales;

CREATE MATERIALIZED VIEW false_mv3
REFRESH FAST ON DEMAND
 NEXT now() + INTERVAL 3 minute
AS
SELECT 
  sale_date, -- The primary key column sale_id of the base table is not in the output.
  quantity
FROM sales;

Assume that the `sales1` table has a composite primary key `PRIMARY KEY(sale_id,sale_date)`.

CREATE MATERIALIZED VIEW demo_mv5
REFRESH FAST ON DEMAND
 NEXT now() + INTERVAL 3 minute
AS
SELECT 
  sale_id, -- Output the primary key column of the base table.
  sale_date, -- Output the primary key column of the base table.
  quantity
FROM sales1;

Assume that the `sales1` table has a composite primary key `PRIMARY KEY(sale_id,sale_date)`.

CREATE MATERIALIZED VIEW false_mv4
REFRESH FAST ON DEMAND
 NEXT now() + INTERVAL 3 minute
AS
SELECT 
  sale_id, -- The other primary key column sale_date of the base table is not in the output.
  quantity
FROM sales1;

Define an alias for each expression column in the output. Use a meaningful alias, such as SUM(price) AS total_price.

Other limits

Non-deterministic expressions, such as `NOW()` and `RAND()`, are not supported.
`ORDER BY` sorting operations are not supported.
The `HAVING` clause is not supported.
Window functions are not supported.
Set operations, such as `UNION`, `EXCEPT`, and `INTERSECT`, are not supported.
Only `INNER JOIN` operations are supported. The join columns must meet all the following conditions: the columns must be original columns of the tables, have the same data type, and have an index. You can join a maximum of five tables.
To join more tables, to contact 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 data type.
`COUNT(DISTINCT)` supports only the INTEGER data type.

Required permissions

To create a materialized view, the user must have all of the following permissions:

The CREATE permission on the database where the materialized view will be created.
The SELECT permission on the relevant columns or the entire base tables of the materialized view.
If you want to create an auto-refresh materialized view, you also need the following permissions:
- Permission to connect to AnalyticDB for MySQL from any IP address ('%').
- The INSERT permission on the view itself, or on all tables in its database, is required to refresh the data in the materialized view.

Examples

Example preparation

The materialized view examples below use the base tables defined in this section. To try the examples, first run the SQL statements here to create the base tables.

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

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

Full Refresh of a Materialized View

Create a materialized view named myview1 that refreshes every 5 minutes.

CREATE MATERIALIZED VIEW myview1
REFRESH   -- Equivalent to REFRESH COMPLETE.
 NEXT now() + INTERVAL 5 minute
AS
SELECT count(*) as cnt FROM customer;

Create a materialized view named myview2 that refreshes daily at 02:00:00.

CREATE MATERIALIZED VIEW myview2
REFRESH COMPLETE
 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 customer;

Create a materialized view named myview3 that refreshes every Monday at 02:00:00.

CREATE MATERIALIZED VIEW myview3
REFRESH COMPLETE ON DEMAND
 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 customer;

Create a materialized view named myview4 that refreshes on the first day of each month at 02:00:00.

CREATE MATERIALIZED VIEW myview4
REFRESH   -- Equivalent to REFRESH COMPLETE. 
 NEXT DATE_FORMAT(last_day(now()) + INTERVAL 1 day, '%Y-%m-%d 02:00:00')
AS
SELECT count(*) as cnt FROM customer;

Create a materialized view named myview5 that refreshes only once.

CREATE MATERIALIZED VIEW myview5
REFRESH   -- Equivalent to REFRESH COMPLETE.
 START WITH now() + INTERVAL 1 day
AS 
SELECT count(*) as cnt FROM customer;

Create a materialized view named myview6 that does not refresh automatically. Refresh it manually.

CREATE MATERIALIZED VIEW myview6 (
  PRIMARY KEY (customer_id)
) DISTRIBUTED BY HASH (customer_id)
AS
SELECT customer_id FROM customer;

Manually refresh the materialized view:

REFRESH MATERIALIZED VIEW myview6;

Create a materialized view named myview7 that refreshes automatically when the base table is overwritten. No manual refresh time is needed.
```
CREATE MATERIALIZED VIEW myview7
REFRESH COMPLETE ON OVERWRITE
AS
SELECT count(*) as cnt FROM customer;
```

Create a single-table materialized view that supports fast refresh

Before you create a materialized view that supports fast refresh, enable binary logging for the cluster and base tables.

SET ADB_CONFIG BINLOG_ENABLE=true;

ALTER TABLE customer binlog=true;

ALTER TABLE sales binlog=true;

Create a single-table materialized view named fast_mv1 that supports fast refresh at 10-second intervals. The materialized view query does not involve an aggregate function.
```
CREATE MATERIALIZED VIEW fast_mv1
REFRESH FAST NEXT now() + INTERVAL 10 second
AS
SELECT sale_id, sale_date, price
FROM sales
WHERE price > 10;
```

Create a single-table materialized view named fast_mv2 that supports fast refresh at 5-second intervals. The materialized view query involves an aggregate function with GROUP BY.

CREATE MATERIALIZED VIEW fast_mv2
REFRESH FAST NEXT now() + INTERVAL 5 second
AS
SELECT
   customer_id, sale_date,                 -- The system uses the GROUP BY columns as the primary key of the materialized view. 
   COUNT(sale_id) AS cnt_sale_id,          -- Perform an aggregate operation on a column. 
   SUM(price * quantity) AS total_revenue, -- Perform an aggregate operation on a column. 
   customer_id / 100 AS new_customer_id    -- You can use an expression to define columns that are not involved in aggregate operations. 
FROM sales
WHERE ifnull(price, 1) > 0                 -- You can use an expression to define WHERE conditions. 
GROUP BY customer_id, sale_date;

Create a single-table materialized view named fast_mv3 that supports fast refresh at 1-minute intervals. The materialized view query involves an aggregate function without GROUP BY.

CREATE MATERIALIZED VIEW fast_mv3
REFRESH FAST NEXT now() + INTERVAL 1 minute
AS
SELECT count(*) AS cnt   -- The system generates a constant as the primary key to ensure that only one record is contained in the materialized view. 
FROM sales;

Create a multi-table materialized view that supports fast refresh

Create a multi-table materialized view named fast_mv4 that supports fast refresh at 5-second intervals and does not use aggregation.

CREATE MATERIALIZED VIEW fast_mv4
REFRESH FAST NEXT now() + INTERVAL 5 second
AS
SELECT 
    c.customer_id,
    c.customer_name,
    s.sale_id,
    (s.price * s.quantity) AS revenue
FROM 
    sales s
JOIN 
    customer c ON s.customer_id = c.customer_id;

Create a multi-table materialized view named fast_mv5 that supports fast refresh at 10-second intervals and uses grouped aggregation.

CREATE MATERIALIZED VIEW fast_mv5
REFRESH FAST NEXT now() + INTERVAL 10 second
AS
SELECT 
    s.sale_id,
    c.customer_name,
    COUNT(*) AS cnt,           
    SUM(s.price * s.quantity) AS revenue                
FROM 
    sales s
JOIN 
    (SELECT customer_id, customer_name FROM customer) c ON c.customer_id = s.customer_id
GROUP BY 
    s.sale_id, c.customer_name;

Partitioned materialized view

Create a materialized view named myview8 with a distribution key and a partition key.

CREATE MATERIALIZED VIEW myview8 (
  quantity INT,    -- The materialized view includes all columns from the query results even if no columns are explicitly displayed.     
  price DECIMAL(10, 2),
  sale_date TIMESTAMP
) 
DISTRIBUTED BY HASH(sale_id)
PARTITION BY VALUE(date_format(sale_date, "%Y%m%d")) LIFECYCLE 30
AS 
SELECT * FROM sales;

Explicitly define primary key, distribution key, indexes, etc.

Create a materialized view named myview9 that indexes only the customer_name column.

CREATE MATERIALIZED VIEW myview9 (
  INDEX (sale_date),
  PRIMARY KEY (sale_id)
) DISTRIBUTED BY HASH (sale_id)
REFRESH
 NEXT now() + INTERVAL 1 DAY
AS
SELECT * FROM sales;

Create a materialized view named myview10 that defines a primary key, a distribution key, a clustered index, a column-based index, and a comment.

CREATE MATERIALIZED VIEW myview10 (
  quantity INT,    -- The materialized view includes all columns from the query results even if no columns are explicitly displayed.     
  price DECIMAL(10, 2),
  KEY INDEX_ID(customer_id) COMMENT 'customer',
  CLUSTERED KEY INDEX(sale_id),
  PRIMARY KEY(sale_id,sale_date)
) 
DISTRIBUTED BY HASH(sale_id)
COMMENT 'MATERIALIZED VIEW c'
AS 
SELECT * FROM sales;

Elastic Materialized View

Create an elastic materialized view named myview11 that uses the serverless job resource group and refreshes daily.

CREATE MATERIALIZED VIEW myview11
MV_PROPERTIES='{
  "mv_resource_group":"serverless"
}'
REFRESH COMPLETE ON DEMAND
 START WITH now()
 NEXT now() + INTERVAL 1 DAY
AS
SELECT * FROM sales;

Create an elastic materialized view named myview12 that uses the serverless job resource group and can consume up to 12 ACUs.

CREATE MATERIALIZED VIEW myview12
MV_PROPERTIES='{
  "mv_resource_group":"serverless",
  "mv_refresh_hints":{"elastic_job_max_acu":"12"}
}'
REFRESH COMPLETE ON DEMAND
 START WITH now()
 NEXT now() + INTERVAL 1 DAY
AS
SELECT * FROM sales;

References

Materialized views: describes applicable scenarios and feature updates.
Create a materialized view: explains how to create materialized views and troubleshoot common errors.
Refresh materialized views: explains how to configure complete or fast refresh.
Manage materialized views: explains how to query definitions, list all materialized views, and delete materialized views.
Query rewrite for materialized views