question Re: Not able to drop database in hive in Support Questions

Not able to drop database in hive

SK1 — Fri, 16 Sep 2022 10:12:16 GMT

Hi Team,

I am not able to drop database though we do not have any table in that database ?

hive> use prashant_db;

Time taken: 0.221 seconds

hive> drop database prashant_db;

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database prashant_db is not empty. One or more functions exist.)

hive> show tables;

Time taken: 0.226 seconds

hive>

Re: Not able to drop database in hive

SK1 — Tue, 05 Apr 2016 13:54:29 GMT

I can see two functions are present in database but when I am trying to drop them, those are not getting delete.

hive> drop function prashant_db.getevent;

Time taken: 0.009 seconds

hive> show functions

> ;

prashant_db.getevent

prashant_db.gettheevent

printf

Re: Not able to drop database in hive

bleonhardi — Tue, 05 Apr 2016 14:36:56 GMT

What does he say when you try to drop these functions? Just silent? Anything in the logs? You can also try to use the drop ... cascade command.

DROP DATABASE IF EXISTS userdb CASCADE;

Re: Not able to drop database in hive

SK1 — Tue, 05 Apr 2016 16:45:44 GMT

@Benjamin Leonhardi: Thanks a lot for your help. It worked and delete database without any error.

But can you please help me to understand why it was not deleting functions and worked with forced option(cascade) ?

hive> DROP DATABASE IF EXISTS prashant_db CASCADE;

Time taken: 0.345 seconds

hive> show databases;

adotest

afrankli

archive_db

baseline_db

Re: Not able to drop database in hive

bleonhardi — Tue, 05 Apr 2016 17:18:40 GMT

So Cascade works because he forces the delete of all objects belonging to that object ( similar to delete ... cascade for row deletes )

Now the question is why your drop function did not work. And I don't know we might have to look into logs to figure that out. But I have seen flakiness with functions in hive before on an older version of hive. So it might be just a bug or a restart required or something. But again without logs hard to say.