In Previous chapter we learned about Hive Insert Into vs Insert Overwrite and today lets check out Hive Drop Table. If we need to remove a table and its data from Hive we do it using DROP TABLE command. If it is an internal table then both the table and data is removed but if its an external table then only the table is removed. Does that mean that the data in external table cannot be removed? Of course not, we will see in the at the end of the article how this will be done.
Hive – Drop Internal table
Using this you can remove the internal table from Hive metastore and also remove the data residing in hive warehoue(HDFS). The syntax for this is simple and is as follow:
DROP TABLE table_name
Hive – Drop External table
Using this you can remove the external table from Hive metastore but the data residing in the warehouse/hdfs will not be removed. We will see at the end how data will also be removed by default. The syntax to drop external table is as follow:
DROP EXTERNAL TABLE table_name
If you are wondering what are Internal and External table you can learn more about them here.
Hive – if exists
While dropping the table if the table does not exists then Hive throws an error. If we want the statement to succeed even if the table is not present then we need to use “IF EXISTS”.
The syntax of IF EXISTS is as follows:
DROP TABLE IF EXISTS table_name
Hive – PURGE
When you delete a file/folder it is not removed permanently . It initially goes into Trash folder. Think of Trash folder as recycle bin in desktop. The deleted file can be recovered from TRASH folder , but once deleted from here then the file is permanently removed. If you are absolutely sure that the table needs to be permanently deleted then you can use PURGE. This makes sure the data is not moved to TRASH instead it is permanently removed.
The syntax of PURGE is as follows:
DROP TABLE table_name PURGE
Hive – drop external table with data
In order to drop the data in external table first we need to convert the external to internal table and then drop the internal table. For this you need to alter the external table and set the table properties (‘EXTERNAL’=’False’). The syntax is below
ALTER TABLE table_nm SET TBLPROPERTIES('EXTERNAL'='False'); DROP TABLE table_nm;
Hive – drop multiple tables in hive
As of now this is not possible in HIVE. If you need to drop all tables then the easiest way is to drop the database .
So today we learnt
- How to drop internal and external hive table
- How to purge the data while dropping table
- Drop data in Hive table