Stored Procedure in Snowflake

Stored Procedure in Snowflake

Summary

How to use snowflake stored procedure using javascript

Stored Procedure in Snowflake

Stored Procedure In Snowflake allows data developer to wrap their complex logic and automate their repeatable manual process. This episode is a comprehensive & practical guide with hands-on excercise on stored procedure. It covers basics of stored procedure including different specification options (input params, language i.e. javascript/sql/java/scala, return type, volatile/immutable, caller/owners rights, strict etc) along side exception handling and transaction. This guide will help you to answer the following questions

  1. What is caller’s right and owner’s right?
  2. Can I write snowflake stored procedure in Javascript?
  3. Can I write it in Java or Scala too? If so, how can I use it via SQL?
  4. Java or Scala code gets converted into SQLs?
  5. How does transaction management work in Snowflake stored procedure?
  6. What is the limitation of stored procedure in snowflake.

Watch Complete Stored Procedure in Snowflake Video

You can watch the complete hands on video tutorial

Stored Procedure SQL Script

Find all the SQL scripts used in ch-21 in this blog.

Step-1: The basic SQL construct

create or replace transient table customer (
    customer_id int,
    salutation varchar,
    first_name varchar,
    last_name varchar,
    birth_day int,
    birth_month int,
    birth_year int,
    birth_country varchar,
    email_address varchar,
    cust_status varchar
) comment ='this is simple customer table';
insert into customer (customer_id ,salutation ,first_name ,last_name ,birth_day ,birth_month ,birth_year ,birth_country ,email_address,cust_status )
values 
(101,'Mr.', 'Cust-1','LName-1',10,12,2000,'Japan','cust-1.lname-1@gmail.com','Active'),
(102,'Mr.', 'Cust-2','LName-2',27,11,1999,'USA','cust-2.lname-2@gmail.com','Inactive'),
(103,'Mr.', 'Cust-3','LName-3',21,2,1998,'UK','cust-3.lname-3@gmail.com','Blocked'),
(104,'Mr.', 'Cust-4','LName-4',19,9,1997,'USA','cust-4.lname-4@gmail.com','Active'),
(105,'Mr.', 'Cust-5','LName-5',11,3,1997,'Canada','cust-5.lname-5@gmail.com','Unknown');

-- lets validate the data
select * from customer;

Lets create our 1st stored procedure

create or replace procedure purge_inactive_customer()
    returns string
    language javascript
    strict
    execute as owner
    as
    $$
    var rs = snowflake.execute( { sqlText: 
      `DELETE FROM CUSTOMER WHERE CUST_STATUS = 'Inactive';`
       } );
    return 'Inactive customers purged.';
    $$;

call purge_inactive_customer();

-- lets desc a stored procedure
desc procedure purge_inactive_customer();

Lets see how we can store the stored procedure data into a variable

var return_value = purge_inactive_customer();
select * from table(result_scan(last_query_id()));

set my_value = (select * from table(result_scan(last_query_id())));
select $my_value;

Multile SQL statement in a single stored procedure

create or replace procedure purge_non_active_customer()
    returns string
    language javascript
    strict
    execute as owner
    as
    $$
    var rs_inactive = snowflake.execute( { sqlText: 
      `DELETE FROM CUSTOMER WHERE CUST_STATUS = 'Inactive';`
       } );
     var rs_blocked = snowflake.execute( { sqlText: 
      `DELETE FROM CUSTOMER WHERE CUST_STATUS = 'Blocked';`
       } );
    var rs_blocked = snowflake.execute( { sqlText: 
      `DELETE FROM CUSTOMER WHERE CUST_STATUS = 'Unknown';`
       } );
    return 'All non-active customers purged.';
    $$;

desc procedure purge_non_active_customer();

See stored procedure from information schema

select * from "TTIPS"."INFORMATION_SCHEMA"."PROCEDURES";

-- procedure name & argument signature make the procedure unique
-- in Snowflake, you can overwrite the stored procedure
-- we will see it later

Now lets try creating stored procedure with an argument

create or replace procedure purge_inactive_customer(in_param string )
    returns string
    language javascript
    strict
    execute as owner
    as
    $$
    var rs = snowflake.execute( { sqlText: 
      `DELETE FROM CUSTOMER WHERE CUST_STATUS = '`+IN_PARAM+`';`
       } );
    return 'Customers purged as per requested status';
    $$;

desc procedure purge_inactive_customer(string) ;
desc procedure purge_inactive_customer(varchar) ;

-- there is a mapping between JavaScript datatype and SQL data type

Numeric Precision for return type (string or number)

create or replace procedure give_numberic_return_sql()
  returns number(10,3) 
  language sql
  as     
    $$
        begin
        return 2.12;
        end;
    $$
;

-- JavaScript based stored procedure
create or replace procedure give_numberic_return_js()
  returns number(10,3) 
  language javascript
  as     
    $$
    return 2.12;
    $$
;