Dynamic file paths for Snowflake stages

Question

I am copying data from a Snowflake table into an S3 external stage:

COPY INTO '@my_stage/my_folder/my_file.csv.gz' FROM (
    SELECT *
    FROM my_table
)

However this code runs daily and I don't want to overwrite my_file.csv.gz but rather keep all the historical versions. However I haven't found a way to create dynamic paths:

SET stage_name=CONCAT('@my_stage/my_folder/my_file', '_date.csv.gz');

COPY INTO $stage_name FROM (
    SELECT *
    FROM my_table
);

COPY INTO IDENTIFIER($stage_name) FROM (
    SELECT *
    FROM my_table
);

None of the later 2 queries work! My question: How can I create dynamic Stage paths in Snowflake? Thanks

I believe you can only do this via a Stored Proc: [Dynamic paths](https://stackoverflow.com/questions/61597017/how-can-i-add-datetime-stamp-to-zip-file-when-unload-data-from-snowflake-to-s3) — NickW, Sep 02 '20 at 12:02

score 3 · Accepted Answer · answered Sep 02 '20 at 22:31

Here's a stored procedure you can use and modify. Note that the line with the comment to modify your copy into statement uses backticks instead of single or double quotes. In JavaScript, that allows use of single or double quotes in the string, multi-line constants, and replacement tokens in the form ${variable_name}

create or replace procedure COPY_TO_STAGE(PATH string)
returns variant
language javascript
as
$$

class Query{
    constructor(statement){
        this.statement = statement;
    }
}

// Start of main function

var out = {};

// Change your copy into statement here. 
var q = getQuery(`copy into '${PATH}' from (select * from my_table);`);

if (q.resultSet.next()) {
    out["rows_unloaded"] = q.resultSet.getColumnValue("rows_unloaded");
    out["input_bytes"] = q.resultSet.getColumnValue("input_bytes");
    out["output_bytes"] = q.resultSet.getColumnValue("output_bytes");
} else {
    out["Error"] = "Unknown error";
}

return out;

// End of main function

function getQuery(sql){
    cmd1 = {sqlText: sql};
    var query = new Query(snowflake.createStatement(cmd1));
    query.resultSet = query.statement.execute();
    return query;
}
$$;

Once you define it, you can use SQL variables as the input if you want:

SET stage_name=CONCAT('@my_stage/my_folder/my_file', '_date.csv.gz');

call copy_to_stage($stage_name);

Thanks Greg! That's what I settled for in the end as well, a basic stored procedure written in Javascript. But I am really quite surprised that Snowflake doesn't support such a basic feature! — cedric, Sep 02 '20 at 23:06

score 1 · Answer 2 · edited Sep 02 '20 at 12:18

This won't work. Unfortunately using variables for identifiers does not work for stages. You might need to create a Stored procedure with Dynamic SQL: https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html#label-example-of-dynamic-sql-in-stored-procedure

So you can just call this procedure every day or generating a SP with several parameters for the path (Stage), the query which will be executed and the target filename.

Dynamic file paths for Snowflake stages

2 Answers2