Unable to create table on synapse analytics

Question

While trying to create a table using my dedicated server pool, I get the error below:

External file access failed due to internal error: 'Error occurred while accessing HDFS: Java exception raised on call to HdfsBridge_IsDirExist. Java exception message: HdfsBridge::isDirExist - Unexpected error encountered checking whether directory exists or not: UnknownHostException:

This is an example of the query:

CREATE EXTERNAL TABLE [dbo].[NYC_Payroll_EMP_MD](
    [EmployeeID] [varchar](10) NULL,
    [LastName] [varchar](20) NULL,
    [FirstName] [varchar](20) NULL
)
WITH (
		LOCATION = 'payroll_emp_md.csv',
      DATA_SOURCE = [filesystem_storagename_dfs_core_windows_net],
      FILE_FORMAT = [SynapseDelimitedTextFormat]
)
GO

Answer 1

@Abodunde - Thanks for the question and using MS Q&A platform.

It seems like you are trying to create an external table in Synapse Analytics and you are getting an error message. The error message indicates that there is an issue with accessing the Hadoop Distributed File System (HDFS). This error can occur if the Synapse workspace's managed identity does not have the required permissions to access the storage account.

To resolve this issue, you need to ensure that you are using managed identity authentication and have granted Storage Blob Data Contributor or Storage Blob Data Reader permissions to the Azure Synapse workspace's managed identity.

OR

You must have access to the workspace with at least the Storage Blob Data Contributor access role to the ADLS Gen2 account or Access Control Lists (ACL) that enable you to query the files.

For more details, refer to Use external tables with Synapse SQL and Create and query external tables from a file in Azure Data Lake

Once you have granted the required permissions, you can try creating the external table again. If you still face any issues, please let me know.