Snowflake - User Defined Functions - Working Session

You can get all snowflake Videos, PPTs, Queries, Interview questions and Practice files in my Udemy course for very less price.. I will be updating this content and will be uploading all new videos in this course.

My Snowflake Udemy Course:
https://www.udemy.com/course/snowflak...

I can be reachable on [email protected].

=======================
User defined functions
========================
// Create database and schemas if not exists
CREATE DATABASE IF NOT EXISTS MYOWN_DB;
CREATE SCHEMA IF NOT EXISTS MYFUNCTIONS;

// If SNOWFLAKE_SAMPLE_DATA database is not available, first get it
// I have explained in Snowsight video how to get this SNOWFLAKE_SAMPLE_DATA

============
Scalar UDFs
============
// Create funtion to calculate Tax

// SCENARIO 1 - Fixed Tax of 10%
CREATE OR REPLACE FUNCTION MYFUNCTIONS.CUST_TAX(PRICE FLOAT)
RETURNS FLOAT
AS
$$
(PRICE * 10)/100
$$
;

GRANT USAGE ON FUNCTION MYFUNCTIONS.CUST_TAX(FLOAT) TO PUBLIC;

select O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE, CUST_TAX(O_TOTALPRICE)
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS;


// SCENARIO 2 - Variable Tax
CREATE OR REPLACE FUNCTION MYFUNCTIONS.CUST_TAX(PRICE FLOAT, TAX_PER FLOAT)
RETURNS FLOAT
AS
$$
(PRICE * TAX_PER)/100
$$
;

// Grant the access on function to all users/roles
GRANT USAGE ON FUNCTION MYFUNCTIONS.CUST_TAX(FLOAT, FLOAT) TO PUBLIC;


// Get the tax on all orders
select O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE, CUST_TAX(O_TOTALPRICE, 7.5)
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS;


=============
Tabular UDFs
=============
// Create some sample tables

CREATE OR REPLACE TABLE PUBLIC.COUNTRIES
(COUNTRY_CODE CHAR(2), COUNTRY_NAME VARCHAR);

INSERT INTO PUBLIC.COUNTRIES(COUNTRY_CODE, COUNTRY_NAME) VALUES
('FR', 'FRANCE'),
('US', 'UNITED STATES'),
('IN', 'INDIA'),
('SP', 'SPAIN');

CREATE OR REPLACE TABLE PUBLIC.USER_ADDRESSES
(USER_ID INTEGER, COUNTRY_CODE CHAR(2));

INSERT INTO PUBLIC.USER_ADDRESSES (USER_ID, COUNTRY_CODE) VALUES
(100, 'SP'),
(123, 'FR'),
(567, 'US'),
(420, 'IN');


// Create a function to fetch country name of customer
CREATE OR REPLACE FUNCTION MYFUNCTIONS.GET_COUNTRIES_FOR_USER(ID NUMBER)
RETURNS TABLE (USER_ID NUMBER, COUNTRY_NAME VARCHAR)
AS
$$
SELECT ID, C.COUNTRY_NAME FROM PUBLIC.USER_ADDRESSES A, PUBLIC.COUNTRIES C
WHERE A.USER_ID = ID
AND C.COUNTRY_CODE = A.COUNTRY_CODE
$$
;


// Fetch country name for specified user id
SELECT * from table(MYFUNCTIONS.GET_COUNTRIES_FOR_USER(100));

// Fetch country name for all users
SELECT F.* from PUBLIC.USER_ADDRESSES, table(MYFUNCTIONS.GET_COUNTRIES_FOR_USER(USER_ID)) F;