Databricks: Delete Delta log file using databricks SQL command

Question

I have an external Delta table in Databricks with data files stored in an S3 backend. I want to completely remove(drop) this external Delta table, including all data files and Delta log files from the S3 location, using Databricks SQL commands (not Python).

I tried deleting the data with DELETE FROM TABLE_NAME, then dropped the external Delta table, and finally ran VACUUM with RETAIN 0 HOURS. This successfully removed all data files from S3, but the Delta log files remain. How can I remove Delta log files using SQL commands?

%sql
CREATE TABLE dev_catal_nm.dbx.table_name1(
  id INT,
  name STRING  -- VARCHAR(25) is equivalent to STRING in Databricks SQL
)
USING DELTA
LOCATION 's3://my_bucket_test/poc/table_name1';

%sql
INSERT INTO  dev_catal_nm.dbx.table_name1
VALUES (1, 'John');

%sql
SET spark.databricks.delta.retentionDurationCheck.enabled = false;

%sql
DROP TABLE IF EXISTS  dev_catal_nm.dbx.table_name1;

%sql
VACUUM 's3://my_bucket_test/poc/table_name1' RETAIN 0 HOURS;

But log file still exists. I want to drop delta log file

You are doing right thing. As per docs.databricks.com/en/sql/language-manual/… all files should be deleted, unless you requested to create external table. Can you double check you never called CREATE EXTERNAL for this location? Can you try you commands for new location which was not (presumably) used before as external? — Alexander Pavlov
– Alexander Pavlov, Commented Sep 6, 2024 at 15:26

VLAZ · Accepted Answer · 2024-11-22 06:33:39Z

0

dbutils.fs.rm("dbfs:/FileStore/tables/Staging/Customer/_delta_log/", recurse=True)

edited Nov 22, 2024 at 6:33

VLAZ

29.6k9 gold badges65 silver badges88 bronze badges

answered Nov 22, 2024 at 5:07

Ganesh

1

Sign up to request clarification or add additional context in comments.

1 Comment

Mark Rotteveel Over a year ago

Although this code might answer the question, I recommend that you also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes.

Collectives™ on Stack Overflow

Databricks: Delete Delta log file using databricks SQL command

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related