SHOW MATERIALIZED VIEWS

NOTE

This feature is included in the Premium software edition. Please see the Software editions documentation for details on the differences between Standard and Premium editions if you are subscribed to the Standard edition.

Description

Shows all or one specific asynchronous materialized view.

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" = "1",
"storage_format" = "DEFAULT"
);

-- Create MV: customer_mv
CREATE MATERIALIZED VIEW customer_mv
DISTRIBUTED BY HASH(c_custkey)
REFRESH MANUAL
PROPERTIES (
    "replication_num" = "1"
)
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" = "1",
"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" = "1",
"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)
In this article
Copyright © 2024 CelerData