文档首页/
MapReduce服务 MRS/
组件操作指南（LTS版）/
使用HetuEngine/
HetuEngine常见SQL语法说明/
HetuEngine DDL SQL语法说明/
SHOW MATERIALIZED VIEWS
更新时间：2024-07-02 GMT+08:00
SHOW MATERIALIZED VIEWS
语法
SHOW MATERIALIZED VIEWS [IN/FROM schema_name] [WITH TABLES LIKE pattern]
描述
列出catalogName为mv中的所有物化视图以及对应的数据表。如果希望只查看某个schema中的物化视图，可以使用子句[IN/FROM schema_name]
通过可选子句“LIKE”，筛选视图名满足规则运算表达式的视图，如果不使用这个子句，会列举所有视图。匹配的视图会按字母顺序排列。
目前规则运算表达式支持“*”或“%”用于匹配任何字符，下划线“_”用于匹配一个字符，或“|”用于条件连接两个或多个条件。
示例
- 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：物化视图的名称
- Last Refresh Time：最近一次刷新物化视图的时间
- Status：物化视图状态
- DISABLE：物化视图连续三次自动刷新失败导致的不可用状态，不可用作被改写
- ENABLE：正常状态
- REFRESHING：刷新中
- INIT: 物化视图首次被创建, 没有实体数据
- SUSPEND：挂起状态，不能改写，不能刷新
- State：物化视图有效期
- Stale：物化视图过期
- Valid：物化视图未过期，正常状态
- SyncStatus：物化视图缓存是否同步
- 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
