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&rsquo'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. First, you can use SQL, and 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. The role is the DB role like an 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 in 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 the 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

CREATEORREPLACETABLEEmployee(emp_idINT,emp_namevarchar,emp_addressvarchar);

Step 2

Create an identity column or sequence on the table. Here, I am creating a sequence

createsequenceifnotexistsemp_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.

CREATE OR REPLACE PROCEDURE employee_insert(name VARCHAR, address VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
    var command = "INSERT INTO Employee (emp_id, emp_name, emp_address) VALUES (emp_id.nextval, '" + name + "', '" + address + "')";
    var cmd1_dict = { sqlText: command };
    var stmt = snowflake.createStatement(cmd1_dict);
    var rs = stmt.execute();
    return 'success';
$$;

Step 4

Call the stored procedure:

CALLemployee_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 the 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.

CREATE OR REPLACE PROCEDURE employee_validate_proc(INPUT VARCHAR)
RETURNS VARIANT NOT NULL
LANGUAGE JAVASCRIPT
AS
$$
    var row_as_json = {};
    var error_count = 0;
    try {
        var employee_data = JSON.parse(INPUT);
        var employee_id = employee_data.employee_id;
        var employee_name = employee_data.employee_name;
        var employee_address = employee_data.employee_address;

        if (!employee_name) {
            row_as_json["employee_name"] = "EmployeeName should not be empty or null.";
            error_count = 1;
        } else {
            var command1 = "SELECT COUNT(*) FROM Employee WHERE name = '" + employee_name + "'";
            var stmt1 = snowflake.createStatement({ sqlText: command1 });
            var res1 = stmt1.execute();
            res1.next();
            var row_count = res1.getColumnValue(1);
            if (row_count > 0) {
                row_as_json["employee_name"] = "EmployeeName already exists.";
                error_count = 1;
            }
        }

        if (employee_address == null || employee_address == undefined) {
            row_as_json["employee_address"] = "Employee address should not be NULL.";
            error_count = 1;
        }
    } catch (err) {
        row_as_json["Exception"] = "Exception: " + err;
        row_as_json["is_error"] = 1;
    }
    
    row_as_json["is_error"] = error_count;
    return row_as_json;
$$;

 Step2. Create an insert procedure that supports 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.

CREATE OR REPLACE PROCEDURE employee_insert_json(input VARCHAR)
RETURNS VARIANT NOT NULL
LANGUAGE JAVASCRIPT
AS
$$
    var row_as_json = {};
    var message = {};
    var detail = {};
    var result = '';
    var error_count = 0;
    
    try {
        var employee_data = JSON.parse(input);
        var employee_name = employee_data.employee_name;
        var employee_address = employee_data.employee_address;

        var command_validate = "CALL employee_validate_proc('" + input + "')";
        var cmd_dict_validate = {
            sqlText: command_validate
        };
        var stmt_validate = snowflake.createStatement(cmd_dict_validate);
        var rs_validate = stmt_validate.execute();
        rs_validate.next();
        var validate_result = rs_validate.getColumnValue(1);

        if (validate_result.is_error > 0) {
            return validate_result;
        } else {
            var command1 = "INSERT INTO employee (emp_id, emp_name, emp_address) VALUES (emp_id.nextval, '" + employee_name + "', '" + employee_address + "')";
            var cmd_dict = {
                sqlText: command1
            };
            var stmt1 = snowflake.createStatement(cmd_dict);
            var rs1 = stmt1.execute();

Step 3. Call the stored procedure.

allEMPLOYEE_INSERT_JSON('{
    "employee_name": "Sam",
    "employee_address": "Hyderabad"
}');

Result {
    "is_error": 0,
    "message": "successfully inserted the record"
}

It will insert a new record to the Employee table. If you will pass the same JSON again, then you will get a validation message like “employee name already exists.

callEMPLOYEE_INSERT_JSON('{
    "employee_name": "Sam",
    "employee_address": "Hyderabad"
}');
-- result {
--     "is_error": 1,
--     "message": "EmployeeName already exists"
-- }

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!