Manage materialized views

Permissions

You must have the SELECT 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 you the permission on the account management page in the console or by running the following SQL statement: GRANT SELECT ON database.mv_name TO username;

Syntax

SHOW CREATE MATERIALIZED VIEW <mv_name>

Example

Query the DDL statement for the materialized view compl_mv1.

SHOW CREATE MATERIALIZED VIEW demo.compl_mv1;

Fields

The information_schema.mv_info view contains metadata for all materialized views. If you only need to view the definition of a single materialized view, use the SHOW CREATE MATERIALIZED VIEW statement.

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

Examples

• Count the materialized views in the cluster.

  SELECT count(*) FROM information_schema.mv_info;

• Query the metadata for all materialized views.

  SELECT * FROM information_schema.mv_info;

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 must grant you the permission on the account management page in the console or by running the following 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 (which can be different from the current one):

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 in the current database whose names start with compl.

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

Automatic refresh records

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

Manual refresh records

• You can use the SQL Audit feature to query manual refresh records for a materialized view from the last 30 days. When querying, enter the keyword REFRESH MATERIALIZED VIEW mv_name to find information such as the time, duration, IP address, and username for manual refreshes.

  Note

  The SQL Audit feature must be enabled separately. SQL operations performed before this feature was enabled are not recorded.

• You can use the SQL Diagnostics and Optimization feature to query both manual and automatic refresh records from the last 14 days. When querying, enter the name of the materialized view, such as compl_mv1. You can retrieve information about all related SQL queries (including creation, manual refresh, automatic refresh, and alterations), such as the start time, username, duration, and query ID.

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 you the permission on the account management page in the console or by running the following 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>]
[BINLOG={TRUE|FALSE}]

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

  mv_resource_group: Specifies the resource group that the materialized view uses. If the specified resource group does not exist, an error is returned when creating the materialized view.

  mv_refresh_hints: Sets parameters for the materialized view. For details on supported configuration parameters, see Common Hints.

  Important

  The <MV_PROPERTIES> properties specified in an ALTER statement completely overwrite the <MV_PROPERTIES> properties specified when the materialized view was created.

  For example, if you create a materialized view and configure both the mv_resource_group and mv_refresh_hints parameters, and then run an ALTER statement that specifies only the mv_resource_group parameter, the mv_resource_group parameter is updated, and the mv_refresh_hints parameter is cleared.

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

• RENAME <new_mv_name>: Renames the materialized view.

• BINLOG={TRUE|FALSE}: Enables or disables the binlog feature for the materialized view. This is supported in cluster kernel versions 3.2.5.0 and later.

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"}
  }';

• Convert the standard materialized view compl_mv1 into 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;

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 you the permission on the account management page in the console or by running the following 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;