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

Parameter	Required	Description
db_name	no	The name of the database to which the materialized view resides. If this parameter is not specified, the current database is used by default.
mv_name	no	The name of the materialized view to show.
mv_name_matcher	no	The matcher used to filter materialized views.

Returns

Return	Description
id	The ID of the materialized view.
database_name	The name of the database in which the materialized view resides.
name	The name of the materialized view.
refresh_type	The refresh type of the materialized view, including ROLLUP, MANUAL, ASYNC, and INCREMENTAL.
is_active	Whether the materialized view state is active. Valid Value: true and false.
partition_type	The partition type of the materialized view, including RANGE and UNPARTITIONED.
task_id	ID of the materialized view refresh task.
task_name	Name of the materialized view refresh task.
last_refresh_start_time	The start time of the last refresh of the materialized view.
last_refresh_finished_time	The end time of the last refresh of the materialized view.
last_refresh_duration	The time taken by the last refresh. Unit: seconds.
last_refresh_state	The status of the last refresh, including PENDING, RUNNING, FAILED, and SUCCESS.
last_refresh_force_refresh	Whether the last refresh is a FORCE refresh.
last_refresh_start_partition	The start partition of the last refresh in the materialized view.
last_refresh_end_partition	The end partition of the last refresh in the materialized view.
last_refresh_base_refresh_partitions	The base table partitions that were refreshed in the last refresh.
last_refresh_mv_refresh_partitions	The materialized view partitions that were refreshed in the last refresh.
last_refresh_error_code	The error code for the last failed refresh of the materialized view (if the materialized view state is not active).
last_refresh_error_message	The reason why the last refresh failed (if the materialized view state is not active).
rows	The number of data rows in the materialized view.
text	The 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)