Writing binary data to ADLS in Synapse notebook

Ljubo Jurkovic 66

Hi all,

Is it possible to write binary data to the ADLS in Synapse notebook using Pyspark? I retrieve data via an API call where the content type is application/excel (basically binary data) and want to save it to the ADLS, a specific location like this:

abfss://<container_name>@full_storage_account_name/<path_to_file>

It seems like a trivial task, but I can't find any examples of this.

Regards,

Ljubo

Vinodh247-1375 11,206 Reputation points

2024-04-19T06:03:25.1233333+00:00
Hi Ljubo Jurkovic,

Thanks for reaching out to Microsoft Q&A.

Is it possible to write binary data to the ADLS in Synapse notebook using Pyspark?

You can try the below

Writing Binary Data to ADLS as CSV/Parquet:

If your binary data is structured (e.g., tabular data), you can convert it to a PySpark DataFrame and then save it as a CSV or Parquet file in ADLS. Example: df.write.mode("overwrite").parquet("abfss://<container-name>@<storage-account-name>.dfs.core.windows.net/path/to/your/file.parquet") --> use .csv instead of .parquet to save as CSV

Do note that the spark by default generates part files with random names when writing data. If you want to specify a custom filename, you have to use pandas.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.
Harishga 3,425 Reputation points Microsoft Vendor

2024-04-19T06:20:05.99+00:00

Hi @Ljubo Jurkovic
Welcome to Microsoft Q&A platform and thanks for posting your question here.
Can you please follow the below documentation link.
https://subscription.packtpub.com/book/data/9781803231501/2/ch02lvl1sec09/reading-and-writing-data-from-adls-gen2-using-pyspark
I hope this information helps you. Let me know if you have any further questions or concerns.
Ljubo Jurkovic 66 Reputation points

2024-04-19T13:04:15.9833333+00:00

Thanks for the suggestions, but neither of these meet my requirements. The data I receive from the API is an Excel file data and I just need to save the content as a binary file to the ADLS. I can later use com.crealytics.spark.excel to read the file and manipulate the data, but the first part where I have to save the binary content is stumping me.
Vinodh247-1375 11,206 Reputation points

2024-04-20T10:55:05.4366667+00:00

Ljubo Jurkovic: first read your source data from excel into spark dataframe. Once you have the data in df, you can save it as a binary file to adls using 'write'.

Do you face error when trying the below? when you try to save the binary file?

df.write

.format("excel")

.mode("overwrite")

.save("adl://yourdatalake.azuredatalakestore.net/path/to/save/binary/file")
Harishga 3,425 Reputation points Microsoft Vendor

2024-04-22T12:20:10.5533333+00:00

@Ljubo Jurkovic
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Ljubo Jurkovic 66 Reputation points

2024-04-22T13:25:07.7+00:00

Hi,
My excel data comes from the API call (response.content). The first issue is to save this into a binary file so I can read it into the data frame. I didn't find a way to load the data frame directly from a variable that has the content (as oppose to read it from the file).
Second, even if I managed to load it somehow to the data frame, I would need to load multiple tabs.
Finally, the save method Vinodh suggested throws an error because of the format. If I remove the format option then it saves the data in multiple parquet files, not a single binary file.
Vinodh247-1375 11,206 Reputation points

2024-04-22T14:18:35.3866667+00:00

are you trying this to write your api response into file?

import requests

response = requests.get("https://api.site.com/data")

with open("output.bin", "wb") as file:

file.write(response.content)
Ljubo Jurkovic 66 Reputation points

2024-04-22T15:27:26.11+00:00

Yes, I am trying to save the response into a file, but how do I do that using the abfss protocol in pyspark? (abfss://<container_name>@full_storage_account_name/<path_to_excel_file>)
Vinodh247-1375 11,206 Reputation points

2024-04-23T04:57:28.9133333+00:00

yes, you are right. are you facing any error? what error do you face?

save the processed data to a file:

df.write.parquet("abfss://<container>@<yourstorageaccount>.dfs.core.storage.net/out.parquet")

reading data from abfss:

df = spark.read.option("multiline", True).option("mode", "PERMISSIVE").json("abfss://<container>@<yourstorageaccount>.dfs.core.storage.net/ex.json")
Ljubo Jurkovic 66 Reputation points

2024-04-25T14:24:42.44+00:00

I can't save the data as parquet because the original content format won't be preserved. Anyway, I decided to use the Azure Function to do the API calls and save the content to the ADSL. After that I can do with the data next steps.

Ljubo
Vinodh247-1375 11,206 Reputation points

2024-04-26T07:04:57.5233333+00:00

pls do share the steps once you have completed so it might be useful for someone else
Ljubo Jurkovic 66 Reputation points

2024-04-26T13:21:14.24+00:00

Basically I used C# libraries to issue the API call and save the content as a blob in the ADLS.
var client = new HttpClient();
var accessToken = await GetToken();
var request = new HttpRequestMessage(HttpMethod.Get, url); request.Headers.Add("Authorization", "Bearer " + accessToken);
var response = await client.SendAsync(request);
response.EnsureSuccessStatusCode();

var blobContainerClient = new BlobContainerClient(connectionString, container);
var responseContent = await response.Content.ReadAsByteArrayAsync();
Stream responseContentStream = new MemoryStream(responseContent);
string filePath = Path.Combine(folder, surveyName + ".xlsx").Replace("\", "/");
var blobClient = blobContainerClient.GetBlobClient(filePath);
await blobClient.UploadAsync(responseContentStream, overwrite: true);

I published the Azure Function and call it from the Synapse pipeline using the Web activity.

Cheers,
Ljubo

Accepted answer

Vinodh247-1375 11,206 Reputation points

2024-04-26T08:26:13.2966667+00:00

Hi Ljubo Jurkovic,

Thanks for reaching out to Microsoft Q&A.

Glad to know that you were able to figure this out and thanks for sharing the rootcause. By the way, Microsoft Q&A community has a policy that "The question author cannot accept their own answer, they can only accept answers by others.". Hence request you to accept this as an answer so we can close this thread. I am summarizing the issue and root cause you have found for it.

Issue Summary:

Write binary data to the ADLS in Synapse notebook using Pyspark? I retrieve data via an API call where the content type is application/excel (basically binary data) and want to save it to the ADLS, a specific location like this:

abfss://<container_name>@full_storage_account_name/<path_to_file>

Solution:

OP finally informed that he can't save the data as parquet because the original content format won't be preserved. He decided to use the Azure Function to do the API calls and save the content to the ADLS followed by the data next steps.

Workaround shared by op:

used C# libraries to issue the API call and save the content as a blob in the ADLS. var client = new HttpClient(); var accessToken = await GetToken(); var request = new HttpRequestMessage(HttpMethod.Get, url); request.Headers.Add("Authorization", "Bearer " + accessToken); var response = await client.SendAsync(request); response.EnsureSuccessStatusCode(); var blobContainerClient = new BlobContainerClient(connectionString, container); var responseContent = await response.Content.ReadAsByteArrayAsync(); Stream responseContentStream = new MemoryStream(responseContent); string filePath = Path.Combine(folder, surveyName + ".xlsx").Replace("", "/"); var blobClient = blobContainerClient.GetBlobClient(filePath); await blobClient.UploadAsync(responseContentStream, overwrite: true); published the Azure Function and call it from the Synapse pipeline using the Web activity.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

1 additional answer

Deleted

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

Comments have been turned off. Learn more