Working with Snowflake Cursors in Stored Procedures: Made Easy 101

Investing your time and resources into having the right knowledge about how to do things in a different way can be the distinction between turning around your business fortune or falling short by continually taking the wrong steps. 

Snowflake, a Cloud-based data storage platform that has become popular among Data Scientists, listened to the needs of its customers and deliberated on new methods to extend its SQL operations so as to allow the running of complex code. It decided to build a system where users could use a new JavaScript-based Stored Procedure language to sort, implement, and invoke complex logic among other things in an easier and modern language within Snowflake.

Snowflake’s Stored Procedures available in all Clouds and regions are used to encapsulate Data Migration, Data Validation, Business-specific Logic, handling Exceptions, etc. The Stored Procedure being part of a relational database supports the use of Snowflake Cursor variables. This article is going to explain how to handle Snowflake Cursors in Snowflake’s Stored Procedures with examples. So, read along to gain insights into Snowflake Cursors.

Table of Contents

• Introduction to Snowflake
• Understanding Snowflake Stored Procedures
• Handling Snowflake Cursors in Stored Procedures
• Conclusion

Introduction to Snowflake

Snowflake is a Cloud-based Software-as-a-Service (SaaS) that offers Cloud-based storage and Analytics services. Its Cloud Data Warehouse is built on Amazon Web Services,  Microsoft Azure, and Google infrastructure, providing a platform for storing and retrieving data. Snowflake has a unique architecture that separates its storage unit from its compute unit, thereby scaling differently so customers can use and pay for both independently. 

With Snowflake, there is no hardware or software to select, install, configure, or manage, therefore, making it ideal for organizations that do not want to have dedicated resources for setup, maintenance, and support for in-house servers. 

Since Snowflake decouples the storage and compute functions, which means you can have an unlimited number of concurrent workloads against the same single copy of data. It does not interfere with the performance of other users, is highly scalable & flexible. Snowflake makes it easy for organizations to quickly share and secure data in real-time. To explore more about Snowflake, visit the official website here.

Understanding Snowflake Stored Procedures

Snowflake Stored Procedures allow users to extend Snowflake’s SQL programmability by creating modular code that encapsulates and harbors complex business logic by combining multiple SQL statements with procedural logic; they are first-class database objects along with corresponding DDL statements. It simply allows you to extend Snowflake SQL capability by combining it with JavaScript to include programming constructs such as branching and looping.

Snowflake Stored Procedures can be likened to functions because as with functions, a Stored Procedure is created once and can be executed as many times as possible. A stored procedure returns a single value and is created using the CREATE PROCEDURE command and executed using the CALL command. Stored procedures are written in JavaScript which executes SQL statements by calling a JavaScript API which is similar to the APIs in Snowflake connectors and drivers like Node.js, Python, etc.

One common use of Stored Procedures is to automate a task that requires multiple SQL statements and is performed frequently for example it is a lot easier to have multiple DELETE statements each of which deletes data from one specific table older than a specified date written into a single stored procedure joining all of these statements as one that can be used to clean up your database by deleting the data whenever the specified date elapsed. The stored procedure can also be updated subsequently to reflect additional data then used to clean up your database.

Snowflake Stored Procedures can be very beneficial to users as they can be used in performing the following:

• Stored Procedures can be used to implement and invoke complex business logic such as Extract, Transform, Load (ETL) procedures. It makes it easier to support relevant language attributes like arrays, exceptions, control structures, garbage collection, etc.
• It allows you to deploy well-known access control primitives without the need to manage an external environment.
• Stored Procedures can be used to build and run dynamic SQL.
• It has seamless support for both structured and semi-structured data.
• It makes use of procedural logic through the IF/ELSE statements.
• Allows you to iterate over result sets.
• Grant schema privileges on stored procedures.
• Caller’s rights stored procedure can be used to run all the supported SQL as the current user.
• It protects access to objects by using the owner’s rights stored procedure, in which case there will be no need for explicit permissions on the actual reference objects.
• Implements error handling through TRY/CATCH.
• To use first-class SQL objects along with the corresponding DDL statements.

