cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

DROP TABLE IF EXISTS does not work

martinch
New Contributor II

When I try to run the command

spark.sql("DROP TABLE IF EXISTS table_to_drop")

and the table does not exist, I get the following error:

AnalysisException: "Table or view 'table_to_drop' not found in database 'null';;\nDropTableCommand `table_to_drop`, true, false, false\n"

The command works when the table does exist.

I am using Python3 in an Azure Databricks notebook, Databricks runtime 5.2 on a high concurrency cluster.

The table was created using the following command:

df.write.option("path", "adl://***.azuredatalakestore.net/delta/table_to_drop").saveAsTable(name="table_to_drop", format="delta")

I thought the whole point of using "IF EXISTS" was to avoid this error. Is this a bug, or is it something I don't understand?

4 REPLIES 4

Eve
New Contributor III

According to this documentation, it is how it works... It throws an error, when the table doesn't exist:

Databricks DOCS

It's pretty annoying though...

martinch
New Contributor II

But that is without the optional "IF EXISTS". With the "IF EXISTS" command the docs says: "If the table does not exist, nothing happens.".

robert4os
New Contributor II

This is not the expected behavior for SQL users.

e.g. https://dev.mysql.com/doc/refman/8.0/en/drop-table.html ...'With

IF EXISTS
, no error occurs for nonexisting tables.'

It is a usability bug.

Best regards, Robert

StevenWilliams
New Contributor II

I agree about this being a usability bug. Documentation clearly states that if the optional flag "IF EXISTS" is provided that the statement will do nothing.

https://docs.databricks.com/spark/latest/spark-sql/language-manual/drop-table.html

Drop Table copy

DROP TABLE [IF EXISTS] [db_name.]table_name

Drop a table and delete the directory associated with the table from the file system if this is not an

EXTERNAL

table. If the table to drop does not exist, an exception is thrown.

IF EXISTS

If the table does not exist, nothing happens.

I am getting the same error as the original post while running the just this command with the SQL API

DROP TABLE IF EXISTS testTable;

I would expect no error but get the following.

Error in SQL statement: AnalysisException: Table or view 'testTable' not found in database 'null';; DropTableCommand

testTable

, true, false, false

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group