Using Snowflake JavaScript API for Stored Procedures Simplified 101

Companies use Data Warehouse to store and analyze their business data for making data-driven business decisions. Data plays an essential role in boosting the company’s performance. Business Intelligence tools need to streamline data flow from Data Warehouse for Data Analysis. 

Snowflake is a Data Warehousing service provider that helps organizations store and analyzes their data. It comes with SQL language support that makes it easier for business users to analyze data. Also, Developers can use SQL and stored procedures to create dynamic queries build flexible applications. Users can use Snowflake JavaScript API to create and execute stored procedures from their web applications.

Snowflake JavaScript API allows Developers to execute procedural logic such as loops and branches using SQL language to access data on request. In this article, you will learn about Snowflake stored procedures, and how to create stored procedures using Snowflake JavaScript API. Also, you will go through a few examples of Snowflake JavaScript API.

Snowflake Stored Procedures

Stored procedures, as the name suggests, are procedures that you can store, for calling repetitively. If you are a programmer, you can draw an analogy with functions. Stored procedures allow you to execute procedural logic (branching and looping), which is not possible with straight SQL. It also allows you to dynamically construct SQL statements and execute them, along with error handling.

There are several use cases for using stored procedures. An example is periodically deleting data older than N days from all the tables of your database. Writing a stored procedure for this task is much better than cleaning up each table individually.

Prerequisites

You will need a basic understanding of SQL and JavaScript to understand this article. If you wish to try out the example queries shown in this article, you will need a Snowflake account (you can create one if you don’t already have it. 

Using Snowflake JavaScript API for Stored Procedures

Now that you have understood about Snowflake Stored Procedures. In this section, you will learn how to create stored procedures using Snowflake JavaScript API. The best way to get started will be through an example. Given below is a sample stored procedure (you can enter it in the query editor of SnowFlake). 

It operates on a weather_data_cleaned table that was created in a previous tutorial from the daily_14_total table in the Weather schema of SNOWFLAKE_SAMPLE_DATA DB. You can find the steps here. For the sake of completeness, reproducing the table contents in the image below:

Note the fourth column (TS), a timestamp column. Now, here’s the stored procedure: 

create or replace procedure read_result_set(TS VARCHAR)
  returns float not null
  language javascript
  as    
  $$ 
    var sql_command = "select * from weather_data_cleaned";
    var stmt1 = snowflake.createStatement( {sqlText: sql_command} );
    var result_set1 = stmt1.execute();
    var count = 0
    var ts_thresh = (new Date(TS))/1000
  
    while (result_set1.next())  {
      var sfdate = result_set1.getColumnValue(4); //Get the value of the fourth column, which is a timestamp column
      if(sfdate.getEpochSeconds() > ts_thresh){
        count = count+1;
      }
      }
  return count;
  $$;

To run this procedure, simply run

CALL read_result_set('2016-01-01 10:20:20');

As you can see, the above code is a pretty simple stored procedure that simply returns the number of rows that have a timestamp column whose value is greater than the passed argument. This is a comprehensive example of understanding the Snowflake JavaScript API. 

First of all, understand that the JavaScript code is enclosed within two $$ symbols (you can alternatively use single quotes for enclosing the JavaScript code, but then you’ll have to escape it within the code if your code uses single quotes)

Snowflake JavaScript API Objects

The Snowflake JavaScript API consists of 4 objects:

• Snowflake
• Statement
• ResultSet
• SfDate

All four of these are used in the above Snowflake JavaScript API example. Each of these objects has some methods, listed below:

1) Snowflake

This object need not be created and is accessible by default. It can be used for creating the Statement object using the .createStatement() method, as we saw in the Snowflake JavaScript API example above. Alternatively, it can directly execute SQL statements, without creating the Statement object, using the .execute() method. An example for this using Snowflake JavaScript API is given below:

var result_set = snowflake.execute( {sqlText: "SELECT count(*) from weather_data_cleaned"} );

Note that this is different from the .execute() method of the Statement object. For the Statement object, the .execute() method doesn’t take in any arguments.

In both cases, the .execute() method returns a ResultSet object.

2) Statement

This object has methods for executing a query, and for fetching query execution metadata. We already saw an example for executing the query in the above Snowflake JavaScript API example.

Query execution metadata typically refers to stuff like getting the column name from column index, getting the datatype of the column, getting the number of rows affected, etc. The detailed list is here.

An example using Snowflake JavaScript API is shown below that returns the first column name in the results of a Statement execution.

create or replace procedure get_first_column_name()
  returns varchar not null
  language javascript
  as    
  $$ 
    var statement1 = snowflake.createStatement( {sqlText: "SELECT * from weather_data_cleaned"} );
    statement1.execute()
    var count2 = statement1.getColumnName(1)
  return count2
  $$
  ;

CALL get_first_column_name();

3) ResultSet

This object, as the name suggests, contains results returned by a query. It is similar to a cursor that is used for iterating over the results of an SQL query in programming languages like Python and Java. The most popular use of this object was illustrated in the Snowflake JavaScript API example at the start of this section.

You typically iterate over the results using the .next() method, and extract values from individual columns using the .getColumnValue() method. Other useful methods include .getRowCount(), .getColumnCount(), .getNumRowsAffected(). The complete list of methods is here.

4) SfDate

SfDate is short-term for SnowflakeDate. This object was introduced because JavaScript doesn’t have a native data type corresponding to the following Snowflake SQL data types:

• TIMESTAMP_LTZ
• TIMESTAMP_TZ
• TIMESTAMP_NTZ

Whenever a Snowflake result set returns a timestamp column, and you wish to assign it to a variable (maybe while iterating through the ResultSet), the variable type becomes SfDate.  

In the example at the start of the section, we saw how the TS column from the table was read into a SfDate variable, and how that variable was used for future comparisons. We also saw one of the methods available with this object (.getEpochSeconds()). There are several others, like .getNanoSeconds(), .getTimezone(), etc.

Conclusion

In this article, you learnt about stored procedures in Snowflake, and how Snowflake JavaScript API can be used for creating the stored procedures. You went through an example covering all the four objects within the Snowflake JavaScript API for stored procedures and used that example as the reference for understanding the objects and their methods. As you would have expected, any single article cannot do justice to the complete API reference. For getting the entire list of methods for each object, please refer to the Snowflake documentation. 

Snowflake stores business and operational data that needs Analysis. Companies can send data from Snowflake to Google Data Studio. Hevo Data is a No-code Data Pipeline that can help you transfer data from Snowflake to Data Source. It fully automates the process to load and transform data from 100+ data sources to a destination of your choice without writing a single line of code. 

Share your experience of learning about Snowflake JavaScript API in the comments section below!