python - Snowflake: how to load files through stage from MS Azure using specific pattern?

Question

I need to load files from MS Azure to Snowflake tables on a weekly basis, and I wonder if I can find a more efficient way of doing it

For example, if i want to load the files in this dir:

my_folder/food/20200101.csv 
... 
my_folder/food/20201201.csv

and name of the stage is my_stage

then if i want to load the files for the month of June, the query would be:

select * from @my_stage (pattern => 'my_folder/food/202006.*.csv')

My question is, what if I want to load the files between 11/28/2020 and 12/05/2020?

The current method I am using is to separate the pattern like this:

pattern => 'my_folder/food/2020112[8-9].*.csv|my_folder/food/2020113[0-1].*.csv|my_folder/food/2020120[1-5].*.csv

Is there a way to make it simpler? like using >= and <= in the pattern?

Thanks and please let me know if my question lacks clarity. :)

question from:https://stackoverflow.com/questions/65622872/snowflake-how-to-load-files-through-stage-from-ms-azure-using-specific-pattern

Answer 1

Inside the PATTERN-clause you can use Regex-expressions. I think using a Regex-expression for a date range may become quite complex and your solution is the simplest one. Maybe also looping over the files may help in your case.

More infos about the PATTERN-clause: https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html#optional-parameters