Describing a materialized view
You can get summary, detailed, and formatted information about a materialized view.
This task builds on the task that creates a materialized view named
mv1
.
-
Get summary information about the materialized view named
mv1
.DESCRIBE mv1;
+------------+---------------+----------+ | col_name | data_type | comment | +------------+---------------+----------+ | empid | int | | | deptname | varchar(256) | | | hire_date | timestamp | | +------------+---------------+----------+
-
Get detailed information about the materialized view named
mv1
.DESCRIBE EXTENDED mv1;
+-----------------------------+---------------------------------... | col_name | data_type ... +-----------------------------+---------------------------------... | empid | int ... | deptname | varchar(256) ... | hire_date | timestamp ... | | NULL ... | Detailed Table Information |Table(tableName:mv1, dbName:default, owner:hive, createTime:1532466307, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:empid, type:int, comment:null), FieldSchema(name:deptname, type:varchar(256), comment:null), FieldSchema(name:hire_date, type:timestamp, comment:null)], location:hdfs://myserver.com:8020/warehouse/tablespace/managed/hive/mv1, inputFormat:org.apache.hadoop.hive.ql.io.orc.OrcInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.ql.io.orc.OrcSerde, parameters:{}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}), storedAsSubDirectories:false), partitionKeys:[], parameters:{totalSize=488, numRows=4, rawDataSize=520, COLUMN_STATS_ACCURATE={\"BASIC_STATS\":\"true\"}, numFiles=1, transient_lastDdlTime=1532466307, bucketing_version=2}, viewOriginalText:SELECT empid, deptname, hire_date\nFROM emps2 JOIN depts\nON (emps2.deptno = depts.deptno)\nWHERE hire_date >= '2017-01-17', viewExpandedText:SELECT `emps2`.`empid`, `depts`.`deptname`, `emps2`.`hire_date`\nFROM `default`.`emps2` JOIN `default`.`depts`\nON (`emps2`.`deptno` = `depts`.`deptno`)\nWHERE `emps2`.`hire_date` >= '2017-01-17', tableType:MATERIALIZED_VIEW, rewriteEnabled:true, creationMetadata:CreationMetadata(catName:hive, dbName:default, tblName:mv1, tablesUsed:[default.depts, default.emps2], validTxnList:53$default.depts:2:9223372036854775807::$default.emps2:4:9223372036854775807::, materializationTime:1532466307861), catName:hive, ownerType:USER)
-
Get formatting details about the materialized view named
mv1
.DESCRIBE FORMATTED mv1;
+-------------------------------+--------------------------------... | col_name | data_type ... +-------------------------------+--------------------------------... | # col_name | data_type ... | empid | int ... | deptname | varchar(256) ... | hire_date | timestamp ... | | NULL ... | # Detailed Table Information | NULL ... | Database: | default ... | OwnerType: | USER ... | Owner: | hive ... | CreateTime: | Tue Jul 24 21:05:07 UTC 2019 ... | LastAccessTime: | UNKNOWN ... | Retention: | 0 ... | Location: | hdfs://myserver... | Table Type: | MATERIALIZED_VIEW ... | Table Parameters: | NULL ... | | COLUMN_STATS_ACCURATE ... | | bucketing_version ... | | numFiles ... | | numRows ... | | rawDataSize ... | | totalSize ... | | transient_lastDdlTime ... | | NULL ... | # Storage Information | NULL ... | SerDe Library: | org.apache.hadoop.hive.ql.io.or... | InputFormat: | org.apache.hadoop.hive.ql.io.or... | OutputFormat: | org.apache.hadoop.hive.ql.io.or... | Compressed: | No ... | Num Buckets: | -1 ... | Bucket Columns: | [] ... | Sort Columns: | [] ... | # View Information | NULL ... | View Original Text: | SELECT empid, deptname, hire_da... | View Expanded Text: | SELECT `emps2`.`empid`, `depts`... | View Rewrite Enabled: | Yes ...