Skip to main content
Version: Stable-3.1

SHOW MATERIALIZED VIEWS

Description

Shows all or one specific asynchronous materialized view.

Since v3.0, the name of this statement is changed from SHOW MATERIALIZED VIEW to SHOW MATERIALIZED VIEWS.

tip

This operation does not require privileges.

Syntax

SHOW MATERIALIZED VIEWS
[FROM db_name]
[
WHERE NAME { = "mv_name" | LIKE "mv_name_matcher"}
]

Parameters in brackets [] is optional.

Parameters

ParameterRequiredDescription
db_namenoThe name of the database to which the materialized view resides. If this parameter is not specified, the current database is used by default.
mv_namenoThe name of the materialized view to show.
mv_name_matchernoThe matcher used to filter materialized views.

Returns

ReturnDescription
idThe ID of the materialized view.
database_nameThe name of the database in which the materialized view resides.
nameThe name of the materialized view.
refresh_typeThe refresh type of the materialized view, including ROLLUP, MANUAL, ASYNC, and INCREMENTAL.
is_activeWhether the materialized view state is active. Valid Value: true and false.
partition_typeThe partition type of the materialized view, including RANGE and UNPARTITIONED.
task_idID of the materialized view refresh task.
task_nameName of the materialized view refresh task.
last_refresh_start_timeThe start time of the last refresh of the materialized view.
last_refresh_finished_timeThe end time of the last refresh of the materialized view.
last_refresh_durationThe time taken by the last refresh. Unit: seconds.
last_refresh_stateThe status of the last refresh, including PENDING, RUNNING, FAILED, and SUCCESS.
last_refresh_force_refreshWhether the last refresh is a FORCE refresh.
last_refresh_start_partitionThe start partition of the last refresh in the materialized view.
last_refresh_end_partitionThe end partition of the last refresh in the materialized view.
last_refresh_base_refresh_partitionsThe base table partitions that were refreshed in the last refresh.
last_refresh_mv_refresh_partitionsThe materialized view partitions that were refreshed in the last refresh.
last_refresh_error_codeThe error code for the last failed refresh of the materialized view (if the materialized view state is not active).
last_refresh_error_messageThe reason why the last refresh failed (if the materialized view state is not active).
rowsThe number of data rows in the materialized view.
textThe statement used to create the materialized view.

Examples

The following examples is based on this business scenario:

-- Create Table: customer
CREATE TABLE customer ( C_CUSTKEY INTEGER NOT NULL,
C_NAME VARCHAR(25) NOT NULL,
C_ADDRESS VARCHAR(40) NOT NULL,
C_NATIONKEY INTEGER NOT NULL,
C_PHONE CHAR(15) NOT NULL,
C_ACCTBAL double NOT NULL,
C_MKTSEGMENT CHAR(10) NOT NULL,
C_COMMENT VARCHAR(117) NOT NULL,
PAD char(1) NOT NULL)
ENGINE=OLAP
DUPLICATE KEY(`c_custkey`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`c_custkey`)
PROPERTIES (
"replication_num" = "3",
"storage_format" = "DEFAULT"
);

-- Create MV: customer_mv
CREATE MATERIALIZED VIEW customer_mv
DISTRIBUTED BY HASH(c_custkey)
REFRESH MANUAL
PROPERTIES (
"replication_num" = "3"
)
AS SELECT
c_custkey, c_phone, c_acctbal, count(1) as c_count, sum(c_acctbal) as c_sum
FROM
customer
GROUP BY c_custkey, c_phone, c_acctbal;

-- Refresh the MV
REFRESH MATERIALIZED VIEW customer_mv;

Example 1: Show a specific materialized view.

mysql> SHOW MATERIALIZED VIEWS WHERE NAME='customer_mv'\G
*************************** 1. row ***************************
id: 10142
name: customer_mv
database_name: test
refresh_type: MANUAL
is_active: true
last_refresh_start_time: 2023-02-17 10:27:33
last_refresh_finished_time: 2023-02-17 10:27:33
last_refresh_duration: 0
last_refresh_state: SUCCESS
inactive_code: 0
inactive_reason:
text: CREATE MATERIALIZED VIEW `customer_mv`
COMMENT "MATERIALIZED_VIEW"
DISTRIBUTED BY HASH(`c_custkey`)
REFRESH MANUAL
PROPERTIES (
"replication_num" = "3",
"storage_medium" = "HDD"
)
AS SELECT `customer`.`c_custkey`, `customer`.`c_phone`, `customer`.`c_acctbal`, count(1) AS `c_count`, sum(`customer`.`c_acctbal`) AS `c_sum`
FROM `test`.`customer`
GROUP BY `customer`.`c_custkey`, `customer`.`c_phone`, `customer`.`c_acctbal`;
rows: 0
1 row in set (0.11 sec)

Example 2: Show materialized views by matching the name.

mysql> SHOW MATERIALIZED VIEWS WHERE NAME LIKE 'customer_mv'\G
*************************** 1. row ***************************
id: 10142
name: customer_mv
database_name: test
refresh_type: MANUAL
is_active: true
last_refresh_start_time: 2023-02-17 10:27:33
last_refresh_finished_time: 2023-02-17 10:27:33
last_refresh_duration: 0
last_refresh_state: SUCCESS
inactive_code: 0
inactive_reason:
text: CREATE MATERIALIZED VIEW `customer_mv`
COMMENT "MATERIALIZED_VIEW"
DISTRIBUTED BY HASH(`c_custkey`)
REFRESH MANUAL
PROPERTIES (
"replication_num" = "3",
"storage_medium" = "HDD"
)
AS SELECT `customer`.`c_custkey`, `customer`.`c_phone`, `customer`.`c_acctbal`, count(1) AS `c_count`, sum(`customer`.`c_acctbal`) AS `c_sum`
FROM `test`.`customer`
GROUP BY `customer`.`c_custkey`, `customer`.`c_phone`, `customer`.`c_acctbal`;
rows: 0
1 row in set (0.12 sec)