What is the difference between a Managed Table with a location other than DBFS root (I created a schema with Location and created a managed table in that schema)and an External Table in Databricks?
What is the difference between a Managed Table with a location other than DBFS root (I created a schema with Location and created a managed table in that schema)and an External Table in Databricks?
Share Improve this question edited Mar 18 at 18:30 Ged 18.3k8 gold badges48 silver badges105 bronze badges asked Mar 17 at 13:29 qudsifqudsif 1038 bronze badges 1- Show what u did as hard to follow. – Ged Commented Mar 18 at 14:02
2 Answers
Reset to default 1Requirements:
Managed Table with Custom Location:
CREATE DATABASE my_schema_mnged_with_loc
LOCATION "abfss://[email protected]/data";
CREATE TABLE my_schema_mnged_with_loc.my_managed_table (
id INT,
name STRING
)
USING DELTA;
External Table:
CREATE DATABASE my_schema_ext;
CREATE TABLE my_schema_ext.my_ext_table (
id INT,
name STRING
)
USING DELTA
LOCATION "abfss://[email protected]/Ext_data";
So the difference between Managed Table with custom location and External table :
When creating a managed table on top of a schema located in a custom location, the metadata of the table is stored in Azure Blob Storage and Databricks. In contrast, for an external table , the metadata is stored within Databricks.
If we try to alter the location of an external table in Databricks, within Databricks the location gets altered successfully. But table metadata is still present in the previous location and in new_location is still acting like External Table.
SQL:
ALTER TABLE my_schema_mnged_with_loc.my_managed_table SET LOCATION "abfss://[email protected]/new_loc"
Before Altering the location:
When you drop the table after altering its location in Databricks, the table itself is removed from the new location. However, the metadata remains in the old location.
After Altering the location and dropping the table:
After dropping the table you can find that table got dropped from Databricks.
Although somewhat unclear, you are talking I presume about Unity Catalog.
In Unity Catalog a Location clause can be defined at Catalog, Schema and Table level, the hierarchy as known there.
if you define Location at Table level, then that is an External Table. Otherwise, it uses the (default) Location that is explicitly defined or inherited from higher level and is a Managed Table.
With External Tables, if you drop it then you will have to delete underlying files yourself.