NO_DROP – Disable Dropping The Table in Hive

There’s always the possibility of dropping the table by accident. In most RDBMSs, we restore a table and its data if we have a backup file, but if we don’t, we lose the table and its data. There’s a feature in Apache Hive that prevents us from dropping the table if we make a mistake.

Let’s look at how to change a table in Hive to prevent it from being destroyed in practice:

Syntax:
ALTER TABLE <tablename> ENABLE NO_DROP;

Example:
Hive> ALTER TABLE emp ENABLE NO_DROP;
Now, let’s issue the drop table statement.

Hive> DROP TABLE emp;
Now, this returns the error as we expected.
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask.Table emp is protected from being dropped.

Disabling the NO_DROP.
Hive> ALTER TABLE emp DISABLE NO_DROP;