topic Re: Create a Hive db in Azure Databricks with the LOCATION parameter set to an ADLS GEN2 account fai in Data Engineering

Create a Hive db in Azure Databricks with the LOCATION parameter set to an ADLS GEN2 account fails

Mihai_Cog — Tue, 22 Aug 2023 07:25:40 GMT

Hello,

I am trying to create a database in Azure Databricks using the abfss Location in the create database statement and it throws an exception.

%sql

CREATE DATABASE IF NOT EXISTS test COMMENT "Database for Test Area" LOCATION "abfss://test@storagetemp.dfs.core.windows.net/database/"

The error from creating the database is:

AnalysisException: org.apache.hadoop.hive.ql.metadata.HiveException: MetaException(message:Got exception: shaded.databricks.azurebfs.org.apache.hadoop.fs.azurebfs.contracts.exceptions.KeyProviderException Failure to initialize configuration for storage account storagetemp.dfs.core.windows.net: Invalid configuration value detected for fs.azure.account.key)

Using a notebook with these settings and running it, is not helping. Any ideea?

I added:

service_credential = dbutils.secrets.get(scope="<secret-scope>",key="<service-credential-key>")

spark.conf.set("fs.azure.account.auth.type.<storage-account>.dfs.core.windows.net", "OAuth")

spark.conf.set("fs.azure.account.oauth.provider.type.<storage-account>.dfs.core.windows.net", "org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider")

spark.conf.set("fs.azure.account.oauth2.client.id.<storage-account>.dfs.core.windows.net", "<application-id>")

spark.conf.set("fs.azure.account.oauth2.client.secret.<storage-account>.dfs.core.windows.net", service_credential)

spark.conf.set("fs.azure.account.oauth2.client.endpoint.<storage-account>.dfs.core.windows.net", "https://login.microsoftonline.com/<directory-id>/oauth2/token")

in a notebook and after that I was running this notebook of the beginning of the MAIN notebook, using magic command:

%run "./conn"

I mention that I CAN do .save(), I can do dbutils.fs.ls(location).

BUT, if I add in the Cluster Spark Config:

fs.azure.account.auth.type.<storage-account>.dfs.core.windows.net OAuth

fs.azure.account.oauth.provider.type.<storage-account>.dfs.core.windows.net org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider

fs.azure.account.oauth2.client.id.<storage-account>.dfs.core.windows.net <application-id>

fs.azure.account.oauth2.client.secret.<storage-account>.dfs.core.windows.net {{secrets/<secret-scope>/<service-credential-key>}}

fs.azure.account.oauth2.client.endpoint.<storage-account>.dfs.core.windows.net https://login.microsoftonline.com/<directory-id>/oauth2/token

IT IS WORKING.

What I am doing wrong?

Thank you.

Re: Create a Hive db in Azure Databricks with the LOCATION parameter set to an ADLS GEN2 account fai

-werners- — Tue, 22 Aug 2023 10:50:45 GMT

is your workspace Unity enabled by any chance? Because Unity Catalog ignores Spark configuration settings when accessing data managed by external locations.

Re: Create a Hive db in Azure Databricks with the LOCATION parameter set to an ADLS GEN2 account fai

Mihai_Cog — Tue, 22 Aug 2023 11:02:57 GMT

Nope. Is not.

Re: Create a Hive db in Azure Databricks with the LOCATION parameter set to an ADLS GEN2 account fai

-werners- — Tue, 22 Aug 2023 11:07:08 GMT

strange. I don't see the issue. Perhaps a typo somewhere?

Can you test with putting the config directly in the notebook (instead of using %run)?

Re: Create a Hive db in Azure Databricks with the LOCATION parameter set to an ADLS GEN2 account fai

Mihai_Cog — Tue, 22 Aug 2023 11:09:14 GMT

Done and is not working.

Only with configuration in Cluster Spark Config is working.

I really don't understand.

Re: Create a Hive db in Azure Databricks with the LOCATION parameter set to an ADLS GEN2 account fai

-werners- — Tue, 22 Aug 2023 11:11:20 GMT

According to the docs it should work:

https://learn.microsoft.com/en-us/azure/databricks/storage/azure-storage#azureserviceprincipal

You are using a Service Principal I suppose?

Re: Create a Hive db in Azure Databricks with the LOCATION parameter set to an ADLS GEN2 account fai

Mihai_Cog — Tue, 22 Aug 2023 11:43:22 GMT

Yes, Service Principal.

Re: Create a Hive db in Azure Databricks with the LOCATION parameter set to an ADLS GEN2 account fai

-werners- — Tue, 22 Aug 2023 13:01:50 GMT

The only things that come to mind are either:

- a typo/wrong value passed

- permission (so the spark conf is not actually updated)
Because it should work.

Re: Create a Hive db in Azure Databricks with the LOCATION parameter set to an ADLS GEN2 account fai

Mihai_Cog — Tue, 22 Aug 2023 13:54:02 GMT

I checked the notebook and what I have in spark config - NO typo

If is working when I set those in Spark Config, it should work also from notebook.

The only thing that is not working with these settings in notebook, is the CREATE DATABASE. Other things like .save(). dbutils.fs.ls(), .write() are working.

It is something else.

Re: Create a Hive db in Azure Databricks with the LOCATION parameter set to an ADLS GEN2 account fai

-werners- — Tue, 22 Aug 2023 14:08:16 GMT

can you, for test purposes mount the external location to dbfs?
and then as path you use /mnt/<mount>/...

Re: Create a Hive db in Azure Databricks with the LOCATION parameter set to an ADLS GEN2 account fai

Mihai_Cog — Tue, 22 Aug 2023 14:11:39 GMT

Already did, works properly.

But, I cannot use anymore the mounting, because Databricks announced that mounting is not recommended to be used because soon will be deprecated..

Re: Create a Hive db in Azure Databricks with the LOCATION parameter set to an ADLS GEN2 account fai

-werners- — Tue, 22 Aug 2023 14:17:49 GMT

correct (mainly for Unity though).
Hey how about you try with CREATE SCHEMA instead of DATABASE? It should not make any difference, but since we are stuck anyway...

Re: Create a Hive db in Azure Databricks with the LOCATION parameter set to an ADLS GEN2 account fai

Mihai_Cog — Tue, 22 Aug 2023 14:40:35 GMT

Tried, same error message.

Re: Create a Hive db in Azure Databricks with the LOCATION parameter set to an ADLS GEN2 account fai

-werners- — Tue, 22 Aug 2023 14:54:53 GMT

ok.

Can you check this link?

Because I suspect something is wrong, and so dbrx uses the account key as a default (fs.azure.account.key)

Re: Create a Hive db in Azure Databricks with the LOCATION parameter set to an ADLS GEN2 account fai

Mihai_Cog — Tue, 22 Aug 2023 15:30:17 GMT

Thank you for the link, is very useful, although not for me.

Everthing is set as it should be.

Still not working.