AnalyticDB:Manage materialized views

Required permissions

You must have the SELECT permission on the materialized view. To obtain the SELECT permission, create a privileged account. You can also create a standard account that has the GRANT permission and grant the SELECT permission to the standard account on the Accounts page or by executing the GRANT SELECT ON database.mv_name TO username; statement.

Syntax

SHOW CREATE MATERIALIZED VIEW <mv_name>

Example

Query the DDL statement that is used to create a materialized view named compl_mv1.

SHOW CREATE MATERIALIZED VIEW demo.compl_mv1;

Query the total number of materialized views in the current cluster

SELECT count(*) FROM information_schema.mv_info;

Query the metadata of all materialized views

SELECT * FROM information_schema.mv_info;

Required permissions

You must have the SELECT permission on all materialized views in the specified database. To obtain the SELECT permission, create a privileged account. You can also create a standard account that has the GRANT permission and grant the SELECT permission to the standard account on the Accounts page or by executing the GRANT SELECT ON database.* TO username; statement.

Syntax

Query all materialized views in the current database.

SHOW MATERIALIZED VIEWS [LIKE 'pattern']

Query all materialized views in a specific database. You can specify a database other than the current database.

SELECT * FROM information_schema.mv_info WHERE mv_schema='schema_name';

Examples

• Query all materialized views in the current database.

  SHOW MATERIALIZED VIEWS;

• Query all materialized views whose names start with compl in the current database.

  SHOW MATERIALIZED VIEWS LIKE 'compl%';

  For more information about the syntax of LIKE 'pattern', see the "LIKE" section of the String functions topic.

• Query all materialized views in the demo database.

  SELECT * FROM information_schema.mv_info WHERE mv_schema='demo';

Query the automatic refresh records

You can execute the following statement to query the automatic refresh records of a materialized view, including the start time, end time, status, and query ID:

SELECT * FROM information_schema.mv_auto_refresh_jobs WHERE mv_schema='<schema_name>' AND mv_name = '<mv_name>';

The information_schema.mv_auto_refresh_jobs table contains up to 1,024 automatic refresh records of materialized views. Only materialized views that are configured to automatically refresh at scheduled intervals by using the START WITH or NEXT parameter are included in the table.

The information_schema.mv_auto_refresh_jobs table does not include the first refresh record when materialized views are created.

Examples:

Query all automatic refresh records of the compl_mv1 materialized view in the demo database.

SELECT * FROM information_schema.mv_auto_refresh_jobs WHERE mv_schema = 'demo' AND mv_name = 'compl_mv1';

Query the most recent automatic refresh job of the compl_mv1 materialized view in the pending state in the demo database.

SELECT * FROM information_schema.mv_auto_refresh_jobs WHERE mv_schema = 'demo' AND mv_name = 'compl_mv1' AND state = 'PENDING';

The following table describes the common fields in the information_schema.mv_auto_refresh_jobs table.

Query the manual refresh records

• To query the manual refresh records of materialized views within the last 30 days, use the SQL audit feature. After you enter REFRESH MATERIALIZED VIEW mv_name in the Keyword field on the XIHE SQL Audit tab, you can view information about the manual refresh records, such as the time, duration, IP address, and database account.

  You must separately enable the SQL audit feature. SQL operations that are performed before the SQL audit feature is enabled are not included in the audit logs.

  

• To query the manual and automatic refresh records of materialized views within the last 14 days, use the SQL diagnostics and optimization feature. After you enter the name of a materialized view, such as compl_mv1, on the SQL Diagnostic and Optimization tab, you can view information about all SQL queries of the materialized view, such as the start time, database account, duration, and query ID.

  

Required permissions

You must have the ALTER permission on the materialized view. To obtain the ALTER permission, create a privileged account. You can also create a standard account that has the GRANT permission and grant the ALTER permission to the standard account on the Accounts page or by executing the GRANT ALTER ON database.mv_name TO username; statement.

Syntax

ALTER MATERIALIZED VIEW <mv_name>
[MV_PROPERTIES=<MV_PROPERTIES>]
[ENABLE|DISABLE QUERY REWRITE]

• MV_PROPERTIES: the properties of the materialized view, including the mv_resource_group and mv_refresh_hints parameters.

  mv_resource_group: the resource group that is used by the materialized view. If the specified resource group does not exist, an error occurs when you create the materialized view.

  mv_refresh_hints: the hint parameter of the materialized view. For more information, see the "Common hint parameters" section of the Config and hint configuration parameters topic.

  Important

  The value of the <MV_PROPERTIES> parameter specified in the ALTER statement overwrites the value of the <MV_PROPERTIES> parameter specified when you create the materialized view.

  For example, you configure the mv_resource_group and mv_refresh_hints parameters when you create a materialized view, and then configure only the mv_resource_group parameter to modify the materialized view. When you execute the ALTER statement, the mv_resource_group parameter is modified and the mv_refresh_hints parameter is deleted.

• ENABLE|DISABLE QUERY REWRITE: enables or disables the query rewrite feature.

Examples

• Enable the query rewrite feature for the compl_mv1 materialized view.

  ALTER MATERIALIZED VIEW compl_mv1 ENABLE QUERY REWRITE;

• Change the resource group that is used by the elastic materialized view job_mv.

  ALTER MATERIALIZED VIEW job_mv 
  MV_PROPERTIES='{
    "mv_resource_group":"my_job_rg_2",
    "mv_refresh_hints":{"query_priority":"NORMAL"}
  }';

• Change the standard materialized view compl_mv1 to an elastic materialized view.

  ALTER MATERIALIZED VIEW compl_mv1 
  MV_PROPERTIES='{
    "mv_resource_group":"my_job_rg_2"
  }';

Required permissions

You must have the DROP permission on the materialized view. To obtain the DROP permission, create a privileged account. You can also create a standard account that has the GRANT permission and grant the DROP permission to the standard account on the Accounts page or by executing the GRANT DROP ON database.mv_name TO username; statement.

Syntax

DROP MATERIALIZED VIEW <mv_name>

Example

Delete the compl_mv1 materialized view.

DROP MATERIALIZED VIEW compl_mv1;