Azure Synapse - PandasDataframe to_csv ADLS Gen2 write fail 443 error

Question

Azure Synapse - PandasDataframe to_csv ADLS Gen2 write fail 443 error

WZIAFP 237

Following the Microsoft Tutorial here

my code is as below

import pandas  
  
data = pandas.DataFrame({'Name':['A', 'B', 'C', 'D'], 'ID':[20, 21, 19, 18]})  
data.to_csv('abfss://

2 answers

Your answer

Answer 1

ShaikMaheer-MSFT 38,546 Microsoft Employee Moderator

Hi @WZIAFP ,

Thank you for posting query in Microsoft Q&A Platform.

There is small issue in your code. Your storage account path should be in abfss://<containerName>@<storageaccountName>.dfs.core.windows.net/<path>

Please check below code, Where I tried same, and it works fine.

Please note, the authentication identity which you are using inside linked service of adls gen2 should have Storage Blob data contributor role.

Hope this helps. Please let me know if any further queries.

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

Please consider hitting Accept Answer button. Accepted answers help community as well.

WZIAFP 237 Reputation points

2022-09-29T09:35:25.95+00:00

Thanks for your response.
The storage account is using the synapse managed identity and has Storage Blob Data Contributor role assigned.

I have changed the storage account path to use dfs.core rather than blob.core and the same error is occurring:

ServiceRequestError: Cannot connect to host <storageaccountname>.blob.core.windows.net:443 ssl:True [Name or service not known]
WZIAFP 237 Reputation points

2022-09-29T09:43:19.893+00:00

Full Stack Trace here
https://file.io/OXmFeUWWnB7r
WZIAFP 237 Reputation points

2022-09-30T09:51:47.087+00:00

@ShaikMaheer-MSFT can you assist further?
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2022-09-30T10:45:17.457+00:00

Hi @WZIAFP ,

Thank you for logs. I was checking them. I could see it says Cannot connect to your resource. That means process is not able to reach it. Is your linked service test connection is getting successful? On storage account your Synapse Managed Identity has Storage Blob Data Contributor role for sure?

Could you please try by creating different linked service and use Account Key or some other authentication and see if that helps?

Also, kindly confirm is your workspace or storage account are part of any Virtual networks? If Yes, Is private link created for your storage account and Synapse?

Please Note, at my end I tried with MSI authentication as well inside linked service. With that also its working fine for me. So kindly cross check above and share updates on same. Looking forward to hear from you. Thank you.
WZIAFP 237 Reputation points

2022-09-30T11:02:46.127+00:00

Yes the Synapse Managed Identity does have Storage Blob Data Contributor

I have attempted to create a new linked storage connection using Account Key and the same error occurs.

The Workspace and storage account are in the same VNet and private links have been created between Storage account and Synapse.
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2022-09-30T11:08:04+00:00

Hi @WZIAFP , Thank you for details. One last reason which I can suspect at this moment by seeing above details, may be due to IR which we are using inside linked service.

Which type of IR we are using inside linked service here? Please note, we should not use AutoResolveIntegration Runtime IR. We should have a IR(self hosted IR or managed Virtual network IR) with in your virtual network and then use same inside linked service.

Could you please share details on above? Also, after using right IR make sure your test connection inside linked service is getting successful. I hope at this moment test connection inside linked service may be getting error right? Kindly confirm?
WZIAFP 237 Reputation points

2022-09-30T11:28:07.057+00:00

We are using an azure hosted IR and have enabled VNet Configuration. I have tested the connection and it is successful.
the managed private endpoint target resource id is correct.
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2022-09-30T12:48:50.227+00:00

Hi @WZIAFP , Could you please check is below setting done for your Workspace inside your secure storage account? Issue looks very strange to me, Test connection is successfully means it should work. Lets try below and see if that helps.

In Azure portal, navigate to your secured storage account. Select Networking from the left navigation pane. In the Resource instances section, select Microsoft.Synapse/workspaces as the Resource type and enter your workspace name for Instance name. Select Save.

Please let me know how it goes.
WZIAFP 237 Reputation points

2022-09-30T12:51:29.237+00:00

this has already been enabled
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2022-09-30T12:55:32.047+00:00

Thanks for confirming. I would encourage you to have a support ticket on this issue for deeper investigation to understand what missing here and get it resolved. Kindly check below link which helps with the steps to create a support ticket.
https://learn.microsoft.com/en-us/azure/azure-portal/supportability/how-to-create-azure-support-request

Between, I will also try to reach some internal team and see if they can share any insights on this. I will keep you posted if any updates. Feel free to share support case number here. So that I can also follow up with team internally. Thank you.
WZIAFP 237 Reputation points

2022-09-30T13:04:28.09+00:00

Support Request Number: 2209300050001318
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2022-09-30T15:28:19.24+00:00

Hi @WZIAFP , It seems pandas uses blob endpoint. Could you please try to create linked service with blob connector and then try to use that in your code and see if that helps?

Please let me know how it goes.
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2022-10-03T07:02:24.317+00:00

Hi @WZIAFP , Just checking you got a chance try above and share updates?
WZIAFP 237 Reputation points

2022-10-03T09:56:34.93+00:00

HI Shaik,

you are correct, pandas does use blob endpoints. We were aware of this and had created private endpoint on the storage account via the portal.
Unfortunately, Synapse does not recognise endpoints that are created outside Synapse. Once we created endpoints via Synapse Studio we were able to write to the storage account.

Thanks for all your assistance
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2022-10-03T10:19:49.09+00:00

Hi @WZIAFP , Great. Thanks for sharing this. I was confused even when private endpoint is then why its not able to write and I was clue less. Thanks for sharing this with community. This helps all the community members as well. Thank you.

---------
Please consider hitting Accept Answer button. Accepted answers help community as well.

Answer 2

WZIAFP 237

We found the issue to be associated with how private endpoints are managed and accessed from the studio.

All storage accounts have private endpoints created as part of ARM template which we deploy via the azure portal. Unfortunately Synapse does not recognise private endpoints that are created outside of the studio.

The resolution to this issue was to create additional private endpoint from within the synapse studio with access to the blob.core.windows.net endpoints.

David Beavon 991 Reputation points

2023-03-01T17:16:29.14+00:00

Did you create a support ticket to find out why the "dfs" endpoint won't work? This seems like buggy behavior.
Joao Silveira 5 Reputation points Microsoft Employee

2023-03-03T11:35:18.4866667+00:00

This needs to be added even thought the storage path used to access the data is via DFS the pandas function uses blob endpoint in the backend.

https://github.com/fsspec/adlfs/blob/39d40b3e7b33dfd3719f5ae62cca06c823d343a5/adlfs/spec.py#L536
David Beavon 991 Reputation points

2023-03-03T14:26:59.2166667+00:00

@Joao Silveira

Thanks for pointing that out. The code appears to be regularly maintained. It is odd that nobody has been motivated to work on fixing this. I'm guessing the problem is specific to Synapse Analytics.
I suppose this doesn't normally appear as a problem until people attempt to use the proprietary ("managed-vnet") technology in Synapse.

I've used private networking in Databricks Spark too (they support an injected vnet along with peering). This issue doesn't appear over there either. I'm guessing that they either implement their own python library for this stuff. Or based on the way they implement their vnet technology, it doesn't cause this interop problem to be surfaced to the users.

I think the most confusing thing is that the Microsoft Synapse samples encourage people to use the "dfs" host name, even though that is fraught with problems. Also the CSS support knows all about this problem, yet they don't provide public-facing information, which would avoid the need for customers to open support cases and work on them for several days. Depending on the month, I can spend up to 10% working on Azure support cases, for well-known bugs ( bugs that dozens of other customers have probably wasted their time on too. ) It is discouraging whenever I find I'm opening a support case for yet another well-known bug that Microsoft has not documented in the public.
Joao Silveira 5 Reputation points Microsoft Employee

2023-03-03T14:48:31.16+00:00

Indeed David, I agree with you that we enforce to use DFS has it is the one with more optimization to run your pipelines.

However, in this case this happens due to spark runtime run on Synapse is different then the on running in Databricks and is due to a abffs python package versioning that this happens.

I've come across this and I've raised to our PG to either see what is the best fix to apply in here or update our documentation to avoid in the future :)
David Beavon 991 Reputation points

