CODEWITHSUNDEEP
external-tablesazure-data-lake-gen2azure-synapse
Astha Mattoo
Astha Mattoo

Reputation: 11

Azure Synapse Analysis: Error while accessing the external table

Failed to execute query. Error: File 'https://track2gen2storage.blob.core.windows.net/\sourcedata\sample.csv' cannot be opened because it does not exist or it is used by another process.

we performed these steps:-

create database SalesdataDemo
use salesdataDemo
-----create master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = <Password>;
SELECT *
FROM sys.symmetric_keys AS SK
WHERE SK.name = '##MS_DatabaseMasterKey##';

CREATE DATABASE SCOPED CREDENTIAL ADL_User
WITH
IDENTITY = '<client_id>@<OAuth_2.0_Token_EndPoint>',Secret = <Key>

CREATE DATABASE SCOPED CREDENTIAL adls_credential
WITH IDENTITY ='SHARED ACCESS SIGNATURE',
SECRET = <azure_storage_account_key>

CREATE EXTERNAL DATA SOURCE adlsdatasource
WITH
( LOCATION = 'https://track2gen2storage.blob.core.windows.net',
CREDENTIAL = adls_credential
) ;

CREATE EXTERNAL FILE FORMAT adls_csv
WITH (
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS ( FIELD_TERMINATOR = ',', STRING_DELIMITER = '"', FIRST_ROW = 2 )
);


CREATE EXTERNAL TABLE sampledata ( <ColumnName><Datatype>)
WITH (
LOCATION = '/sourcedata/sample.csv',
DATA_SOURCE = adlsdatasource,
FILE_FORMAT = adls_csv
)

select * from sampledata

Upvotes: 1

Views: 4119

Answers (1)

vvasic-MSFT
vvasic-MSFT

Reputation: 219

I think the problem is your external table location is starting with /. Try changing it to:

CREATE EXTERNAL TABLE sampledata ( <ColumnName><Datatype>)
WITH (
LOCATION = 'sourcedata/sample.csv',
DATA_SOURCE = adlsdatasource,
FILE_FORMAT = adls_csv
)

Here is the document you can also take a look for reference: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/create-use-external-tables

One more question, why do you need database scoped credential named ADL_User?

Upvotes: 1

Related Questions