Cannot create external table in Synapse from ADLS2

Question

When I right click on a parquet file within my linked ADLS storage account in Synapse Studio in order to create an External Table, I get the following error message. I have already tried to walk through this process: https://learn.microsoft.com/en-us/azure/synapse-analytics/security/how-to-set-up-access-control.

ERROR MESSAGE
Failed to detect schema.
Please review and update the file format settings to allow file schema detection. Details: Failed to execute query. Error: File 'https://oeadevadl1.dfs.core.windows.net/3-curated/authoritative/CuratedDptDiv.parquet' cannot be opened because it does not exist or it is used by another process. The batch could not be analyzed because of compile errors. . If the issue persists, contact support and provide the following id : f4d8b76d-1c65-4c39-b1e2-665e36cbdfc8. Tracking id: 137723a4-dbc0-42a5-a76f-21f2f512aae5

Answer 1

Hi @Rappel Schmid, Russ B (DOA) ,

Thanks for your explanation. Let's start troubleshooting something. Can you run the following queries step by step, in perhaps a new serverless database, and say where you get an error. In step 6 and 8 you need to change {storage-account-name} with your own storage account name.

-- 1  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'qwe@##@5324aSD127123g'  
GO  
  
-- 2  
CREATE DATABASE SCOPED CREDENTIAL WorkspaceIdentity WITH IDENTITY = 'Managed Identity'  
GO  
  
-- 3  
CREATE LOGIN TestUser WITH PASSWORD = 'abcdef123!@#'  
GO  
  
-- 4  
CREATE USER Test FOR LOGIN TestUser  
GO  
  
-- 5  
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::WorkspaceIdentity TO Test  
      
-- 6  
IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'data_{storage-account-name}_dfs_core_windows_net')   
    CREATE EXTERNAL DATA SOURCE [data_{storage-account-name}_dfs_core_windows_net]   
    WITH (  
        LOCATION   = 'https://{storage-account-name}.dfs.core.windows.net/data',  
        CREDENTIAL = WorkspaceIdentity   
    )  
GO  

 -- 7   
IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseParquetFormat')   
	CREATE EXTERNAL FILE FORMAT [SynapseParquetFormat]   
	WITH ( FORMAT_TYPE = PARQUET)  
GO  

-- 8  
CREATE EXTERNAL TABLE TestTable (  
	[Id] INT,  
	[FirstName] VARCHAR(7),  
	[Lastname] VARCHAR(38,18),  
	)  
	WITH (  
	LOCATION = 'test.parquet',  
	DATA_SOURCE = [data_{storage-account-name}_dfs_core_windows_net],  
	FILE_FORMAT = [SynapseParquetFormat]  
	)  
GO  

-- 9  
SELECT TOP 100 * FROM TestTable  
GO  

Answer 2

If you aren't using a Managed Identity, Synapse uses passthrough authentication. Your account would need Storage Blob Data Contributor role on the Storage Account (even if you are already an Owner).