This topic describes how to manage materialized views.
View the definition of a materialized view
Required permissions
You must have the SELECT permission on materialized views.
Syntax
SHOW CREATE MATERIALIZED VIEW <mv_name>
Description
Returns details about a DDL statement that is 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']
Description
Returns a list of materialized views on which you have the SELECT permission. If you specify a pattern, all materialized views that match the pattern are returned.
Delete a materialized view
Required permissions
You must have the DROP permission on materialized views.
Syntax
DROP MATERIALIZED VIEW <mv_name>
Description
Deletes a materialized view. You cannot execute the DROP TABLE statement to delete materialized views.
Materialized view information tables
information_schema.mv_info
This table contains 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 you can execute to query the materialized view.
FIRST_REFRESH_TIME
The time of the first refresh if auto-refresh is configured.
NEXT_REFRESH_TIME_FUNC
The function that deduces the next refresh time if auto-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 to query the information_schema.mv_info table:
# Query the total number of materialized views in the cluster. SELECT count(*) FROM information_schema.mv_info
information_schema.mv_auto_refresh_jobs
This table contains the auto-refresh records of materialized views. Only the materialized views for which auto-refresh is configured are 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. Valid values:
PENDING: The refresh is to be triggered.
RUNNING: The refresh is in progress. If multiple materialized views are refreshed at the same time, the views may be waiting for computing resources.
FINISHED: The refresh is complete.
FAILED: The refresh failed. If a refresh fails, it is retried until the allowed number of retries is reached. 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 to query the information_schema.mv_auto_refresh_jobs table:
# Query the auto-refresh records of the mymv view. SELECT * FROM information_schema.mv_auto_refresh_jobs where mv_name = 'mymv'
Modify a materialized view
Required permissions
You must have the ALTER permission on materialized views.
Syntax
ALTER MATERIALIZED VIEW <mv_name> [<ENABLE|DISABLE> QUERY REWRITE]
Description
<ENABLE|DISABLE> QUERY REWRITE: enables or disables the query rewrite feature for a materialized view.