SQL Statement Example of Materialized Views¶

For details about the SQL statements for materialized views, see Table 1.

**Table 1** Operations on materialized views¶
Operation	Function	SQL Statement Example of Materialized View	Remarks
Creating a materialized view	Creat a materialized view that never expires.	create materialized view mv.default.mv1 with(mv_storage_table='hive.default.mv11') AS select id from hive.mvschema.t1;	mv_storage_table specifies the location where the materialized view data is materialized into a physical table. When creating a materialized view, you must specify mv for the catalog. You can also create a schema. For the AS SELECT clause, pay attention to the items listed in Creating the AS SELECT Clause for a Materialized View.
	Create a materialized view that is valid for one day and cannot automatically refresh.	create materialized view mv.default.mv1 with(mv_storage_table='hive.default.mv11', mv_validity = '24h') AS select id from hive.mvschema.t1;	mv_validity specifies the validity of a materialized view.
	Create a materialized view that automatically refreshes data every hour.	create materialized view mv.default.mv1 with(mv_storage_table='hive.default.mv1', need_autorefresh = true, mv_validity = '1h', start_refresh_ahead_of_expiry = 0.2, refresh_priority = 3, refresh_duration = '5m') AS select id from hive.mvschema.t1;	need_autorefresh: indicates whether to enable automatic refresh. start_refresh_ahead_of_expiry: a refresh task is triggered for the materialized view at the time specified by mv_validity* (1-start_refresh_ahead_of_expiry) so that the task status is changed to Refreshable. refresh_priority specifies the priority of refreshing tasks. refresh_duration specifies the maximum duration of a refreshing task.
Showing materialized views	Show all MVs whose catalog name is mv and schema name is mvschema.	show materialized views from mvschema;	mvschema indicates the schema name. The value of catalog is fixed to mv.
	Use the LIKE clause to filter the materialized views whose names meet the rule expression.	show MATERIALIZED VIEWs in mvschema tables like '*mvtb_0001';	mvschema indicates the schema name.
Querying the statement for creating a materialized view	Query the statement for creating the the materialized view of mv.default.mv1.	show create materialized view mv.default.mv1;	mv1 indicates the name of the materialized view.
Querying a materialized view	Query data in mv.default.mv1.	select * from mv.default.mv1;	mv1 indicates the name of the materialized view.
Refreshing a materialized view	Refresh the materialized view of mv.default.mv1.	refresh materialized view mv.default.mv1;	`-`
Modifying the properties of materialized views	Modifying the properties of the mv.default.mv1 materialized view	Alter materialized view mv.mvtestprop.pepa_ss set PROPERTIES(refresh_priority = 2);	refresh_priority = 2 is the property of the materialized view.
Changing the status of materialized views	Changing the status of the mv.default.mv1 materialized view	alter materialized view mv.default.mv1 set status SUSPEND;	SUSPEND is the status of the materialized view. The status can be: SUSPEND: The materialized view is suspended. The suspended materialized view is not rewritten. ENABLED: The materialized view is available. Refreshing: The materialized view data is being refreshed and cannot be rewritten. DISABLED: The materialized view has been disabled. UNKNOWN: The cache is inconsistent with the database. You are advised to run the refresh catalog mv command. Manual refresh supports only the conversion between ENABLED and SUSPEND.
Deleting a materialized view	Delete the materialized view of mv.default.mv1.	drop materialized view mv.default.mv1;	`-`
Enabling materialized view rewriting capability to optimize SQL statements	Enabling materialized view rewriting capability at the session level to optimize SQL statements	set session materialized_view_rewrite_enabled=true;	`-`
Verifying whether SQL statements can be optimized by rewriting a query to a materialized view	Verify whether the SELECT statement can be rewritten and optimized by mv.default.mv1.	verify materialized view mvname(mv.default.mv1) originalsql select id from hive.mvschema.t1;	`-`
Enabling the specified materialized view at the SQL level to optimize the SQL statements	Forcibly use mv.default.mv1 for SQL statement optimization in queries.	/+ REWRITE(mv.default.mv1) / select id from hive.mvschema.t1;	`-`
Disabling materialized views at the SQL level to optimize the SQL statements	Do not use materialized views for SQL statement optimization in queries.	/+ NOREWRITE / select id from hive.mvschema.t1;	`-`
Refreshing the metadata cache of materialized views	Synchronize the metadata cache of materialized views between tenants.	refresh catalog mv;	`-`

Creating the AS SELECT Clause for a Materialized View¶

The AS SELECT clause for creating materialized views cannot contain reserved keywords in Calcite SQL parsing and rewriting functions, such as default. To use reserved keywords in the AS SELECT clause, use either of the following solutions:

When creating MVs and executing original queries, you need to add double quotes to the default schema name.
The following uses reserved keyword default in the AS SELECT clause as an example:
Creating a materialized view
```
CREATE MATERIALIZED VIEW mv.default.mv1 WITH(mv_storage_table='hive.default.mv11') AS SELECT id FROM hive."default".t1;
```
SELECT query
```
SELECT id FROM hive."default".t1;
```
Set the corresponding catalog and schema at the Session level, rather than passing fully qualified names in the query.
For example, set catalogname to hive and schemaname to default.
```
USE hive.default;
```
Creating a materialized view
```
CREATE MATERIALIZED VIEW mv.default.mv1 WITH(mv_storage_table='hive.default.mv11') AS SELECT id FROM t1;
```
SELECT query
```
SELECT id FROM t1;
```

last updated: 2024-11-14 12:28 UTC - commit: 9d95be538b931742820cff066f12ce18f037ed96