Help Center/ MapReduce Service/ Component Operation Guide (LTS)/ Using HetuEngine/ Configuring HetuEngine Materialized Views/ HetuEngine Materialized View Recommendation
Updated on 2024-10-09 GMT+08:00
View PDF
Share

HetuEngine Materialized View Recommendation

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 Permission Role.

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