Administrator

Overview of User Defined Functions in Snowflake

Overview of User Defined Functions in Snowflake

How to use User Defined Functions in Snowflake using SQL Sript, JavaSript & Java Programming Language.

Overview of User Defined Functions in Snowflake

User Defined Functions (aka UDFs) In Snowflake allows data developer to wrap their SQL logic into a function (so called User Defined Function) and bring simplicity to their SQL expressions. This episode is a comprehensive & practical guide with hands-on excercise on user defined functions (UDFs). It covers basics of user defined function including different type of UDFs like standard, secure, scalar & table. This tutorial also covers how to write user defined function standard sql as well as javascript & java programming language. This guide will help you to answer the following questions

  1. 🙋 What is secure user defined functions (UDFs) & what role does it play?
  2. 🙋 How to write a JavaScript user defined functions (UDFs) & their limitations?
  3. 🙋 Can I write user defined functions (UDFs) in Java programming language?
  4. 🙋 Does snowflake support UDAF (User Defined Aggregated Function) or Aggregated functions?
  5. 🙋 How to invoke table functions? Will they be part of select expression
  6. 🙋 Does Snowflake support function overloading?

User Defined Functions in Snowflake Video

You can watch the complete hands on video tutorial

User Defined Function SQL Script

Find all the SQL scripts used in ch-21 (Part-2) in this blog.

Step-1: The basic SQL construct for User Defined Function

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- creating a simple orders table
create or replace table orders(
    order_id number,
    customer_id_fk number,
    item_id_fk number,
    retail_price number(10,2),
    purchase_price number(10,2),
    sold_quantity number(3),
    country_code varchar(2)
);

-- inserting handful records
insert into orders 
(order_id, customer_id_fk, item_id_fk,retail_price,purchase_price, sold_quantity,country_code)
values
(1,1,1,99.2,89.6,2,'US'),
(2,8,2,17.1,11,10,'IN'),
(3,5,1,827,900.99,5,'JP'),
(4,10,4,200,172,7,'DE');

-- lets check the records
select * from orders;

create or replace function 
calculate_profit(retail_price number, purchase_price number, sold_quantity number)
returns number(10,2)
comment = 'this is simple profit calculator'
as 
$$
 select ((retail_price - purchase_price) * sold_quantity))
$$;

Lets use the User Defined Function in SQL Expression

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select 
    item_id_fk,
    retail_price,
    purchase_price, 
    sold_quantity, 
    calculate_profit(retail_price,purchase_price, sold_quantity) as profit_udf 
from orders ;

select 
    item_id_fk,
    retail_price,
    purchase_price, 
    sold_quantity,
    ((retail_price - purchase_price) * sold_quantity) as profit
from orders ;

Now try bit more complex User Defined Function

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- create bit more complex udf
create or replace function country_name(country_code string)
returns string
as 
$$
 select country_code || '-' ||case
        when country_code='US' then 'USA'
        when country_code='IN' then 'India'
        when country_code='JP' then 'Japan'
        when country_code='DE' then 'Germany'
        else 'Unknown'
    end
$$;

select 
    item_id_fk,
    retail_price,
    purchase_price, 
    sold_quantity,
    calculate_profit(retail_price,purchase_price, sold_quantity) as profit_udf ,
    country_name(country_code) as country_udf
 from orders ;
 
select 
    item_id_fk,
    retail_price,
    purchase_price, 
    sold_quantity,
    ((retail_price - purchase_price) * sold_quantity) as profit ,
    country_code || '-' ||case
        when country_code='US' then 'USA'
        when country_code='IN' then 'India'
        when country_code='JP' then 'Japan'
        when country_code='DE' then 'Germany'
        else 'Unknown'
        end as country
 from orders ;

How to monitor User defined functions in snowflake

1
2
3
4
5
6
7
8
9
10
11
-- now lets understand show and desc function features.
show functions;

-- filter by using object name
show functions like 'COUNTRY%';
show functions like'%PROFIT';

-- how to describe a function using desc function sql keywords
desc function country_name(string);


Convert Show Function result to a table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- how will you filter builtin vs user defined
-- lets use result scan table function and use the result as table data
show functions;
select * from table(result_scan( LAST_QUERY_ID()));
select * 
    from table(result_scan(query_id => '01a4xgt6-0000-9e5d-0002-5532000b643e')) t 
    where "is_builtin" = 'N';

select * from table(result_scan('01a4xgt6-0000-9e5d-0002-5532000b643e')) t 
    where 
    "name" in ('CALCULATE_PROFIT','COUNTRY_NAME') ;


select 
        "language",
        "schema_name" , 
        "is_builtin", 
        count(1) 
    from table(result_scan('01a4d5bf-0000-9e5d-0002-5532000b643e')) t 
    group by "language","schema_name" , "is_builtin";
  

SQL Scripts - Part 02 to Part 09

  1. Part-01 User Defined Function in Snowflake SQLs
  2. Part-02 JavaScript & SQL User Defined Function in Snowflake SQLs
  3. Part-03 Strict Property User Defined Function in Snowflake SQLs
  4. Part-04 Not Null Return User Defined Function in Snowflake SQLs
  5. Part-05 Secure User Defined Function in Snowflake SQLs
  6. Part-06 Inbuilt Vs. User Defined Function in Snowflake SQLs
  7. Part-07 Table User Defined Function in Snowflake SQLs
  8. Part-08 Method Overloading SQL Script
  9. Part-09 Java User Defined Function SQL & Java Code