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 Drop 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 Drop Table

By Priya PedamkarPriya Pedamkar

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.

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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-

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,241 ratings)

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