Unable to load parquet file from azure storage account

Blasko, Dan W 40

I have created a credential and created a data source.

I have placed a csv and a parquet file on the azure DL G2 storage account.

I run this and it returns the first column of data as expected...........

SELECT id FROM OPENROWSET(

BULK 'calendar2.csv',

DATA_SOURCE = 'crm_ods_ds',

FORMAT = 'CSV',

firstrow = 2

) WITH (id varchar(18) 1

) AS DataFile;

I run either of these two and get file does not exist or access denied error message.

SELECT * FROM OPENROWSET(

BULK 'calendar.parquet',

DATA_SOURCE = 'crm_ods_ds',

FORMAT = 'PARQUET'

) AS DataFile;

SELECT * FROM OPENROWSET(

BULK 'calendar',

DATA_SOURCE = 'crm_ods_ds',

FORMAT = 'PARQUET'

) AS DataFile;

Msg 16535, Level 16, State 1, Line 69

Cannot bulk load. The file "https://ecrmdlstoragedev.blob.core.windows.net/crm-ods/calendar/calendar.parquet" does not exist or you don't have file access rights.

Since the csv file works, i do not see how it could be a permission issue since they are in the same folder.

Blasko, Dan W 40 Reputation points

2025-01-21T20:47:59.2366667+00:00
Please ensure the file path and name are accurate, including case sensitivity. The error message suggests the file may not exist or there could be access rights issues. Make sure that the file calendar.parquet is actually located in the specified directory.

The calendar.parquet is located in the same folder as the csv. And the csv query works.

Although the csv file works fine, but the parquet file might have different permissions. Make sure the credentials you are using have read access to the parquet file. Please check for the appropriate permissions for the storage account. Please make sure to check both at share-level and file-level permissions.

Both files were generated using data factory. I would assume this means the permissions are the same on both of them. I cannot see any differences.

Please ensure the appropriate Azure role is assigned for accessing the Parquet file. Suitable roles include Storage Blob Data Reader or Storage Blob Data Contributor.

Would accessing parquet need different roles than accessing csv?

Please ensure that the database scoped credential you created is properly configured to access the storage account. If you are using a Shared Access Signature (SAS), verify that it has the necessary permissions and is not expired.

I can access the csv, so the SAS and credential must be working.

Also, please make sure that you are specifying the correct format in your OPENROWSET function. The format should be set to 'PARQUET' as you have done but confirm that the file is indeed in the Parquet format.

tried creating a second file one with and one without the extension and get the same results.

Please verify that the DATA_SOURCE you are referencing (crm_ods_ds) is properly configured to point to the Azure storage account and is using the correct credentials.

Again, if the csv works, i am assuming the data source and the credential are ok.

I will try to generate another parquet file and attempt to load that.
Hari Babu Vattepally 1,635 Reputation points Microsoft Vendor

2025-01-22T16:08:27.5333333+00:00
Hi @Blasko, Dan W,

I understand that the permissions and roles required are generally the same as those for CSV files, but we have to ensure that the permissions are properly configured for each file type.

Although both files were generated using Data Factory and it is assumed they share the same permissions, it is advisable to verify the permissions specifically for the Parquet file. It is also possible that the permissions were not inherited correctly. Please ensure that your Microsoft Entra identity has the Blob Data Reader permission or the necessary permissions to List and Read access control lists (ACL) in Azure Data Lake Storage.

The roles for accessing Parquet and CSV files are typically the same. However, please ensure that your user has the necessary Azure roles, such as Storage Blob Data Reader or Storage Blob Data Contributor, specifically for Parquet files and make sure that the roles assigned to your identity has not changed or been restricted.

Since you are able to access the CSV file, the SAS and credential may be working fine. But it is still recommended to check the database scoped credential is correctly configured to access the storage account for the parquet file.

Please confirm that the file you are attempting to access is in Parquet format. You mentioned trying various file extensions, which is a good approach. Ensure that the OPENROWSET function is correctly set to use FORMAT='PARQUET'.

Since the CSV works, it looks like the data source and credentials are set up right. However, it's a good idea to double-check that the DATA_SOURCE is correctly pointing to the Azure storage account.

I would suggest testing the above suggestion. Unfortunately, if issue still persists, please consider checking the detailed logs for any specific error messages that might provide more insight into the problem.

For additional information, please refer the below links:

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-parquet-files?source=recommendations#read-parquet-file

