User Defined Functions (Udfs) in Snowflake

In this tutorial, we show you how to create user defined functions (UDF) in Snowflake.

In Snowflake, you can create:

• Functions in SQL and JavaScript languages
• Functions that return a single value (scalar)
• Functions that return multiple values (table)

(This article is part of our Snowflake Guide. Use the right-hand menu to navigate.)

Create data

If you want to follow the tutorials below, use the instructions from this tutorial on statistical functions to load some data into Snowflake. The data is 41 days of hourly weather data from Paphos, Cyprus.

Snowflake UDF SQL function

We start with a SQL example.

The code below takes the input weather conditions, described in the table column main, and converts that to an integer. This solves a common problem with machine learning: converting categorical data to an integer.

Notice that the function has parameters (dt varchar(20)) and a return value (int). The rest of it is just a SQL select statement.

The code below uses the iff() and regex() statement to see whether the word rain, cloud, etc., is found in the main column. It works by adding the numbers from 1 to 9. Since only one of these if statements will be true, then the sum will be one of the values 1 to 9, thus giving the weather conditions.

create or replace function weathercategorical (dt varchar(20) )
returns int
as $$select (iff(main regexp '.*Clear.*',1,0) +
iff(main regexp '.*Clouds.*',2,0) +
iff(main regexp '.*Rain.*',3,0) +
iff(main regexp '.*Thunderstorm.*', 4,0) +
iff(main regexp '.*Mist.*', 5, 0) +
iff(main regexp '.*Fog.*', 6, 0) +
iff(main regexp '.*Squall.*',7,0) +
iff(main regexp '.*Tornado.*', 8, 0) +
iff(main regexp '.*Haze.*', 9, 0))  
from weather as w where w.dt = dt$$

The date and time is in epoch time format. The SQL statement below calls the function weathercategorical for the date January 1, 2000, returning the scalar value 1, meaning clear weather.

sselect weathercategorical (946684800) from weather where dt = 946684800