CODEWITHSUNDEEP
snowflake-cloud-data-platform
cedric
cedric

Reputation: 358

Dynamic file paths for Snowflake stages

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

Upvotes: 2

Views: 3274

Answers (2)

Greg Pavlik
Greg Pavlik

Reputation: 11086

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);

Upvotes: 3

sxsn
sxsn

Reputation: 96

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.

Upvotes: 1

Related Questions