T-SQL OPENROWSET - Datasource

Question

I am using T-SQL to query a storage account container in the Azure Data Lake Gen 2.

At the moment I have the following query where I supply the entire path to the CSV file:

SELECT * 
FROM  OPENROWSET(BULK 'https://xxxxxxxxxx.blob.core.windows.net/[CONTAINER]/[GUID]/CustomerActivity/*.csv', 
                 format = 'CSV', parser_version = '2.0')

How do I use the 'datasource' parameter to specify the storage account? I tried using datasource ='https://xxxxxxxxxx.blob.core.windows.net' but it didn't work

Answer 1

Turns out I had to created an external data source in Azure SQL to read data using serverless SQL pool in Azure Syapse Analytics

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop

I used the following script:

CREATE EXTERNAL DATA SOURCE [MyDataSource] WITH (LOCATION = N'https://XXXXXXXX.dfs.core.windows.net/[CONTAINER]/[GUID]', CREDENTIAL = [cred])

and was able to reference it using data_source for OPENROWSET