Polybase using SQL Server 2019 RM CU4 - Error while querying external tables pointing to a folder of ADLS Gen 2 storage account using wasb end point

Question

Scenario - Created external tables using Polybase on a Azure VM with SQL Server 2019 RM CU4 version. The data source for these external tables is wasb endpoint of a container associated to ADLS Gen 2 storage account.

Error - When simple select * from External table is ran, it runs for 15 mins and returns below error.
Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL" for linked server "(null)". External file access failed due to internal error:
'Error occurred while accessing HDFS: Java exception raised on call to HdfsBridge_GetDirectoryFiles. Java exception message:
java.util.NoSuchElementException: An error occurred while enumerating the result, check the original exception for details.'

Environment -

• Azure VM of E series in West US 2. Installed SQL server 2019 RM CU4.
• ADLS storage account also in WEST US 2.
• The file format is Parquet.
• Within the container of ADLS Gen2 account there are multiple folders and sub folders.
• Data is distributed to multiple parquet files within each of sub folders.

Steps tried to figure out the root cause -

• The query works fine when the external table is pointed to a particular parquet file instead of root folder. This rules out any credential or access issues.
• Limited the sub folder to just two parquet files and with one column. Running in to same error.

This setup has been working for close to two months. No changes have been made to the ADLS Gen 2 account or the Azure VM but from June 26th 2020 onwards running to this issue. Appreciate any help on this.

Attached the Screenshot

Answer 1

@AbhilashIbrahimpatnam-4589 Apologies for the delay in response.

Iaas SQL server do not support ABFSS endpoint. Only Azure SQL Data-warehouse. So the solution to the issue is to use blob storage instead of ADLS Gen2.

Hope this was helpful.

Please 'Accept as answer' if it helped, so that it can help others in the community looking for help on similar topics