2

I am trying to open a Parquet files that is generated by Stream Analytics and stored in Azure Datalake V2. I have connected datalake and Synapse successfully, but I keep getting "https://datalake.dfs.core.windows.net/eh-orca-iot-pack-parquet/packdata/3134_A1_P1/2020/12/01/test.parquet"

I have Global Administrator in the Azure Tenant the datalake and synapse is in, and I renamed the file in question to test.parquet using Azure Storage Explorer so I was sure Stream Analytics was not holding onto it.

SELECT
    TOP 100 *
FROM
    OPENROWSET(
        BULK 'https://stiotdata.dfs.core.windows.net/eh-orca-iot-pack-parquet/packdata/3134_A1_P1/2020/12/01/test.parquet',
        FORMAT='PARQUET'
    ) AS [result]

The documentation gives two options, that something else is holding the file (checked and not the case, hence rename) and access rights to my own account, but I have Global Administrator. Is there anything else to check?

ruffen
  • 1,695
  • 2
  • 25
  • 51
  • "Admin rights" are a bit nebulous and may not cover this scenario. In the ADLS account IAM, make sure you have Storage Blob Data Contributor or Storage Blob Data Owner role. – Joel Cochran Dec 02 '20 at 18:17
  • I have Global Administrator, I am also able to view the files in storage explorer, as well as selecting the files in Synapse.Edited the question to specify Global admin. I also renamed one file to test.parquet just to test, still getting same error on this file. – ruffen Dec 02 '20 at 20:05
  • Global Administrator is a Tenant role and does not directly apply to Azure resources. Did you try one of the roles I suggested? – Joel Cochran Dec 03 '20 at 14:19
  • Global admin and contributor was listed on the resource, I was always able to change files through other software as well. The problem went away after removing the link and adding it again, it was an old link so don't know what happened there. – ruffen Dec 07 '20 at 15:28
  • @ruffen In my case I was using wrong container name. I was using this [Microsoft tutorial](https://learn.microsoft.com/en-us/azure/synapse-analytics/get-started-analyze-sql-on-demand) that has named the container as `users`. I had used a different name `mycontainer` but in the code shown in the tutorial I forgot to change `users` to `mycontainer`. Once I fixed the naming issue, it started working fine. If someone else has the same error , they may also want to double check names in the url of the path inside code. – nam Oct 02 '21 at 01:30

1 Answers1

1

It is as the documentation behind the error says. Tested it myself, had to read twice or thrice.

You need to assign your own Azure Active Directory identity at least the role of Storage Blob Data Reader.

For me this solved the error!

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/resources-self-help-sql-on-demand#query-fails-because-file-cannot-be-opened

Arthur
  • 11
  • 3
  • it would be helpful to explain where to go to actually do that as i am looking at the docs and it is not obvious. – simbo1905 Jan 03 '23 at 09:13