Hive Drop Table

The DROP TABLE command is used to delete an existing table from the Hive database. When we drop a table:

  • The table metadata (like columns, schema) is removed from the Hive metastore.
  • For managed tables, the actual data in HDFS is also deleted.
  • For external tables, only the table’s definition is removed, but the actual data remains safe in HDFS.

Why Do We Use DROP TABLE?

There are several reasons why we might want to use the DROP TABLE command:

  • The table was created for temporary use (like testing or development).
  • The table is outdated and replaced by a new one.
  • We want to free up space by removing unnecessary tables.
  • We are cleaning up after completing a project.
Syntax -
DROP TABLE [IF EXISTS] [db_name.]table_name

If it’s a managed table, both the data and the table will be deleted. If it’s an external table, only the table definition will be removed, not the actual data files.

Examples -

Scenario1: Drop the table safely:

DROP TABLE IF EXISTS temp_sales_summary;

Scenario2: Drops the table and also deletes the data from Hive’s warehouse directory in HDFS.

DROP TABLE IF EXISTS orders_table;

Scenario3: Only the table definition is dropped. The data files stay safe where they are in HDFS.

DROP TABLE IF EXISTS external_orders_table;