dynamic in Snowflake , to pass in to stage

user_7587176 0
Viewed 90

I have a query that is select from an s3 bucket, but the value I need to select from changes each quarter - indicated below by {}. Is there anyway in snowflake I can write logic to be the most recent quarter

Select $1:date 
from
'@lake.lake./s3key/{variable}/data.json.gzip' );

I would want to variable = 2021Q3, and then next quarter 2022Q1 ect

Is this possible? Or will I have to get python involved

snowflake-cloud-data-platform sql
1 Answers

I tried to use identifier for stages, but it does not seem to work.

I guess you can use Stored Procedure to achieve this.

create or replace procedure get_stage_data(quarter varchar)
returns string
language javascript
as
$$
    var query = "Select $1::date from '@lake.lake./s3key/"+QUARTER+"/data.json.gzip";
    var stmt = snowflake.createStatement({sqlText: query});
    var res = stmt.execute();
    
    var retVal = '';
    while (res.next()) {
        retVal += res.getColumnValue(1) + "\n"
    }
    
    return retVal;
$$;

Maybe write data to a table then you can analyze later.

user_98793790
Related