SQL Statement Example of Materialized Views¶
For details about the SQL statements for materialized views, see Table 1.
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;
|
|
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;
|
| |
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:
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;