{"id":762,"date":"2025-08-19T15:24:58","date_gmt":"2025-08-19T15:24:58","guid":{"rendered":"https:\/\/dataopsschool.com\/blog\/?p=762"},"modified":"2025-08-19T16:52:13","modified_gmt":"2025-08-19T16:52:13","slug":"databricks-lab-catalog-with-external-location-storage-credentials-in-unity-catalog","status":"publish","type":"post","link":"https:\/\/dataopsschool.com\/blog\/databricks-lab-catalog-with-external-location-storage-credentials-in-unity-catalog\/","title":{"rendered":"Databricks Lab – Catalog with External Location, & Storage Credentials in Unity Catalog"},"content":{"rendered":"\n

Good Read – https:\/\/dataopsschool.com\/blog\/databricks-catalog-schemas-tables-with-external-location\/<\/a><\/p>\n\n\n\n

\n\n\n\n

<\/h1>\n\n\n\n

1. Create Catalog without External Location<\/strong> <\/h3>\n\n\n\n
    \n
  • Go to Catalog tab<\/strong> in Databricks workspace.<\/li>\n\n\n\n
  • Click \u201c+ Add Catalog\u201d<\/strong>.<\/li>\n\n\n\n
  • Provide a catalog name<\/strong> (e.g., Dev<\/code>).<\/li>\n\n\n\n
  • Choose type = Standard<\/strong>.<\/li>\n\n\n\n
  • Do not<\/strong> specify any external location \u2192 data defaults to metastore location<\/strong>.<\/li>\n\n\n\n
  • Click Create<\/strong> \u2192 Catalog is created with default schemas (default<\/code>, information_schema<\/code>).<\/li>\n\n\n\n
  • To check catalog details: DESCRIBE CATALOG EXTENDED Dev;<\/code> (Shows no location specified \u2192 fallback to metastore storage.)<\/li>\n<\/ul>\n\n\n\n
    \n\n\n\n

    2. Create Catalog with SQL<\/strong> <\/h3>\n\n\n\n
      \n
    • Open a SQL notebook<\/strong> in Databricks.<\/li>\n\n\n\n
    • Ensure cluster is Unity Catalog enabled<\/strong>.<\/li>\n\n\n\n
    • Run SQL command: CREATE CATALOG DevSQL COMMENT \"This catalog is created using SQL\";<\/code><\/li>\n\n\n\n
    • Verify with: DESCRIBE CATALOG EXTENDED DevSQL;<\/code><\/li>\n<\/ul>\n\n\n\n
      \n\n\n\n

      3. Drop Catalog and Drop Catalog Recursively<\/strong> <\/h3>\n\n\n\n
        \n
      • To drop a catalog: DROP CATALOG DevSQL;<\/code> \u274c Error \u2192 cannot drop because it contains schemas\/tables.<\/li>\n\n\n\n
      • Use CASCADE<\/strong> to drop everything inside recursively: DROP CATALOG DevSQL CASCADE;<\/code> \u2705 Removes catalog + schemas + tables.<\/li>\n<\/ul>\n\n\n\n
        \n\n\n\n

        4. Create External Location in Databricks<\/strong> <\/h3>\n\n\n\n
          \n
        • Go to Catalog > External Data > Create Location<\/strong>.<\/li>\n\n\n\n
        • Or via SQL: CREATE EXTERNAL LOCATION `ext_catalogs` URL 'abfss:\/\/data@<storage_account>.dfs.core.windows.net\/ADB\/catalog\/' WITH STORAGE CREDENTIAL `SC_catalog_storage`;<\/code><\/li>\n<\/ul>\n\n\n\n
          \n\n\n\n

          5. Create Storage Credential in Databricks<\/strong> <\/h3>\n\n\n\n
            \n
          • Navigate to Catalog > Storage Credentials > Create Credential<\/strong>.<\/li>\n\n\n\n
          • Choose Azure Managed Identity<\/strong>.<\/li>\n\n\n\n
          • Provide credential name (e.g., SC_catalog_storage<\/code>).<\/li>\n\n\n\n
          • Supply UC connector Resource ID<\/strong> (from Azure portal).<\/li>\n\n\n\n
          • Click Create<\/strong> \u2192 Storage credential created.<\/li>\n<\/ul>\n\n\n\n
            \n\n\n\n

            6. Create Catalog with External Location<\/strong> <\/h3>\n\n\n\n
              \n
            • Run SQL to create a catalog with managed location: <\/li>\n<\/ul>\n\n\n\n
              CREATE CATALOG DevEXT\nMANAGED LOCATION 'abfss:\/\/data@<storage_account>.dfs.core.windows.net\/ADB\/catalog\/'\nCOMMENT \"Catalog with external location\";\n<\/code><\/pre>\n\n\n\n
                \n
              • <\/li>\n\n\n\n
              • Verify: <\/li>\n<\/ul>\n\n\n\n
                DESCRIBE CATALOG EXTENDED DevEXT;\n<\/code><\/pre>\n\n\n\n
                \n\n\n\n
                Final Flow Summary<\/h1>\n\n\n\n
                  \n
                1. Create Catalog without external location \u2192 defaults to metastore.<\/li>\n\n\n\n
                2. Create Catalog using SQL.<\/li>\n\n\n\n
                3. Drop catalog normally or recursively with CASCADE<\/strong>.<\/li>\n\n\n\n
                4. Create External Location in Databricks.<\/li>\n\n\n\n
                5. Create Storage Credential for external storage.<\/li>\n\n\n\n
                6. Create Catalog with External Location bound to that credential.<\/li>\n<\/ol>\n\n\n\n
                  \n\n\n\n
                  Let\u2019s build a practical example<\/strong> using your catalog dev_ext<\/code>.
                  I\u2019ll show you:<\/p>\n\n\n\n
                    \n
                  1. Create a schema<\/strong> inside the catalog.<\/li>\n\n\n\n
                  2. Create a table<\/strong> in that schema.<\/li>\n\n\n\n
                  3. Insert sample data.<\/li>\n\n\n\n
                  4. Query the data back.<\/li>\n<\/ol>\n\n\n\n
                    \n\n\n\n
                    1\ufe0f\u20e3 Create a Schema in dev_ext<\/code><\/h2>\n\n\n\n
                    -- Create a schema called bronze inside catalog dev_ext\nCREATE SCHEMA IF NOT EXISTS dev_ext.bronze\nCOMMENT 'Bronze schema for raw ingested data in dev_ext catalog';\n<\/code><\/pre>\n\n\n\n
                    \n\n\n\n
                    2\ufe0f\u20e3 Create a Managed Table in the Schema<\/h2>\n\n\n\n
                    -- Create a managed table (no LOCATION clause \u2192 stored at catalog\/schema location)\nCREATE TABLE IF NOT EXISTS dev_ext.bronze.sales_data (\n  id INT,\n  product STRING,\n  amount DOUBLE,\n  sale_date DATE\n)\nCOMMENT 'Sales transactions table';\n<\/code><\/pre>\n\n\n\n
                    \n\n\n\n
                    3\ufe0f\u20e3 Insert Some Sample Data<\/h2>\n\n\n\n
                    INSERT INTO dev_ext.bronze.sales_data VALUES\n  (1, 'Laptop', 1200.50, DATE'2025-08-01'),\n  (2, 'Phone', 750.00, DATE'2025-08-05'),\n  (3, 'Tablet', 500.00, DATE'2025-08-10'),\n  (4, 'Headphones', 120.00, DATE'2025-08-12');\n<\/code><\/pre>\n\n\n\n
                    \n\n\n\n
                    4\ufe0f\u20e3 Query the Data<\/h2>\n\n\n\n
                    SELECT * \nFROM dev_ext.bronze.sales_data;\n<\/code><\/pre>\n\n\n\n
                    \u2705 You\u2019ll see the four rows returned.<\/p>\n\n\n\n
                      \n
                    • The data files will be stored in the catalog-level managed location<\/strong> (since you didn\u2019t set schema\/table LOCATION<\/code>).<\/li>\n<\/ul>\n\n\n\n
                      \n\n\n\n
                      <\/p>\n","protected":false},"excerpt":{"rendered":"
                      Good Read – https:\/\/dataopsschool.com\/blog\/databricks-catalog-schemas-tables-with-external-location\/ 1. Create Catalog without External Location 2. Create Catalog with SQL 3. Drop Catalog and Drop Catalog Recursively 4. Create External Location in… <\/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-762","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/762","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=762"}],"version-history":[{"count":3,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/762\/revisions"}],"predecessor-version":[{"id":780,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/762\/revisions\/780"}],"wp:attachment":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=762"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=762"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=762"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}