Stored Procedure in Snowflake

Introduction 

 
The stored procedure is a group of database statements. I am sure you are familiar with the stored procedure if you have worked with SQL, ORACLE, MY SQL, POSTGRACE etc… In this article, I am going to explain the stored procedure in Snowflake. It’s very interesting because Snowflake is new and in demand.
 
Before going directly into the stored procedure query, let me give you a little background on Snowflake.
 

Snowflake

 
It’s a cloud data warehouse. It is an analytic data warehouse provided as Software-as-a-Service (SaaS). It provides a data warehouse that is faster, easier to use, and far more flexible than traditional data warehouse offerings.
 
To work with Snowflake, you should have a Snowflake account. Once you have an account, you can connect with the language connectors (Python, Go, Node.js, etc). You can also connect through JDBC and ODBC drivers. Snowflake provides a Web Interface as well where you can write your query and execute it.
 
The goal of using Snowflake is to access/fetch data faster. Snowflake supports DDL and DML operations.
 

Stored Procedure in Snowflake

 
As we know, a stored procedure is a pre-compiled object of the database. It’s a group of SQL statements. In Snowflake, a stored procedure can be written in two ways. The first you can use SQL, and another you can use JavaScript. I know you are wondering how we can use Java script in a stored procedure. Snowflake provides a way to use Javascript inside the stored procedure. Here we are discussing this functionality only.
 
Inside Snowflake, you can create a database, table, view, function, and stored procedure. To work with these objects, we run the query under the following context,
  • Role: role is the DB role like owner, admin, dev role
  • Warehouse: it’s just like your EC2 instance in AWS, but here you don’t need to do any big configuration like AWS. You can simply create a warehouse. Actually, the main use of it is computing. It comes with sizes, like XS,S, L,XL,XXL etc
  • Database: It stores all the tables and DB objects.
  • Schema: schema can be created under the database. The table and view will be inside schema only.
Let’s create a scenario to work on stored procedures. You have a database in Snowflake called ‘EmployeeManagement’ and schema is ‘EM’. So now, we have a problem statement here.
 

Scenario 1

 
How to insert the record in a table using a stored procedure in Snowflake
 

Solution

 
Step 1
 
Create a table in Snowflake
  1. CREATE OR REPLACE TABLE Employee(emp_id INT, emp_name varchar,emp_address varchar);  
Step 2
 
Create an identity column or sequence on the table. Here, I am creating a sequence
  1. create sequence if not exists emp_id;  
Step 3
 
Create a stored procedure like below. Here, I have used Javascript. The Javascript code should be between $$ ..$$. The important thing here is that we have a parameter in lowercase, but in snowflake, inside the stored proc you have to use the parameter as a capital letter, like I have used.
  1. CREATE OR REPLACE PROCEDURE employee_insert(name varchar,address varchar)  
  2. RETURNS VARCHAR  
  3. LANGUAGE JAVASCRIPT  
  4. AS  
  5. $$  
  6. var command = "INSERT INTO Employee (emp_id, emp_name,emp_address) VALUES (emp_id.nextval, '"+NAME+"','"+ADDRESS+"')";  
  7. var cmd1_dict = {sqlText: command};  
  8. var stmt = snowflake.createStatement(cmd1_dict);  
  9. var rs = stmt.execute();  
  10. return 'success';  
  11. $$;  
Step 4
 
Call the stored procedure:
  1. CALL employee_insert('Nitesh','Hyderabad');  
The syntax is:
 
CALL stored_procedure_name(parameter_value)
 
Now, if you will see the below query, you will get your result.
  1. Select * from Employee;  

Scenario 2

 
How to pass JSON data to the stored procedure to insert the record in a table using a stored procedure in Snowflake.
 

Solution

 
In this real-time scenario, we may use these stored procedures as an API and can connect through JDBC/ODBC connection. In that case, I want to implement the validation as well in the stored procedure. If we are passing a JSON object to the stored procedure, then it must validate first. Only then, it will insert the record. Like a typical validation connection we implement in API and other programming languages.
 
Validation rule:
  1. Employee Name should not be empty or NULL
  2. Employee Name should be unique.
  3. The employee address should not be null. 
To achieve this, we should create two stored procedures. However. we can implement it in a single procedure as well, but to give more clarity on this I have implemented in 2 procedures. The first one I have is a validation stored procedure and another one is the insert stored procedure. We will pass the employee JSON object to insert stored procedure, and internally it will validate the data and insert a record accordingly. If validation fails, then it will return the result in JSON only. The stored procedure in snowflake supports the try-catch block functionality as well.
 
Let’s see the steps below.
 
Step 1
 
