Snowflake Introduces JavaScript Stored Procedures

Author: Artin Avanes | Contributing Authors: Saber Mirzaei

How to Use Snowflake, Snowflake Technology

At Snowflake, we have been building a complete relational SQL data warehouse from day one. Also from day one, we have always put our customers first. We heard from customers about the need to extend our SQL programmability to allow encapsulating and running complex business logic more easily within Snowflake. We have a long history of extending our SQL programmability. For example, we provided built-in new SQL primitives that allow customers to query and process semi-structured data in a flexible fashion, and we introduced JavaScript user-defined functions (UDFs) customers can use to easily and intuitively manipulate and process variant and JSON data.

Today, we are reaching another important milestone in our extensibility journey: We are excited to announce the General Availability of Snowflake’s new JavaScript-based stored procedure language, which is production-ready and available in our standard edition. We have been piloting this feature for some time; it has been largely adopted and we have received great feedback since the start of this feature’s preview. With the feature becoming generally available, users in all clouds and all regions can now use Snowflake’s new stored procedure language to:

  • Implement and invoke complex business logic, such as ETL procedures, in an easier and more modern language within Snowflake to support arrays, exceptions, control structures, and garbage collection—to name a few relevant language attributes
  • Deploy well-known access control primitives without the need to manage an external environment
  • Build and run dynamic SQL
  • Do all of the above with seamless support for both structured and semi-structured data

With the introduction of Snowflake’s stored procedures, users will be able to:  

  • Use a first-class SQL object (procedure) along with the corresponding DDL statements
  • Grant schema privileges on stored procedures
  • Make use of procedural logic via IF/ELSE statements
  • Iterate over result sets  
  • Implement error handling via TRY/CATCH  
  • Use the owner’s rights stored procedure to protect access to objects (that is, there is no need for explicit permissions on the actual referenced objects)
  • Use the caller’s rights stored procedure to run all the supported SQL as the current user   

We are introducing a new DDL statement in Snowflake to allow users creating stored procedures. It includes the ability to define owner’s or caller’s rights as an optional parameter with owner’s rights as the default:


CREATE [ OR REPLACE ] PROCEDURE <function_name>( [ <arg_name> 
     <arg_type> [ , ... ] ] )
     RETURNS { <result_data_type> }
    [ LANGUAGE JAVASCRIPT ]
    [ CALLED ON NULL INPUT | 
      {RETURNS NULL ON NULL INPUT | STRICT } ]
    [ COMMENT = '<string_literal>' ]
    [ EXECUTE AS = OWNER | CALLER ]
    AS 
    $$ 
    <procedure_body>
    $$

Finally, we are introducing a light-weight JavaScript API users can use to easily wrap and execute numerous SQL queries:

Here are a few simple stored procedure examples illustrating the new capabilities and concepts.

Example #1:

The following is a stored procedure that iterates over tables in a source schema and copies them into the current schema using Create Table as Select. Queries run are logged in SP_OUTPUT.

create or replace procedure ctas_tables (SRC_DB STRING, SRC_SCHEMA STRING)
 returns float
language javascript
strict
as
$$

  var counter = 0;

  // create the logging table
var create_log_table_stmt = snowflake.createStatement({ sqlText: `create or 
replace table sp_output (qry string);` });
create_log_table_stmt.execute();

   // list the tables in the source schema
   var list_tables_stmt = snowflake.createStatement({ 
    sqlText: `SELECT table_catalog, table_schema, table_name 
              FROM information_schema.tables 
              WHERE table_catalog = :1 
              AND table_schema = :2
              AND table_type = 'BASE TABLE';`,
    binds: [SRC_DB, SRC_SCHEMA]
    });

    var tables = list_tables_stmt.execute();

  // for each table
  while (tables.next())
  {
    var database = tables.getColumnValue(1);
    var schema = tables.getColumnValue(2);
    var table = tables.getColumnValue(3);

    var ctas_qry = `create or replace table ` + table + ` as select * from ` + 
    database + `.` + schema + `.` + table + `;`
    var ctas_stmt = snowflake.createStatement({ sqlText: ctas_qry });
    ctas_stmt.execute();

    // log the ddl query to the output table
    var log_stmt = snowflake.createStatement({ sqlText: `insert into sp_output 
    (qry) values (:1);`,
    binds: [ctas_qry]});
    log_stmt.execute();

    counter++;

}

  return counter;
$$;

Example #2:

The following is a stored procedure that interrogates the information schema and returns the result set as “ARRAY of ARRAYS” (each row is represented as an ARRAY).

CREATE OR REPLACE PROCEDURE SP_ARRAY_GET_INF_SCHEMA_TABLES (
  )
RETURNS VARIANT
LANGUAGE JAVASCRIPT
AS $$
  var results_array = [];
    var rs = snowflake.createStatement( { sqlText: `SELECT TABLE_SCHEMA, 
TABLE_NAME FROM INFORMATION_SCHEMA.TABLES` } ).execute();
  while (rs.next()) {
    var schema_name = rs.getColumnValue(1);
    var table_name = rs.getColumnValue(2);
    results_array.push([schema_name, table_name]);
  }
  return results_array;
$$
;
CALL SP_ARRAY_GET_INF_SCHEMA_TABLES()

Here are a few more examples from our partners and users illustrating their use of Snowflake’s stored procedures:

Please visit our official documentation to learn more about this new feature. We have already received great feedback from customers, and we will continue working with them to further evolve and extend our stored procedure language. We also invite you to join our new Stored Procedure group in the Snowflake lodge.

Separately, we are in the final stages of releasing additional enterprise SQL capabilities, which we will announce during our first user conference (Snowflake Summit) in San Francisco. Stay tuned.