CREATE MATERIALIZED VIEW syntax reference - AnalyticDB - Alibaba Cloud Documentation Center

This topic describes the CREATE MATERIALIZED VIEW statement. Use this statement to create a materialized view with a complete or fast refresh policy and define its refresh schedule.

Syntax

CREATE [OR REPLACE] MATERIALIZED VIEW mv_name
[mv_definition]
[mv_properties]
[COMMENT 'view_comment']
[REFRESH {COMPLETE|FAST}]
[ON {DEMAND|OVERWRITE}]
[START WITH date] [NEXT date]
[{DISABLE|ENABLE} QUERY REWRITE]
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
This parameter is supported only by clusters with kernel version 3.1.4.7 or later. If a materialized view with the same name does not exist, a new materialized view is created. If a materialized view with the same name already exists, the system creates a temporary view, populates it with new data, and then replaces the original materialized view.
mv_definition	Optional		Changeable after creation: No
Defines the schema of the materialized view. If you omit this clause, the system infers the schema from the query_body. By default, the system defines a primary key, creates indexes on all columns, sets the storage policy to hot storage, and uses the XUANWU engine. To manually define the schema of a materialized view, including its distribution key, partition key, primary key, indexes, and hot/cold data storage policy, use the same syntax as the CREATE TABLE statement. For example, if you do not want to index all columns, you can use the INDEX keyword to specify which columns to index. To reduce storage costs, you can also define a mixed hot/cold storage policy or even retain only the data from the last year. Primary key rules Complete refresh: If a primary key is not explicitly defined, the system automatically generates the column `__adb_auto_id__` as the primary key for the materialized view. If you want to explicitly define a primary key, you can use any column from the query_body output as the primary key for the materialized view. `fast refresh`: The primary key, whether explicitly defined or automatically generated, must follow these rules: For a group aggregate query (an aggregate query with a `GROUP BY` clause), the primary key must be the `GROUP BY` columns. For example, if you use `GROUP BY a,b`, the primary key must be a and b. For non-grouped aggregate queries (without a `GROUP BY` clause), the primary key must be a constant. For non-aggregate queries, the primary key must be the same as the primary key of the base table. For example, if the primary key of the base table is `PRIMARY KEY(sale_id,sale_date)`, the primary key of the materialized view must also be `PRIMARY KEY(sale_id,sale_date)`. Recommendations For optimal query performance, we recommend defining a primary key, distribution key, and partition key when creating a materialized view.
mv_properties	Optional		Changeable after creation: Yes (by using ALTER MATERIALIZED VIEW)
This parameter is supported only by Enterprise Edition, Basic Edition, and Data Lakehouse Edition clusters with kernel version 3.1.9.3 or later. Defines the resource policy for the materialized view. This includes `mv_resource_group` for resource allocation and `mv_refresh_hints` for refresh task configuration. The value must be in JSON format. Example: `MV_PROPERTIES='{ "mv_resource_group":"<resource_group_name>", "mv_refresh_hints":{"<hint_name>":"<hint_value>"} }'` mv_resource_group Specifies the resource group used to create and refresh the materialized view. If not specified, the default `user_default` resource group is used. The value of this parameter can be an Interactive or Job resource group of the XIHE engine. The difference is that Job resource groups provision resources on demand, which typically introduces a latency of seconds or minutes. If you have a high tolerance for refresh latency, you can specify a Job resource group. A materialized view that uses a Job resource group is also known as an elastic materialized view. To increase the refresh speed of an elastic materialized view, you can configure the elastic_job_max_acu parameter in `mv_refresh_hints` to modify the maximum amount of resources that the materialized view can use. For usage details, see the Elastic Materialized View Example section below. You can view the available resource groups for your cluster on the Resource Groups page in the console or by calling the DescribeDBResourceGroup operation. The operation fails if the specified resource group does not exist. mv_refresh_hints Specifies configuration parameters for the materialized view. For a list of supported parameters and their usage, see Common hints.
REFRESH [COMPLETE \| FAST]	Optional	Default value: COMPLETE	Changeable after creation: No
Defines the refresh policy of the materialized view. For information about the differences between refresh policies and their use cases, see Select a refresh policy. COMPLETE A complete refresh runs the original query SQL to scan the data of all target partitions in the base table and completely overwrites the old data with the newly calculated data. Complete refresh supports the `ON DEMAND [START WITH date] [NEXT date]` and `ON OVERWRITE` refresh trigger mechanisms, which allow you to perform manual refreshes on demand, schedule automatic refreshes, or automatically refresh when the base table is overwritten. FAST This parameter is supported in versions 3.1.9.0 and later. Version 3.1.9.0 supports fast refresh only for single-table materialized views. Version 3.2.0.0 and later support fast refresh for both single-table and multi-table materialized views. Performs a fast refresh. The system rewrites the view's query (`query_body`) to scan only the changed data (from `INSERT`, `DELETE`, and `UPDATE` operations) in the base table and applies these changes to the materialized view. This avoids scanning the entire base table in each cycle and reduces the computational cost of each refresh. Before you create a materialized view that uses fast refresh, you must enable the binary logging feature for both the cluster and the base tables. Otherwise, the creation fails. For instructions, see Enable the binary logging feature. For a materialized view that uses fast refresh, the refresh trigger mechanism must be a scheduled automatic refresh. You must define the next refresh time by using `ON DEMAND {NEXT date}`. Fast refresh has some limitations. If the `query_body` does not support fast refresh, an error occurs when you try to create the materialized view.
ON [DEMAND \| OVERWRITE]	Optional	Default value: DEMAND	Changeable after creation: No
Defines the refresh trigger mechanism of the materialized view. For information about the differences between refresh trigger mechanisms and their use cases, see Select a refresh trigger mechanism. DEMAND On-demand refresh. This means you can manually refresh the materialized view when needed, or use `NEXT` to specify a scheduled automatic refresh. Fast refresh materialized views only support `ON DEMAND`. OVERWRITE The materialized view is automatically refreshed after the data in its base table is overwritten by an `INSERT OVERWRITE` statement. When the refresh trigger mechanism is `ON OVERWRITE`, you cannot define `START WITH` or `NEXT`.
[START WITH date] [NEXT date]	Optional		Changeable after creation: No
When the refresh trigger mechanism of a materialized view is `ON DEMAND`, you can define its refresh schedule. If no schedule is defined, the view is not refreshed periodically. START WITH The time of the first refresh. If omitted, the view refreshes immediately after creation. NEXT The time of the next scheduled refresh. For a materialized view that uses fast refresh, you must specify `NEXT`. The automatic refresh interval must be between 5 seconds (s) and 5 minutes (min). For a materialized view that uses complete refresh, `NEXT` is optional. If specified, the minimum automatic refresh interval is 60 seconds (s). *date* Time functions are supported. The time is precise to the second, and milliseconds are truncated.
[DISABLE \| ENABLE] QUERY REWRITE	Optional	Default value: DISABLE	Changeable after creation: Yes (by using ALTER MATERIALIZED VIEW)
This parameter is supported only in versions 3.1.4 and later. Enables or disables automatic query rewrite for this materialized view. For more information, see Query rewrite for materialized views. DISABLE Disables the query rewrite feature for the current materialized view. ENABLE Enables the query rewrite feature for the current materialized view. After you enable this feature, the optimizer can rewrite all or part of a query based on its SQL pattern and route it to the materialized view. This avoids running the original computations on the base table and improves query performance.
query_body	Required		Changeable after creation: No
Defines the query on the base tables for the materialized view. For a materialized view that uses complete refresh, the base table can be an AnalyticDB for MySQL internal table, an external table, an existing materialized view, or a view. There are no restrictions on the query. For more information about the query syntax, see SELECT. For a materialized view that uses fast refresh, the base table must be an AnalyticDB for MySQL internal table. The query must follow these rules: SELECT column rules The columns in the SELECT list must follow these rules: With GROUP BY and aggregate functions: Include all GROUP BY columns in the SELECT list. View examples Correct example (all GROUP BY columns included): `CREATE MATERIALIZED VIEW demo_mv1 REFRESH FAST ON DEMAND NEXT now() + INTERVAL 3 minute AS SELECT sale_id, -- GROUP BY column sale_date, -- GROUP BY column max(quantity) AS max, -- expression columns require aliases sum(price) AS sum FROM sales GROUP BY sale_id, sale_date;` Incorrect (missing GROUP BY column `sale_date`): `CREATE MATERIALIZED VIEW false_mv1 REFRESH FAST ON DEMAND NEXT now() + INTERVAL 3 minute AS SELECT sale_id, max(quantity) AS max, sum(price) AS sum FROM sales GROUP BY sale_id, sale_date; -- sale_date is in GROUP BY but not in SELECT` With aggregate functions and no GROUP BY: The SELECT list may contain only aggregate columns, or only constant and aggregate columns. View examples `-- Aggregate columns only CREATE MATERIALIZED VIEW demo_mv2 REFRESH FAST ON DEMAND NEXT now() + INTERVAL 3 minute AS SELECT max(quantity) AS max, sum(price) AS sum FROM sales; -- Constant plus aggregate columns (the constant becomes the primary key) CREATE MATERIALIZED VIEW demo_mv3 REFRESH FAST ON DEMAND NEXT now() + INTERVAL 3 minute AS SELECT 1 AS pk, max(quantity) AS max, sum(price) AS sum FROM sales;` Without aggregation: Include all primary key columns of the base table. View examples `-- Single primary key CREATE MATERIALIZED VIEW demo_mv4 REFRESH FAST ON DEMAND NEXT now() + INTERVAL 3 minute AS SELECT sale_id, -- primary key column of base table sales quantity FROM sales; -- 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, -- first primary key column sale_date, -- second primary key column quantity FROM sales1;` UNION ALL queries: Each branch must output a column named `union_all_marker` with a distinct constant value per branch. Include all base table primary key columns. The materialized view primary key must include both the base table primary key columns and `union_all_marker`. `CREATE MATERIALIZED VIEW demo_union_all_mv (PRIMARY KEY(id, union_all_marker)) REFRESH FAST NEXT now() + INTERVAL 5 minute AS SELECT customer_id AS id, "customer" AS union_all_marker FROM customer UNION ALL SELECT sale_id AS id, "sales" AS union_all_marker FROM sales;` Expression columns: All expression columns in the SELECT list must have aliases, for example `SUM(price) AS total_price`. Other limitations Non-deterministic expressions, such as `NOW()` and `RAND()`, are not supported. The `ORDER BY` clause is not supported. The `HAVING` clause is not supported. Window functions are not supported. Set operations such as `UNION`, `EXCEPT`, and `INTERSECT` are not supported. `UNION ALL` is supported in versions 3.2.5.0 and later. Only `INNER JOIN` is supported. The join keys must be original columns from the tables, have the same data type, and be indexed. 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

