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 secondshive> 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