This topic describes how to manage materialized views.

View the definition of a materialized view

Required permissions
You must have SELECT permissions on a materialized view.
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.

Delete a materialized view

Required permissions
You must have DROP permissions on a materialized view.
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 tables

information_schema.mv_info
This table contains the metadata of all materialized views.

The following table describes the common fields.

FieldDescription
MV_SCHEMAThe name of the database where the materialized view is stored.
MV_NAMEThe name of the materialized view.
MV_DEFINITIONThe DDL statements that define the materialized view.
MV_QUERYThe SQL statements that can be executed to query the materialized view.
FIRST_REFRESH_TIMEThe time of the first refresh if auto-refresh is configured.
NEXT_REFRESH_TIME_FUNCThe function that deduces the next refresh time if auto-refresh is configured.
OWNERThe 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:
# Query the number of materialized views in the cluster.
SELECT count(*) FROM information_schema.mv_info
information_schema.mv_auto_refresh_jobs
The table contains the auto-refresh records of materialized views. Only the materialized views that are configured with auto-refresh are displayed in this table. Up to 1,024 materialized views can be displayed.

The following table describes the common fields.

FieldDescription
MV_SCHEMAThe name of the database where the materialized view is stored.
MV_NAMEThe name of the materialized view.
SCHEDULED_START_TIMEThe scheduled start time of the refresh.
START_TIMEThe start time of the refresh.
END_TIMEThe end time of the refresh.
STATEThe 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:
# Query the auto-refresh records of the mymv view.
SELECT * FROM information_schema.mv_auto_refresh_jobs where mv_name = 'mymv'