CODEWITHSUNDEEP
apache-sparkdatabricksazure-databricksdelta-lake
Piotr K
Piotr K

Reputation: 1025

AssertionError: assertion failed: No plan for DeleteFromTable In Databricks

Is there any reason this command works well:

%sql SELECT * FROM Azure.Reservations WHERE timestamp > '2021-04-02'

returning 2 rows, while the below:

%sql DELETE FROM Azure.Reservations WHERE timestamp > '2021-04-02'

fails with:

Error in SQL statement: AssertionError: assertion failed: No plan for DeleteFromTable (timestamp#394 > 1617321600000000)

?

I'm new to Databricks but I'm sure I ran similar command on another table (without WHERE clause). The table is created basing on a Parquet file.

Upvotes: 3

Views: 9438

Answers (3)

user3752365
user3752365

Reputation: 91

just delete from the delta

%sql 
delete from delta.`/mnt/path`
where x

Upvotes: 0

Hila DG
Hila DG

Reputation: 718

It could be that you are trying to DELETE from a VIEW (in case it is not a parquet file)

Unfortunately, there is no easy way to differentiate between a VIEW and a TABLE in databricks; the only way you can test if it's indeed a view is by:

SHOW VIEWS FROM Azure like 'reser*' or, if it's a table: SHOW TABLES FROM Azure like 'reser*'

Show tables syntax Show views syntax

Upvotes: 0

Alex Ott
Alex Ott

Reputation: 87069

DELETE FROM (and similarly UPDATE, or MERGE) aren't supported on the Parquet files - right now on Databricks it's supported for Delta format. You can convert your parquet files into delta using CONVERT TO DELTA, and then this command will work for you.

Another alternative is to implement it is to read parquet files, filter out the rows that you want to keep, and overwrite your parquet files.

Upvotes: 7

Related Questions