May 02, 2019 | 4 Min Read
Author: Artin Avanes | Contributing Authors: Saber Mirzaei
How to Use Snowflake, Snowflake Technology
- 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:
Here are a few simple stored procedure examples illustrating the new capabilities and concepts.
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.
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).
Here are a few more examples from our partners and users illustrating their use of Snowflake’s stored procedures:
- How to ingest and enrich IOT data with Apache NiFi and Snowflake stored procedures
- How to use Snowflake’s stored procedure to implement a machine learning algorithm
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.