1

How to run Python code without defining pernament/temporary User Defined Function or stored procedure?

The use case is to run the code one time inside a script and it should avoid creating/dropping additional database objects.

I am searching for an equivalent of anonymous block (SQL) known from Snowflake Scripting but for Python or other supported languages(Java, Scala).

Snowflake Scripting - Using an Anonymous Block

If you don’t want to store the block in a stored procedure in the database, you can define and use an anonymous block. An anonymous block is a block that is not part of a stored procedure. You define the block as a separate, standalone SQL statement.

The BEGIN … END statement that defines the block also executes the block. (You don’t run a separate CALL command to execute the block.)

Improve this question

2 Answers 2

Reset to default
5

It is possible to define inline stored procedure using WITH <stored_procedure> AS ... CALL <stored_procedure> syntax.

CALL (with Anonymous Procedure)

Creates and calls an anonymous procedure that is like a stored procedure but is not stored for later use.

With this command, you both create an anonymous procedure defined by parameters in the WITH clause and call that procedure.

You need not have a role with CREATE PROCEDURE schema privileges for this command.

Sample:

WITH proc AS PROCEDURE(str TEXT)
RETURNS TEXT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'main'
AS
$$
import unicodedata
def main(session, str):
  return unicodedata.category(str)
$$
CALL proc('-');

Output:

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thats awesome Sir @Lukasz Szozda
I believe the code above should also have: PACKAGES = ('snowflake-snowpark-python'). Without the PACKAGES declaration, I get an error running the above code: 100357 (P0000): Python Interpreter Error: ModuleNotFoundError: No module named 'snowflake' in function PROC with handler main
@DaveWelden Hi Dave, Thank you for your comment. Strangely enough the provided code works for both Classic UI and Snowsight. SELECT CURRENT_VERSION(); "6.34.0". I have added explicit package declaration just in case.
Agreed it is odd. I get the error in both Classic and Snowsight. We are on CURRENT_VERSION 6.34.0, version 0.9.0 of Snowpark package, and we have accepted the 3rd party terms for Anaconda.
1

Snowsight natively supports Notebooks:

enter image description here

Writing Snowpark Code in Python Worksheets

Write Snowpark code in Python worksheets to process data using Snowpark Python in Snowsight. By writing code in Python worksheets, you can perform your development and testing in Snowflake without needing to install dependent libraries.

To develop with Python worksheets, do the following:

  1. Prepare roles and packages in Snowflake.
  2. Set up your worksheet for development.
  3. Write Snowpark code in your Python worksheet.
  4. Run your Python worksheet.

enter image description here

If we check query history, the CALL(with Anonymous Procedure) is still used:

enter image description here

When development is done, the code can be deployed as a stored procedure.

Related: Creating a Python Stored Procedure to Automate Your Python Worksheet Code

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.