SQL scripting: Live in Snowflake

Snowflake Scripting is a SQL extension to facilitate migrations and your development of stored procedures. Find here my notes and basic scripts to get you started.

A basic SQL script with Snowflake Scripting.

My goal in this post is to review some basic scripts to get you started. Note that this is the initial public preview release — test these scripts, check the docs, and share your feedback with us. And don’t forget to check the Snowflake Scripting docs.

Watch on Youtube

Why you would want to write SQL scripts with Snowflake Scripting

Stored procedures simplification

You can simplify your existing JS scripts with SQL scripts. For example, check the difference on how I would have answered this Stack Overflow question with a SQL script:

Replacing a JS stored procedure with a SQL stored procedure

Migrations

Check out the post “Welcome Snowflake Scripting” from Mauricio Rojas at Mobilize. It goes deeper into variable declarations, passing variables, reading into variables, and conditional logic. And they are looking forward to bring these improvements into their migration product SnowConvert.

As a matter of fact, Mobilize.Net SnowConvert is being updated so you can start modernizing your Oracle, Teradata, and SQL Server to Snowflake Scripting. So we hope you enjoy it as much as I am enjoying it.

Welcome Snowflake Scripting!

Basic scripts

Find here some basic scripts that you can use as starting templates for your own:

Run a loop 200 times

execute immediate $$
declare
    x int default 0;
begin
    loop
        x := x + 1;
        if (x >= 200) then break;
        end if;
    end loop;
    return x;
end;
$$;

• I love the ability to either create a procedure to call later, or just have an execute immediate block to debug interactively.
• Runs in about 200ms (<1 ms per iteration at scale).

Execute dynamic SQL queries on a loop

execute immediate $$
declare
    x int default 0;
    res resultset;
begin
    loop
        res := (execute immediate 'select 1 +' || x);
        let cur cursor for res;
        open cur;
        fetch cur into x;
        if (x >= 20) then break;
        end if;
    end loop;
    return x;
end;
$$;

• Snowflake Scripting has support for cursor, which it’s used here to retrieve the results of a query.
• You can execute immediate any hand crafted queries within the script.
• A resultset contains the results of a query (which the procedure could also return with a table() envelope).
• Runs in about 1.1s, which means that each query executes in <55ms.

Run many inserts

execute immediate $$
declare
    x int default 0;
begin
    create or replace table xxx(i int);
    loop
        x := x + 1;
        execute immediate 'insert into xxx(i) values (' || x || ')';
        if (x >= 20) then break;
        end if;
    end loop;
    return x;
end;
$$;

• Inserting many individual rows is not a recommended pattern — and you can use this script to benchmark how long it takes to run 20 successive inserts.
• Runs in ~9.4s, which shows that each insert takes about 0.5s as it gets persisted.

Working with the results of DESCRIBE TABLE:

This is a stored procedure to get a list of all columns in a table, to later use in a query — in lieu of the SELECT * EXCEPT() syntax:

create or replace procedure cols_except(table_name varchar, except varchar)
returns varchar
language sql as 
begin
    describe table identifier(:table_name);
    return (
        select listagg("name", ', ') cols_except
        from table(result_scan(last_query_id())) 
        where not array_contains("name"::variant, (split(:except, ','))) 
    );
end;

You can use it to get the columns to select for, after eliminating the values that match the except clause:

call cols_except('snowflake_sample_data.tpch_sf1.nation', 'N_NAME,N_REGIONKEY'); 

Capturing all the columns in a select statement except one from a table in Snowflake

Wanted syntax

Snowflake scripting is in preview and looking forward for your feedback.

Hopefully we’ll soon see extended syntax for execute immediate as the above scripts could have been shorter with the ability to request execute immediate '' into and execute immediate (?) with .

Performance

I was pleasantly surprised looking at the performance of these scripts, compared to my previous experience working with BigQuery. If you want to run your own comparisons or improve my scripts, here they are:

BigQuery equivalent SQL scripts

DECLARE x INT64 DEFAULT 0;
LOOP
  SET x = x + 1;
  IF x >= 200 THEN
    LEAVE;
  END IF;
END LOOP;
SELECT x;
--
DECLARE x INT64 DEFAULT 0;
LOOP
  execute immediate 'select 1 +' || cast(x as string) into x;
  IF x >= 20 THEN
    LEAVE;
  END IF;
END LOOP;
SELECT x;
--
DECLARE x INT64 DEFAULT 0;
create or replace table temp.xxx(i int);
LOOP
  SET x = x + 1;
  execute immediate 'insert into temp.xxx (i) values(?)' using x;
  IF x >= 20 THEN
    LEAVE;
  END IF;
END LOOP;
SELECT x;

Next steps

• Try out SQL templates with the open cursor using (v1, v2) syntax:

• Explore advanced features, like table return support, and secure sharing of stored procedures.
• Check how to iterate over the results of a SHOW command on Stack Overflow:

Snowflake Scripting in SQL - how to iterate over the results of a SHOW command?

• Check how to return a table created in memory while looping:

SQL Snowflake Scripting - How can I return a table created in memory while looping?

Documentation

• https://docs.snowflake.com/en/developer-guide/snowflake-scripting/index.html
• https://docs.snowflake.com/en/sql-reference-snowflake-scripting.html

Want more?

• Try this out with a Snowflake free trial account — you only need an email address to get started.

I’m Felipe Hoffa, Data Cloud Advocate for Snowflake. Thanks for joining me on this adventure. You can follow me on Twitter and LinkedIn. Check reddit.com/r/snowflake for the most interesting Snowflake news.