HIVE: Create table

In this post, I am going to explain how to create and manage table using hive interactive shell.

 

Step 1: Open terminal and execute the command hive.

 

Step 2: Execute following command to create employee table.

create table employee(id int, name string);

hive> create table employee(id int, name string);
OK
Time taken: 1.356 seconds

Execute the command ‘show tables’ to list out all the tables.

hive> show tables;
OK
employee
Time taken: 0.111 seconds, Fetched: 1 row(s)

You can get more information about the employee table by executing the command ‘describe employee’.

hive> describe employee;
OK
id                    int                                       
name                  string                                    
Time taken: 0.113 seconds, Fetched: 2 row(s)

 show create table {table_name}

Above statement return the create statement that we used to create given table and the location where this table is located in hdfs.

hive> show create table employee;
OK
CREATE TABLE `employee`(
  `id` int, 
  `name` 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://localhost:9000/user/hive/warehouse/employee'
TBLPROPERTIES (
  'bucketing_version'='2', 
  'transient_lastDdlTime'='1610682814')
Time taken: 0.103 seconds, Fetched: 14 row(s)

As you see above snippet, employee table is located at hdfs location /user/hive/warehouse/employee. You can confirm the same by querying hdfs file system.

$hdfs dfs -ls /user/hive/warehouse/
Found 1 items
drwxr-xr-x   - krishna supergroup          0 2021-01-15 09:23 /user/hive/warehouse/employee

 

From the above output, you can confirm that when you create a table ‘employee’ using hive create command, a directory structure /user/hive/warehouse/employee is created in hdfs.

 

Get extended information about the table

Syntax

describe extended table_name

 

Example

hive> describe extended employee;
OK
id                    int                                       
name                  string                                    
     
Detailed Table Information  Table(tableName:employee, dbName:default, owner:krishna, createTime:1610682814, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:id, type:int, comment:null), FieldSchema(name:name, type:string, comment:null)], location:hdfs://localhost:9000/user/hive/warehouse/employee, 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:[], parameters:{totalSize=0, numRows=0, rawDataSize=0, COLUMN_STATS_ACCURATE={\"BASIC_STATS\":\"true\",\"COLUMN_STATS\":{\"id\":\"true\",\"name\":\"true\"}}, numFiles=0, transient_lastDdlTime=1610682814, bucketing_version=2}, viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE, rewriteEnabled:false, catName:hive, ownerType:USER)  
Time taken: 0.095 seconds, Fetched: 4 row(s)

Previous                                                    Next                                                    Home