Use Cases for Dynamic SQL

Dynamic SQL allows you to create and manipulate a string, and then run the resulting string as a SQL statement. Snowflake supports dynamic SQL using the identifier keyword and table() function. There are some notable exceptions; however, where the Snowflake SQL parser currently does not support dynamic SQL.

For example, suppose you want to unload data to stage on a daily basis. To keep the data organized, you decide to put the unloaded data into paths with the current date, like this:

@my_stage/2021-01-22/data.csv  -- Data unloaded daily, organized by date
@my_stage/2021-01-23/data.csv

To accomplish this, you want a single line of SQL to run on a daily basis with the date dynamically generated. The problem is the path in a stage is a string literal that currently does not support using the identifier keyword, variables, or other dynamic SQL methods.

Snowflake Stored Procedures for Dynamic SQL

Writing a stored procedure is one option to run dynamic SQL along these lines. External calls from something like Python or Java can generate and run dynamic SQL. One disadvantage of external code to run dynamic SQL is that it requires external dependencies to schedule and run the code. Stored procedures elimination of any external dependencies offers a major advantage. Because Snowflake tasks also require no external dependencies, it’s possible schedule and run dynamic SQL with no external dependencies.

There are two options for stored procedures to run dynamic SQL. One option is to build the SQL statement inside the stored procedure code. While this approach has advantages, it has a major disadvantage. It requires the creation and maintenance of a new stored procedure for each dynamic SQL statement to run. The other approach is to generate SQL statements outside the stored procedure that a single general-purpose stored procedure runs.

That is the approach the following stored procedure uses. It’s intended to run general-purpose dynamic SQL generated outside the procedure and passed in as a parameter.

For example in the previously cited example, this allows running the daily data offload like this:

call run_dynamic_sql('copy into @mystage/' || current_date || 
     '/data.csv from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION file_format = (type = CSV)');

A General-Purpose Stored Procedure to Run Dynamic SQL

This stored procedure will run any SQL statement that can be run in a Snowflake stored procedure. The procedure will return a JSON object, either an error indication of a JSON document containing the rows from the execution. Since Snowflake JSON documents have a 16 Mb limit, the stored procedure should return only small result sets. Although intended to execute non-query statements, because it returns a JSON you can use it to convert select query results to JSON.

create or replace procedure RUN_DYNAMIC_SQL("sqlStatement" string)
returns variant
language javascript
execute as caller
as
$$

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

var out = {};
var query = getQuery(sqlStatement);
if (query.error == null) {
    return rsToJSON(query);
} else {
    return {"error": query.error};
}

function rsToJSON(query) {
    var i;
    var row = {};
    var table = [];
    while (query.resultSet.next()) {
        for(col = 1; col <= query.statement.getColumnCount(); col++) {
            row[query.statement.getColumnName(col)] = query.resultSet.getColumnValue(col);
        }
        table.push(row);
    }
    return table;
}

function getQuery(sql){
    var cmd = {sqlText: sql};
    var query = new Query(snowflake.createStatement(cmd));
    try {
        query.resultSet = query.statement.execute();
    } catch (e) {
        query.error = e.message;
    }
    return query;
}
$$;

-- Usage samples:

-- Create a table. Note the use of $$ to define strings to avoid problems with single quotes
call run_dynamic_sql($$ create or replace temp table foo(v variant) $$);

-- Run a select statement. While this SP is intended to execute non queries, it will also
-- Return a query's result set as a JSON as long as the JSON is under 16 MB in size.
call run_dynamic_sql($$ select * from "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."NATION" $$);

-- Show an example copying into a dynamically-named path in a stage:
-- Create a scratch stage for the test
create or replace stage my_stage;

-- Set a variable for the path
set today = current_date;

select $today;

-- Build the copy command. Use ' or $$ to terminate strings as convenient.
set copycommand = 'copy into @my_stage/' || $TODAY || '/data.csv' ||
$$ from "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."NATION" file_format = (type = CSV, field_optionally_enclosed_by = '"') $$;

-- Examine the statement to make sure it looks okay
select $copycommand;

-- Copy the file to the dynamic path
call run_dynamic_sql($copycommand);

create stage mystage;
call run_dynamic_sql('copy into @mystage/' || current_date || '/data.csv from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION file_format = (type = CSV)');