Since Snowflake got its start, you have had the ability to create stored procedures. However, this capability was limited to using JavaScript to write those stored procedures.
Using JavaScript opens up great potential, but JS is a new language and some data engineers may face a steep learning curve.
What is Snowflake Scripting?
Snowflake has just extended their SQL dialect to allow programmatic instructions. For example, you can now write conditional blocks:
CREATE OR REPLACE TABLE TEST(LINE VARCHAR);
INSERT INTO TEST(LINE) VALUES('FIST LINE');
EXECUTE IMMEDIATE $$
DECLARE
COUNT INT;
BEGIN
SELECT COUNT(*) INTO :COUNT FROM TEST;
IF (COUNT < 2) THEN
INSERT INTO TEST(LINE) VALUES('SECOND LINE');
RETURN 'INSERTED';
END IF;
RETURN 'NOT INSERTED. COUNT = ' || :COUNT;
END;
$$;
Ok. Now that we've seen a conditional block, let's see in more detail what Snowflake Scripting brings to the table.
Variable Declaration
Snowflake Scripting provides a declare section just before your
BEGIN/
END block. You can
declare variables in that section. If you want them to have an initial value, you can use the DEFAULT clause. Here's an example:
EXECUTE IMMEDIATE $$
DECLARE
VAR1 VARCHAR DEFAULT 'Hello World';
BEGIN
return VAR1;
END;
$$;
You could also declare them inline with your code using a let statement and ":=". Here's an example of that:
EXECUTE IMMEDIATE $$
BEGIN
let VAR1 VARCHAR := 'Hello World';
return VAR1;
END;
$$;
Passing variables to SQL statements in Snowflake Scripting
Binding variables is something that was a little more complicated in the JavaScript world. But in Snowflake Scripting, it's super easy to
pass variables to SQL statements. Just remember to use a semicolon (':') before the variable name as shown here:
EXECUTE IMMEDIATE
$$
BEGIN
let VAR1 VARCHAR := 'Hello World';
CREATE OR REPLACE TABLE TEST AS select :VAR1 as LINE;
END;
$$;
Reading values into variables
Retrieving results is also easy. You can
read values into a variable as shown below, but just like before, remember to use a semicolon (':') character before the variable name.
EXECUTE IMMEDIATE
$$
BEGIN
let VAR1 INT := 0;
select 1000 INTO :VAR1;
return VAR1;
END;
$$;
This will print something like this:
+-----------------+
| anonymous block |
|-----------------|
| 1000 |
+-----------------+
And what about non-scalar values? What about doing a query?
That can be done too:
execute immediate
$$
BEGIN
CREATE OR REPLACE TABLE MYTABLE as SELECT $1 as ID, $2 as Name FROM VALUES(1,'John'),(2,'DeeDee');
LET res RESULTSET := (select Name from MYTABLE ORDER BY ID);
LET c1 CURSOR for res;
LET all_people VARCHAR := '';
FOR record IN c1 DO
all_people := all_people || ',' || record.Name;
END FOR;
RETURN all_people;
END
$$;
And it will print something like:
+-----------------+
| anonymous block |
|-----------------|
| ,John,DeeDee |
+-----------------+
The results from a query are called a
RESULTSET. To iterate on the results of a query, you can open a cursor for that
RESULTSET and the user a
FOR with the cursor variable.
Conditional Logic
Snowflake Scripting brings operators to branch on conditions. You can use both
IF and
CASE. For example:
EXECUTE IMMEDIATE
$$
DECLARE
VAR1 INT DEFAULT 10;
BEGIN
IF (VAR1 > 10) THEN
RETURN 'more than 10';
ELSE
RETURN 'less than 10';
END IF;
END;
$$;
With this being the result:
+-----------------+
| anonymous block |
|-----------------|
| less than 10 |
+-----------------+
Snowflake Scripting can be super convenient to simplify some administrative tasks. For example, I usually have this code on a Snowflake worksheet and I need to change it each time I need to create a test database.
create database database1;
create warehouse database1_wh;
create role database1_role;
grant ownership on database database1 to database1_role;
grant ownership on schema database1.public to database1_role;
grant ownership on warehouse database1_wh to database1_role;
grant role database1_role to user USER1;
I can use a Snowflake Scripting block and then I only need to change the database and user :)
execute immediate
$$
declare
client varchar default 'database1';
user varchar default 'user1';
sql varchar;
begin
execute immediate 'create database if not exists ' || client;
execute immediate 'create warehouse if not exists ' || client || '_wh';
execute immediate 'create role if not exists ' || client || '_role';
execute immediate 'grant ownership on database ' || client || ' to ' || client || '_role';
execute immediate 'grant ownership on schema ' || client || '.public to ' || client || '_role';
execute immediate 'grant ownership on warehouse ' || client || '_wh to ' || client || '_role';
execute immediate 'grant role ' || client || '_role to user ' || user;
end;
$$;
Snowflake Scripting for me and all of us here at Mobilize.Net is a game changer. It really makes it easier for people coming from Teradata, Oracle, or SQL Server to start enjoying the Snowflake Data Platform.
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.
Thanks for reading!