SHOW TABLE/PARTITION EXTENDED

Syntax

SHOW TABLE EXTENDED [IN | FROM schema_name] LIKE 'identifier_with_wildcards' [PARTITION (partition_spec)]

Description

This statement is used to display details about a table or partition.

Regular expressions can be used to match multiple tables at the same time, but cannot be used to match partitions.

The displayed information includes basic table information and file system information. The file system information includes the total number of files, total file size, maximum file length, minimum file length, last access time, and last update time. For a specified partition, its file system information is provided, instead of the file system information of the table where the partition is located.

Parameters

• IN | FROM schema_name

  This parameter specifies the schema name. If this parameter is not specified, the current schema is used by default.

• LIKE 'identifier_with_wildcards'

  identifier_with_wildcards supports only rule matching expressions that contain asterisks (*) and vertical bars (|).

  The asterisk (*) can match one or more characters. The vertical bar (|) separates multiple matching rules.

  Spaces at the beginning and end of a rule matching expression are not used for matching.

• partition_spec

  This parameter is optional. It specifies the partition list using key pairs. Multiple key pairs are separated by commas. Table names do not support fuzzy match when partitions are specified.

Example

-- Data preparation
create schema show_schema;

use show_schema;

create table show_table1(a int,b string);
create table show_table2(a int,b string);
create table from_table1(a int,b string);
create table in_table1(a int,b string);

-- Query the detailed information about tables whose names start with "show".
show table extended  like 'show*';
                                 tab_name                                 
--------------------------------------------------------------------------
 tableName:show_table1                                                    
 owner:admintest                                                          
 location:hdfs://hacluster/user/hive/warehouse/show_schema.db/show_table1 
 InputFormat:org.apache.hadoop.hive.ql.io.orc.OrcInputFormat              
 OutputFormat:org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat            
 columns:struct columns {int a,string b}                                  
 partitioned:false                                                        
 partitionColumns:                                                        
 totalNumberFiles:0                                                       
 totalFileSize:0  
                                                        
 tableName:show_table2                                                    
 owner:admintest                                                          
 location:hdfs://hacluster/user/hive/warehouse/show_schema.db/show_table2 
 InputFormat:org.apache.hadoop.hive.ql.io.orc.OrcInputFormat              
 OutputFormat:org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat            
 columns:struct columns {int a,string b}                                  
 partitioned:false                                                        
 partitionColumns:                                                        
 totalNumberFiles:0                                                       
 totalFileSize:0                                                          

(1 row)

-- Query the detailed information about tables whose names start with "from" or "show".
 show table extended  like 'from*|show*';
                               tab_name                               
----------------------------------------------------------------------
 tableName           show_table1                                      
 owner               admintest                                        
 location            hdfs://hacluster/user/hive/warehouse/show_table1 
 InputFormat         org.apache.hadoop.hive.ql.io.orc.OrcInputFormat  
 OutputFormat        org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat 
 columns             struct columns {int a,string b}                  
 partitioned         false                                            
 partitionColumns                                                     
 totalNumberFiles    0                                                
 totalFileSize       null                                             

 tableName           from_table1                                      
 owner               admintest                                        
 location            hdfs://hacluster/user/hive/warehouse/from_table1 
 InputFormat         org.apache.hadoop.hive.ql.io.orc.OrcInputFormat  
 OutputFormat        org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat 
 columns             struct columns {int a,string b}                  
 partitioned         false                                            
 partitionColumns                                                     
 totalNumberFiles    0                                                
 totalFileSize       null                                             

 tableName           show_table2                                      
 owner               admintest                                        
 location            hdfs://hacluster/user/hive/warehouse/show_table2 
 InputFormat         org.apache.hadoop.hive.ql.io.orc.OrcInputFormat  
 OutputFormat        org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat 
 columns             struct columns {int a,string b}                  
 partitioned         false                                            
 partitionColumns                                                     
 totalNumberFiles    0                                                
 totalFileSize       null                                             

(1 row)
-- Query the detailed information about the page_views table in the web schema.
 show table extended from web like 'page*';
                                  tab_name                                   
-----------------------------------------------------------------------------
 tableName:page_views                                                        
 owner:admintest                                                             
 location:hdfs://hacluster/user/web.db/page_views                            
 InputFormat:org.apache.hadoop.hive.ql.io.orc.OrcInputFormat                 
 OutputFormat:org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat               
 columns:struct columns {timestamp view_time,bigint user_id,string page_url} 
 partitioned:true                                                            
 partitionColumns: struct partition_columns {date ds,string country}         
 totalNumberFiles:0                                                          
 totalFileSize:0                                                             

(1 row)