I hope this helps, please let us if you have any queries in comments. We will be glad to assist you closely.
Hari Babu Vattepally 1,635 Reputation points Microsoft Vendor

2025-01-23T12:00:01.2433333+00:00

Hi @Blasko, Dan W,

Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Hari Babu Vattepally 1,635 Reputation points Microsoft Vendor

2025-01-24T03:32:14.8966667+00:00

Hi @Blasko, Dan W,

Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

Hari Babu Vattepally 1,635 Reputation points Microsoft Vendor

2025-01-21T14:31:14.98+00:00
Hi @Blasko, Dan W,

Greetings! Welcome to Microsoft Q&A Forum, thanks for posting your query here.

As per the provided error message, we see that you are encountering an issue loading a parquet file from Azure Storage using the OPENROWSET function. Since the CSV file loads successfully, the problem might be related to the specific configuration or permissions for the Parquet file.

Please follow the below suggestions and give a try:

Please ensure the file path and name are accurate, including case sensitivity. The error message suggests the file may not exist or there could be access rights issues. Make sure that the file calendar.parquet is actually located in the specified directory.

Although the csv file works fine, but the parquet file might have different permissions. Make sure the credentials you are using have read access to the parquet file. Please check for the appropriate permissions for the storage account. Please make sure to check both at share-level and file-level permissions.

Please ensure the appropriate Azure role is assigned for accessing the Parquet file. Suitable roles include Storage Blob Data Reader or Storage Blob Data Contributor.

Please ensure that the database scoped credential you created is properly configured to access the storage account. If you are using a Shared Access Signature (SAS), verify that it has the necessary permissions and is not expired.

Also, please make sure that you are specifying the correct format in your OPENROWSET function. The format should be set to 'PARQUET' as you have done but confirm that the file is indeed in the Parquet format.

Please verify that the DATA_SOURCE you are referencing (crm_ods_ds) is properly configured to point to the Azure storage account and is using the correct credentials.

After verifying all these aspects, if issues persist, please test with a different Parquet file to eliminate any file-specific problems. Also, please review the Azure storage logs for more detailed error messages that could offer further insights.

Please refer the below links for additional information:

Examples of bulk access to data in Azure Blob Storage

I hope the above suggestions resolves issue. If the issue persists, please feel free to reach us for the further assistance. We will glad to assist you closely.

Please do consider to “up-vote” wherever the information provided helps you, this can be beneficial to other community members.
Please sign in to rate this answer.
Blasko, Dan W 40 Reputation points

2025-01-24T11:48:05.9333333+00:00

I appreciate the suggestions, but nothing has worked so far. Still investigating.

Hari Babu Vattepally 1,635 Reputation points Microsoft Vendor

2025-01-24T19:28:05.7066667+00:00

Hi Blasko, Dan W,

If you're still having trouble after checking these points, I would suggest you to please try generating a new Parquet file. It might help rule out any file-specific issues.

Also, please verify if the sticky bit setting is enabled in your ADLS Gen2 storage account, as this can lead to authorization failures. You can check and modify this setting using the Azure CLI.

However, I would request you to please review the Azure Storage logs for any access denied errors or other relevant messages that might provide additional information about why the file cannot be loaded. This can help identify the specific issue.

Please refer the link for the information related to sticky bit settings in ADLS Gen2 storage account.

Hope this helps!

Hari Babu Vattepally 1,635 Reputation points Microsoft Vendor

2025-01-27T11:18:05.0933333+00:00

Hi Blasko, Dan W,

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.

Blasko, Dan W 40 Reputation points

2025-01-27T18:38:11.24+00:00

No messages in the azure storage activity log when I attempt to read the file.

Hari Babu Vattepally 1,635 Reputation points Microsoft Vendor

2025-01-28T17:24:31.5533333+00:00

Hi Blasko, Dan W,

I think the request didn't reach the Storage account, due to which you are not able to see the storage activity logs while reading the file. Could you please try generating a new Parquet file. Which might help rule out any file-specific issues.

Also, could you please verify if the sticky bit setting is enabled in your ADLS Gen2 storage account, as this can lead to authorization failures. You can check and modify this setting using the Azure CLI and give a try.

Hari Babu Vattepally 1,635 Reputation points Microsoft Vendor

2025-01-29T12:58:44.8166667+00:00

Hi Blasko, Dan W,

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.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Unable to load parquet file from azure storage account

1 answer

Your answer