2023-03-03T15:03:07.05+00:00

@Joao Silveira I think the people you need to talk to are Sateesh (CSS) and Martin Lee (PM).

Please start by asking for documentation (and/or fixing the samples they share for python programmers). I think they can update the docs in a very small fraction of the time that it takes to fix any bug (esp a vnet-related bug). An issue in Spark or an issue in this managed-vnet is probably something that takes a year or more to fix.

I forget to mention that the problem only appears in python but not in a .Net application for Synapse. Our .Net apps can interact with the dfs endpoint from Synapse-Spark without any problems. You might consider re-writing your spark stuff in a higher quality language like c#.net. ;-)
Braun, Bill 5 Reputation points

2023-08-14T14:58:22.86+00:00

@Joao Silveira I've run into this same exact problem where I get "ServiceRequestError: Cannot connect to host <storageaccountname>.blob.core.windows.net:443 ssl:True [Name or service not known]." only when trying to write to storage with a pandas dataframe from a notebook. I've double checked everything in this thread and even tried recreating the private endpoints from within the studio. We can read and write to the storage account from other places, just can't write using pandas in the notebook.

I agree with your assessment that the adlfs/spec.py needs to be updated. I looked for a relevant PR but couldn't find one. Did I miss it? If not, what I can do to get this rolling?
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Share via

Azure Synapse - PandasDataframe to_csv ADLS Gen2 write fail 443 error

2 answers

Your answer