CODEWITHSUNDEEP
pysparkdelta-lakedelta
Yash
Yash

Reputation: 75

How to do delta table deletion for a partition based on the creation/modification date of the partition folder

WHEN TRIED WITH Solutions, there is a vaccum command in delta to remove the old versions from delta table. However, I would like to remove the partion based on the modification date of the partition.

How to perform this efficiently with the delta features and without losing the durability

Upvotes: -1

Views: 97

Answers (2)

Kashyap
Kashyap

Reputation: 17534

Read up on time travel and vacuuming.

DELETE command will delete the data from current/latest version of the table (say v10) and create a new version (say v11), which becomes the current/latest version. By default you interact with a table (e.g. SELECT * from table) you're interacting with v11. You can interact with an older version of the table if you want as well, as long as that version exists. E.g. SELECT * from table VERSION AS OF v8

Note the versions are actually numbers (8, 10, 11) not text (v8, v10, v11).

VACUUM deletes older "versions" (snapshots) of the table, e.g. v10. After you vacuum, you lose the ability to time travel to the deleted versions of the table.

Upvotes: 1

Steven
Steven

Reputation: 15283

You're mixing up two different things here:

  1. Cleaning up files that no longer belong to the Delta table (this is what VACUUM does).
  2. Deleting data that still exists in the table but is considered outdated.

In your case, there's no built-in way to remove partitions based on the modification date directly. A good workaround would be to add a new column to your table to track the "update timestamp" (basically your modification date).

Once you have that, you can simply run a DELETE query like this:

DELETE FROM your_table
WHERE update_date < '2024-11-30';

Delta Lake supports standard DML commands like DELETE, so this works natively without any additional setup.

If you can't add a new column, you'll need to extract some other information to identify the rows you want to remove. If the partition date itself is sufficient, you can use it and perform a simple DELETE.

Upvotes: 1

Related Questions