Synapse Analytics - How to create linked service to SQL Serverless Database?

Question

Help

I have recently started using Synapse Analytics to tap into my Azure Data Storage Gen 2 Container.

I have many .csv and .parquet files and find it very useful to create - within Synapse Analytics - SQL Databases which point to folders that contain many .csv or .parquet files - which I can now query via SQL scripts.

However, how can I use these in the Data Copy Activity to via SQL script access the data and move them around...

I cannot seem to create a linked service to my new SQL database in the Synapse workspace:

how do i now create a linked service I can use via data copy activity to query my table or execute a custom query?

I can only see the master database?

Answer 1

I managed to get this to work, likely not the most secure method.

But the trick was to create an SAS token in Azure Storage Account and use that when creating a SCOPED CREDENTIAL in the Synapse Analytics SQL Database:

CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '?sv=secret_key_here';

Then create an EXTERNAL DATA SOURCE, using the SCOPED Credential

IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'my_datasource_name') 
    CREATE EXTERNAL DATA SOURCE [my_datasource_name] 
    WITH (
        LOCATION = 'abfss://container_name@my_storage_account_name.dfs.core.windows.net' ,
        CREDENTIAL = AzureStorageCredential
    )

Then create the external table using the data source created above:

CREATE EXTERNAL TABLE [dbo].[revenue] (
    [ID] bigint,
    [Client ID] bigint,
    [Amount] nvarchar(4000),
    [Type] nvarchar(4000),
    )
    WITH (
    LOCATION = 'source/*.csv',
    DATA_SOURCE = [my_datasource_name],
    FILE_FORMAT = [SynapseDelimitedTextFormat]  
    )

FYI, this is my FILE_FORMAT:

IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseDelimitedTextFormat') 
    CREATE EXTERNAL FILE FORMAT [SynapseDelimitedTextFormat] 
    WITH ( FORMAT_TYPE = DELIMITEDTEXT ,
           FORMAT_OPTIONS (
             FIELD_TERMINATOR = ',',
             FIRST_ROW = 2,
             USE_TYPE_DEFAULT = FALSE
            ))