CODEWITHSUNDEEP
regexsnowflake-cloud-data-platform
Nik
Nik

Reputation: 371

Snowflake External Stage file name pattern match not working

I am trying to run a Select query on multiple files placed on External Stage(s3). The file names are

SAMPLE_FILE_NAME_20201014_155022.csv
SAMPLE_FILE_NAME_20201016_092711.csv
SAMPLE_FILE2_NAME2_20201014_155022.csv
SAMPLE_FILE2_NAME2_20201016_092711.csv

Want to select only the files with name SAMPLE_FILE_NAME.* If I query like below then it is not able to find any files or data

select $1 as col1, $2 as col2, $3 as col3, $4 as col4
FROM @ROLE1_ID1.LOCATION_ESTG (file_format => 'SEMICOLON', PATTERN => '.*SAMPLE_FILE_NAME.csv.*') t;

If I do like below then both SAMPLE_FILE_NAME & SAMPLE_FILE2_NAME2 get selected and I get incorrect data

select $1 as col1, $2 as col2, $3 as col3, $4 as col4
FROM @ROLE1_ID1.LOCATION_ESTG (file_format => 'SEMICOLON', PATTERN => '.*_20201014_155022.csv') t;

I tried several combinations of REGEXP but they don't seem to work while reading the external stage. For Ex. I tried 'SAMPLE_FILE_NAME.*\.csv' which didn't work. What can be the correct expression to filter SAMPLE_FILE_NAME* and make the select work

Upvotes: 1

Views: 2342

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626845

The .*SAMPLE_FILE_NAME.csv.* pattern does not work because . before csv only matches any single char, while there are many more chars between NAME and csv.

You can use

'SAMPLE_FILE_NAME_.*\\.csv'

If the path includes directory name, you might need to add .* at the start and use

'.*SAMPLE_FILE_NAME_.*\\.csv'

See the regex demo

Note that to match a literal dot, you need to escape it with a backslash, and since it can form string escape sequences in the string literals, it needs doubling.

Upvotes: 2

Related Questions