Using Snowflake JavaScript API for Stored Procedures Simplified 101

on API, Data Integration, Data Warehouses, JavaScript, Snowflake, SQL, Stored Procedures, Tutorials • March 10th, 2022 • Write for Hevo

Snowflake Javascript API

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.

Table of Contents

What is Snowflake?

Snowflake Logo
Image Source

Snowflake is a Data Warehouse-as-a-Service offering. Like other data warehouses, it is optimized for analytical queries and organizes data into columns instead of rows to enable parallel computations. It gives you the choice of AWS, Azure, or GCP as the cloud resource provider, and also provides flexibility in choosing the region for your cloud resources.

Its separation of storage and computing makes it very scalable. You can resize your cluster when your workload increases, and pause it when there is no workload. 

Key Features of Snowflake

Some of the main features of Snowflake are listed below.

  • Standard SQL Support: Snowflake offers standard as well as extended SQL Language support that allows users to manipulate and access data easily. It supports DDL and DML commands along with the lateral view, transactions, stored procedures and advanced DML commands.
  • Fail-Safe: With the help of the fail-safe feature, Snowflake ensures that historical data is protected at the time of disaster or system failure.
  • Connectivity: Snowflake comes with many 3rd party connectors for easy data flow. Also, it supports connectors in many languages such as Python, Go, JDBC, Spark, etc.
  • Security: Snowflake always protects the data communication between the client and the user using TLS. Also, it offers enhanced authentication methods such as MFA, SSO, and OAuth.

To know more about Snowflake, click here.

Simplify Data Analysis with Hevo’s No-code Data Pipeline

Hevo Data, a No-code Data Pipeline helps to load data from any data source such as Databases, SaaS applications, Cloud Storage, SDK,s, and Streaming Services and simplifies the ETL process. It supports 100+ data sources and is a 3-step process by just selecting the data source, providing valid credentials, and choosing the destination. Hevo not only loads the data onto the desired Data Warehouse/destination but also enriches the data and transforms it into an analysis-ready form without having to write a single line of code.

Get Started with Hevo for Free

Its completely automated pipeline offers data to be delivered in real-time without any loss from source to destination. Its fault-tolerant and scalable architecture ensure that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different BI tools as well.

Check out why Hevo is the Best:

  1. Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  2. Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  3. Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
  4. Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  5. Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
  6. Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, E-Mail, and support calls.
  7. Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!

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. You can read more about stored procedures here. Snowflake JavaScript API can help you create stored procedures.

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, and get a 30-day free trial). 

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:

Reproducing Table Content
Image Source: Self

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');
Stored Procedure Example
Image Source: Self

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:

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. You can find the complete list here.

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

Visit our Website to Explore Hevo

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. 

Want to take Hevo for a spin? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite first hand.

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

No-code Data Pipeline For your Snowflake