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.
If you are not familiar with the Snowflake User Interface, then please watch the video below.
Snowflake lets you try out their services for free with a trial account. Follow the prompts to activate your account via email.
If you already have a Snowflake account, you can use it. You just need a role with permission to create a database.
Log in to the web interface from your browser. The URL contains your account name and potentially the region.
Switch the account role from the default
ACCOUNTADMIN. This is not required, but will ensure your web UI aligns with the screen shots in this lab.
With your new account created and the role configured, you're ready to begin creating database objects in the following section.
With your Snowflake account at your fingertips, it's time to create the database objects.
Within the Snowflake web console, navigate to Worksheets and use a fresh worksheet to run the following commands.
create or replace database udf_db;
Build your new database named
udf_db with the command above.
create schema if not exists udf_schema_public;
Use the above command to whip up a schema called
create or replace table udf_db.udf_schema_public.sales as (select * from snowflake_sample_data.tpcds_sf10tcl.store_sales sample block (1));
Create a table named ‘sales' and import the sales data with this command. Bear in mind, importing the sample data will take a longer time to execute than the previous steps.
With the necessary database objects created, it's time to move onto the main course of working with a UDF in the next section.
With the database primed with sample sales data, we're almost ready to try creating a scalar UDF. Before diving in, let's first understand more about UDF naming conventions.
create function udf_max() returns NUMBER(7,2) as $$ select max(SS_LIST_PRICE) from udf_db.udf_schema_public.sales $$ ;
The SQL function
max returns the highest value in the column
The image shows the successful creation of the function
Summon your new UDF with the SQL command
Pictured above is the returned Results.
Now that you've practiced the basics of creating a UDF, we'll kick it up a notch in the next section by creating a UDF that returns a new table.
After creating a successful scalar UDF, move onto making a function that returns a table with a UDTF(user-defined table function).
create or replace function udf_db.udf_schema_public.get_market_basket(input_item_sk number(38)) returns table (input_item NUMBER(38,0), basket_item_sk NUMBER(38,0), num_baskets NUMBER(38,0)) as 'select input_item_sk, ss_item_sk basket_Item, count(distinct ss_ticket_number) baskets from udf_db.udf_schema_public.sales where ss_ticket_number in (select ss_ticket_number from udf_db.udf_schema_public.sales where ss_item_sk = input_item_sk) group by ss_item_sk order by 3 desc, 2';
The code snippet above creates a function that returns a table with a market basket analysis.
select * from table(udf_db.udf_schema_public.get_market_basket(6139));
Just like for the scalar UDF, this will execute your function.
Returned is the market basket analysis table based on the sample sales data.
You've practiced making UDTFs and have become familiar with UDFs. In the last section, we'll delete our unneeded database objects.
We've covered a lot of ground! Before we wrap-up, drop the practice database you created in this guide. This will remove the database and all of the tables and functions that you created.
Drop the database:
drop database if exists udf_db;
Verify the database is entirely gone by checking the Results for
UDF_DB successfully dropped.
You should now have a good handle on SQL UDFs by practicing both scalar and table functions. With our database objects cleared, it's time to look ahead.
Consider the potential of a sharable and secure user-defined function. You can learn how to share user-defined functions, such as the market basket analysis table, following this post about the power of secure UDFs.
For more information on UDF's: