Stored Procedures, much like functions, are created once and can be executed many times. They are created with the CREATE PROCEDURE command and are executed with the CALL command. It is important to note that Stored Procedures in Snowflake always return a single value or nothing at all. So, while SELECT statements can be executed inside a procedure, their results must be utilized somewhere within the stored procedure or narrowed down to a single value to be returned.
Stored Procedures can be written in one of the following languages:
Stored Procedures allow for procedural logic, error handling, as well as the dynamic execution of SQL Statements. Using store procedures, it is also possible to write code that executes with the privileges of the role owning the procedure, rather than the privileges of the role running the procedure. This allows the stored procedure owner to delegate the ability to perform specific operations to users who could not otherwise. There are limitations, which we will discuss when going into Owner's Rights and Caller's Rights Stored Procedures.
The CALL statement used to execute a stored procedure, much like other SQL commands, runs within and inherits context from that session, including session variables, the current database, the current warehouse, and others. The context inherited depends on whether the procedure is a caller's rights procedure or an owner's rights procedure. Changes the procedure makes to the session can persist after the end of the CALL if the stored procedure is a caller’s rights stored procedure. Owner’s rights stored procedures are not permitted to change the session state.
Caller's rights stored procedures adhere to the following rules in a session:
A Caller's Rights Stored Procedure can see variables that were set by statements before the procedure was called. Statements executed after the stored procedure can see the variable(s) set inside the procedure. This is a little difficult to grasp, so I've included an example below to help.
create table sv_table (f float); insert into sv_table (f) values (49), (51);
set SESSION_VAR1 = 50;
create procedure session_var_user() returns float language javascript EXECUTE AS CALLER as $$ // Set the second session variable var stmt = snowflake.createStatement( {sqlText: "set SESSION_VAR2 = 'I was set inside the StProc.'"} ); var rs = stmt.execute(); //we ignore the result in this case // Run a query using the first session variable stmt = snowflake.createStatement( {sqlText: "select f from sv_table where f > $SESSION_VAR1"} ); rs = stmt.execute(); rs.next(); var output = rs.getColumnValue(1); return output; $$ ;
CALL session_var_user(); +------------------+ | SESSION_VAR_USER | |------------------| | 51 | +------------------+
SELECT $SESSION_VAR2;
+------------------------------+
$SESSION_VAR2 I was set inside the StProc. +------------------------------+
Although you can set a session variable inside a stored procedure and leave it set after the end of the procedure, Snowflake does not recommend doing this.
TL;DR:
In many cases, this is the desired behavior since we may want to inherit contextual information. In cases where the stored procedure should be more isolated, Snowflake provides a few pointers:
Owner's Rights Stored Procedures:
Owner's Rights Procedures have several additional restrictions which affect the following:
If a stored procedure is created as an owner's rights stored procedure, then callers (other than the owner), cannot call the following built-in functions:
Owenr's rights stored procedures cannot execute ALTER USER statements which use the current user for the session. Owner's Rights Stored Procedures can execute ALTER USER statements that explicitly state the user as long as the user is not the current one.
Neither the owner nor the caller of an owner’s rights stored procedure necessarily has privileges to monitor execution of the stored procedure.
A user with the WAREHOUSE MONITOR privilege can monitor execution of the individual warehouse-related SQL statements within that stored procedure. Most queries and DML statements are warehouse-related statements. DDL statements, such as CREATE, ALTER, etc. do not use the warehouse and cannot be monitored as part of monitoring stored procedures.
An owner’s rights stored procedure does not have sufficient privileges to read information about users other than the caller. For example, running SHOW USERS LIKE <current_user> will show information about the current user, but the more general SHOW USERS does not work unless the current user is the only user.
The following SHOW commands are permitted:
Caller's rights stored procedures can execute any SQL statement that the caller has sufficient privileges to execute outside of a stored procedure. Owner's rights stored procedures, however, can call only a subset of SQL statements. These are the SQL statements that can be called from inside an owner's rights stored procedure:
All other SQL statements cannot be called from inside an owner's rights stored procedure.
If an owner’s rights stored procedure is called by a caller’s rights stored procedure, or vice-versa, the following rules apply:
- A stored procedure behaves as a caller’s rights stored procedure if and only if the procedure and the entire call hierarchy above it are caller’s rights stored procedures.
- An owner’s rights stored procedure always behaves as an owner’s rights stored procedure, no matter where it was called from.
- Any stored procedure called directly or indirectly from an owner’s rights stored procedure behaves as an owner’s rights stored procedure.
Create a stored procedure as an owner's rights stored procedure if all of the following is true:
Create a stored procedure as a caller's rights stored procedure if the following is true:
If a particular procedure can work correctly with either caller's rights or owner's rights, then the following rule might help you chose which rights to use:
Both stored procedures and UDFs (user-defined functions) make it easier to write modular code. However, there are important differences between UDFs and stored procedures.
CALL procedure_1(argument_1); -- Stored Procedure Call
SELECT function_1(argument_1) from table_1; -- Function Call
CALL command does not provide a place to store the returned value or a way to operate on it or pass it to another operation.y = stored_procedure1(x) --NOT ALLOWED
We have discussed some of the benefits of stored procedures, how session state can be handled in stored procedures, and examined the differences between User-Defined Functions and Stored Procedures. Now we will begin our deep dive into Stored Procedures by creating a new stored procedure that will, given the type of animal, return the names of all the creatures of that type.
In Snowflake, Stored Procedures are First-Class Objects[6], and as such can use the following commands: CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE, DESCRIBE PROCEDURE and SHOW PROCEDURES. Snowflake also provides the CALL command for executing Stored Procedures.
Let's walk through an example to view the different components of a Stored Procedure in Snowflake.
CREATE OR REPLACE PROCEDURE stproc1(animal varchar)
RETURNS table()
LANGUAGE sql
AS
declare
res resultset default (select pet:name::String as name, pet:species::String as species, pet:age_in_months::Number as age_in_months from pets where pet:species = :animal);
begin
return table(res);
end;
The parameters of stproc1():
CREATE OR REPLACE PROCEDURE stproc1()
Specifies the name (and optionally one or more arguments/inputs) for the stored procedure. [7]
RETURNS table()
Specifies the results returned by the stored procedure. This return value cannot be used since the call cannot be a part of an expression.
For result_data_type, use the Snowflake data type that corresponds to the type of the language that you are using:
LANGUAGE SQL
Specifies the language in which the stored procedure is written. A procedure can currently be written in one of the following languages: JavaScript, Snowflake Scripting (SQL), Scala (using Snowpark), and Java (using Snowpark)
AS $$ <procedure_logic> $$;
Defines the code executed by the stored procedure. The definition can consist of any valid code.
Note the following:
' or $$) around the procedure definition if:
CREATE PROCEDURE command. If the code is not valid, CREATE PROCEDURE will succeed, but errors will be returned when the stored procedure is called.For Snowpark (Scala and Java):
RUNTIME_VERSION = '<scala_or_java_runtime_version>'
The runtime version of Scala or Jva to use. The only supported varsions are:
2.12 - Scala11 - JavaPACKAGES = ( '<fully_qualified_package_name>' )
The fully qualified package name of the Snowpark library. This must be fully qualified in this format: com.snowflake:snowpark:<version> where the version is the version number or latest for the most recent version.
- For Scala specify version 1.1.0 or later.
- For Java specify the version 1..3.0 or later.
For the list of supported packages and versions, query the INFORMATION_SCHEMA.PACKAGES view for rows with LANGUAGE = 'scala' or LANGUAGE = 'java'. For example:
select * from information_schema.packages where language = 'scala';
select * from information_schema.packages where language = 'java';
HANDLER = '<fully_qualified_method_name>'
The fully qualified name of the method or function for the stored procedure, in the form: com.my_company.my_package.MyClass.myMethod
There are also optional parameters that can be specified in a CREATE PROCEDURE statement:
[[NOT] NULL]
Specifies whether the stored procedure can return NULL values or must only return non-null values
CALLED ON NULL INPUT or RETURNS NULL ON NULL INPUT | STRICT
CALLED ON NULL INPUT: Snowflake will call the procedure even if the inputs are null. It is up to the procedure to handle the nulls appropriately.RETURNS NULL ON NULL INPUT (or its synonym STRICT): Snowflake will not call the procedure if any of the inputs are null. A null value will be returned instead.CALLED ON NULL INPUTCOMMENT = '<string_literal>'
stored procedureEXECUTE AS <CALLER | OWNER>
CREATE PROCEDURE ... EXECUTE AS CALLER, then in the future the procedure will execute as a caller’s rights procedure.CREATE PROCEDURE ... EXECUTE AS OWNER, then the procedure will execute as an owner’s rights procedure.For Snowpark (Scala and Java):
IMPORTS = ( '<stage_path_and_file_name_to_read>' [ '<stage_path_and_file_name_to_read>' ...] )
The location (stage), path, and name of the file(s) to import. You must set the IMPORTS clause to include any files that your stored procedure dependes on. This clause can be omitted for an in-line stored procedure unless your code depends on classes defined outside the stored procedure or resource files. If you are writing a pre-compiled store procedure, you must include the JAR file containing the definition of the stored procedure. Additionally, each file in the IMPORTS clause must have a unique name even if the files are in different subdirectories or different stages.
TARGET_PATH = '<stage_path_and_file_name_to_write>'
The TARGET_PATH clause specifies the location to which Snowflake should write the compiled code (JAR file) after compiling the source code specified in the procedure definition. If this clause is omitted, Snowflake re-compiles the source code each time the code is needed.
If you specify this clause:
TARGET_PATH points to an existing file.IMPORTS and TARGET_PATH clauses, the file name in the TARGET_PATH clause must be different from each file name in the IMPORTS clause, even if the files are in different subdirectories or different stages.Snowflake stored procedures can also be written in JavaScript. With Javascript, SQL statements are executed using the Snowflake JavaScript API. This way it is possible to execute an SQL statement, retrieve the result set of a query, and retrieve metadata about the result set within a stored procedure. Each of these operations is carried out by calling methods on the following objects:
A typical stored procedure contains code similar to the following.
var my_sql_command1 = "delete from history_table where event_year < 2016";
var statement1 = snowflake.createStatement(my_sql_command1);
statement1.execute();
This code uses the object snowflake, which is a special object that does not need to be declared. This object is present within the context of each stored procedure and exposes the API for use. All other variables, such as my_sql_command1 and statement1 are created as JavaScript vars. You can also retrieve the ResultSet and iterate through it. For more on each of the four objects and API methods Snowflake provides, look at the Snowflake Example Documentation.
To finish off our discussion of Stored Procedures, I am going to delve a little bit into the access control features Snowflake provides for Stored Procedures. There are two types of privileges that stored procedures utilize: Those directly on the procedure and those on the database objects that the procedure can access. Similar to other database objects in Snowflake, stored procedures are owned by a role and have one or more privileges that can be granted to other roles.
There are currently two privileges that can apply to stored procedures: USAGE and OWNERSHIP. For a role to use a stored procedure, it must either be the owner of the stored procedure or have been granted USAGE on the procedure [8]. In addition, the roles executing the procedure must also have the USAGE privilege for all database objects accessed during the course of the procedure.
During this blog, we have examined Snowflake Stored Procedures from creation to execution. We have also discussed some of the key differences between stored procedures and user-defined functions, as well as examined session state and the two types of procedures Snowflake provides. To round out procedures, we briefly viewed access control in Snowflake and how privileges can affect procedure execution.
For more information on how Ippon Technologies, a Snowflake Services Partner, can help your organization utilize the benefits of Snowflake for a migration from a traditional data warehouse, data lake, or POC, contact sales@ipponusa.com.
This includes viewing, setting and unsetting session variables, calling functions such as CURRENT_USER() and querying INFORMATION_SCHEMA table functions that return results based on the current user. ↩︎
If your stored procedure needs values that are stored in the current session's SQL variables, then the values in those variables should be passed as explicit arguments to the procedure ↩︎
Only the stored procedure owner can view the source code of the stored procedure ↩︎
Enables behavior changes included in the specified release bundle for the current account. By default, behavior change bundles are not enabled during the pre-announcement period. Use this function to test behavior changes before they are enabled for your account. ↩︎
Disables behavior changes in the specified release bundle that are currently enabled by default. This is typically done in your production accounts to opt-out of the changes in the bundle while you continue testing the changes in your non-production accounts. ↩︎
An entity that can be dynamically created, destroyed, passed to a function or returned as a value ↩︎
The name does not need to be unique in a schema since procedures are identified by both name and arguments. In this way, you can have overloaded procedures, as long as the procedures differ by the number of arguments or the argument type. ↩︎
For a more in-depth view on Access Control in Snowflake, I refer you to Access Control Considerations in the Snowflake Documentation. ↩︎