17

I have a database in hive which has around 100 tables. I would like to delete the whole database in a single shot query.

How can we achieve that in Hive?

franklinsijo
  • 17,784
  • 4
  • 45
  • 63

3 Answers3

30

Use, 

DROP DATABASE database_name CASCADE;

By default, the mode is RESTRICT which blocks the deletion of database if it holds tables.

franklinsijo
  • 17,784
  • 4
  • 45
  • 63
  • 1
    Same situation, 2,414 tables. IT TAKES FOREVER and it's an external table supplied data by parquet. WHY DOES HIVE TAKE SO LONG TO DROP EXTERNAL DB CASCADE? Seriously, like 3 seconds per table dropped. – Rimer Mar 13 '20 at 14:38
  • if you get an error `no viable alternative at input drop database` try putting backticks around the name of the database. `DROP DATABASE \`database_name\` CASCADE;` – Vincent Claes Jan 28 '21 at 10:30
10

You can use this command:

DROP DATABASE IF EXISTS HIVE_DATABASE_NAME CASCADE;

In case, you are using older version of Hive: Drop all tables and views from hive database first, and then drop the database. You can consolidate below command in a shell script to execute.

hive -e 'use HIVE_DATABASE_NAME;show tables' | xargs -I '{}' hive -e 'use HIVE_DATABASE_NAME;DROP TABLE IF EXISTS {}'

hive -e 'use HIVE_DATABASE_NAME;show tables' | xargs -I '{}' hive -e 'use HIVE_DATABASE_NAME;DROP VIEW IF EXISTS {}'

hive -e 'DROP DATABASE IF EXISTS HIVE_DATABASE_NAME;'
Sandeep Singh
  • 7,790
  • 4
  • 43
  • 68
6

General syntax is as follows:

hive> DROP DATABASE [IF EXISTS] database_name [RESTRICT|CASCADE];

The default behaviour is RESTRICT, where DROP DATABASE will fail if the database is not empty. To drop the tables in the database as well, use DROP DATABASE … with CASCADE option.

1. Drop database without table or Empty Database:

hive> DROP DATABASE database_name;

2. Drop database with tables:

hive> DROP DATABASE database_name CASCADE;

It dropping respective tables before dropping the database.

Santosh Singh
  • 1,112
  • 14
  • 14
  • any reason this operation takes 3 seconds per table, in serial when dropping a DB with 2,414 external tables (parquet locations)? – Rimer Mar 13 '20 at 14:40