AnalyticDB:Manage materialized views

Prerequisites

Before you begin, ensure that you have:

• The SELECT permission on the materialized view

To grant it, a privileged account or a standard account with the GRANT permission can run the following statement or use the Account Management page in the console:

GRANT SELECT ON <database>.<mv_name> TO <username>;

Syntax

SHOW CREATE MATERIALIZED VIEW <mv_name>

Example

-- Retrieve the DDL for compl_mv1
SHOW CREATE MATERIALIZED VIEW demo.compl_mv1;

Fields

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;

Prerequisites

Before you begin, ensure that you have:

• The SELECT permission on the materialized views you want to list

To grant it, a privileged account or a standard account with the GRANT permission can run the following statement or use the Accounts page in the console:

GRANT SELECT ON <database>.* TO <username>;

Syntax

-- List all materialized views in the current database
SHOW MATERIALIZED VIEWS [LIKE 'pattern']

-- List all materialized views in a specific database
SELECT * FROM information_schema.mv_info WHERE mv_schema = '<schema_name>';

Examples

-- List all materialized views in the current database
SHOW MATERIALIZED VIEWS;

-- List materialized views whose names start with "compl"
SHOW MATERIALIZED VIEWS LIKE 'compl%';

-- List all materialized views in the demo database
SELECT * FROM information_schema.mv_info WHERE mv_schema = 'demo';

For LIKE pattern syntax, see String functions.

Automatic refresh records

The information_schema.mv_auto_refresh_jobs table records automatic refresh jobs for materialized views configured with the START WITH or NEXT parameter.

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

-- Query all automatic refresh records for compl_mv1 in the demo database
SELECT * FROM information_schema.mv_auto_refresh_jobs
WHERE mv_schema = 'demo' AND mv_name = 'compl_mv1';

-- Query only pending jobs for compl_mv1 in the demo database
SELECT * FROM information_schema.mv_auto_refresh_jobs
WHERE mv_schema = 'demo' AND mv_name = 'compl_mv1' AND state = 'PENDING';

Manual refresh records

Use one of the following tools to look up manual refresh activity:

• SQL Audit (past 30 days): On the SQL Audit tab, enter REFRESH MATERIALIZED VIEW <mv_name> in the Keyword field. The results show the time, duration, IP address, and username for each manual refresh.

  SQL Audit must be enabled before use. Operations that occurred before you enabled it are not recorded.

  

• SQL diagnostics and optimization (past 14 days): On the SQL Diagnostics and Optimization tab, enter the view name (for example, compl_mv1). The results include all related SQL activity—creation, manual refresh, automatic refresh, and modification—with start time, username, duration, and query ID.

  

Prerequisites

Before you begin, ensure that you have:

• The ALTER permission on the materialized view

To grant it, a privileged account or a standard account with the GRANT permission can run the following statement or use the Account Management page in the console:

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>]

Parameters:

Examples

-- Enable query rewrite
ALTER MATERIALIZED VIEW compl_mv1 ENABLE QUERY REWRITE;

-- Change the resource group for an elastic materialized view
ALTER MATERIALIZED VIEW job_mv
  MV_PROPERTIES = '{
    "mv_resource_group": "my_job_rg_2",
    "mv_refresh_hints": {"query_priority": "NORMAL"}
  }';

-- Convert a standard materialized view to an elastic materialized view
ALTER MATERIALIZED VIEW compl_mv1
  MV_PROPERTIES = '{
    "mv_resource_group": "my_job_rg_2"
  }';

-- Rename a materialized view
ALTER MATERIALIZED VIEW mv1 RENAME mv2;

Prerequisites

Before you begin, ensure that you have:

• The DROP permission on the materialized view

To grant it, a privileged account or a standard account with the GRANT permission can run the following statement or use the Accounts page in the console:

GRANT DROP ON <database>.<mv_name> TO <username>;

Syntax

DROP MATERIALIZED VIEW <mv_name>

Example

-- Delete compl_mv1
DROP MATERIALIZED VIEW compl_mv1;