Overview of HetuEngine Materialized Views

The materialized views of the HetuEngine are available for MRS 3.2.0 and later versions.

HetuEngine Materialized Views Background

HetuEngine provides the materialized view capability. It enables you to pre-compute frequently accessed and time-consuming operators (such as join and aggregation operators) through materialized views. In this way, queries or subqueries that can match the materialized views are converted into corresponding materialized views, avoiding repeated data computing and improving the query response efficiency.

A materialized view is typically created based on the results of queries that aggregate and join multiple data tables.

Materialized views support query rewrite. It is an optimization technique that converts query statements compiled based on an original table into equivalent requests for querying one or more materialized view statements. The following is an example of the SQL statement of a materialized view:

create materialized view mv.default.mv1 with(storage_table='hive.default.mv1') AS select id from hive.mvschema.t1;

The actual data of the materialized view is stored in the hive.default.mv1 table. During query rewriting, the SQL statement select id from hive.mvschema.t1 is rewritten as the table for querying the materialized view, that is, select id from hive.default.mv1.

HetuEngine Materialized Views Scenario

Compared with common views, materialized views occupy storage resources and cause data delay because of actual data storage and pre-computation. Therefore, materialized views are recommended in the following scenarios:

Frequently executed queries are required.
Queries involve time-consuming operations like aggregation and join operations.
A certain delay is allowed for the query result data.
Materialized views can only be connected to co-deployed Hive and external Hive data sources. Data source tables are stored in ORC or PARQUET format. Cross-source and cross-domain scenarios are not supported.

HetuEngine Materialized Views Permission Introduction

Table 1 lists materialized view permissions. Permission control for materialized views depends on the Ranger. If Ranger authentication is disabled, permissions may become invalid.

**Table 1** HetuEngine materialized view permissions
Operation	Permission on catalog mv	Permission on Tables Stored in MVs	Permission on Original Physical Table
Creating a materialized view	Permission to create tables	NA	Column query permission
Deleting a materialized view	Permission to delete tables	N/A	N/A
Refreshing a materialized view	Permission to update tables	N/A	Column query permission
Modifying the properties or state of a materialized view	Permission to alter tables	NA	NA
Overwriting query statements using materialized views	N/A	N/A	Column query permission
Using materialized views to rewrite the execution plan of query statements (EXPLAIN)	N/A	Column query permission	Column query permission
Querying a materialized view	Column query permission	N/A	N/A
Querying physical tables of materialized and non-materialized views	Column query permission	N/A	Column query permission
Viewing a materialized view	N/A	N/A	N/A
Viewing the statement for creating a materialized view	Permission to show tables	Permission to show tables	N/A

How to Use HetuEngine Materialized Views

**Table 2** Introduction to materialized views
Phase	Description	Reference
SQL statement example of materialized views	This section describes the operations supported by materialized views, including creating, listing, and querying materialized views.	SQL Examples of HetuEngine Materialized Views
Configuring rewriting of materialized views	Enables the materialized view capability for faster query response.	Rewriting of HetuEngine Materialized Views
Configuring recommendation of materialized views	Automatically learns and recommends materialized view SQL statements that are most valuable to services, improving online query efficiency and reducing system load pressure.	HetuEngine Materialized View Recommendation
Configuring caching of materialized views	The SQL statements that have been executed and rewritten for multiple times can be saved to the cache. When the SQL statements are executed again, the rewritten SQL statements are directly obtained from the cache instead of rewriting the SQL statements, improving query efficiency.	HetuEngine Materialized View Caching
Configuring the validity period and data update of materialized views	Configures the validity period of the materialized view. Currently, only the materialized view within the validity period is automatically overwritten. Configures periodic data update. Materialized views can be refreshed manually or automatically.	Validity Period and Data Update of HetuEngine Materialized Views
Configuring intelligent materialized views	Provides automatic creation of materialized views. You do not need to manually execute SQL statements to create materialized views (recommended).	HetuEngine Intelligent Materialized Views
Viewing automatic tasks of materialized views	Views the task execution status to evaluate the cluster health status.	Automatic Tasks of HetuEngine Materialized Views