Hive: Drop a database

Syntax

DROP DATABASE|SCHEMA [IF NOT EXISTS] <databaseName>

 

Example

DROP DATABASE demo_db1;

 

hive> SHOW DATABASES;
OK
default
demo_db1
demo_db2
Time taken: 0.009 seconds, Fetched: 3 row(s)
hive> ;
hive> ;
hive> DROP DATABASE demo_db1;
OK
Time taken: 0.033 seconds
hive> ;
hive> ;
hive> SHOW DATABASES;
OK
default
demo_db2
Time taken: 0.008 seconds, Fetched: 2 row(s)

‘DROP DATABASE demo_db1;’ will not delete the database, if the database has some data in it.

 

For example, let’s create a table in demo_db2 database and try to delete it.

hive> USE demo_db2;
OK
Time taken: 0.01 seconds
hive> ;
hive> ;
hive> create table employee(id int, name string);
OK
Time taken: 0.055 seconds
hive> ;
hive> ;
hive> DROP DATABASE demo_db2;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database demo_db2 is not empty. One or more tables exist.)

As you see the above output, Hive throws the error ‘Database demo_db2 is not empty. One or more tables exist.’.

 

How to delete a database along with tables?

Use the option CASCADE to delete a database along with tables.

hive> DROP DATABASE demo_db2 CASCADE;
OK
Time taken: 0.215 seconds
hive> ;
hive> ;
hive> SHOW DATABASES;
OK
default
Time taken: 0.007 seconds, Fetched: 1 row(s)

Previous                                                    Next                                                    Home