Dropping Tables in Hive with DROP TABLE

Learn how to use the `DROP TABLE` command in Hive to permanently delete tables and their associated data. This guide explains the syntax and precautions to take before performing this irreversible operation.

Dropping Tables in Hive using DROP TABLE

The DROP TABLE command in Hive permanently deletes a table and all its data. This is a destructive operation; ensure you have a backup of your data before running this command. Once a table is dropped, all the data associated with that table will be permanently deleted. This action cannot be easily reversed.

Dropping a Table: Syntax and Steps

To drop a table, use the following syntax:

DROP TABLE Syntax


DROP TABLE databaseName.tableName;

You need to specify the fully qualified table name (databaseName.tableName). Before running this command, always ensure that you have the necessary privileges to drop the table and that you have backed up the data if necessary.

List Databases: Use SHOW DATABASES; to see available databases.
Select Database: Use USE databaseName; to select the database containing the table you want to drop.
List Tables: Use SHOW TABLES; to list the tables in the selected database.
Drop Table: Use DROP TABLE tableName; to delete the table.
Verify Deletion: Run SHOW TABLES; again to confirm the table has been removed.

Example: Dropping an Employee Table

Let's assume you have a table named `employees` in the database `mydatabase`. To drop this table:

Dropping employees Table


USE mydatabase;
DROP TABLE employees;
SHOW TABLES;

The final `SHOW TABLES` command will not list the `employees` table if the `DROP TABLE` command was successful.

Follow On