EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Hive Tutorial Hive Table
Secondary Sidebar
Hive Tutorial
  • Basics
    • Hive JDBC Driver
    • What is a Hive
    • Hive Architecture
    • Hive Installation
    • How To Install Hive
    • Hive Versions
    • Hive Commands
    • Hive Data Types
    • Hive Built-in Functions
    • Hive Function
    • Hive String Functions
    • Date Functions in Hive
    • Hive Table
    • Hive Drop Table
    • Hive Show Tables
    • Hive Group By
    • Hive Order By
    • Hive Cluster By
    • Joins in Hive
    • Hive Inner Join
    • Map Join in Hive
    • Hive nvl
    • Hive UDF
    • Dynamic Partitioning in Hive
    • HiveQL
    • HiveQL Queries
    • HiveQL Group By
    • Partitioning in Hive
    • Bucketing in Hive
    • Views in Hive
    • Indexes in Hive
    • External Table in Hive
    • Hive TimeStamp
    • Hive Database
    • Hive Interview Questions
    • Hive insert into

Related Courses

Hive Certification Course

Hadoop Course Training

All in One Data Science Course

Hive Table

By Priya PedamkarPriya Pedamkar

Hive Table

Introduction to Hive Table

In the hive, the tables are consisting of columns and rows and store the related data in the table format within the same database. The table is storing the records or data in tabular format. The tables are broadly classified into two parts i.e.; external table and internal table.

The default storage location of the Table varies from the hive version. From HDP 3.0, we are using hive version 3.0 and more. The default Table location was changed from HDP 3.0 version / Hive version 3.0. The location for external hive Table is “/warehouse/tablespace/external/hive/” and the location for manage Table is “/warehouse/tablespace/managed/hive”.

In the older version of the hive, the default storage location of hive Table is “/apps/hive/warehouse/”.

Syntax of Hive Table

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [ database name ] table name
[( column name  data type [ COMMENT column comment], ...)] [ COMMENT table comment] [ ROW FORMAT row format] [ STORED AS file format]

How to create Table in Hive?

As per the requirement, we can create the tables. We can broadly classify our table requirement in two different ways;

  • Hive internal table
  • Hive external table
Note: We have the hive “hql” file concept with the help of “hql” files we can directly write the entire internal or external table DDL and directly load the data in the respective table.

1. Internal Table

The internal table is also called a managed table and it is own by “hive” only. Whenever we are creating the table without specifying the keyword “external” then the tables will create in the default location.

If we will drop the internal or manage table then the table DDL, metadata information, and table data will be lost. The table data is available on HDFS it will also lose.  We should be very careful while dropping any internal or manage the table.

DDL Code for Internal Table

create table emp.customer
(
idint,
first_name string,
last_name string,
gender string,
company_name string,
job_title string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
lines terminated by '\n'
location "/emp/table1"
tblproperties ("skip.header.line.count"="1");

Note: To load the data in hive internal or manage the table. We are using the “location” keyword in DDL Code. Form the same location, we have kept the CSV file and load the CSV file data in the table.

Output:

Hive Table output 1

2. External Table

The best practice is to create an external table. Many organizations are following the same practice to create tables. It does not manage the data of the external table and the table is not creating in the warehouse directory. We can store the external table data anywhere on the HDFS level.

The external tables having the facility to recover the data i.e. if we will delete/drop the external table. Still no impact on the external table data present on the HDFS. It will only drop the metadata associated with the table.

If we will drop the internal or manage table then the table DDL, metadata information, and table data will be lost. The table data is available on HDFS it will also lose.  We should be very careful while dropping any internal or manage the table.

DDL Code for External Table

create external table emp.sales
(
idint,
first_name string,
last_name string,
gender string,
email_id string,
city string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
lines terminated by '\n'
location "/emp/sales"
tblproperties ("skip.header.line.count"="1");

Note: we can directly store the external table data on the cloud or any other remote machine in the network. It will depend on the requirement.

Output:

Hive Table output 2

How to modify/alter the Table?

Here we have the facility to alter or modify the existing attributes of the Table. With the help of “alter” functionality, we can change the column name, add the column, drop the column, change the column name, replace the column.

We can alter the below Table attributes

1. Alter/ rename the tablename

Syntax:

ALTER TABLE [current table name] RENAME TO [new table name]

Query to Alter Table Name :

ALTER TABLE customer RENAME TO cust;

Output:

Before alter

Hive Table output 3

After alter

Hive Table output 4

Hive Table output 5

2. Alter/ add column in the table

Syntax:

ALTER TABLE [current table name] ADD COLUMNS (column spec[, col_spec ...])

Query to add Column :

ALTER TABLE cust ADD COLUMNS (dept STRING COMMENT 'Department');

Output:

Sample view of the table

Hive Table output 6

We are adding a new column in the table “department = dept”

output 7

3. Alter/change the column name

Syntax:

ALTER TABLE [current table name] CHANGE [column name][new name][new type]

Query to change column name :  

ALTER TABLE cust CHANGE first_name name string;

Output:

Sample view of the customer table.

output 8

Now we are changing the column name “first_name” to “name”

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,650 ratings)

output 9

output 10

How to drop the Table?

Here we can drop or delete the table form the system. When you will drop/delete the table form the hive database, the table entry will delete it from hive metastore.  If it is an internal table then the table and data will complete delete. If it is an external table then the table entry will delete it from metastore but the data is available on HDFS Level.

Drop Internal or External Table

Syntax:

DROP TABLE [IF EXISTS] table name;

Drop Query:

drop table cust;

Output:

Before drop query run

output 11

After drop query run on “cust” table.

output 12

output 13

Conclusion

We have seen the uncut concept of “Hive Table” with the proper example, explanation, syntax, SQL Query with different outputs. The table is useful to store the structure data. The table data is helpful for various analysis purposes like BI, reporting, helpful/easy in data slicing and dicing, etc. The internal table is managed and the external table is not managed by the hive. As per the requirement, we can choose which type of table we need to create.

Recommended Articles

This is a guide to Hive Table. Here we discuss the concept of “Hive Table” with the proper example, explanation, syntax, SQL Query. You may also have a look at the following articles to learn more –

  1. Hive Cluster By
  2. Hive Versions
  3. Indexes in Hive
  4. Hive Data Types
Popular Course in this category
Hive Training (2 Courses, 5+ Projects)
  2 Online Courses |  5 Hands-on Projects |  25+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Hadoop Training Program (20 Courses, 14+ Projects, 4 Quizzes)4.9
All in One Data Science Bundle (360+ Courses, 50+ projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more