Azure Synapse Serverless CETAS error "External table location is not valid"

Question

I'm using Synapse Serverless Pool and get the following error trying to use CETAS

Msg 15860, Level 16, State 5, Line 3
External table location path is not valid. Location provided: 'https://accountName.blob.core.windows.net/ontainerName/test/'

My workspace managed identity should have all the correct ACL and RBAC roles on the storage account. I'm able to query the files I have there but is unable to execute the CETAS command.

CREATE DATABASE SCOPED CREDENTIAL WorkspaceIdentity WITH IDENTITY = 'Managed Identity'
GO

CREATE EXTERNAL DATA SOURCE MyASDL
WITH (    LOCATION   = 'https://accountName.blob.core.windows.net/containerName'
   ,CREDENTIAL = WorkspaceIdentity)
GO

CREATE EXTERNAL FILE FORMAT CustomCSV  
WITH (  
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS (ENCODING = 'UTF8')    
);  
GO

CREATE EXTERNAL TABLE Test.dbo.TestTable
WITH (
LOCATION = 'test/',  
DATA_SOURCE = MyASDL,
FILE_FORMAT = CustomCSV  
) AS
WITH source AS
(
SELECT
    jsonContent
    , JSON_VALUE (jsonContent, '$.zipCode') AS ZipCode
FROM
    OPENROWSET(        
        BULK '/customer-001-100MB.json',
        FORMAT = 'CSV',
        FIELDQUOTE = '0x00',
        FIELDTERMINATOR ='0x0b',
        ROWTERMINATOR = '\n',
        DATA_SOURCE = 'MyASDL'
    )
    WITH (
        jsonContent varchar(1000) COLLATE Latin1_General_100_BIN2_UTF8
    ) AS [result]
)

SELECT ZipCode, COUNT(*) as Count 
FROM source
GROUP BY ZipCode
;

If I've tried everything in the LOCATION parameter of the CETAS command, but nothing seems to work. Both folder paths, file paths, with and without leading / trailing / etc.

The CTE select statement works without the CETAS.

Can't I use the same data source for both reading and writing? or is it something else?

I'm not aware of such a limitation. If you can Read from the Data Source but not Write to the Data Source, it's likely an IAM issue. Make sure the managed identity has Storage Blob Data Contributor (not Reader). If that is not an issue, you can try creating the Scoped Credential with a SAS token instead. — Joel Cochran, Apr 26 '22 at 14:52

score 2 · Accepted Answer · answered Apr 27 '22 at 06:30

The issue was with my data source definition.

Where I had used https:\\ when I changed this to wasbs:\\ as per the following link TSQL CREATE EXTERNAL DATA SOURCE

Where it describes you have to use wasbs, abfs or adl depending on your data source type being a V2 storage account, V2 data lake or V1 data lake

Azure Synapse Serverless CETAS error "External table location is not valid"

1 Answers1