CODEWITHSUNDEEP
amazon-s3etlsnowflake-cloud-data-platform
Randy B.
Randy B.

Reputation: 453

How to handle spaces in S3 File Path in Snowflake

I am trying to specify a specific filepath within our S3 buckets for Snowflake ingestion.

copy into DATABASE.SCHEMA.TABLE
  from @SANTABA_ETL_STAGE/Table Name/2020/04/15
  pattern='.*[.]csv'
  on_error = 'continue'
  file_format = csv_etl;

But because of the space in this specific filepath I just get this error.

SQL compilation error: syntax error line 2 at position 36 unexpected '/'.

I am not finding any easy way to handle this in the documentation. Is there one?

Upvotes: 3

Views: 3254

Answers (1)

user13472370
user13472370

Reputation:

While this appears to be resolved over comments, I just wanted to point out the official documentation that covers this scenario.

Per the documentation for COPY INTO statements, for using any special characters including space the use of single quotes surrounding the stage or location literal is required.

To quote the relevant portion (as of early 2020):

The URI string for an external location (Amazon S3, Google Cloud Storage, or Microsoft Azure) must be enclosed in single quotes; however, you can enclose any string in single quotes, which allows special characters, including spaces, to be used in location and filenames. For example:

-- Stages
copy into '@mystage/path 1/file 1.csv' from mytable;
copy into '@%mytable/path 1/file 1.csv' from mytable;
copy into '@~/path 1/file 1.csv' from mytable;

-- S3 bucket
copy into 's3://mybucket 1/prefix 1/file 1.csv' from mytable;

-- Azure container
copy into 'azure://a.blob.core.windows.net/c/f/file 1.csv' from mytable;

Upvotes: 1

Related Questions