This topic describes how to manage materialized views.

View the definition of materialized views

Required permissions
You must have SELECT permissions on materialized views.
Syntax
SHOW CREATE MATERIALIZED VIEW <mv_name>
Feature description
Returns details about a DDL statement used to create a materialized view.

Query materialized views

Required permissions
You must have SELECT permissions on materialized views.
Syntax
SHOW MATERIALIZED VIEWS [LIKE 'pattern']
Feature description
Returns a list of materialized views on which you have SELECT permissions. If a pattern is specified, all materialized views that match the pattern are returned.

Modify materialized views

Required permissions
You must have ALTER permissions on materialized views.
Syntax
ALTER MATERIALIZED VIEW <mv_name>
[<ENABLE|DISABLE> QUERY REWRITE]
Feature description
<ENABLE|DISABLE> QUERY REWRITE: enables or disables the smart rewrite feature for queries.
Note You cannot modify other properties.

Delete materialized views

Required permissions
You must have DROP permissions on materialized views.
Syntax
DROP MATERIALIZED VIEW <mv_name>
Feature description
Deletes a materialized view. You cannot execute the DROP TABLE statement to delete materialized views.

Materialized view information table

information_schema.mv_info
The metadata of all materialized views.

The following table describes the common fields.

Field Description
MV_SCHEMA The name of the database where the materialized view is stored.
MV_NAME The name of the materialized view.
MV_DEFINITION The DDL statements that define the materialized view.
MV_QUERY The SQL statements that can be executed to query the materialized view.
FIRST_REFRESH_TIME The time of the first refresh if automatic refresh is configured.
NEXT_REFRESH_TIME_FUNC The function that deduces the next refresh time if automatic refresh is configured.
OWNER The creator of the materialized view.
If you want to view the definition of a single materialized view, we recommend that you execute the SHOW CREATE MATERIALIZED VIEW statement.
The following example shows how this statement is used:
# Count the number of materialized views in the cluster.
SELECT count(*) FROM information_schema.mv_info
information_schema.mv_auto_refresh_jobs
Records of automatic refresh tasks. Only materialized views configured with automatic refresh is displayed in this table. Up to 1,024 materialized views can be displayed.

The following table describes the common fields.

Field Description
MV_SCHEMA The name of the database where the materialized view is stored.
MV_NAME The name of the materialized view.
SCHEDULED_START_TIME The scheduled start time of the refresh.
START_TIME The start time of the refresh.
END_TIME The end time of the refresh.
STATE The status of the refresh.
  • PENDING: The refresh is to be triggered.
  • RUNNING: The refresh is in progress. If multiple materialized views are refreshed at the same time, they may be waiting for computing resources.
  • FINISHED: The refresh is complete.
  • FAILED: The refresh has failed. If a refresh fails, it is retried until the allowed number of retries is exceeded. If the refresh still fails, the refresh task is canceled.
  • TIMEOUT: The refresh times out and is in an unknown state.
The following example shows how this statement is used:
# View the automatic refresh records of the mymv view.
SELECT * FROM information_schema.mv_auto_refresh_jobs where mv_name = 'mymv'