Unable to query deltalake file in Azure synapse

Question

I have created a synapse link for dataverse for F&O data but when I tried query deltalake file it is throwing the error "Content of directory on path 'https://hslsynapsedatalake.dfs.core.windows.net/dataverse-hsd365fouat-unq59c1243eb00fef119f836045bdd1c/purchline/createdonpartition=/.csv' cannot be listed."

. I have all the required access and even I can query the .csv tables

I can see table purchline(.csv) and purchline_partitioned(parquet) , purchline is giving the result but the partitioned table is throwing this error

Answer 1

@Athira Reghuraman

Thanks for using Microsoft Q&A forum and posting your query.

It looks like you’re encountering a common issue when querying Delta Lake files in Azure Synapse. The error message “Content of directory on path cannot be listed” typically indicates a problem with permissions or the structure of the files.

Here are a few things you can check to troubleshoot this issue:

1. Permissions: Ensure that your Azure Synapse workspace has the necessary permissions to access the Delta Lake files. You might need to set up server-scoped credentials or database-scoped credentials with a Managed Identity or Shared Access Signature (SAS) that includes both Read and List permissions.
2. File Structure: Verify that the Delta Lake folder structure is correct. The root folder should contain a _delta_log directory. If this folder is missing, it may not be recognized as a Delta Lake format.
3. Schema Consistency: If you’re querying multiple files, ensure that all files have the same schema. Inconsistent schemas can lead to errors when trying to read from a directory.
4. Query Syntax: When using the OPENROWSET function, make sure you’re pointing to the correct path and specifying the format as DELTA. For example:

   SELECT * FROM OPENROWSET(BULK 'https://your-path-to-delta/', FORMAT='delta') AS rows;

5. Check for Hidden Files: Sometimes, hidden files or success markers can cause issues. Ensure that there are no unexpected files in the directory that might interfere with the query.

Hope this helps. Do let us know if you any further queries.