using a variable for a stage path in snowflake sql

Viewed 555

I'd like to keep my stage path in a variable when I run queries. It looks like there is support to get this working for tables (link), but I can't get it working for stages. Is this supported? Thanks.

CREATE STAGE "MY_DB"."EXTERNAL".AZURE_BLOBS
    URL = 'azure://example.blob.core.windows.net/my-csv-container' 
    CREDENTIALS = (AZURE_SAS_TOKEN = '****');

CREATE FILE FORMAT "INSIGHT_ETL"."EXTERNAL".CSV_GZ 
    TYPE = 'CSV' 
    COMPRESSION = 'GZIP' 
    FIELD_DELIMITER = ',' 
    RECORD_DELIMITER = '\n' 
    SKIP_HEADER = 1 
    FIELD_OPTIONALLY_ENCLOSED_BY = '\042' 
    TRIM_SPACE = FALSE 
    ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE 
    ESCAPE = 'NONE' 
    ESCAPE_UNENCLOSED_FIELD = '\134' 
    DATE_FORMAT = 'AUTO' 
    TIMESTAMP_FORMAT = 'AUTO' 
    NULL_IF = ('\\N');

//This works
SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, A.$1
FROM '@AZURE_BLOBS/' (FILE_FORMAT  => CSV_GZ) A
limit 10;

SET StagePath = '@AZURE_BLOBS/';

//This gets a compile error
SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, A.$1
FROM $StagePath (FILE_FORMAT  => CSV_GZ) A
limit 10;
1 Answers

Feel free to have the community correct me if I am wrong, but I don't believe this is supported today due to special parsing and handling for the stage path expression.

You can submit a feature request on the Snowflake Ideas page so that users can vote on it. The Snowflake Product Management Team actively monitors this page and features with a lot of votes often get priority consideration in a future release: https://community.snowflake.com/s/ideas

Related