AnalyticDB:Manage materialized views

Required permissions

You must have the SELECT permission on the materialized view. To obtain this permission, a privileged account or a standard account with the GRANT permission can grant the SELECT permission to your account on the Account Management page in the console 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 was used to create the materialized view compl_mv1.

SHOW CREATE MATERIALIZED VIEW demo.compl_mv1;

Field descriptions

The `information_schema.mv_info` table contains the metadata of all materialized views. If you want to query the definition of a single materialized view, we recommend that you execute the SHOW CREATE MATERIALIZED VIEW statement.

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

Examples

• Count all materialized views in the cluster.

  SELECT count(*) FROM information_schema.mv_info;

• Query metadata for 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. If you do not have this permission, a privileged account or a standard account with the GRANT permission can grant it to your account on the Accounts page in the console or using the SQL statement GRANT SELECT ON database.* TO username;.

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%';

  Note

  For more information about how to use `LIKE 'pattern'`, see String functions.

• Query all materialized views in the demo database.

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

Query the automatic refresh records

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

Query the manual refresh records

• To query manual refresh records from the past 30 days, use the SQL Audit feature. Enter REFRESH MATERIALIZED VIEW mv_name in the Keyword field on the SQL Audit tab. You can then view the time, duration, IP address, and username for each manual refresh.

  Note

  You must enable the SQL Audit feature. SQL operations that occurred before you enabled this feature are not recorded in audit logs.

  

• To query manual and automatic refresh records from the past 14 days, use the SQL diagnostics and optimization feature. Enter the name of a materialized view, such as compl_mv1, on the SQL Diagnostics and Optimization tab. You can then view the start time, username, duration, and query ID for all related SQL queries, including creation, manual refresh, automatic refresh, and modification.

  

Required permissions

You must have the ALTER permission on the materialized view. If you do not have this permission, a privileged account or a standard account with the GRANT permission must grant the ALTER permission to your account. This can be done on the Account Management page in the console or by executing the SQL statement GRANT ALTER ON database.mv_name TO username;.

Syntax

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

• 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 created the materialized view.

  For example, assume you configure the mv_resource_group and mv_refresh_hints parameters when you create a materialized view. If you then configure only the mv_resource_group parameter in the `ALTER` statement, the mv_resource_group parameter is modified and the mv_refresh_hints parameter is purged after you execute the statement.

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

• RENAME <new_mv_name>: Renames the materialized view.

Examples

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

  ALTER MATERIALIZED VIEW compl_mv1 ENABLE QUERY REWRITE;

• Change the resource group 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"
  }';

• Rename the materialized view `mv1` to `mv2`.

  ALTER MATERIALIZED VIEW mv1 RENAME mv2;

Required permissions

You must have the DROP permission on the materialized view. If you do not have this permission, a privileged account or a standard account with the GRANT permission must grant the DROP permission to your account. This can be done on the Accounts page in the console or by executing the SQL statement GRANT DROP ON database.mv_name TO username;.

Syntax

DROP MATERIALIZED VIEW <mv_name>

Example

Delete the materialized view compl_mv1.

DROP MATERIALIZED VIEW compl_mv1;