topic Re: DROP TABLE IF EXISTS does not work in Data Engineering

DROP TABLE IF EXISTS does not work

martinch — Fri, 01 Mar 2019 15:40:53 GMT

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?

Re: DROP TABLE IF EXISTS does not work

Eve — Fri, 01 Mar 2019 15:52:20 GMT

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...

Re: DROP TABLE IF EXISTS does not work

martinch — Fri, 01 Mar 2019 15:59:29 GMT

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

Re: DROP TABLE IF EXISTS does not work

robert4os — Mon, 24 Jun 2019 09:26:12 GMT

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

Re: DROP TABLE IF EXISTS does not work

StevenWilliams — Tue, 30 Jul 2019 12:46:30 GMT

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