how to delete data from a delta file in databricks?

user_10323366 0
Viewed 5562

I want to delete data from a delta file in databricks. Im using these commands
Ex:

PR=spark.read.format('delta').options(header=True).load('/mnt/landing/Base_Tables/EventHistory/')
PR.write.format("delta").mode('overwrite').saveAsTable('PR')
spark.sql('delete from PR where PR_Number=4600')

This is deleting data from the table but not from the actual delta file. And i want to delete the data in the file without using merge operation, because the join condition is not matching. Can anyone please help me in resolving this issue.

Thanks

delta-lake pyspark azure-databricks apache-spark-sql sql
3 Answers

Please do remember : Subqueries are not supported in the DELETE in Delta.

Issue Link : https://github.com/delta-io/delta/issues/730

From the documentation itself , an Alternative is as follows

For Example :

DELETE FROM tdelta.productreferencedby_delta 
WHERE  id IN (SELECT KEY 
              FROM   delta.productreferencedby_delta_dup_keys) 
       AND srcloaddate <= '2020-04-15'

Can be written as below in case of DELTA

MERGE INTO delta.productreferencedby_delta AS d 
using (SELECT KEY FROM   tdatamodel_delta.productreferencedby_delta_dup_keys) AS k 
ON d.id = k.KEY 
  AND d.srcloaddate <= '2020-04-15' 
WHEN MATCHED THEN DELETE
user_68422540
user_102017450

It worked like

delete from delta.`/mnt/landing/Base_Tables/EventHistory/` where PR_Number=4600
user_103233660
Related