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);