Stored procedures in Snowflake

Snowflake is poetry. They handcrafted every tiny detail of how a modern datawarehouse should work. But when it comes to stored procedures, Snowflake has done a half-baked job, forcing SQL developers to fiddle with JavaScript and a ton of other nuances.

There is no pure SQL-only stored procedure in Snowflake. You will have to mix and match javascript and sql and few other hacks if you want to make your procs work. Sorry developers!

Snowflake stored procedures use JavaScript and SQL:

• JavaScript provides the control structures (branching and looping).
• SQL is executed by calling functions in a JavaScript API

Example:

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

call read_result_set();

Stored Procedure DDL

Stored procedures are first-class database objects. The following DDL commands apply to stored procedures:

• CREATE PROCEDURE
• ALTER PROCEDURE
• DROP PROCEDURE
• DESCRIBE PROCEDURE
• SHOW PROCEDURES

In addition, Snowflake provides the following command for executing stored procedures:

• CALL

create or replace procedure get_row_count(table_name VARCHAR)
  returns float not null
  language javascript
  as
  $$
  var row_count = 0;
  var sql_command = "select count(*) from " + TABLE_NAME;
  // Run the statement.
  var stmt = snowflake.createStatement(
         {
         sqlText: sql_command
         }
      );
  var res = stmt.execute();
  res.next();
  row_count = res.getColumnValue(1);
  return row_count;
  $$
  ;

call get_row_count('stproc_test_table1');

Implementation and API

Snowflake stored procedures are written in JavaScript. The JavaScript code can execute SQL statements by calling a JavaScript API

• The JavaScript provides error handling and procedural logic.
• The SQL executed through the API provides database access.

The API enables you to perform operations such as:

• Executing a SQL statement.
• Retrieving the results of a query (i.e., a result set).
• Retrieving metadata about the result set (number of columns, data types of the columns, etc.).

These operations are carried out by calling methods on the following objects:

• snowflake, which has methods to create a Statement object and execute a SQL command.
• Statement, which helps you execute prepared statements and access metadata for those prepared statements, and allows you to get back a ResultSet object.
• ResultSet, which holds the results of a query (e.g., the rows of data retrieved for a SELECT statement).
• SfDate, which is an extension of JavaScript Date (with additional methods) and serves as a return type for the Snowflake SQL data types TIMESTAMP_LTZ, TIMESTAMP_NTZ, and TIMESTAMP_TZ

SQL statements can be quite long, and it is not always practical to fit them on a single line, Use backticks (single backquotes) rather than double quotes around the string. For example:

var sql_command = `SELECT *
                       FROM table1;`;

So, As you have realized, Though snowflake procedures does the job, it takes a struggle to hack the existing logic and port it to snowflake. Tell us! How you feel about the snowflake procedures in the comments!

nVector

posted on 19 Oct 19