Create a validation stored procedure:
  1. create or replace procedure employee_validate_proc(INPUT VARCHAR)  
  2. RETURNS VARIANT NOT NULL  
  3. LANGUAGE JAVASCRIPT  
  4. AS  
  5. $$  
  6. var row_as_json = {};  
  7. var error_count = 0;  
  8. try {  
  9.     var employee_data = JSON.parse(INPUT);  
  10.     var employee_id = employee_data.employee_id;  
  11.     var employee_name = employee_data.employee_name;  
  12.     var employee_address = employee_data.employee_address;  
  13.     if (!employee_name) {  
  14.         row_as_json["employee_name"] = "Employee Name should not be empty or null.";  
  15.         error_count = 1;  
  16.     } else {  
  17.         var command1 = "select count(*) from Employee where name='" + employee_name + "'";  
  18.         var stmt1 = snowflake.createStatement({  
  19.             sqlText: command1  
  20.         });  
  21.         var res1 = stmt1.execute();  
  22.         res1.next();  
  23.         row_count = res1.getColumnValue(1);  
  24.         if (row_count > 0) {  
  25.             row_as_json["employee_name"] = "Employee Name already exists.";  
  26.             error_count = 1;  
  27.         }  
  28.     }  
  29.     if (employee_address == null || employee_address == undefined) {  
  30.         row_as_json["employee_address"] = "employee address should not be NULL.";  
  31.         error_count = 1;  
  32.     }  
  33. }  
  34. row_as_json["is_error"] = error_count;  
  35. }  
  36. catch (err) {  
  37.     row_as_json["Exception"] = "Exception: " + err;  
  38.     row_as_json["is_error"] = 1;  
  39. }  
  40. return row_as_json;  
  41. $$;  
Step 2 - Create insert procedure which support json.
 
In the below stored procedure, I am calling internally the validation stored procedure. So when you will pass the data to insert SP, it will validate and insert if it passes the validation. If it fails then it will return the validation error in the form of json. That’s why I have used VARIANT as a return type.
  1. create or replace procedure employee_insert_json(input VARCHAR)  
  2. RETURNS VARIANT NOT NULL  
  3. LANGUAGE JAVASCRIPT  
  4. AS  
  5. $$  
  6. row_as_json = {};  
  7. var message = {};  
  8. var detail = {};  
  9. var result = '';  
  10. var error_count = 0;  
  11. try {  
  12.     var employee _data = JSON.parse(INPUT);  
  13.     var employee_name = employee_data.employee_name;  
  14.     var employee_address = employee_data.employee_address;  
  15.     var command_validate = "call employee_validate_proc('" + INPUT + "')";  
  16.     var cmd_dict_validate = {  
  17.         sqlText: command_validate  
  18.     };  
  19.     var stmt_validate = snowflake.createStatement(cmd_dict_validate);  
  20.     var rs_validate = stmt_validate.execute();  
  21.     rs_validate.next();  
  22.     var validate_result = rs_validate.getColumnValue(1);  
  23.     if (validate_result.is_error > 0) {  
  24.         return validate_result;  
  25.     } else {  
  26.         var command1 = "INSERT INTO employee(emp_id,emp_name,emp_address) VALUES(emp_id.nextval, '" + employee_name + "','" + employee_address + "')";  
  27.         var cmd_dict = {  
  28.             sqlText: command1  
  29.         };  
  30.         var stmt1 = snowflake.createStatement(cmd_dict);  
  31.         var rs1 = stmt1.execute();  
  32.         row_as_json["message"] = ”successfully inserted the record”;  
  33.     }  
  34. } catch (err) {  
  35.     row_as_json["exception"] = err;  
  36.     error_count = 1;  
  37. }  
  38. row_as_json["is_error"] = error_count;  
  39. return row_as_json;  
  40. $$;  
Step 3
 
Call the stored procedure:
  1. call EMPLOYEE _INSERT_JSON('{  
  2.     "employee_name""Sam""employee_address""Hyderabad"  
  3. }  
  4. ');  
  5. Result {  
  6.     "is_error": 0,  
  7.     "message""successfully inserted the record"  
  8. }  
It will insert a new record to the Employee table. If you will pass the same JSON again, then you will get validation message like “employee name already exists”
  1. call EMPLOYEE _INSERT_JSON('{  
  2.     "employee_name""Sam""employee_address""Hyderabad"  
  3. }  
  4. ');  
  5. --result {  
  6.     "is_error": 1,  
  7.     "message""Employee Name already exists"  
  8. }  

Conclusion

 
Creating a stored procedure in Snowflake can be done in two ways. One. you can write your SQL command with Language as SQL. Another is with Javascript. If you create a procedure with javascript, then it will behave like an API where you can implement exception handling. All logic can be a part of your Snowflake procedure. 
 
I can take up any questions that you have on this functionality. 
 
Happy learning!