<\/p>\n\n\n\n
Managed vs External Tables + UNDROP (with External Location setup)<\/strong><\/p>\n\n\n\n You\u2019ll learn to:<\/p>\n\n\n\n Prereqs<\/strong><\/p>\n\n\n\n In the workspace, click ? \u2192 What\u2019s new<\/strong> to see the monthly release notes (features are rolled out in stages). For reference: August 2024<\/strong> release notes pages: Azure and Databricks docs. (Microsoft Learn<\/a>, Databricks Documentation<\/a>)<\/p>\n\n\n\n In your ADLS Gen2 container<\/strong> (e.g., You\u2019ll store all external table data here (the video used If you already have one (e.g., UI path (as in the video):<\/strong> SQL equivalent<\/strong> (does the same thing):<\/p>\n\n\n\n Reference: SQL syntax for CREATE EXTERNAL LOCATION<\/strong>. (Databricks Documentation<\/a>)<\/p>\n\n\n\n We\u2019ll use catalog Tip: attach your SQL notebook to a Unity Catalog\u2013enabled<\/strong> cluster before running.<\/p>\n<\/blockquote>\n\n\n\n External table requires a LOCATION<\/strong> pointing into your external location path.<\/p>\n<\/blockquote>\n\n\n\n Now we\u2019ll drop<\/strong> both tables and recover them.<\/p>\n\n\n\n You\u2019ll get rows including Copy the The Keep in mind<\/strong><\/p>\n\n\n\n <\/p>\n","protected":false},"excerpt":{"rendered":" Databricks Unity Catalog Tutorial Managed vs External Tables + UNDROP (with External Location setup) Introduction (what we\u2019ll build) You\u2019ll learn to: What\u2019s new in Databricks? (Updates &… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-770","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/770","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=770"}],"version-history":[{"count":2,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/770\/revisions"}],"predecessor-version":[{"id":772,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/770\/revisions\/772"}],"wp:attachment":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=770"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=770"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=770"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Introduction (what we\u2019ll build)<\/h2>\n\n\n\n
\n
\n
\n
\n
Dev<\/code> and schema bronze<\/code> you can write to.<\/li>\n\n\n\nSC_catalog_storage<\/code>).<\/li>\n\n\n\nUSE CATALOG<\/code>, USE SCHEMA<\/code>, CREATE TABLE<\/code>, etc.<\/li>\n\n\n\n
\n\n\n\nWhat\u2019s new in Databricks? (Updates & Releases)<\/h2>\n\n\n\n
\n\n\n\nCreate External Location in Databricks<\/h2>\n\n\n\n
A) Make a folder in ADLS for external tables<\/h3>\n\n\n\n
data<\/code>), create:<\/p>\n\n\n\n\/ADB\/external-tables\/\n<\/code><\/pre>\n\n\n\nexternaltables<\/code>\u2014name doesn\u2019t matter).<\/p>\n\n\n\nB) (If needed) Reuse or create a storage credential<\/strong><\/h3>\n\n\n\n
SC_catalog_storage<\/code>), reuse it. Otherwise, create it via Catalog \u2192 Storage credentials<\/strong> (Azure Managed Identity or SAS\/Key as per your setup). Docs: Create external locations<\/strong> (Azure\/AWS). (Microsoft Learn<\/a>, Databricks Documentation<\/a>)<\/p>\n\n\n\nC) Create the external location<\/strong> (UI or SQL)<\/h3>\n\n\n\n
Catalog \u2192 External data<\/strong> \u2192 External locations<\/strong> \u2192 Create<\/strong><\/p>\n\n\n\n\n
ext_tables<\/code><\/li>\n\n\n\nSC_catalog_storage<\/code><\/li>\n\n\n\nabfss:\/\/data@<storage_account>.dfs.core.windows.net\/ADB\/external-tables\/<\/code><\/li>\n\n\n\nCREATE EXTERNAL LOCATION `ext_tables`\nURL 'abfss:\/\/data@<storage_account>.dfs.core.windows.net\/ADB\/external-tables\/'\nWITH STORAGE CREDENTIAL `SC_catalog_storage`;\n<\/code><\/pre>\n\n\n\n
\n\n\n\n03:37 \u2014 Managed Tables vs External Tables (same schema)<\/h2>\n\n\n\n
Dev<\/code><\/strong> and schema bronze<\/code><\/strong>.<\/p>\n\n\n\n\n
1) Create a managed<\/strong> table and insert a row<\/h3>\n\n\n\n
-- Managed table (no LOCATION clause)\nCREATE TABLE Dev.bronze.sales_managed (\n id INT,\n product STRING,\n amount DOUBLE\n);\n\nINSERT INTO Dev.bronze.sales_managed VALUES (1, 'Laptop', 1200.50);\n<\/code><\/pre>\n\n\n\n2) Create an external<\/strong> table and insert a row<\/h3>\n\n\n\n
\n
-- External table (explicit LOCATION under the external location root)\nCREATE TABLE Dev.bronze.sales_external (\n id INT,\n product STRING,\n amount DOUBLE\n)\nLOCATION 'abfss:\/\/data@<storage_account>.dfs.core.windows.net\/ADB\/external-tables\/sales_external';\n\nINSERT INTO Dev.bronze.sales_external VALUES (2, 'Phone', 750.00);\n<\/code><\/pre>\n\n\n\n3) Verify each table\u2019s type<\/strong> and storage path<\/strong><\/h3>\n\n\n\n
-- Managed table details\nDESCRIBE EXTENDED Dev.bronze.sales_managed;\n\n-- External table details\nDESCRIBE EXTENDED Dev.bronze.sales_external;\n<\/code><\/pre>\n\n\n\n\n
sales_managed<\/code> \u2192 tableType = MANAGED<\/strong>, path resolves to your metastore<\/strong> managed storage (Unity Catalog-managed).<\/li>\n\n\n\nsales_external<\/code> \u2192 tableType = EXTERNAL<\/strong>, path is exactly the LOCATION<\/strong> you specified under \/ADB\/external-tables\/<\/code>.
Background docs on table types. (Databricks Documentation<\/a>)<\/li>\n<\/ul>\n\n\n\n
\n\n\n\n07:07 \u2014 UNDROP in Databricks (recover dropped tables)<\/h2>\n\n\n\n
1) Drop the tables<\/h3>\n\n\n\n
DROP TABLE Dev.bronze.sales_managed;\nDROP TABLE Dev.bronze.sales_external;\n<\/code><\/pre>\n\n\n\n\n
2) List dropped tables (get names\/IDs)<\/h3>\n\n\n\n
USE CATALOG Dev;\n\n-- See dropped tables for a schema\nSHOW TABLES DROPPED IN Dev.bronze;\n<\/code><\/pre>\n\n\n\ntablename<\/code>, tableid<\/code> (UUID), deletedat<\/code>, and more. (Microsoft Learn<\/a>)<\/p>\n\n\n\n3) UNDROP<\/strong> by name<\/strong><\/h3>\n\n\n\n
UNDROP TABLE Dev.bronze.sales_managed;\n<\/code><\/pre>\n\n\n\n\n
4) UNDROP<\/strong> by ID<\/strong> (alternative)<\/h3>\n\n\n\n
tableid<\/code> for sales_external<\/code> from SHOW TABLES DROPPED<\/code>, then:<\/p>\n\n\n\nUNDROP TABLE WITH ID '<table_uuid_from_show_tables_dropped>';\n<\/code><\/pre>\n\n\n\nWITH ID<\/code><\/strong> form is handy if a new table with the same name was recreated and blocks name-based recovery (in that case you can rename the new one or use the ID). (Databricks Documentation<\/a>, kb.databricks.com<\/a>)<\/p>\n\n\n\n\n
\n
\n\n\n\n(Optional) Quick sanity checks in storage<\/h2>\n\n\n\n
\n
<table-id><\/code>\/\u201d). Cleanup is deferred. (Microsoft Learn<\/a>)<\/li>\n\n\n\n\/ADB\/external-tables\/sales_external\/<\/code> regardless; dropping only removes the table\u2019s entry from the catalog. (Databricks Documentation<\/a>)<\/li>\n<\/ul>\n\n\n\n
\n\n\n\nClean-up (optional)<\/h2>\n\n\n\n
-- Re-drop if you restored them\nDROP TABLE IF EXISTS Dev.bronze.sales_managed;\nDROP TABLE IF EXISTS Dev.bronze.sales_external;\n<\/code><\/pre>\n\n\n\n
\n\n\n\nReferences (docs used)<\/h3>\n\n\n\n
\n
\n\n\n\n