Help Center>
MapReduce Service>
Component Operation Guide (LTS)>
Using HetuEngine>
HetuEngine SQL Syntax>
SQL Syntax>
DDL Syntax>
SHOW MATERIALIZED VIEWS
Updated on 2024-05-29 GMT+08:00
SHOW MATERIALIZED VIEWS
Syntax
SHOW MATERIALIZED VIEWS [IN/FROM schema_name] [WITH TABLES LIKE pattern]
Description
This statement is used to list all materialized views whose catalogName is mv and corresponding data tables. If you want to view only the MVs in a schema, use the [IN/FROM schema_name] clause.
The LIKE clause is used to filter views whose names meet the regular expression. If this clause is not used, all views are listed. The matched views are sorted in alphabetic order.
Currently, the regular expression supports the following matching rules: asterisk (*) or percent sign ( %) for matching any character, underscore (_) for matching one character, and vertical bar (|) for connecting two or more conditions.
Example
- SHOW MATERIALIZED VIEWS;
hetuengine:tpcds_2gb> SHOW MATERIALIZED VIEWS; Materialized Views | Last Refresh Time | Status | State | SyncStatus -----------------------------------------------------------|------------------------------|--------|-------|------------ mv.auto_created_mv.auto_created_mv_20220906_201815_mv_1 | Wed Sep 07 15:37:39 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_103 | Wed Sep 07 15:31:41 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_15 | Wed Sep 07 14:47:09 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_16 | Wed Sep 07 15:39:09 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_18 | Wed Sep 07 14:46:42 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_23 | Wed Sep 07 15:39:49 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_24 | Wed Sep 07 14:48:19 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_28 | Wed Sep 07 15:38:38 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_29 | Wed Sep 07 15:38:19 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_30 | Wed Sep 07 15:38:04 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_31 | Wed Sep 07 15:39:28 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_33 | Wed Sep 07 15:39:39 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_34 | Wed Sep 07 14:47:38 CST 2022 | ENABLE | Stale | true
- Materialized Views: name of a materialized view
- Last Refresh Time: last time when the materialized view is refreshed
- Status: materialized view status
- DISABLE: The materialized view is unavailable because it fails to be automatically refreshed for three consecutive times. The materialized view cannot be rewritten.
- ENABLE: normal state
- REFRESHING: refreshing
- INIT: The materialized view is created for the first time and has no entity data.
- SUSPEND: suspended state, which cannot be rewritten or refreshed
- State: validity period of the materialized view
- Stale: The materialized view expires.
- Valid: The materialized view does not expire and is in the normal state.
- SyncStatus: whether the materialized view cache is synchronized.
- SHOW MATERIALIZED VIEWS FROM tpcds;
hetuengine:tpcds_2gb> SHOW MATERIALIZED VIEWS FROM auto_created_mv; Materialized Views | Last Refresh Time | Status | State | SyncStatus -----------------------------------------------------------|------------------------------|--------|-------|------------ mv.auto_created_mv.auto_created_mv_20220906_201815_mv_1 | Wed Sep 07 15:37:39 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_103 | Wed Sep 07 15:31:41 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_15 | Wed Sep 07 14:47:09 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_16 | Wed Sep 07 15:39:09 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_18 | Wed Sep 07 14:46:42 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_23 | Wed Sep 07 15:39:49 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_24 | Wed Sep 07 14:48:19 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_28 | Wed Sep 07 15:38:38 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_29 | Wed Sep 07 15:38:19 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_30 | Wed Sep 07 15:38:04 CST 2022 | ENABLE | Stale | true - SHOW MATERIALIZED VIEWS WITH TABLES LIKE 'hive.tpcds_bin_partitioned_orc_2.call_center';
hetuengine:tpcds_2gb> SHOW MATERIALIZED VIEWS WITH TABLES LIKE 'hive.tpcds_bin_partitioned_orc_2.call_center'; Materialized Views | Tables | Last Refresh Time | Status | State | SyncStatus ----------------------------------------------------------|----------------------------------------------|------------------------------|--------|-------|------------ mv.auto_created_mv.auto_created_mv_20220907_142132_mv_65 | hive.tpcds_bin_partitioned_orc_2.call_center | Wed Sep 07 15:28:20 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220907_152143_mv_19 | hive.tpcds_bin_partitioned_orc_2.call_center | Wed Sep 07 15:37:07 CST 2022 | ENABLE | Stale | true - SHOW MATERIALIZED VIEWS WITH TABLES LIKE '_ive.tpcds_bin_partitioned_orc_2.call_center';
hetuengine:tpcds_2gb> SHOW MATERIALIZED VIEWS WITH TABLES LIKE '_ive.tpcds_bin_partitioned_orc_2.call_center'; Materialized Views | Tables | Last Refresh Time | Status | State | SyncStatus ----------------------------------------------------------|----------------------------------------------|------------------------------|--------|-------|------------ mv.auto_created_mv.auto_created_mv_20220907_142132_mv_65 | hive.tpcds_bin_partitioned_orc_2.call_center | Wed Sep 07 15:28:20 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220907_152143_mv_19 | hive.tpcds_bin_partitioned_orc_2.call_center | Wed Sep 07 15:37:07 CST 2022 | ENABLE | Stale | true - SHOW MATERIALIZED VIEWS TABLES LIKE '*.call_center';
hetuengine:tpcds_2gb> SHOW MATERIALIZED VIEWS WITH TABLES LIKE '*.call_center'; Materialized Views | Tables | Last Refresh Time | Status | State | SyncStatus ----------------------------------------------------------|----------------------------------------------|------------------------------|--------|-------|------------ mv.auto_created_mv.auto_created_mv_20220907_142132_mv_65 | hive.tpcds_bin_partitioned_orc_2.call_center | Wed Sep 07 15:28:20 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220907_152143_mv_19 | hive.tpcds_bin_partitioned_orc_2.call_center | Wed Sep 07 15:37:07 CST 2022 | ENABLE | Stale | true - SHOW MATERIALIZED VIEWS WITH TABLES LIKE '*.call_center|*.date_dim';
hetuengine:tpcds_2gb> SHOW MATERIALIZED VIEWS WITH TABLES LIKE '*.call_center|*.date_dim'; Materialized Views | Tables | Last Refresh Time | Status | State | SyncStatus ----------------------------------------------------------|----------------------------------------------|------------------------------|--------|-------|------------ mv.auto_created_mv.auto_created_mv_20220907_142132_mv_65 | hive.tpcds_bin_partitioned_orc_2.call_center | Wed Sep 07 15:28:20 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220907_152143_mv_19 | hive.tpcds_bin_partitioned_orc_2.call_center | Wed Sep 07 15:37:07 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_1 | hive.tpcds_bin_partitioned_orc_2.date_dim | Wed Sep 07 15:37:39 CST 2022 | ENABLE | Stale | true mv.auto_created_mv.auto_created_mv_20220906_201815_mv_103| hive.tpcds_bin_partitioned_orc_2.date_dim | Wed Sep 07 15:31:41 CST 2022 | ENABLE | Stale | true
Parent topic: DDL Syntax
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
The system is busy. Please try again later.
For any further questions, feel free to contact us through the chatbot.
Chatbot
