Skip to content
Related Articles

Related Articles

Improve Article

How to Create Table in Hive?

  • Difficulty Level : Easy
  • Last Updated : 04 Nov, 2020
Geek Week

In Apache Hive we can create tables to store structured data so that later on we can process it. The table in the hive is consists of multiple columns and records. The table we create in any database will be stored in the sub-directory of that database. The default location where the database is stored on HDFS is /user/hive/warehouse. The way of creating tables in the hive is very much similar to the way we create tables in SQL. We can perform the various operations with these tables like Joins, Filtering, etc. 

To perform the below operation make sure your hive is running. Below are the steps to launch a hive on your local system.

Step 1: Start all your Hadoop Daemon

start-dfs.sh                    # this will start namenode, datanode and secondary namenode

start-yarn.sh                   # this will start node manager and resource manager  

jps                             # To check running daemons

Step 2: Launch hive from terminal



hive

Creating Table in Hive

Let’s create a database first so that we can create tables inside it. The command for creating a database is shown below.

Syntax To Make Database:

CREATE DATABASE <database-name>;

Command:

CREATE DATABASE student_detail;   # this will create database student_detail

SHOW DATABASES;                   # list down all the available databases

Now, to have access to this database we have to use it.

Syntax:

USE <database-name>;

Command:



USE student_detail;

Syntax To Create Table in Hive

CREATE TABLE [IF NOT EXISTS] <table-name> (
<column-name>    <data-type>,
<column-name>    <data-type> COMMENT 'Your Comment',
<column-name>    <data-type>,
.
.
.
<column-name>    <data-type>
)
COMMENT 'Add if you want'
LOCATION 'Location On HDFS'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

Note:

1. We can add a comment to the table as well as to each individual column.

2. ROW FORMAT DELIMITED shows that whenever a new line is encountered the new record entry will start. 

3. FIELDS TERMINATED BY ‘,’ shows that we are using ‘,’ delimiter to separate each column. 

4. We can also override the default database location with the LOCATION option.

So let’s create the table student_data in our student_detail database with the help of the command shown below.

CREATE TABLE IF NOT EXISTS student_data(
Student_Name STRING COMMENT 'This col. Store the name of student', 
Student_Rollno INT COMMENT 'This col. Stores the rollno of student',
Student_Marks FLOAT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','; 

We have successfully created the table student_data in our student_detail database with 3 different fields Student_Name, Student_Rollno, Student_Marks as STRING, INT, FLOAT respectively.



We can list down the table available in our database with the help of the command explained below.

Syntax:

SHOW TABLES [IN <database_name>];

Command:

SHOW TABLES IN student_detail;

Now, Finally, let’s check the location on HDFS where our student_detail database and student_data  table is made. Move to localhost:50070/ for Hadoop 2 and to localhost:9870/ for Hadoop 3. Then Utilities -> Browse the file system and go to /user/hive/warehouse which is a default location where hive databases are created.

In the above image, we can observe that the student_data table is available in HDFS inside student_detail.db.




My Personal Notes arrow_drop_up
Recommended Articles
Page :