Databricks - is not empty but it's not a Delta table

Question

I run a query on Databricks:

DROP TABLE IF EXISTS dublicates_hotels;
CREATE TABLE IF NOT EXISTS dublicates_hotels
...

I'm trying to understand why I receive the following error:

Error in SQL statement: AnalysisException: Cannot create table ('default.dublicates_hotels'). The associated location ('dbfs:/user/hive/warehouse/dublicates_hotels') is not empty but it's not a Delta table

I already found a way how to solve it (by removing it manually):

dbutils.fs.rm('.../dublicates_hotels',recurse=True)

But I can't understand why it's still keeping the table? Even though that I created a new cluster (terminated the previous one) and I'm running this query with a new cluster attached.

Anyone can help me to understand that?

Nkrumah

Answer 1

DROP TABLE & CREATE TABLE work with entries in the Metastore that is some kind of database that keeps the metadata about databases and tables. There could be the situation when entries in metastore don't exist so DROP TABLE IF EXISTS doesn't do anything. But when CREATE TABLE is executed, then it additionally check for location on DBFS, and fails if directory exists (maybe with data). This directory could be left from some previous experiments, when data were written without using the metastore.

Barogram

Answer 2

I also faced a similar problem, then tried the command line CREATE OR REPLACE TABLE and it solved my problem.

Omnivore

I got the same error but CREATE OR REPLACE TABLE doesn't work for me. – Cotquean

Answer 3

if the table created with LOCATION specified - this means the table is EXTERNAL, so when you drop it - you drop only hive metadata for that table, directory contents remains as it is. You can restore the table by CREATE TABLE if you specify the same LOCATION (Delta keeps table structure along with it's data in the directory).

if LOCATION wasn't specified while table creation - it's a MANAGED table, DROP will destroy metadata and directory contents

Dorothadorothea

Answer 4

I end up deleting the empty directory using notebook that allowed me to create the table -

%rm -r "/dbfs/user/hive/warehouse/schemaname.db/tablename"

Lowis