Unable to copy Delta Lake data to Synapse Dedicated SQL Pool with Azure Data Factory

Gary Knowles 1

When trying to use data factory to copy data to a Synapse Dedicated SQL Pool Table I received the following error: ErrorCode=AzureDatabricksCommandError,Hit an error when running the command in Azure Databricks. Error details: shaded.databricks.org.apache.hadoop.fs.azure.AzureException: hadoop_azure_shaded.com.microsoft.azure.storage.StorageException: This request is not authorized to perform this operation.
Caused by: hadoop_azure_shaded.com.microsoft.azure.storage.StorageException: This request is not authorized to perform this operation.

I have created the data source using the Delta connector with no issues I can test connections/preview data all is good.

I have created the sink dataset and linked service. Link service connects to sink table all is good.

On the sink side I am using Polybase Staging with all the necessary settings and link service to the storage account. Link service test out as good and allows me to browse to the proper storage container I wish to use as the polybase staging location.

For the Databricks cluster that is handling the source Delta Table I have added the following lines to the Spark config on the cluster.
spark.sql.shuffle.partitions 8
spark.hadoop.fs.azure.account.key.xxxxxxxxx.blob.core.windows.net <storage account access key value>
spark.databricks.delta.preview.enabled true
spark.databricks.delta.autoCompact.enabled true
spark.databricks.delta.optimizeWrite.enabled true

Note: I have tried both the storage account key and the storage container SAS key with full rights, with both methods giving me the exact same error message
Also note before I found the documentation to modifying the spark config on the cluster I was getting an error message about not allowing anonymous connections. So adding the spark config option for spark.hadoop did change the error message. But still the error above remains.

I have tried everything I can think of over the course of several days. At this point I have looked at it so long I am afraid I can not see the forest for the trees. Anyone have any comments, questions or suggestions? I did notice when you execute the ADFY Pipeline in Debug mode in the typical activity run view you get:

It would appear that it is using the storage account to do some work before the actual polybase staging step. Would love to understand that portion better as well.

KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-02-15T23:30:23.013+00:00
Hello @Gary Knowles ,

Thanks for the question and using MS Q&A platform.
My understanding is that you are having Azure Blob storage connection error (unauthorized) while trying to read from Delta Lake and write Azure Synapse tables using Polybase. Please correct me if I'm not accurate.

From the error message it sounds like a storage authorization error. Looks like you are using the correct configuration in your Cluster configuration. But could you please confirm if you have configured your cluster to have access to both the source (Delta Lake underlying storage) AND the target storage account (Staging) as you using polybase?

Also, could you please confirm if there is a VNET involved here? In case if you are accessing storage account from the databricks VNET, adding the databricks VNET to storage account should allow the connection.

The cluster configuration looks like below:

# Using an account access key spark.hadoop.fs.azure.account.key.<storage-account-name>.blob.core.windows.net <storage-account-access-key> # Using a SAS token spark.hadoop.fs.azure.sas.<container-name>.<storage-account-name>.blob.core.windows.net <complete-query-string-of-sas-for-the-container>

Do let us know how it goes.

Thank you
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-02-17T01:00:46.283+00:00

Hello @Gary Knowles ,

Just checking in to see if the above suggestion was helpful. Do let us know how it goes.
Gary Knowles 1 Reputation point

2022-02-21T13:52:24.763+00:00

@KranthiPakala-MSFT

Sorry it has taken a few days to get back to this issue.

Your understanding is correct Delta Lake is the source for my ADFY pipeline copy activity and Synapse via Polybase is the sink.

As far as the cluster having access to the source Delta Lake , I am able to preview the data in the source dataset. So my assumption is the cluster can see the Delta Lake Storage.

As for the the target storage account I am able to test successfully the linked service used by the sink dataset which I would assume is really testing the connectivity for the integration runtime.

So I guess the real question is how would I test the connection between the Databricks cluster and the storage account? (This is where the problem seems to be anyway.)
The only thing I have seen in the documentation is the cluster configuration mentioned above. I have tired both options. But did have concerns that I was not using the 2nd option correctly. Guess I did not understand what the <complete-query-string-of-sas-for-the-container> consisted of. Is that referring to the entire string https://<storage acct Name>.blob.core.windows.net/<container name>? <SAS key> or is it just the portion from the ? on or something else. That is why I was sticking with the <storage-account-access-key> in the first configuration option..

Finally I can confirm that the Databricks cluster is not in it's own VNET.
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-03-14T18:42:17.183+00:00

Hello @Gary Knowles ,

Thanks for confirming.

The Databricks cluster needs to have access to Azure Blob or Azure Data Lake Storage Gen2 account, both the storage container/file system used for source/sink/staging and the container/file system where you want to write the Delta Lake tables.

To use Azure Data Lake Storage Gen2, you can configure a service principal on the Databricks cluster as part of the Apache Spark configuration. Follow the steps in Access directly with service principal.

To use Azure Blob storage, you can configure a storage account access key or SAS token on the Databricks cluster as part of the Apache Spark configuration. Follow the steps in Access Azure Blob storage using the RDD API.

And yes, it is referring to entire string https://<storage acct Name>.blob.core.windows.net/<container name>? <SAS key>

Also, would recommend referring to this old thread where a similar topic was discussed. Kindly read this thread and see if that is relevant to your scenario - Azure DataBricks ADF connectivity issue Delta Tables

Hope this helps. Do let us know if you have further questions.

Thank you
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-03-16T05:36:39.913+00:00

Hello @Gary Knowles ,

Just checking in to see if the above information was helpful. And, if you have any further query do let us know.