Hive Table Related Commands

This tutorials will explain the multiple table related commands for Hive.

➠ Hive Table for Examples: Lets create below table for demonstrating table related commands.


CREATE TABLE hiveFirstTable
(  
   order_id INT,
   order_date STRING,
   cust_id INT,
   order_status STRING
)
ROW FORMAT delimited 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
STORED AS textfile;

➠ Detail and description of the Hive tables

Simple describe command will return only table columns and datatypes.


Syntax: describe tablename;

Example 1: desc hiveFirstTable;

Output:
OK
order_id                int                                         
order_date              string                                      
cust_id                 int                                         
order_status            string                                      
Time taken: 0.038 seconds, Fetched: 4 row(s)


Example 2: describe hiveFirstTable;

Output:
OK
order_id                int                                         
order_date              string                                      
cust_id                 int                                         
order_status            string                                      
Time taken: 0.036 seconds, Fetched: 4 row(s)

Extended describe command will return complete detail of Hive table but in a clumsy format.


Syntax: describe extended tablename;

Example 1: describe extended hiveFirstTable;

Output:
OK
order_id            	int                 	                    
order_date          	string              	                    
cust_id             	int                 	                    
order_status        	string              	                    
	 	 
Detailed Table Information	Table(tableName:hivefirsttable, dbName:default, owner:Pradeep, createTime:1542483250, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:order_id, type:int, comment:null), FieldSchema(name:order_date, type:string, comment:null), FieldSchema(name:cust_id, type:int, comment:null), FieldSchema(name:order_status, type:string, comment:null)], location:hdfs://localhost:9000/user/hive/warehouse/hivefirsttable, 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=,, line.delim=\n, field.delim=,}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}), storedAsSubDirectories:false), partitionKeys:[], parameters:{totalSize=0, numRows=0, rawDataSize=0, COLUMN_STATS_ACCURATE={\"BASIC_STATS\":\"true\",\"COLUMN_STATS\":{\"cust_id\":\"true\",\"order_date\":\"true\",\"order_id\":\"true\",\"order_status\":\"true\"}}, numFiles=0, transient_lastDdlTime=1542483250, bucketing_version=2}, viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE, rewriteEnabled:false, catName:hive, ownerType:USER)	
Time taken: 0.203 seconds, Fetched: 6 row(s)

Formatted describe command will return complete detail of Hive table in well formatted manner unlike 'Extended describe' command.


Syntax: describe formatted tablename;

Example 1: describe formatted hiveFirstTable;

Output:
OK
# col_name              data_type               comment             
order_id                int                                         
order_date              string                                      
cust_id                 int                                         
order_status            string                                      
         
# Detailed Table Information         
Database:               default                  
OwnerType:              USER                     
Owner:                  Pradeep                  
CreateTime:             Sun Nov 18 01:04:10 IST 2018     
LastAccessTime:         UNKNOWN                  
Retention:              0                        
Location:               hdfs://localhost:9000/user/hive/warehouse/hivefirsttable     
Table Type:             MANAGED_TABLE            
Table Parameters:        
    COLUMN_STATS_ACCURATE   {\"BASIC_STATS\":\"true\",\"COLUMN_STATS\":{\"cust_id\":\"true\",\"order_date\":\"true\",\"order_id\":\"true\",\"order_status\":\"true\"}}
    bucketing_version       2                   
    numFiles                0                   
    numRows                 0                   
    rawDataSize             0                   
    totalSize               0                   
    transient_lastDdlTime   1542483250          
         
# 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:         
    field.delim             ,                   
    line.delim              \n                  
    serialization.format    ,                   
Time taken: 0.061 seconds, Fetched: 36 row(s)

➠ Display column header for Queries: SELECT queries does not show column headers by default in hive console. Below property can be set to display same.


SET hive.cli.print.header=true;

➠ Adding/replacing columns in Hive Table

Adding new column/columns to the existing tables.


ALTER TABLE hiveFirstTable ADD COLUMNS (col1_name String);

Replace all the columns with new columns, this command will only work if datatypes of new and old columns will be compatible with each other.
```
ALTER TABLE hiveFirstTable REPLACE COLUMNS (name String,id Int);
```