Configuring Recommendation of Materialized Views

Scenario

HetuEngine QAS module provides automatic detection, learning, and diagnosis of historical SQL execution records. After the materialized view recommendation function is enabled, the system can automatically learn and recommend the most valuable materialized view SQL statements, enabling HetuEngine to have the automatic precomputation acceleration capability. In related scenarios, the online query efficiency is improved by multiple times, and the system load pressure is effectively reduced.

Prerequisites

• The cluster is running properly and at least one QAS instance has been installed.
• You have created a user for accessing the HetuEngine web UI, for example, Hetu_user. For details, see Creating a HetuEngine User.

Enabling Materialized View Recommendation

1. Log in to FusionInsight Manager as user Hetu_user.
2. Choose Cluster > Services > HetuEngine and then choose Configurations > All Configurations. In the navigation tree, choose QAS(Role) > Materialized View Recommendation. Set materialized view recommendation parameters by referring to Table 1 and retain the default values for other parameters.

   Table 1 Materialized view recommendation parameters

   Parameter	Example Value	Description
   qas.enable.auto.recommendation	true	Whether to enable materialized view recommendation. The default value is false.
   qas.sql.submitter	default,zuhu1	Name of the tenant for which the materialized view recommendation function is enabled. Use commas (,) to separate multiple tenants.
   qas.schedule.fixed.delay	1d	Interval for recommending materialized views. Once a day is recommended.
   qas.threshold.for.mv.recommend	0.05	Filtering threshold of materialized view recommendation. The value ranges from 0.001 to 1. You are advised to adjust the value based on the site requirements.

3. Click Save.
4. Click Instance, select all QAS instances, click More, and select Restart Instance. In the displayed dialog box, enter the password to restart all QAS instances for the parameters to take effect.

Viewing Materialized View Recommendation Results

1. Log in to FusionInsight Manager as user Hetu_user.
2. Choose Cluster > Services > HetuEngine to go its service page.
3. In the Basic Information area on the Dashboard page, click the link next to HSConsole WebUI. The HSConsole page is displayed.
4. Choose SQL O&M > Automatic MV Recommendation. You can search for materialized views by tenant, status, recommendation period, and materialized view name. Fuzzy search is supported. You can export the recommendation result of a specified materialized view.
   The status of a materialized view task can be:

   Table 2 Status of a materialized view task

   Status Name	Description	Status Name	Description
   To Be Created	To be created	Deleting	Terminating
   Creating	Creating	Deleted	Terminated
   Created	Created	Planning	Being planned
   Failed	Creation failed	Aborted	Aborted
   Updating	Updating	Duplicated	Repeated recommendation