AnalyticDB for MySQL materialized views precompute user-defined queries and store the query results in advance. When you analyze complex queries, you can directly retrieve precomputed query results from materialized views. This accelerates query response. This topic describes how to create a materialized view.
Required permissions
To create a view, you must have the CREATE permission on databases or tables.
To refresh a materialized view, you must have the INSERT permission on databases or tables.
You must have the SELECT permission on the relevant table columns or all tables that are involved in a materialized view.
If you want to configure auto-refresh for a materialized view that you created, you must have permissions to refresh views by using the on-premises server (
127.0.0.1
) or an IP address ('%'
).
Limits
You cannot perform
INSERT
,DELETE
, orUPDATE
operations on materialized views.You cannot delete or rename base tables that are involved in a materialized view or columns in the base tables. Before you modify the base tables, you must delete the materialized view.
The maximum number of materialized views that can be created for an AnalyticDB for MySQL cluster varies based on the cluster version.
Clusters of V3.1.4.7 or earlier: up to eight materialized views.
Clusters of V3.1.4.7 or later: up to 64 materialized views.
NoteTo query the minor version of an AnalyticDB for MySQL Data Lakehouse Edition cluster, execute the
SELECT adb_version();
statement. To update the minor version of a cluster, contact technical support.For information about how to view and update the minor version of an AnalyticDB for MySQL Data Warehouse Edition cluster, see Update the minor version of a cluster.
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]]]
[QUERY REWRITE]
AS
<QUERY BODY>;
Parameters
Parameter | Description |
| The rule that is used to create a materialized view based on whether the name of the materialized view that you want to create is the same as the name of an existing materialized view.
Note This parameter takes effect in AnalyticDB for MySQL clusters of V3.1.4.7 or later.
|
| The name of the materialized view. |
| The table-related properties in the materialized view.
|
| The refresh method of the materialized view. Valid values:
|
| The trigger mode of refreshes. Valid values:
|
| The first point in time when you want the materialized view to be automatically and fully refreshed. If you do not specify this parameter, the current point in time is used. Note Time functions are supported and must be accurate to the second. Milliseconds are truncated. For information about how to refresh a materialized view, see Configure full refresh for materialized views and Configure incremental refresh for materialized views. |
| The next point in time when you want the materialized view to be automatically and fully refreshed. If you want to enable auto-refresh, you must specify the |
| Enables or disables the query rewrite feature for a materialized view. After you enable this feature for a materialized view, queries can be rewritten to the materialized view. You can use the materialized view as a cache. Valid values:
Note You can enable this feature only for AnalyticDB for MySQL clusters of V3.1.4 or later. For information about how to view the minor version of an AnalyticDB for MySQL cluster, see How do I query the minor version of an AnalyticDB for MySQL cluster?
|
| The query body of the materialized view, which can be a table, logical view, or materialized view. Take note of the following items:
|
| To create an elastic materialized view, you must configure the MV_PROPERTIES parameter. When you create or refresh an elastic materialized view, the system uses a job resource group to dynamically schedule resources. This reduces costs but results in slower response than standard materialized views. Before you create an elastic materialized view, make sure that the following requirements are met:
MV_PROPERTIES supports the mv_resource_group and mv_refresh_hints parameters in the JSON format.
|
Examples
Create a materialized view named
myview1
that refreshes every 5 minutes.CREATE MATERIALIZED VIEW myview1 REFRESH NEXT now() + interval 5 minute AS SELECT count(*) as cnt FROM base;
Create a materialized view named
myview2
that refreshes at 02:00:00 every Monday.CREATE MATERIALIZED VIEW myview2 REFRESH 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 base;
Create a materialized view named
myview3
that refreshes at 02:00:00 every day.CREATE MATERIALIZED VIEW myview3 REFRESH 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 base;
Create a materialized view named
myview4
that refreshes at 02:00:00 on the first day of every month.CREATE MATERIALIZED VIEW myview4 REFRESH NEXT DATE_FORMAT(last_day(now()) + interval 1 day, '%Y-%m-%d 02:00:00') AS SELECT count(*) as cnt FROM base;
Create a materialized view named
myview5
that refreshes only once.CREATE MATERIALIZED VIEW myview5 REFRESH START WITH now() + interval 1 day AS SELECT count(*) as cnt FROM base;
Create a materialized view named
myview6
that does not use auto-refresh.CREATE MATERIALIZED VIEW myview6 ( PRIMARY KEY (id) ) DISTRIBUTED BY HASH (id) AS SELECT id, name FROM base;
Create a materialized view named
myview7
for which specified columns are indexed. By default, all columns are indexed.CREATE MATERIALIZED VIEW myview7 ( INDEX (name), PRIMARY KEY (id) ) DISTRIBUTED BY HASH (id) AS SELECT id, name, age FROM base;
Create a materialized view named
myview8
for which a partition key and a comment are specified.CREATE MATERIALIZED VIEW myview8 ( name varchar(10), value double, KEY INDEX_ID(id) COMMENT 'id', CLUSTERED KEY INDEX(name, value), PRIMARY KEY(id) ) DISTRIBUTED BY hash(id) PARTITION BY value(date_format(dat, "%Y%m%d")) LIFECYCLE 30 COMMENT 'MATERIALIZED VIEW c' AS SELECT * FROM base;
Create an elastic materialized view named
myview9
that refreshes once every day by using the elastic resource group my_job_rg_1.CREATE MATERIALIZED VIEW myview9 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 base;
References
Configure full refresh for materialized views: describes how to specify a refresh method for a materialized view or manually refresh a materialized view.
Manage materialized views: describes how to query materialized views, auto-refresh records, and information tables, and how to delete a materialized view.
Query data from a materialized view: describes how to query data from a materialized view.