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.
Watch on Youtube

Stored procedures simplification

Replacing a JS stored procedure with a SQL stored procedure

Migrations

Basic scripts

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

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

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

Working with the results of DESCRIBE TABLE:

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;
call cols_except('snowflake_sample_data.tpch_sf1.nation', 'N_NAME,N_REGIONKEY'); 

Wanted syntax

Performance

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

Documentation

Want more?

--

--

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

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