SQL Statement Example of Materialized Views

Updated on 2024-11-29 GMT+08:00

View PDF

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 (When a materialized view is created, only the definition of the materialized view is created. To fill in data, run the refresh materialized view name command.)	Create a materialized view that never expires.	create materialized view mv.default.mv1 with(storage_table='hive.default.mv11') AS select id from hive.mvschema.t1;	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(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(storage_table='hive.default.mv1', need_auto_refresh = 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_auto_refresh: 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.
Showing materialized views	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. ENABLE: The materialized view is available. REFRESHING: The materialized view data is being refreshed and cannot be rewritten. DISABLE: The materialized view is disabled. You can only convert the status between ENABLE and SUSPEND, and chang the DISABLE state to SUSPEND or ENABLE.
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(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(storage_table='hive.default.mv11') AS SELECT id FROM t1;
```
SELECT query
```
SELECT id FROM t1;
```