Synapse dynamic copy command with wild card

Question

Synapse dynamic copy command with wild card

Moniker131 25

Having trouble using dynamic sql with the COPY command to import multiple files into a table. I have multiple environments, so the blob path is parameterized, and looks something like this:

'https://' + @storageaccountname +'.endpoint/container/folder/*.csv'

When I try to execute a copy command dynamically like this, it interprets /* as beginning a comment block. I cannot find a way to escape this comment block.

Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2023-11-17T08:00:27.56+00:00

Hi @Moniker131

Thank you for reaching out to us with your query.

Here is a similar thread that you can achieve using the same concept.

sql - Syntax error when using input parameter to create procedure - Stack Overflowsql - Syntax error when using input parameter to create procedure - Stack Overflow
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2023-11-18T06:36:50.3533333+00:00

@Moniker131 We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2023-11-19T16:59:21.3133333+00:00

@Moniker131 We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Moniker131 25 Reputation points

2023-11-19T17:06:30.6466667+00:00

Hi Smaran,

The stackoverflow post you replied with is not the same issue I am facing. The problem I have is when I execute the sql in my string, sql is interpreting the /* as the beginning of a comment block. I have tried escaping the asterisk, but I get a syntax error on the backslash. Please see my other comment with example code. You can try executing dynamic sql with wildcard path and see the result for yourself.

To reiterate, I am already able to use dynamic SQL to import files. I do this in many procedures already. The problem is when you use a wildcard in the path to import multiple files. It just seems there is no way to escape /*.

Accepted answer

1 additional answer

Your answer

Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2023-11-17T08:00:27.56+00:00

Hi @Moniker131

Thank you for reaching out to us with your query.

Here is a similar thread that you can achieve using the same concept.

sql - Syntax error when using input parameter to create procedure - Stack Overflowsql - Syntax error when using input parameter to create procedure - Stack Overflow
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2023-11-18T06:36:50.3533333+00:00

@Moniker131 We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2023-11-19T16:59:21.3133333+00:00

@Moniker131 We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Moniker131 25 Reputation points

2023-11-19T17:06:30.6466667+00:00

Hi Smaran,

The stackoverflow post you replied with is not the same issue I am facing. The problem I have is when I execute the sql in my string, sql is interpreting the /* as the beginning of a comment block. I have tried escaping the asterisk, but I get a syntax error on the backslash. Please see my other comment with example code. You can try executing dynamic sql with wildcard path and see the result for yourself.

To reiterate, I am already able to use dynamic SQL to import files. I do this in many procedures already. The problem is when you use a wildcard in the path to import multiple files. It just seems there is no way to escape /*.

Answer 1

Smaran Thoomu 24,110 Microsoft External Staff Moderator

Moniker131 To escape the /* in your dynamic SQL query, you can use the CHAR function to insert the ASCII code for the forward slash and asterisk characters. Here's an example:

SET @path = 'https://' + @storageaccountname + '.endpoint/container/folder/' + CHAR(47) + CHAR(42) + '.csv'
SET @sql = 'COPY INTO myTable FROM ''' + @path + ''' WITH (FORMAT=''CSV'')'
EXEC (@sql)

In this example, CHAR(47) inserts the ASCII code for the forward slash character, and CHAR(42) inserts the ASCII code for the asterisk character. This will allow you to use the wildcard in your path without it being interpreted as a comment block.

Moniker131 25 Reputation points

2023-11-21T17:50:44.43+00:00

Aha, that worked! Using the ASCII codes wasn't necessary. If you build the URL string first, and then concat that into the COPY command, it works. Thanks a lot!
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2023-11-21T18:21:46.7733333+00:00

@Moniker131 Glad to know it helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

Sedat SALMAN 14,180 MVP

In Azure Synapse Analytics, when using the COPY command for dynamic SQL with wildcards, you may encounter an issue where the wildcard character is misinterpreted. To correctly use wildcards in the file path for the COPY command, you should be aware of the following:

Wildcard path name matching is case-sensitive.
Wildcards can be escaped using the backslash character ().
Wildcard expansion is applied recursively, meaning all files matching the pattern in the specified directory and its subdirectories are included.

For example, to load all CSV files under a specified folder (including subdirectories), you can use a path like Account/Container/Folder/*.csv. In your case, ensure the wildcard is correctly formatted and escaped if necessary.

https://learn.microsoft.com/en-us/sql/t-sql/statements/copy-into-transact-sql?view=azure-sqldw-latest

Moniker131 25

You can't escape it with backslash. You get error "incorret syntax near ''"

Try executing something like this:

set @cmd = 'copy into schema.table
(json_data)
from ' + @path + '/container/*.json''
with (
	file_type = ''csv''
	,fieldterminator = ''0x0b''
	,fieldquote = ''0x0b''
	,rowterminator = ''0x0c''
	,credential=identity = ''managed identity'')
)'

exec (@cmd)

Moniker131 25 Reputation points

2023-11-16T16:10:07.9133333+00:00

Looks like my backslash was removed from my comment. Just put the backslash in front of the asterisk and you'll see what I mean.

Sedat SALMAN 14,180 MVP

maybe you can use an alternative approach

we can create a PowerShell script as a workaround. This script will list all files matching your wildcard pattern in the Azure blob storage and then construct a dynamic SQL command to COPY each file individually. This approach bypasses the need for the wildcard in the SQL command.

param(
    [string]$StorageAccountName,
    [string]$ContainerName,
    [string]$FolderPath,
    [string]$ConnectionString,
    [string]$SynapseWorkspaceName,
    [string]$SynapseDatabaseName,
    [string]$SynapseTableName
)

# Load Azure Storage module
Import-Module Az.Storage

# Create a context for the storage account
$ctx = New-AzStorageContext -ConnectionString $ConnectionString

# List all .csv files in the specified folder
$blobs = Get-AzStorageBlob -Container $ContainerName -Context $ctx -Prefix "$FolderPath/*.csv"

# Loop through each blob and construct the COPY command
foreach ($blob in $blobs) {
    $blobPath = 'https://' + $StorageAccountName + '.blob.core.windows.net/' + $ContainerName + '/' + $blob.Name
    $copyCommand = @"
COPY INTO $SynapseTableName
FROM '$blobPath'
WITH (
    FILE_TYPE = 'csv',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    CREDENTIAL = (IDENTITY = 'Managed Identity')
)
"@

    # Execute the COPY command in Azure Synapse
    Invoke-Sqlcmd -ServerInstance $SynapseWorkspaceName.database.windows.net -Database $SynapseDatabaseName -Query $copyCommand
}

Write-Host "Data copy completed."

Execute the script with the required parameters. It will list all CSV files in the specified folder and then execute a COPY command for each file individually.

Moniker131 25 Reputation points

2023-11-20T16:07:51.87+00:00

Thanks for the suggestion, but I am trying to avoid adding external code to our ETL.
Sedat SALMAN 14,180 Reputation points MVP

2023-11-20T20:42:45.76+00:00

ok let me work on it one more time

Share via

Synapse dynamic copy command with wild card

1 additional answer

Your answer