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 ('%'
).
Syntax
CREATE [OR REPLACE] MATERIALIZED VIEW <mv_name>
[MV DEFINITION]
[REFRESH [COMPLETE|FAST] [ON [DEMAND |OVERWRITE] [START WITH date] [NEXT date]]]
[QUERY REWRITE]
AS
<QUERY BODY>;
Parameters
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 full refresh method. If you do not specify a refresh method when you create a materialized view, full refresh is used. |
| The incremental refresh method. Note For AnalyticDB for MySQL clusters of V3.1.9.0 or later, you can perform incremental refresh on materialized views that are defined based on a single table. For AnalyticDB for MySQL clusters of V3.2.0.0 or later, you can perform incremental refresh on materialized views that are defined based on multiple tables. For more information, see Configure incremental refresh for materialized views (preview).
|
| The on-demand trigger mode of refreshes. To configure the next refresh on demand, you can manually trigger a refresh or specify the By default, a full refresh is triggered in |
| The on-overwrite trigger mode of refreshes. When the base table that is involved in a materialized view is overwritten by executing the If you configure the |
| 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 more information about how to refresh a materialized view, see Refresh a materialized view. |
| 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 Note Time functions are supported and must be accurate to the second. Milliseconds are truncated. For more information about how to refresh a materialized view, see Refresh a materialized view. |
| 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
|
| The query body of the materialized view, which can be a table, logical view, or materialized view. Take note of the following items:
|
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.
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;
References
Refresh a materialized view: 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.