Snowflake Scripting 101: Snowflake Cursors

In another article, we talked about using blocks in Snowflake scripting and Snowflake scripting error handling.. Now, let’s learn about something called the Snowflake cursors. We’ll see how to use it in stored procedures and anonymous blocks.

Page Content

Introduction to Snowflake Cursors

Understanding Cursor in Snowflake

Cursor Syntax and how to use it?

Declaring Cursors

Opening Cursors

Fetching Data from Cursors

Closing Cursors

Advanced Cursor Techniques

Bulk fetching

Avoiding large result sets

Snowflake Cursor Examples

Snowflake Anonymous Block with Cursor

Snowflake Stored Procedure with Cursor

Returning a Table for a Snowflake Cursor

Best Practices and Performance Considerations

Conclusion

Introduction to Snowflake Cursors

Snowflake, a cloud-based data platform, offers powerful scripting capabilities for efficient data management and analysis. One notable feature is the use of Snowflake cursors, which play a crucial role in data iteration and manipulation. In this context, users can explore Snowflake cursor examples and understand the Snowflake cursor syntax to grasp how to utilize cursors effectively in Snowflake scripting stored procedure and in an anonymous block.

This article will explain the practical aspects of how to use cursors in Snowflake, providing insights into best practices for optimizing performance. Additionally, it will cover troubleshooting Snowflake cursors, addressing common issues users may encounter.

Understanding Cursor in Snowflake

In SQL, a cursor is like a temporary storage area formed in the system’s memory when executing a SQL SELECT statement. Essentially, it’s a set of rows, and it has an internal pointer that indicates the current row. Cursors come into play when you need to retrieve data from the outcome of a query. They are handy in loops, enabling you to go through each row in the results systematically.

Various SQL programming languages, such as PL/SQL and pgPL/SQL, heavily rely on cursors to navigate through result sets. Notably, Snowflake has traditionally supported only JavaScript. However, it has now expanded its support to include scripting, resembling the functionality seen in PL/SQL. This enhancement broadens the scripting capabilities within Snowflake, offering users more flexibility and familiarity in working with cursors for efficient data handling.

Cursor Syntax and how to use it?

Using cursors into your Snowflake scripting stored procedure or anonymous block is quite simple. The following steps provide guidance on how to employ a cursor in Snowflake scripting.

Declare a Snowflake Cursor

You have the flexibility to declare a cursor in the DECLARE section of a block or within the BEGIN … END section of the block in your Snowflake script.

For example,

DECLARE
  c1 CURSOR FOR SELECT column1 FROM table;

Use following syntax to declare cursor for a RESULTSET.

DECLARE
  res RESULTSET DEFAULT (SELECT column1 FROM table);
  c1 CURSOR FOR res;

You can also choose to declare a cursor within the BEGIN and END blocks by using LET keyword.

BEGIN
  LET c1 CURSOR FOR SELECT price FROM invoices;

Open the Snowflake Cursor

Use the OPEN command to open a cursor in your Snowflake script. The DECLARE statement sets up the cursor and defines the associated query, but the query remains inactive until you open the cursor using the OPEN command.

OPEN c1;

In case your query involves bind parameters (denoted by ? characters), include a USING clause to specify the variables binding to these parameters.

LET c1 CURSOR FOR SELECT col1 FROM table WHERE col2 > ? and col2 < ?;
OPEN c1 USING (minimum_price, maximum_price);

Fetch Rows from Snowflake Cursor

Execute the FETCH command to retrieve one or more rows. This command fetches the current row from the result set and moves the internal current row pointer to the subsequent row in the result set.

FETCH c1 INTO var_for_column_value;

Close Snowflake Cursor

Finally, close cursor using CLOSE command.

CLOSE c1;

Advanced Cursor Techniques

Here are some advanced cursor techniques, with the note that not all of them are directly supported.

Bulk fetching

Snowflake cursors themselves do not directly support bulk fetching. Unlike traditional cursors in other databases, they iterate through data row by row. As an alternative solution, create a temporary table and perform a join with the primary table rather than iterating through the data row by row.

Avoiding Large Result Sets

Avoiding large result sets is crucial when working with Snowflake cursors, as iterating through millions of rows can be inefficient and resource-intensive. Here are some strategies to keep your result sets lean and mean:

Apply WHERE clauses in your cursor declaration query to restrict the data fetched.
Consider using subqueries or temporary tables to pre-process and filter data before feeding it to the cursor.
Utilize functions like LIMIT and OFFSET to restrict the number of rows retrieved in each FETCH iteration.

Snowflake Cursor Examples

Here are a few instances where Snowflake cursors can be used:

Snowflake Anonymous Block with Cursor

The subsequent example of an anonymous block illustrates all the previously mentioned steps for utilizing cursors in Snowflake scripting.

declare
  id integer default 0;
  c1 cursor for select id from invoices where price > 10 and price < 33.33;
begin
  open c1;
  fetch c1 into id;
  close c1;
  return id;
end;

Snowflake Stored Procedure with Cursor

You can also create a stored procedure for above anonymous block using Snowflake scripting.

create or replace procedure cursor_demo()
returns integer
language sql 
as
$$
declare
  id integer default 0;
  c1 cursor for select id from invoices where price > 10 and price < 33.33;
begin
  open c1;
  fetch c1 into id;
  close c1;
  return id;
end;
$$;

Returning a Table for a Snowflake Cursor

If you wish to retrieve a table of data from a cursor, you can provide the cursor to RESULTSET_FROM_CURSOR(cursor), and then pass the result to TABLE(...).

DECLARE
  c1 CURSOR FOR SELECT * FROM invoices;
BEGIN
  OPEN c1;
  RETURN TABLE(RESULTSET_FROM_CURSOR(c1));
END;

Best Practices and Performance Considerations

Best practices

Use cursors only when necessary. Cursors can be slow and resource-intensive, so they should be used when necessary.
Use cursors to iterate over a small number of rows. Cursors are not well-suited for iterating over large result sets.
Close the cursor when you are finished with it. This frees up resources and prevents the cursor from being left open.

Performance Considerations

Use a WHERE clause to filter the cursor result set. This will reduce the number of rows that the cursor needs to iterate over.
Use a bind variable to pass a parameter to the cursor query. This will help the database to optimize the query execution.
Avoid opening and closing the cursor multiple times. If you need to iterate over the cursor result set multiple times, you can use a loop instead of opening and closing the cursor each time.
Use a temporary table to store the cursor result set. This can be more efficient than storing the result set in memory.

Conclusion

Snowflake cursors are a powerful features for iterating over result sets in Snowflake. They can be used to perform a variety of tasks, such as processing data row by row, updating data in batches, and performing complex calculations. However, cursors can also be slow and resource-intensive, so it is important to use them wisely.

In this blog post, we discussed the basic syntax of Snowflake cursors and provided some tips for using them efficiently and effectively.