EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Hive Tutorial Hive Drop Table
 

Hive Drop Table

Priya Pedamkar
Article byPriya Pedamkar

Updated March 21, 2023

Hive Drop Table

 

 

Introduction on Hive Drop Table

The keyword “DROP” refers to deletion. For the deletion of data, we require data to be present in the hive.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

In Hadoop, we have two functionalities:

  1. Data Storage
  2. Data Processing

For data storage, HDFS (Hadoop Distributed File System) comes into the picture. Now when we say we have data in hive table it means two things:

  1. Data is in HDFS
  2. We have a hive table created over that HDFS file, and we load that HDFS file’s data into the hive table.

Basically, for the hive drop table to have the data, the data file is a prerequisite. In this article, we will see how to drop tables in the hive, what happens when the table is dropped and all things related to the drop table in the hive.

Types of Drop Table in Hive

In the hive, there are two types of tables:

  1. Internal Table or Managed Table
  2. External Table or Unmanaged Table

Managed Table/Internal Table

  1. In Hive,” user/hive/warehouse” is the default directory. Internal tables are stored in this directory by default. We do not have to provide the location manually while creating the table.
  2. “Drop table” command deletes the data permanently.
  3. Hive manages all the security for managed tables.

I do have a table already present in the “user/hive/warehouse” directory called “codes”.

Hive Drop Table

To check if the existing table is managed or unmanaged, we could use the below command:

Command Syntax:

Describe formatted table_name;

Output:

formatted table_name;

Let us see the data presented in the table “codes”.

First, using hive command-

Hive Drop Table

Second, using Hue (Hadoop User Experience a Web UI)

Hive Drop Table

Delete command: Drop table table_name;

Output:

Output

Now, if I want to select the data from “codes” it will give me an error because the table is deleted.

Hive Drop Table

Also, it will not be able to see this table in the default directory which is

“user/hive/warehouse.”

Observe here,

Hive Drop Table

Unmanaged table/ External table

  1. External tables, we are required to provide the path where we need to store that table using the keyword ‘location’ in create table command.

For Example: 

CREATE EXTERNAL TABLE stg_s2_json.products

( product no string,   product name string,   description string,   active string,   created date string,   updated date string) row format delimited fields terminated by ‘,’

LOCATION ‘/user/data/hive/stg_s2_json.db/products’;

  1. Hive only deletes the metadata. Data is permanent.
  2. These tables could be used by anyone who has access to HDFS, so they need to manage security at the folder level.

For understanding the dropping of the external table, we will use the table “products”.

Let’s check if the table is internal or external.  Again, “describe formatted table_name” command.

Hive Drop Table

Observe “limit 10” in the select command. Table Products contains the below data:

Table Products

To check it in Hue, it looks like this:

Hive Drop Table

Let’s see what happens when we drop this table:

Syntax:

Drop table table_name;

Output:

Hive Drop Table

Now, if trying to retrieve the table’s data, It throws an error.

First, using the “select” command on the terminal, it is going to throw me an error which means the metadata for the external table is deleted.

Observe Error here:

Hive Drop Table

Second, checking on hue the state of data, the file “products.json” is still present in HDFS, which means the data is permanent.

products.json

Conclusion

I am going to make it easy and provide you with key points for both kinds of tables. You decide which type will suit your requirements.

Internal Table

External Table

Also called “Managed Table.” Also called “Unmanaged table.”
No need to provide location, Hive default directory manages this data. Need to provide location
Deletes table’s metadata as well as (Data is temporary) Hive will leave the data untouched(Data is permanent)
The hive itself controls the security of the table. Need to manage security at the folder level

Recommended Articles

This is a guide to Hive Drop Table. Here we discuss a brief overview with types of Drop Table in Hive along with Syntax respectively. You can also go through our other suggested articles to learn more –

  1. Hive Architecture
  2. Using Order By Function in Hive
  3. Guide to Download and Install Hive Step by Step
  4. Complete Guide to Partitioning in Hive
  5. Top 7 Hive Versions

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

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

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW