User-defined functions (UDFs) let you extend the system to perform operations that are not available through the built-in, system-defined functions provided by Snowflake. Snowflake currently supports the following languages for writing UDFs:
SQL: A SQL UDF evaluates an arbitrary SQL expression and returns either scalar or tabular results.
JavaScript: A JavaScript UDF lets you use the JavaScript programming language to manipulate data and return either scalar or tabular results.
Java: A Java UDF lets you use the Java programming language to manipulate data and return scalar results.
Introduction to SQL UDFs:
A SQL UDF evaluates an arbitrary SQL expression and returns the result(s) of the expression.
The function definition can be a SQL expression that returns either a scalar (i.e. single) value or, if defined as a table function, a set of rows.
We start with SQL syntax,
Example 1: (Scalar)
Write an UDF to add ‘@@’ sign beginning and end of First name and Last name.
Input:
First Name: Tata
Last Name: Harrier
Expected Output:
@@Tata Harrier@@
Script:
CREATE OR REPLACE FUNCTION String_Concat(Firstname varchar(100),Lastname varchar(100))
returns VARCHAR
as
$$
SELECT ‘@@’||Firstname||’ ‘||Lastname||’@@’ FROM DUAL
$$
;
Output:
SELECT String_Concat(‘TATA’,’HARRIER’) as FULLNAME;
Example 2: (Scalar)
How to find biggest of two numbers ?
Input:
100,500
Expected Output:
500
Script:
CREATE OR REPLACE FUNCTION Biggest_of_Two_Numbers (Num1 float, Num2 float)
returns float
as
$$
SELECT CASE WHEN Num1 > Num2 THEN Num1 ELSE Num2 END
$$
;
Output:
SELECT Biggest_of_Two_Numbers (100,500) as Biggest_Number;
Note: When using a query expression in a SQL UDF, do not include a semicolon within the UDF body to terminate the query expression.
You can include only one query expression. The expression can include UNION [ALL].
Tabular SQL UDFs (UDTFs)
Snowflake supports SQL UDFs that return a set of rows, consisting of 0, 1, or multiple rows, each of which has 1 or more columns. Such UDFs are called tabular UDFs, table UDFs, or, most frequently, UDTFs (user-defined table functions).
Syntax:
Example 1: (Tabular)
Fetch TRIPDURATION, BIKEID, BIRTH_YEAR from Trips table by passing parameters START_STATION_ID, END_STATION_ID AND TRIPDURATION.
Below tables considered for this example,
Script:
CREATE OR REPLACE FUNCTION GetTripsDetail (START_STATION_IN number, END_STATION_IN number, TRIPDURATION_IN number)
returns table (TRIPDURATION number, BIKEID number, BIRTH_YEAR number)
as
$$
SELECT TRIPDURATION,BIKEID,BIRTH_YEAR FROM TRIPS
WHERE
TRIPS.START_STATION_ID = START_STATION_IN AND
TRIPS.END_STATION_ID = END_STATION_IN AND
TRIPS.TRIPDURATION < TRIPDURATION_IN
$$
;
Output:
SELECT TRIPDURATION,BIKEID,BIRTH_YEAR
from table(GetTripsDetail (537,435,200));
In next article we will see more about JavaScript UDFs, Java UDFs & Secure UDFs
References:-