CODEWITHSUNDEEP
snowflake-cloud-data-platform
Xi12
Xi12

Reputation: 1213

Dynamic stage path in snowflake

I have a stage path as below

copy into table1 as (
select $1:InvestorID::varchar as Investor_ID from  @company_stage/pbook/2022-03-10/Invor/part-00000-33cbc68b-69c1-40c0-943c-f586dfab3f49-c000.snappy.parquet

)

This is my S3 location company_stage/pbook/2022-03-10/Invor,

I need to make this dynamic:

I) I need to change this "2022-03-10" folder to current date

II)it must take all parquet files in the folder automatically, without me mentioning of filename. How to achieve this?

Upvotes: 0

Views: 1561

Answers (1)

Maja F.
Maja F.

Reputation: 333

Here is one approach. Your stage shouldn't include the date as part of the stage name because if it did, you would need a new stage every day. Better to define the stage as company_stage/pbook/.

To make it dynamic, I suggest using the pattern option together with the COPY INTO command. You could create a variable with the regex pattern expression using current_date(), something like this:

set mypattern = '\.*'||to_char(current_date(), 'YYYY-MM-DD')||'\.*';

Then use this variable in your COPY INTO command like this:

copy into table1 as (
select $1:InvestorID::varchar as Investor_ID from  @company_stage/pbook/ pattern = $mypattern
)

Of course you can adjust your pattern matching as you see fit.

Upvotes: 1

Related Questions