azure-databricksazure-data-lake

Unable to create Tables in mounted Storage on Databricks

Am trying to create a database on my mounted storage. I am following this course Azure Databricks & Spark For Data Engineers:Hands-on Project why does this code

%sql
CREATE DATABASE IF NOT EXISTS f1_demo
MANAGED LOCATION '/mnt/formula1dlbymilind/deltalake'

return me this error

UnityCatalogServiceException:
[RequestId=bc2af946-2461-456b-8565-f144d38155de ErrorClass=EXTERNAL_LOCATION_DOES_NOT_EXIST.RESOURCE_DOES_NOT_EXIST] External Location 'abfss://deltalake@forlabymilind.dfs.core.windows.net/' does not exist.

even though i have mounted the storage and am trying to create a Database on it

When i did

%sql
CREATE DATABASE IF NOT EXISTS f1_demo
LOCATION '/mnt/formula1dlbymilind/deltalake'

It said

AnalysisException: CREATE SCHEMA in Unity Catalog must use MANAGED LOCATION, not LOCATION

Solution

  • When you are creating schema in unity catalog you must use managed location passing external location.

    enter image description here

    If you observe here when i am using catalog jgsbricks giving error. if you did not mention also, it automatically takes default catalog and gives error.

    Next, if you want to create in unity catalog you need to create external location and use it.

    Follow below steps.

    Got Catalog > External data

    enter image description here

    Then click on Create external location

    enter image description here

    In next page give the details.

    External location name : Location name

    URL : Give the abfss URL or select from dbfs mount location.

    Storage credential : you either select the access connector already available when you created the workspace or you create new access connector

    For both ways you need to give access to storage account by adding Storage Blob Data Contributor role to it.

    enter image description here

    I used existing access connector, then click on create.

    Know run below query to create schema.

    %sql
CREATE DATABASE IF NOT EXISTS jgsbricks.f1_demo
MANAGED LOCATION 'abfss://data@jadlsgen2.dfs.core.windows.net/deltalake/'

    Here you give full abfss url or mount point.

    Output:

    enter image description here