Hive: create database fail with 'database already exists'

Question

I have a test suite which runs several spark unit tests. Each of these tests shares the same underlying spark context. During the running of these tests I check if a db exists and if not I create it:

def dbExists(db: String) = spark.sql(s"show databases like '$db'").count > 0

if (!dbExists(db)) spark.sql(s"create database $db")

For some reasons, one of the tests is failing. Debugging I saw that for a certain db dbExists(db) returns false and the creation command fails with

ERROR RetryingHMSHandler:159 - AlreadyExistsException(message:Database db already exists)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_database(HiveMetaStore.java:891)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

Everytime a test start, I clean the environment running drop database db cascade for each db that is not the default one. The only explanation I can give is that some corrupted metadata is in the catalogue and spark sql thinks that the db exists, while it is not anymore.

The problem happens also inside a container with a fresh git clone of the project, meaning that it is not a previous run of the application that might pollute environment.

I run with hive support enabled.

Answer 1

Try this:

You are absolutely right, It is important to check the existence of the database before creation. This should work and would be much easier for the hive to check.

def dbExists(db: String) = spark.sql(s"show databases like '$db'").count > 0

spark.sql(s"create database if not exists $db")

This should work for you.