hive中查看表的信息

---

在hive中，我们可以通过命令查看表的创建语句、分区、及其他详细信息

查看表的分区情况

> show partitions new_partition;
OK
partition
daystr=201501120030
daystr=201501120100
daystr=201501120130
daystr=201501120200
daystr=201501120230
daystr=201501120300
daystr=201501120330
daystr=201501120400
daystr=201501120430
daystr=201501120530
Time taken: 0.305 seconds, Fetched: 10 row(s)

模糊查看表

> show tables like '*par*';
OK
tab_name
new_partition
old_partition
Time taken: 0.3 seconds, Fetched: 2 row(s)

查看表的创建语句

> show create table new_partition;
OK
createtab_stmt
CREATE TABLE `new_partition`(
  `shop_id` int, 
  `stat_date` string, 
  `ordamt` double)
PARTITIONED BY ( 
  `daystr` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://bigdata-training01.fuyun.com:8020/user/hive/warehouse-3.1.1/db_window.db/new_partition'
TBLPROPERTIES (
  'transient_lastDdlTime'='1532834516')
Time taken: 0.282 seconds, Fetched: 16 row(s)

查看表的结构信息(详细)

> desc formatted new_partition;
OK
col_name	data_type	comment
# col_name            	data_type           	comment             
shop_id             	int                 	                    
stat_date           	string              	                    
ordamt              	double              	                    
	 	 
# Partition Information	 	 
# col_name            	data_type           	comment             
daystr              	string              	                    
	 	 
# Detailed Table Information	 	 
Database:           	db_window           	 
OwnerType:          	USER                	 
Owner:              	rdedu               	 
CreateTime:         	Sun Jul 29 11:21:56 CST 2018	 
LastAccessTime:     	UNKNOWN             	 
Retention:          	0                   	 
Location:           	hdfs://bigdata-training01.fuyun.com:8020/user/hive/warehouse-3.1.1/db_window.db/new_partition	 
Table Type:         	MANAGED_TABLE       	 
Table Parameters:	 	 
	numFiles            	10                  
	numPartitions       	10                  
	numRows             	0                   
	rawDataSize         	0                   
	totalSize           	515                 
	transient_lastDdlTime	1532834516          
	 	 
# Storage Information	 	 
SerDe Library:      	org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe	 
InputFormat:        	org.apache.hadoop.mapred.TextInputFormat	 
OutputFormat:       	org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat	 
Compressed:         	No                  	 
Num Buckets:        	-1                  	 
Bucket Columns:     	[]                  	 
Sort Columns:       	[]                  	 
Storage Desc Params:	 	 
	serialization.format	1                   
Time taken: 0.255 seconds, Fetched: 36 row(s)

简单查看表的结构信息

> desc new_partition;
OK
col_name	data_type	comment
shop_id             	int                 	                    
stat_date           	string              	                    
ordamt              	double              	                    
daystr              	string              	                    
	 	 
# Partition Information	 	 
# col_name            	data_type           	comment             
daystr              	string              	                    
Time taken: 0.124 seconds, Fetched: 8 row(s)

查看表的扩展信息（信息更详细）

> desc extended new_partition;
OK
col_name	data_type	comment
shop_id             	int                 	                    
stat_date           	string              	                    
ordamt              	double              	                    
daystr              	string              	                    
	 	 
# Partition Information	 	 
# col_name            	data_type           	comment             
daystr              	string              	                    
	 	 
Detailed Table Information	Table(tableName:new_partition, dbName:db_window, owner:rdedu, createTime:1532834516, lastAccessTime:0, retention:0, sd:Storag
eDescriptor(cols:[FieldSchema(name:shop_id, type:int, comment:null), FieldSchema(name:stat_date, type:string, comment:null), FieldSchema(name:ordamt, type:double, comment:null), FieldSchema(name:daystr, type:string, comment:null)], location:hdfs://bigdata-training01.fuyun.com:8020/user/hive/warehouse-3.1.1/db_window.db/new_partition, inputFormat:org.apache.hadoop.mapred.TextInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, parameters:{serialization.format=1}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}), storedAsSubDirectories:false), partitionKeys:[FieldSchema(name:daystr, type:string, comment:null)], parameters:{transient_lastDdlTime=1532834516, totalSize=515, numRows=0, rawDataSize=0, numFiles=10, numPartitions=10}, viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE, rewriteEnabled:false, catName:hive, ownerType:USER)	
Time taken: 0.119 seconds, Fetched: 10 row(s)