Ive been playing around with Synapse today and I spent ages trying to troubleshoot an external table not working and trying all kinds of elaborate permission troubleshooting for something which was a stupid simple error.
The error I got was:
Started executing query at Line 35BatchId: 0, record affected: -1(Encountered empty schema.)External table 'dbo.HelloWorldPopulations' is not accessible because location does not exist or it is used by another process. Total execution time: 00:00:00.448
It turns out the url for the location is case sensitive.
I should have used:
CREATE EXTERNAL DATA SOURCE [HelloWorld] WITH ( LOCATION = 'https://[my_dl].dfs.core.windows.net', CREDENTIAL = [WorkspaceIdentity] ) CREATE EXTERNAL TABLE [dbo].[HelloWorldPopulations] ( C1 nvarchar, C2 nvarchar, C3 nvarchar, C4 nvarchar ) WITH ( LOCATION = 'blogmsdemofs/raw/helloworld/SloveniaTestData.csv', DATA_SOURCE = [HelloWorld], FILE_FORMAT = [PopulationFile] ) ;
Hopefully this saves someone else wasting a few hours trying to work this out