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

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?

CodePudding user response：

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