query snowflake s3 external file

Question

I've created an S3 [external] stage and uploaded csv files into \stage*.csv folder. I can see stage content by doing list @my_stage.

if I query the stage select $1,$2,$3,$4,$5,$6 from @my_s3_stage it looks like I'm randomly picking up files.

So I'm trying to select from specific file by adding a pattern PATTERN => job.csv

This returns no results.

Note: I've used snowflake for all of 5 hours so pretty new to syntax

add the entire stage and file name select $1,$2,$3,$4,$5,$6 from @my_s3_stage/job.csv.gz — Rajib Deb

Marcel Marcel · Accepted Answer · 2020-11-05T07:49:35

For a pattern you can use

select t.$1, t.$2 from @mystage1 (file_format => 'myformat', pattern=>'.*data.*[.]csv.gz') t;

The pattern is a regex expression. For a certain file you have to add the file name to the query like this:

select t.$1, t.$2 from @mystage/data1.csv.gz;

If your file format is set in your stage definition, you don't need the file format-parameter.