How to create a table in hive

In this post, we will learn how to create a table in hive .

Creating a database in hive

In Hive, we either have to use an existing database or to create a new database before creating a table.

The following show command lists the number of available databases in the hive.

hive> show databases; 
OK 
default Time taken: 0.009 seconds, Fetched: 1 row(s)

The create database command helps us to create a new database as shown below.

hive> create database vehicle_details;
OK
Time taken: 0.208 seconds 
hive> show databases;
OK
default
vehicle_details 
Time taken: 0.011 seconds, Fetched: 2 row(s)

Creating table

The create syntax contains the following options.

if not exists(optional) – If any table exists already, it will ignore the statement.

other options – The specifications of the file to be loaded with the table. 

tblproperties – can skip the header in the file with this property.

hive> create table if not exists truck 
    > (year string , temp int , place string )
    > row format delimited
    > fields terminated by ','
    > stored as textfile
    > tblproperties ("skip.header.line.count"="1");
OK
Time taken: 0.278 seconds

hive> show tables; 
OK 
deliveries 
matches 
truck 
Time taken: 0.012 seconds, Fetched: 3 row(s)

How the file looks like

cat /tmp/temp.csv |head -6
year|temp|place
1900,45,chennai 
1901,50,bangalore 
1903,48,kolkata 
1900,26,pune 
1902,12,darjeling 

Reference

https://cwiki.apache.org/confluence/display/Hive/Tutorial#Tutorial-Creating,Showing,Altering,andDroppingTables