DESCRIBE EXTENDED and DESCRIBE FORMATTED

You can get extensive formatted and unformatted information about a materialized view.

Syntax

DESCRIBE [EXTENDED | FORMATTED] [db_name.]materialized_view_name;
db_name
The database name.
materialized_view_name
The name of the materialized view.

Examples

Get summary, details, and formatted information about the materialized view in the default database and its partitions.

DESCRIBE FORMATTED default.partition_mv_1;

Example output is:

col_name data_type comment
# col_name data_type comment
name varchar(256)
NULL NULL
# Partition Information NULL NULL
# col_name data_type comment
deptno int
NULL NULL
# Detailed Table Information NULL NULL
Database: default NULL
OwnerType: USER NULL
Owner: hive NULL
CreateTime: Wed Aug 24 19:46:08 UTC 2022 NULL
LastAccessTime: UNKNOWN NULL
Retention: 0 NULL
Location: hdfs://myserver:8020/warehouse/ tablespace/managed/hive/partition_mv_1 NULL
Table Type: MATERIALIZED_VIEW NULL
Table Parameters: NULL NULL
COLUMN_STATS_ACCURATE {\"BASIC_STATS\":\"true\"}
bucketing_version 2
numFiles 2
numPartitions 2
numRows 4
rawDataSize 380
totalSize 585
transient_lastDdlTime 1534967168
NULL NULL
# Storage Information NULL NULL
SerDe Library: org.apache.hadoop.hive.ql.io.orc.OrcSerde NULL
InputFormat: org.apache.hadoop.hive.ql.io.orc.OrcInputFormat NULL
OutputFormat: org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat NULL
Compressed: No NULL
Num Buckets: -1 NULL
Bucket Columns: [] NULL
Sort Columns: [] NULL
NULL NULL
# Materialized View Information NULL NULL
Original Query: SELECT hire_date, deptno FROM emps WHERE deptno > 100 AND deptno < 200 NULL
Expanded Query: SELECT `hire_date`, `deptno` FROM (SELECT `emps`.`hire_date`, `emps`.`deptno` FROM `default`.`emps` WHERE `emps`.`deptno` > 100 AND `emps`.`deptno` < 200) `default.partition_mv_1` NULL
Rewrite Enabled: Yes NULL
Outdated for Rewriting: No NULL