How to handle spaces in external table location path

user_3426269 0
Viewed 143

I am trying to create an external table based on files stored in Azure Data Lake storage.

Everything works as expected for all paths except the ones containing spaces in them.

create or replace external table CG 
with location = '@stg/bc4ps/dbo/test$G_L Entry/'
file_format = ( FORMAT_NAME = csv);

Above code returns error message "invalid URL: '@stg/bc4ps/dbo/test$G_L Entry/'".

Single quotes don't seem to be escaping the spaces in external table location path.

Documentation page does not mention anything regarding spaces in paths either.

How do I get the paths with spaces working?

snowflake-cloud-data-platform
2 Answers

so yes, drop the single quotes and replace the space with %20

create stage stg  URL ='s3://buck_name/path_mc_pathy_path_path/';

create file format csv;

create external table CG
  with location = @stg/bc4ps/dbo/test$G_L%20Entry/
  file_format = ( FORMAT_NAME = csv);
user_439920

So for what ever reason CREATE EXTERNAL TABLE location is not accepting spaces when using Azure Data Lake Gen 2. The only way I got this working was with the help of a regex pattern.

I pointed the location to be the root folder and then used pattern to filter the right files.

create external table CG
  with location = @stg/bc4ps/dbo/
  file_format = ( FORMAT_NAME = csv)
  pattern = '.*G_L Entry.*.csv$';

Above regex does this:

.* - multiple characters
G_L Entry - my search string
.* - multiple characters
.csv - only takes csv extension
$ - indicates end of string

At this point in time I have only a limited dev environment with very little data to test with and am not able to check in what way regex pattern affects performance (if it does at all).

user_34262690
Related