User-defined functions with Snowpark in Snowflake - Commands & Configuration

Introduction

Sometimes you need to create your own small programs inside your database to reuse logic easily. User-defined functions with Snowpark let you write these programs in Python and run them close to your data in Snowflake.

When you want to reuse a calculation or transformation on your data multiple times without rewriting it.

When you need to run custom Python code on your data inside Snowflake for better performance.

When you want to keep your data logic centralized and easy to update.

When you want to avoid moving large data sets outside Snowflake for processing.

When you want to combine SQL and Python logic seamlessly in your queries.

Commands

Connect to your Snowflake account using snowsql with your account, user, role, warehouse, database, and schema specified.

Terminal

snowsql -a myaccount -u myuser -r myrole -w mywarehouse -d mydatabase -s public

Expected Output

Welcome to Snowflake

You are now connected to Snowflake as user 'myuser'.

→

-a - Specifies the Snowflake account name

→

-u - Specifies the username

→

-r - Specifies the role to use

→

-w - Specifies the warehouse to use

Create a user-defined function named add_one that takes an integer and returns the integer plus one using Python code inside Snowflake.

Terminal

CREATE OR REPLACE FUNCTION add_one(x INT) RETURNS INT LANGUAGE PYTHON RUNTIME_VERSION = '3.8' HANDLER = 'add_one' AS $$ def add_one(x):
    return x + 1
$$;

Expected Output

Function ADD_ONE successfully created.

→

OR REPLACE - Replaces the function if it already exists

→

LANGUAGE PYTHON - Specifies the function is written in Python

→

RUNTIME_VERSION = '3.8' - Specifies the Python version to use

Run the user-defined function add_one with input 5 to test that it returns 6.

Terminal

SELECT add_one(5);

Expected Output

ADD_ONE(5)

6

Check that the user-defined function add_one exists in your current schema.

Terminal

SHOW USER FUNCTIONS LIKE 'ADD_ONE';

Expected Output

created_on                      name     argument_signature  return_type  language  owner

2024-06-01 12:00:00.000 +0000  ADD_ONE  (X INT)             INT          PYTHON   MYUSER

Key Concept

If you remember nothing else from this pattern, remember: user-defined functions with Snowpark let you run your own Python code inside Snowflake close to your data for reusable and efficient processing.

Common Mistakes

Forgetting to specify the HANDLER function name inside the Python code.

Snowflake needs to know which Python function to run; without HANDLER, it cannot execute your code.

Always include HANDLER = 'function_name' matching the Python function defined inside the $$ code block.

Using unsupported Python versions or syntax not compatible with Snowflake's Python runtime.

Snowflake only supports specific Python versions and features; unsupported code will cause errors.

Use supported Python versions like 3.8 and test your code locally before deploying.

Not testing the function after creation with a SELECT statement.

You might miss syntax or runtime errors until you try to use the function.

Always run a simple SELECT with your function to verify it works as expected.

Summary

Connect to Snowflake using snowsql with your account details.

Create a Python user-defined function with CREATE FUNCTION specifying language, handler, and runtime.

Test the function by running a SELECT query calling it with sample input.

Verify the function exists using SHOW USER FUNCTIONS.

Practice

1. What is the main purpose of user-defined functions (UDFs) in Snowflake using Snowpark?

easy

A. To manage user permissions and roles

B. To create new database tables automatically

C. To run custom code inside Snowflake and reuse logic

D. To monitor Snowflake account usage

Solution

1. Step 1: Understand what UDFs do

   User-defined functions let you write your own code to run inside Snowflake, which helps reuse logic.

2. Step 2: Identify the main purpose

   UDFs simplify queries by encapsulating custom logic, not managing tables, permissions, or monitoring.

3. Final Answer:

   To run custom code inside Snowflake and reuse logic -> Option C

4. Quick Check:

   UDFs = Custom code reuse [OK]

Hint: UDFs run your own code inside Snowflake [OK]

Common Mistakes:

• Thinking UDFs create tables
• Confusing UDFs with security roles
• Assuming UDFs monitor usage

2. Which of the following is the correct way to define a simple Python UDF in Snowpark that adds 10 to an integer input?

easy

A. def add_ten(x): return x + 10

B. def add_ten(x): x + 10

C. def add_ten(x): print(x + 10)

D. def add_ten(x): return x - 10

Solution

1. Step 1: Check function syntax

   A Python function must use 'return' to output a value; def add_ten(x): return x + 10 correctly returns x + 10.

2. Step 2: Verify logic correctness

   def add_ten(x): return x + 10 adds 10 to input x, matching the requirement; others either miss return or subtract.

3. Final Answer:

   def add_ten(x): return x + 10 -> Option A

4. Quick Check:

   Return correct value with 'return' keyword [OK]

Hint: Always use 'return' to output from Python UDFs [OK]

Common Mistakes:

• Omitting the return statement
• Using print instead of return
• Returning wrong calculation

3. Given this Snowpark Python UDF code:

def multiply_by_two(x):
    return x * 2

result = multiply_by_two(5)

What is the value of result?

medium

A. 5

B. None

C. 25

D. 10

Solution

1. Step 1: Understand the function logic

   The function multiplies input x by 2 and returns the result.

2. Step 2: Calculate the function output for input 5

   5 multiplied by 2 equals 10, so result is 10.

3. Final Answer:

   10 -> Option D

4. Quick Check:

   5 * 2 = 10 [OK]

Hint: Multiply input by 2 to get output [OK]

Common Mistakes:

• Confusing multiplication with addition
• Assuming function returns None without return
• Mistaking input for output

4. You wrote this Snowpark Python UDF:

def square(x):
    x * x

When you run it, you get no output. What is the problem?

medium

A. Function name is invalid

B. Missing return statement to output the result

C. Syntax error due to missing colon

D. Input parameter x is not defined

Solution

1. Step 1: Check function syntax

   The function lacks a 'return' statement, so it does not output any value.

2. Step 2: Understand Python function behavior

   Without 'return', the function returns None by default, causing no output.

3. Final Answer:

   Missing return statement to output the result -> Option B

4. Quick Check:

   Functions need 'return' to output values [OK]

Hint: Always include 'return' to output from functions [OK]

Common Mistakes:

• Assuming last expression auto-returns
• Thinking function name causes error
• Confusing missing colon with missing return

5. You want to create a Snowpark Python UDF that returns the length of a string only if it is not empty; otherwise, it returns 0. Which function correctly implements this?

hard

A. def str_length(s):
if s:
return len(s)
else:
return 0

B. def str_length(s):
return len(s) if s != '' else 0

C. def str_length(s):
if s == '':
return len(s)
else:
return 0

D. def str_length(s):
return len(s) or 0

Solution

1. Step 1: Understand the condition for empty string

   Empty strings are falsy in Python, so 'if s:' checks if string is not empty.

2. Step 2: Verify correct return values

   If string is not empty, return its length; else return 0. def str_length(s): if s: return len(s) else: return 0 matches this logic exactly.

3. Final Answer:

   def str_length(s): if s: return len(s) else: return 0 -> Option A

4. Quick Check:

   Check string truthiness to decide length [OK]

Hint: Use 'if s:' to check non-empty string [OK]

Common Mistakes:

• Checking 's != \"\"' crashes on None
• Returning length for empty string incorrectly
• Using 'or' without proper condition