" durati

U3">

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:

This guide will walk you through setting up Snowflake and getting familiar with

Prerequisites

If you are not familiar with the Snowflake User Interface, then please watch the video below.

What You'll Learn

What You'll Need

What You'll Build

First, we'll go over how to create your Snowflake account and manage user permissions.

Create a Snowflake Account

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.

Access Snowflake's Web Console

https://<your-account-name>.snowflakecomputing.com/console/login

Log in to the web interface from your browser. The URL contains your account name and potentially the region.

Changing your role

Snowflake_SwitchRole_DemoUser-image

Switch the account role from the default SYSADMIN to 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.

1. Create a new Database

create or replace database udf_db;

Build your new database named udf_db with the command above.

Snowflake_udf_CreateDB-image

2. Create a new Schema

create schema if not exists udf_schema_public;

Use the above command to whip up a schema called udf_schema_public.

Snowflake_udf_CreateSchema-image

3. Copy Sample Data Into New Table

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.

Snowflake_udf_CreateTable-image

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.

1. Create UDF

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 SS_LIST_PRICE.

Snowflake_udf_max-image

The image shows the successful creation of the function udf_max.

2. Call the UDF

select udf_max();

Summon your new UDF with the SQL command select.

Snowflake_select_udf_max-image

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).

1. Create a UDTF

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.

Snowflake_udtf-image

2. Run the UDTF

select * from table(udf_db.udf_schema_public.get_market_basket(6139));

Just like for the scalar UDF, this will execute your function.

Snowflake_select_udtf-image

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.

1. Drop the Database

Drop the database: udf_db.

drop database if exists udf_db;

Snowflake_udf_DropDB-image

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.

What we've covered

  • Registered a Snowflake account
  • Configured role permissions
  • Created a database and other objects
  • Created a table to analyze data with a UDTF
  • Queried a custom UDF
  • Droping a database to clean up all objects

Related Resources

For more information on UDF's:

/script>