User Defined Functions

In [0]:

%sql
create table if not exists dev.bronze.emp

%sql create table if not exists dev.bronze.emp

In [0]:

%sql
ALTER TABLE dev.bronze.emp
ADD COLUMNS (
    id INT,
    first_name STRING,
    last_name STRING,
    salary INT,
    state STRING,
    country STRING
);

INSERT INTO dev.bronze.emp (id, first_name, last_name, salary, state, country) VALUES
    (1, 'John', 'Smith', 50000, 'New York', 'USA'),
    (2, 'Jane', 'Doe', 60000, 'California', 'USA'),
    (3, 'Bob', 'Johnson', 45000, 'Texas', 'USA');

%sql ALTER TABLE dev.bronze.emp ADD COLUMNS ( id INT, first_name STRING, last_name STRING, salary INT, state STRING, country STRING ); INSERT INTO dev.bronze.emp (id, first_name, last_name, salary, state, country) VALUES (1, 'John', 'Smith', 50000, 'New York', 'USA'), (2, 'Jane', 'Doe', 60000, 'California', 'USA'), (3, 'Bob', 'Johnson', 45000, 'Texas', 'USA');

num_affected_rows	num_inserted_rows
3	3

In [0]:

%sql
CREATE OR REPLACE FUNCTION dev.bronze.tax_sql (sal double)
RETURNS DOUBLE 
LANGUAGE SQL
RETURN SAL * 0.3

%sql CREATE OR REPLACE FUNCTION dev.bronze.tax_sql (sal double) RETURNS DOUBLE LANGUAGE SQL RETURN SAL * 0.3

In [0]:

%sql
select dev.bronze.tax_sql(salary) as tax from dev.bronze.emp;

%sql select dev.bronze.tax_sql(salary) as tax from dev.bronze.emp;

tax
15000.0
18000.0
13500.0

In [0]:

%python
from pyspark.sql.functions import expr

df = spark.read.table('dev.bronze.emp')
df.withColumn("tax",expr('dev.bronze.tax_sql(salary)')).display()

%python from pyspark.sql.functions import expr df = spark.read.table('dev.bronze.emp') df.withColumn("tax",expr('dev.bronze.tax_sql(salary)')).display()

id	first_name	last_name	salary	state	country	tax
1	John	Smith	50000	New York	USA	15000.0
2	Jane	Doe	60000	California	USA	18000.0
3	Bob	Johnson	45000	Texas	USA	13500.0

In [0]:

%sql
-- SCALAR FUNCTION IN PYTHON

CREATE OR REPLACE FUNCTION dev.bronze.fruit_nutrition(fruit_name STRING)
RETURNS STRING
LANGUAGE PYTHON
AS 
$$
  import requests
  api_url = f'https://www.fruityvice.com/api/fruit/{fruit_name}'
  response = requests.get(api_url)
  data = response.json()
  return str(data.get('nutritions','NA'))
$$

%sql -- SCALAR FUNCTION IN PYTHON CREATE OR REPLACE FUNCTION dev.bronze.fruit_nutrition(fruit_name STRING) RETURNS STRING LANGUAGE PYTHON AS $$ import requests api_url = f'https://www.fruityvice.com/api/fruit/{fruit_name}' response = requests.get(api_url) data = response.json() return str(data.get('nutritions','NA')) $$

In [0]:

%sql
SELECT dev.bronze.fruit_nutrition('apple')

%sql SELECT dev.bronze.fruit_nutrition('apple')

dev.bronze.fruit_nutrition(apple)
{'calories': 52, 'fat': 0.4, 'sugar': 10.3, 'carbohydrates': 11.4, 'protein': 0.3}

In [0]:

%sql
CREATE OR REPLACE FUNCTION dev.bronze.get_emp(country STRING)
RETURNS TABLE(first_name STRING,last_name STRING)
LANGUAGE SQL
RETURN (
  SELECT first_name,last_name from dev.bronze.emp where country = country
)

%sql CREATE OR REPLACE FUNCTION dev.bronze.get_emp(country STRING) RETURNS TABLE(first_name STRING,last_name STRING) LANGUAGE SQL RETURN ( SELECT first_name,last_name from dev.bronze.emp where country = country )

In [0]:

%sql
SELECT * FROM dev.bronze.get_emp('USA')

%sql SELECT * FROM dev.bronze.get_emp('USA')

first_name	last_name
John	Smith
Jane	Doe
Bob	Johnson