Overview of Numeric FunctionsΒΆ
Here are some of the numeric functions we might use quite often.
Let us start spark context for this Notebook so that we can execute the code provided. You can sign up for our 10 node state of the art cluster/labs to learn Spark SQL using our unique integrated LMS.
val username = System.getProperty("user.name")
import org.apache.spark.sql.SparkSession
val username = System.getProperty("user.name")
val spark = SparkSession.
builder.
config("spark.ui.port", "0").
config("spark.sql.warehouse.dir", s"/user/${username}/warehouse").
enableHiveSupport.
appName(s"${username} | Spark SQL - Predefined Functions").
master("yarn").
getOrCreate
If you are going to use CLIs, you can use Spark SQL using one of the 3 approaches.
Using Spark SQL
spark2-sql \
--master yarn \
--conf spark.ui.port=0 \
--conf spark.sql.warehouse.dir=/user/${USER}/warehouse
Using Scala
spark2-shell \
--master yarn \
--conf spark.ui.port=0 \
--conf spark.sql.warehouse.dir=/user/${USER}/warehouse
Using Pyspark
pyspark2 \
--master yarn \
--conf spark.ui.port=0 \
--conf spark.sql.warehouse.dir=/user/${USER}/warehouse
abs
- always return positive numbersum
,avg
round
- rounds off to specified precisionceil
,floor
- always return integer.greatest
min
,max
rand
pow
,sqrt
cumedist
,stddev
,variance
Some of the functions highlighted are aggregate functions, eg: sum
, avg
, min
, max
etc.
%%sql
SELECT abs(-10.5), abs(10)
%%sql
USE itversity_retail
%%sql
SHOW tables
%%sql
SELECT order_item_order_id, order_item_subtotal FROM order_items
WHERE order_item_order_id = 2
%%sql
SELECT avg(order_item_subtotal) AS order_revenue_avg FROM order_items
WHERE order_item_order_id = 2
%%sql
SELECT order_item_order_id,
avg(order_item_subtotal) AS order_revenue_avg
FROM order_items
GROUP BY order_item_order_id
LIMIT 10
%%sql
SELECT order_item_order_id,
sum(order_item_subtotal) AS order_revenue_sum
FROM order_items
GROUP BY order_item_order_id
LIMIT 10
%%sql
SELECT
round(10.58) rnd,
floor(10.58) flr,
ceil(10.58) cl
%%sql
SELECT
round(10.44) rnd1,
round(10.44, 1) rnd1,
round(10.46, 1) rnd2,
floor(10.44) flr,
ceil(10.44) cl
%%sql
SELECT avg(order_item_subtotal) AS order_revenue_avg FROM order_items
WHERE order_item_order_id = 2
%%sql
SELECT round(avg(order_item_subtotal), 2) AS order_revenue_avg
FROM order_items
WHERE order_item_order_id = 2
%%sql
SELECT order_item_order_id,
round(avg(order_item_subtotal), 2) AS order_revenue_avg
FROM order_items
GROUP BY order_item_order_id
LIMIT 10
%%sql
SELECT order_item_order_id,
round(sum(order_item_subtotal), 2) AS order_revenue_sum
FROM order_items
GROUP BY order_item_order_id
LIMIT 10
%%sql
SELECT greatest(10, 11, 13, -13)
%%sql
SELECT rand() AS rand
%%sql
SELECT cast(round(rand() * 1) AS int) AS random_int
%%sql
SELECT order_item_order_id,
round(sum(order_item_subtotal), 2) AS order_revenue_sum,
min(order_item_subtotal) AS order_item_subtotal_min,
max(order_item_subtotal) AS order_item_subtotal_max
FROM order_items
GROUP BY order_item_order_id
LIMIT 10
%%sql
SELECT order_item_order_id, order_item_subtotal
FROM order_items
WHERE order_item_order_id = 2
%%sql
SELECT round(sum(order_item_subtotal), 2) AS order_revenue_sum,
min(order_item_subtotal) AS order_item_subtotal_min,
max(order_item_subtotal) AS order_item_subtotal_max
FROM order_items
WHERE order_item_order_id = 2