Using Apache HiveQL
Also available as:
PDF

Describe 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.

  1. Get summary information about the materialized view named mv1.
    DESCRIBE mv1;
    +------------+---------------+----------+
    |  col_name  |   data_type   | comment  |
    +------------+---------------+----------+
    | empid      | int           |          |
    | deptname   | varchar(256)  |          |
    | hire_date  | timestamp     |          |
    +------------+---------------+----------+
  2. 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://krishahn-hdp3-1.field.hortonworks.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) 
  3. 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 2018   ...
    | LastAccessTime:               | UNKNOWN                        ...
    | Retention:                    | 0                              ...
    | Location:                     | hdfs://mycluster-hdp3-1.field. ...
    | 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                            ...