In the next section, you will learn how to handle Snowflake Cursors in the Stored Procedures.

Handling Snowflake Cursors in Stored Procedures

Snowflake Cursors are extensively useful in Stored Procedures to loop through the records from SELECT statements for several business needs in ETL batches or other flows. Snowflake uses JavaScript as a procedural language therefore, Stored Procedures which are part of Snowflake uses JavaScript and the result-set object can be used as an alternative to a Snowflake Cursor variable.

The example below shows how a Snowflake Cursor in Stored Procedure uses a result-set to loop through results and return concatenated results.

create or replace procedure sample_cursor()
   returns varchar not null
   language javascript
   as
   $$
var return_value = "";var sql_query = "select * from customers_tmp" ;
   var sql_statement = snowflake.createStatement(
          {
          sqlText: sql_query
          }
       );
/* Creates result set */
var result_scan = sql_statement.execute();
   while (result_scan.next())  {
       return_value += "n";
       return_value += result_scan.getColumnValue(1);
       return_value += ", " + result_scan.getColumnValue(2);
       }return return_value;
$$
;

This procedure is executed using the CALL command as seen below:

call sample_cursor();
 
+---------------+                                                              
| SAMPLE_CURSOR |
|---------------|
|               |
| 1, c 1        |
| 2, c 2        |
+---------------+

A Snowflake Cursor data type can also be the output of a SQL Server Stored Procedure. Snowflake Cursor can be declared in the body of a Stored Procedure. Then the Snowflake Cursor output from the Stored Procedure can be assigned just like any output of a Stored Procedure to the same data type. You can then get the Snowflake Cursor variable result set output like the normal cursor retrieval operations. The advantage of this method is the reusability of the code as the Developer does not have to write the same Snowflake Cursor declaration in multiple places.

USE tempdb
GO
 
-- Cursor as an output of a stored procedure
CREATE PROCEDURE dbo.usp_cursor_db
@cursor_db CURSOR VARYING OUTPUT
AS
BEGIN
   DECLARE
      @database_id INT, 
      @database_name   VARCHAR(255);
 
   SET @cursor_db = CURSOR
   FOR SELECT 
         database_id, name
      FROM sys.master_files;
 
   OPEN @cursor_db;
 
END
GO
 
-- Code to retrieve the cursor resultset output from the stored procedure
DECLARE 
    @cursor_db CURSOR
 
DECLARE
    @database_id INT, 
    @database_name   VARCHAR(255);
 
EXEC dbo.usp_cursor_db @cursor_db = @cursor_db OUTPUT
 
FETCH NEXT FROM @cursor_db INTO 
   @database_id, @database_name;
 
WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT @database_name + ' id:' + CAST(@database_id AS VARCHAR(10));
 
   FETCH NEXT FROM @cursor_db INTO 
      @database_id, 
      @database_name;
END;
 
CLOSE @cursor_db;
 
DEALLOCATE @cursor_db;
GO

Conclusion

This write-up looked at Snowflake Stored Procedures and tried to show how to handle Snowflake Cursors in Stored Procedures by using examples. A general overview of the Stored Procedure was explained, stating its benefits to users, how it operates and likening it to a function because it is created once and can be called as many times as possible when required.

However, it can be confusing to understand Snowflake Stored Procedures and handle Snowflake Cursors in Stored Procedures. But don’t worry, there is an easier way in which this can be done for you without having to write any line of code. This is done using Hevo Data, a no-code data pipeline that can be used to sort your data needs and requirements with simple clicks and all in one location to give you your desired output.

Hevo Data offers a faster way to move data from 100+ Data Sources including 40+ Free Sources, into your Data Warehouse such as Snowflake to be visualized in a BI tool.

VISIT OUR WEBSITE TO EXPLORE HEVO

Want to take Hevo for a spin?

SIGN UP and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience with Snowflake Cursors in the comments section below!