Sometimes the built-in system functions don't offer answers to the specific questions your organization has. Custom functions are necessary when managing and analyzing data. Snowflake provides a way to make diverse functions on the fly with user-defined functions.
This guide will walk you through getting set up with Snowflake and becoming familiar with creating and executing user-defined functions(UDFs) and user-defined table functions(UDTFs).
Review the material below and start with the essentials in the following section.
First, we'll go over how to create your Snowflake account and manage user permissions.
Snowflake lets you try out their services for free with a trial account. Follow the prompts to activate your account via email.
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
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.
The Results displays a status message of
Database UDF_DB successfully created if all went as planned.
create schema if not exists udf_schema_public;
Use the above command to whip up a schema called
The Results show a status message of
Schema UDF_SCHEMA_PUBLIC successfully created.
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.
The Results will display a status of
Table SALES successfully created if the sample data and table made it.
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.
If the function name doesn't specify the database and schema(e.x.
udf_db.udf_schema_public.udf_name) then it defaults to the active session. Since UDFs are database objects, it's better to follow their naming conventions. For this quick practice, we'll rely on our active session.
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 objects created in this guide.
drop table if exists sales;
Begin by dropping the child object before dropping parent database objects. Use the command above to start by removing the table.
Ensure you've successfully dropped the table in the Results section.
drop schema if exists udf_schema_public;
The command above drops the schema
The Results return should display
UDF_SCHEMA_PUBLIC successfully dropped.
drop database if exists udf_db;
Complete the process by dropping the parent object
Verify the database is entirely gone by checking the Results for
UDF_DB successfully dropped.
You have a good handle on UDFs by practicing both scalar and table functions. With our database objects cleared, it's time to look ahead.
Consider the potential in 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.