Snowflake
Published in

Snowflake

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.

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

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:

Documentation

Want more?

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.

--

--

--

Snowflake articles from engineers using Snowflake to power their data.

Recommended from Medium

Introducing Payload — 2021’s Node + React Headless CMS for JavaScript Developers

Cue the Confetti: Trailhead Reaches One Million Learners!

Are Scrum Masters just the timekeepers of a Scrum team?

How to use SQL to perform data analysis (House Property Sales Time Series)

Amazon Elastic Kubernetes(EKS)

Rendezvous Hashing

Tic Tac Toe in c

Using InVision for AR prototyping

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Felipe Hoffa

Felipe Hoffa

Data Cloud Advocate at Snowflake ❄️. Originally from Chile, now in San Francisco and around the world. Previously at Google. Let’s talk data.

More from Medium

Snowflake Scripting Series: Exception Handling makes developer life easy now…!

How to Generate ERDs from a Snowflake Model

How To Generate an Audit Table with Python and Matillion ETL for Snowflake

Snowflake — Performance Tuning And DB Characteristics