The user creating the materialized view 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 table) for all base tables of the materialized view.
To create an automatically refreshed materialized view, you also need the following two permissions:
- Permission to connect to AnalyticDB for MySQL from any IP address (i.e., '%').
- The INSERT permission on the materialized view or on all tables in the database where the materialized view resides. Otherwise, the system cannot refresh the data in the materialized view.

Examples

Prerequisites

The examples in this topic use the base tables created in this section. To run the examples, first create the base tables using the following SQL statements.

/*+ 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
);

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

Complete refresh materialized view

Create the materialized view myview1, which 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 the materialized view myview2, which refreshes at 2:00 AM every day.

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 myview3 that refreshes every Monday at 2:00 AM.

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 the materialized view myview4, which is refreshed at 2:00 AM on the first day of every month.

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 the materialized view myview5 and refresh it 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 the materialized view myview6 that is not automatically refreshed and relies entirely on manual refreshes.

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 the materialized view myview7. You do not need to manually define a refresh time because the materialized view is automatically refreshed after the base table is overwritten by an INSERT OVERWRITE operation.
```
CREATE MATERIALIZED VIEW myview7
REFRESH COMPLETE ON OVERWRITE
AS
SELECT count(*) as cnt FROM customer;
```

Single-table materialized view with fast refresh

Before you create a materialized view that uses fast refresh, you must enable the binary logging feature 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 with fast refresh and no aggregation. It refreshes every 10 seconds.

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 with fast refresh and a grouped aggregation. It is refreshed every 5 seconds.

CREATE MATERIALIZED VIEW fast_mv2
REFRESH FAST NEXT now() + INTERVAL 5 second
AS
SELECT
   customer_id, sale_date,                 -- The system automatically includes the GROUP BY columns as the primary key of the materialized view.
   COUNT(sale_id) AS cnt_sale_id,          -- Aggregate output column.
   SUM(price * quantity) AS total_revenue, -- Aggregate output column.
   customer_id / 100 AS new_customer_id    -- Non-aggregate output columns can use any expression.
FROM sales
WHERE ifnull(price, 1) > 0                 -- Any expression can be used in the condition.
GROUP BY customer_id, sale_date;

Create a single-table materialized view named fast_mv3 with fast refresh and a non-grouped aggregation. It is refreshed every minute.

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

Multi-table materialized view with fast refresh

Create a multi-table materialized view named fast_mv4 with fast refresh and no aggregation. It is refreshed every 5 seconds.

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 with fast refresh and a grouped aggregation. It is refreshed every 10 seconds.

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 the materialized view myview8 and define the distribution key and partition key.

CREATE MATERIALIZED VIEW myview8 (
  quantity INT,    -- The materialized view includes all columns from the query results even if they are not explicitly listed in the definition.
  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;

Define keys and indexes

Create the materialized view myview9, creating an index only on the specified column customer_name instead of on all columns.

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 with a primary key, a distribution key, a clustering index, indexes on specified columns, and a comment.

CREATE MATERIALIZED VIEW myview10 (
  quantity INT,    -- The materialized view includes all columns from the query results even if they are not explicitly listed in the definition.
  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 the elastic materialized view myview11, using the Job-type resource group serverless to create and refresh it, with a refresh frequency of once per day.

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 the elastic materialized view myview12, which uses the Job-type resource group serverless for creation and refreshes, and can use 12 ACU of resources from this group.

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;

AnalyticDB:CREATE MATERIALIZED VIEW

Syntax

Parameters

OR REPLACE

mv_definition

Primary key rules

Recommendations

mv_properties

mv_resource_group

mv_refresh_hints

REFRESH [COMPLETE | FAST]

COMPLETE

FAST

ON [DEMAND | OVERWRITE]

DEMAND

OVERWRITE

[START WITH date] [NEXT date]

START WITH

NEXT

date

[DISABLE | ENABLE] QUERY REWRITE

DISABLE

ENABLE

query_body

SELECT column rules

Other limitations