When you drop a Hive table all the metadata information related to the table is dropped. If the tables is an internal/managed table then the data along with metadata is removed permanently. If the table is external table then only the metadata is dropped.

hive – drop Internal table

The syntax to drop internal table is as follow:

drop table table_name

hive – drop External table

As mentioned earlier only the metadata is removed, the data is not removed.

The syntax to drop external table is as follow:

drop external table table_name

If you want to learn more about the difference between Hive Internal/Managed and External Tables then you can click 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 deleted. 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 multiple tables in hive

As of now this is not possible in HIVE. The first thing that comes to mind if if we can show multiple tables using LIKE then can we DROP multiple tables as well. The answer sadly is no.

If you need to drop all tables then the easiest way is to drop the database .

If you want to learn more you can always refer cwiki.apache.org page confluence page.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.