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;
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)
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)
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)
SET hive.cli.print.header=true;
ALTER TABLE hiveFirstTable ADD COLUMNS (col1_name String);
ALTER TABLE hiveFirstTable REPLACE COLUMNS (name String,id Int);