Quick Tip: Dropping Phantom Hive Databases (e.g. CDH5 Canary test dB)

While I’m a big fan of CDH5 and Hue – sometimes I will see some funkiness that’s a tad irritating.  Specifically, there is a database with a name similar to

cloudera_manager_metastore_canary_test_db_hive_hivemetastore_$guid$_2014_10_06_11_20_41

Even more irritating there is a table called cm_test_table which cannot be deleted (or renamed or even described).

hive> describe cm_test_table;
FAILED: SemanticException [Error 10001]: Table not found cm_test_table

hive> alter table cm_test_table RENAME to cm_test_table2;
FAILED: SemanticException [Error 10001]: Table not found cm_test_table

hive> drop table cm_test_table;
FAILED: SemanticException [Error 10001]: Table not found cm_test_table

To work around this problem, its a matter of using the CASCADE reference to your DROP DATABASE statement as per below.

hive> drop database cloudera_manager_metastore_canary_test_db_hive_hivemetastore_b7e213b8a8062c022f6ef36d67d4ee80_2014_10_06_11_20_41;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database cloudera_manager_metastore_canary_test_db_hive_hivemetastore_b7e213b8a8062c022f6ef36d67d4ee80_2014_10_06_11_20_41 is not empty)

hive> drop database cloudera_manager_metastore_canary_test_db_hive_hivemetastore_b7e213b8a8062c022f6ef36d67d4ee80_2014_10_06_11_20_41 cascade;
OK
Time taken: 0.3 seconds

Note that the first drop database statement failed because according to the Hive metastore, there was a cm_test_table – which could not be deleted, described, or dropped.   By adding the cascade statement to the drop database statement, now you’re good to go!

Hope this helps!

Addendum

Note that the CDH5 Canary test Hive database is not stored under the typical path of /user/hive/warehouse but it is located within the /user/hue/.cloudera_manager_hive_metastore_canary.  If you want, you can also clear out the various folders underneath (but not including) the hive_HIVEMETASTORE_$guid$ folder.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s