User-defined functions (UDFs) are a powerful way to extend the functionality of Snowflake. They allow you to encapsulate complex logic in a reusable function that can be called from SQL. This can save you time and effort, and it can also make your queries more efficient.
There are two types of UDFs in Snowflake: scalar and tabular. A scalar UDF returns a single value, while a tabular UDF returns a table. Scalar UDFs are the most common type of UDF, and they are used for a variety of tasks, such as:
- Calculating complex mathematical expressions
- Formatting data
- Validating data
- Performing string manipulation
Tabular UDFs are less common, but they can be very powerful. They can be used to:
- Join data from different tables
- Aggregate data
- Apply complex business logic
To create a UDF, you use the CREATE FUNCTION statement. The syntax for this statement is as follows:
CREATE FUNCTION function_name ( [parameter_name data_type] [, ...] ) RETURNS data_type AS BEGIN -- function body END;
The function_name is the name of the function. The parameter_name and data_type clauses define the parameters of the function. The RETURNS clause specifies the data type of the value that the function returns. The function body is the code that implements the function’s logic.
Once you have created a UDF, you can call it from SQL. The syntax for calling a UDF is as follows:
SELECT function_name ( [parameter_value] [, ...] );
The parameter_value clauses are the values that you want to pass to the function.
Here are some examples of how UDFs can be used in Snowflake:
- You can use a UDF to calculate the area of a circle.
- You can use a UDF to format data in a specific way.
- You can use a UDF to validate data before it is inserted into a table.
- You can use a UDF to join data from different tables.
- You can use a UDF to aggregate data.
- You can use a UDF to apply complex business logic.
The below shows a simple scalar UDF, which simply takes an input value and multiplies it by 100.
CREATE OR REPLACE FUNCTION simplecalc(inputval int) RETURNS INT AS $$ select inputval*100 $$; SELECT simplecalc(100);
Here is another SQL example, which returns the output of a query.
CREATE OR REPLACE FUNCTION get_total_articles() RETURNS INT LANGUAGE SQL AS $$ SELECT COUNT(*) FROM KODEY.KODEY_CO_UK.ARTICLES $$; SELECT get_total_articles();
We can implement a Python example too. This function returns a given value, depending on the input value.
CREATE OR REPLACE FUNCTION py_simplecalc(inputval int) RETURNS INT LANGUAGE PYTHON handler = 'calc' runtime_version = '3.8' AS $$ def calc(inputval): if inputval < 100: return inputval*100 else: return 0 $$; SELECT py_simplecalc(100);
We can call UDF’s directly in our SQL:
SELECT * FROM KODEY.KODEY_CO_UK.ARTICLES WHERE py_simplecalc(length(TITLE)) != 0
Much of the base text of this article was generated with the help of Google Bard