Regex Expression for Snowflake Pattern

Question

I have a list of files in the s3 bucket as below. I want to load only the current date files, for example, I want to get all file_a.csv which are under 2020/06/09, similarly get all file_b.csv under same 2020/06/09

I tried out the syntax but this gets me all files from all dates select metadata$filename from @stage/snflk/ts/(file_format=>CSV_SKIP_HEADER,pattern=>'.*/file_a.*[.]csv')

snflk/ts/ts-prod/2020/06/08/file_a.csv
snflk/ts/ts-prod/2020/06/09/file_a.csv
snflk/ts/ts-prod3/2020/06/08/file_a.csv
snflk/ts/ts-prod3/2020/06/09/file_a.csv
snflk/ts/ts-prod2/2020/06/08/file_a.csv
snflk/ts/ts-prod2/2020/06/09/file_a.csv
snflk/ts/ts-prod/2020/06/08/file_b.csv
snflk/ts/ts-prod/2020/06/09/file_b.csv
snflk/ts/ts-prod3/2020/06/08/file_b.csv
snflk/ts/ts-prod3/2020/06/09/file_b.csv
snflk/ts/ts-prod2/2020/06/08/file_b.csv
snflk/ts/ts-prod2/2020/06/09/file_b.csv

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

3

I want to load only the current date files

.*/file_a.*[.]csv

this gets me all files from all dates

The pattern (.*) supplied at the beginning here is a wildcard that will match everything available.

If you need to limit the date to a constant value, specify the constant values inside the regular expression pattern (i.e. ts-prod.*/2020/06/09/file_a\.csv, ts-prod.*/2020/06/09/file_b\.csv, etc.):

select
  metadata$filename
from @stage/snflk/ts/
(pattern=>'ts-prod.*/2020/06/09/file_a\.csv');

To iteratively test and write your regex patterns, you can use web tools such as Regex101, RegExr, etc.

P.s. You can also construct the pattern to use the current date dynamically, if you are trying to automate this:

-- Produces 'ts-prod.*/2020/06/11/file_a\.csv'
SET curr_dt_a=(
  SELECT 
    'ts-prod.*/' || 
    TO_VARCHAR(CURRENT_DATE(), 'YYYY/MM/DD') || 
    '/file_a\\.csv'
)

-- Referenced as a SQL variable in the PATTERN option
SELECT
  metadata$filename
FROM @stage/snflk/ts/
(PATTERN=>$curr_dt_a);

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 11 '20 at 03:22

Thanks, the above regex didn't work out .. Made slight changes and it worked `select metadata$filename from @stage/snflk/ts/ (pattern=>'.*/2020/06/09/file_a.csv');` – Kar Jun 11 '20 at 03:30
Your detailed explanation is worth a lot. Thanks – Kar Jun 11 '20 at 03:48
This solution only works for a specific date. What if the files to be loaded are between 2 different dates? I just posted this question and wonder if it is possible to do that. https://stackoverflow.com/questions/65622872/snowflake-how-to-load-files-through-stage-from-ms-azure-using-specific-pattern – Michael Zeng Jan 08 '21 at 03:11

Regex Expression for Snowflake Pattern

1 Answers1