ADF CopyActivity from delta lake to SQL

Question

ADF CopyActivity from delta lake to SQL

Prakash14 126

Using ADF to run copy activity from azure delta lake( linked service) to Sql database. Getting the below error

ErrorCode=AzureDatabricksCommandError,Hit an error when running the command in Azure Databricks. Error details: Failure to initialize configurationInvalid configuration value detected for fs.azure.account.key
Caused by: Invalid configuration value detected for fs.azure.account.key.

where exactly we need to set up this configuration ? edited the cluster and gave in spark configs. It did not work.

fs.azure.account.auth.type.<storage-account-name>.dfs.core.windows.net OAuth
fs.azure.account.oauth.provider.type.<storage-account-name>.dfs.core.windows.net org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider
fs.azure.account.oauth2.client.id.<storage-account-name>.dfs.core.windows.net <application-id>
fs.azure.account.oauth2.client.secret.<storage-account-name>.dfs.core.windows.net <service-credential>
fs.azure.account.oauth2.client.endpoint.<storage-account-name>.dfs.core.windows.net https://login.microsoftonline.com/<tenant-id>/oauth2/token

Also, gave spark.hadoop.fs.azure.account.key.<accountname>.dfs.core.windows.net as well and was not working.

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-10-13T22:16:26.76+00:00

Hello @Prakash14 ,

Welcome to the MS Q&A platform.

Could you please confirm whether you use 'Azuredatabricksdeltalake' in your source? If yes, could you please let me know your data bricks runtime version?

And are you using copy activity or dataflow? The reason for the ask is I am getting the below error when trying to use the copy activity.

Copy data1
Direct copying data from Azure Databricks Delta Lake is only supported when sink dataset is DelimitedText, Parquet or Avro with Azure Blob Storage linked service or Azure Data Lake Storage Gen2, for other dataset or linked service, please enable staging
Prakash14 126 Reputation points

2022-10-14T02:01:29.863+00:00

Yes Azure Databricks Delta Lake is the source connector i have used. Databricks run time version- 10.4 LTS . Am using copyactivity task(not dataflow). Staging has been enabled already. Still facing the error-
Caused by: Invalid configuration value detected for fs.azure.account.key.
Prakash14 126 Reputation points

2022-10-15T06:39:38.527+00:00
where does this configuration to be done to run ADF CopyActivity task.

Databricks notebook or

Edit the databricks cluster and under advanced spark configs

Tried both and did not work.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-10-16T21:30:39.623+00:00
Hello @Prakash14 ,
You need to run this in the databricks notebook to establish a connection from the staging storage account to Databricks.

I was able to establish a connection using the Account key and able to read the data from the storage account.

Access Azure Data Lake Storage Gen2 or Blob Storage using the account key

https://learn.microsoft.com/en-us/azure/databricks/external-data/azure-storage#--access-azure-data-lake-storage-gen2-or-blob-storage-using-the-account-key

spark.conf.set( "fs.azure.account.key.<storage-account>.dfs.core.windows.net", dbutils.secrets.get(scope="<scope>", key="<storage-account-access-key>"))

Here is a video tutorial from WafaStudies explaining accessing Gen2 using account key in data bricks.

In the below screenshot, I hard-coded the access_key

1 answer

Your answer

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-10-13T22:16:26.76+00:00

Hello @Prakash14 ,

Welcome to the MS Q&A platform.

Could you please confirm whether you use 'Azuredatabricksdeltalake' in your source? If yes, could you please let me know your data bricks runtime version?

And are you using copy activity or dataflow? The reason for the ask is I am getting the below error when trying to use the copy activity.

Copy data1
Direct copying data from Azure Databricks Delta Lake is only supported when sink dataset is DelimitedText, Parquet or Avro with Azure Blob Storage linked service or Azure Data Lake Storage Gen2, for other dataset or linked service, please enable staging
Prakash14 126 Reputation points

2022-10-14T02:01:29.863+00:00

Yes Azure Databricks Delta Lake is the source connector i have used. Databricks run time version- 10.4 LTS . Am using copyactivity task(not dataflow). Staging has been enabled already. Still facing the error-
Caused by: Invalid configuration value detected for fs.azure.account.key.
Prakash14 126 Reputation points

2022-10-15T06:39:38.527+00:00

where does this configuration to be done to run ADF CopyActivity task.

Databricks notebook or

Edit the databricks cluster and under advanced spark configs

Tried both and did not work.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-10-16T21:30:39.623+00:00

Hello @Prakash14 ,
You need to run this in the databricks notebook to establish a connection from the staging storage account to Databricks.

I was able to establish a connection using the Account key and able to read the data from the storage account.

Access Azure Data Lake Storage Gen2 or Blob Storage using the account key

https://learn.microsoft.com/en-us/azure/databricks/external-data/azure-storage#--access-azure-data-lake-storage-gen2-or-blob-storage-using-the-account-key

spark.conf.set( "fs.azure.account.key.<storage-account>.dfs.core.windows.net", dbutils.secrets.get(scope="<scope>", key="<storage-account-access-key>"))

Here is a video tutorial from WafaStudies explaining accessing Gen2 using account key in data bricks.

In the below screenshot, I hard-coded the access_key

Answer 1

Hello @Prakash14 ,
Thank you for providing more details.
You are getting this error because your databricks don't have access to Azure data lake storage gen2/Blob storage.

Please follow this document to provide access to databricks cluster

Access Azure Data Lake Storage Gen2 or Blob Storage using OAuth 2.0 with an Azure service principal

service_credential = dbutils.secrets.get(scope="<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")

or Access Azure Data Lake Storage Gen2 or Blob Storage using a SAS token or storage access key

spark.conf.set("fs.azure.account.auth.type.<storage-account>.dfs.core.windows.net", "SAS")  
spark.conf.set("fs.azure.sas.token.provider.type.<storage-account>.dfs.core.windows.net", "org.apache.hadoop.fs.azurebfs.sas.FixedSASTokenProvider")  
spark.conf.set("fs.azure.sas.fixed.token.<storage-account>.dfs.core.windows.net", "<token>")

I hope this helps. Please let me know if you have any further questions.

------------------------------

Please don't forget to click on and upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification
If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Share via

ADF CopyActivity from delta lake to SQL

1 answer

Your answer