Change databricks delta table typr to external

Question

I have a MANAGED table in delta format in databrciks and I wanted to change it to EXTERNAL to make sure dropping the table would not affect the data. However the following code did not change the table TYPE and just added a new table property. How can I correctly convert my managed table to an external table ?

%sql
alter table db_delta.table1 SET TBLPROPERTIES('EXTERNAL'='TRUE')

Describe Table:

# Detailed Table Information

Name
db_delta.table1

Location
dbfs:/user/hive/warehouse/db_delta.db/table1

Provider
delta

Type
MANAGED

Table Properties
[EXTERNAL=TRUE,overwriteSchema=true]

score 0 · Answer 1 · answered Feb 15 '23 at 13:30

I found the following workaround for the above scenario.

1.Copy the Managed table location to external location

dbutils.fs.cp('dbfs:/user/hive/warehouse/amazon_data_agg','abfss://data@amazondata.dfs.core.windows.net/amzon_aggred/',True)

Now drop the managed table.

drop table amazon_data_agg;

Now create the external table by the schema of the already created table, if there is schema mismatch you will get error.

Now you can append and do all operation

df_agg.write.format('delta').mode('append').option('path','abfss://data@amazondata.dfs.core.windows.net/amzon_aggred/').saveAsTable('amazon_data_agg')

Change databricks delta table typr to external

1 Answers1