Windowed Aggregation
Apache Spark 2.4.4 / Spark SQL
@jaceklaskowski
/
StackOverflow
/
GitHub
The "Internals" Books:
Apache Spark
/
Spark SQL
/
Spark Structured Streaming
## Agenda 1. [Window Aggregate Functions](#/window-aggregate-functions) 1. [Window Functions](#/window-functions) 1. [Window Specification](#/window-specification) 1. [over Column Operator](#/over-column-operator) 1. [Demo](#/demo)
## Window Aggregate Functions 1. **Window Aggregate Functions** are standard functions that perform calculations over a group of records called **window** 1. Window defines a logical group of rows that are in some relation to the current record 1. Generate a value for **every row** * Unlike basic aggregations that generate **at most** the number of input rows 1. Switch to [The Internals of Spark SQL](https://bit.ly/spark-sql-internals) * [Window Aggregation Functions](https://jaceklaskowski.gitbooks.io/mastering-spark-sql/content/spark-sql-functions-windows.html)
## Window Functions 1. **Aggregate functions** * sum, avg, min, max, count, etc. * User-Defined Aggregate Functions (UDAFs) 1. **Ranking functions** * rank, dense_rank, percent_rank * ntile * row_number 1. **Analytic functions** * cume_dist, lag, lead
## Window Specification / WindowSpec 1. **Window Specification** defines a **window** (logical view) that includes the rows that are in relation to the current row (for every row) 1. (Logically) **partitions** dataset into groups * **partitionBy** 1. May define **frame boundary** * **rangeBetween**, **rowsBetween** 1. May define **ordering** * **orderBy** * Uses an unbounded window frame (rowFrame, unboundedPreceding, unboundedFollowing) for undefined ordering * Uses a growing window frame (rangeFrame, unboundedPreceding, currentRow) with ordering defined
## Window Factory Object 1. Use **Window** factory object to create **WindowSpec** <pre style="margin-left: 0px;"><code style="width: 900px;" class="lang-scala hljs">import org.apache.spark.sql.expressions.Window val departmentById = Window .partitionBy("department") .orderBy("id") .rangeBetween(Window.unboundedPreceding, Window.unboundedFollowing) </code></pre>
<!-- .slide: style="font-size: 90%" --> ## over Column Operator 1. **over** column operator defines a **windowing column** (aka **analytic clause**) 1. Applies window function over window ```scala val overUnspecifiedFrame = sum('someColumn).over() val overRange = rank over someWindow ``` 1. Use with **withColumn** or **select** operators ```scala numbers.withColumn("max", max("num") over dividedBy2) ``` 1. Switch to [The Internals of Spark SQL](https://bit.ly/spark-sql-internals) * [Defining Windowing Column (Analytic Clause) — over Operator](https://jaceklaskowski.gitbooks.io/mastering-spark-sql/content/spark-sql-Column.html#over)
<!-- .slide: style="font-size: 90%" --> ## Demo: Finding Most Populated Cities Per Country 1. [Exercise: Finding Most Populated Cities Per Country](https://jaceklaskowski.github.io/spark-workshop/exercises/sql/Finding-Most-Populated-Cities-Per-Country.html) using windowed aggregation 1. Compare the execution plan to a structured query with groupBy and join operators (web UI) * Disable [broadcast join](https://jaceklaskowski.gitbooks.io/mastering-spark-sql/spark-sql-joins-broadcast.html) using spark.sql.autoBroadcastJoinThreshold property
## Recap 1. [Window Aggregate Functions](#/window-aggregate-functions) 1. [Window Functions](#/window-functions) 1. [Window Specification](#/window-specification) 1. [over Column Operator](#/over-column-operator) 1. [Demo](#/demo)
# Questions? * Read [The Internals of Apache Spark](https://bit.ly/apache-spark-internals) * Read [The Internals of Spark SQL](https://bit.ly/spark-sql-internals) * Read [The Internals of Spark Structured Streaming](https://bit.ly/spark-structured-streaming) * Follow [@jaceklaskowski](https://twitter.com/jaceklaskowski) on twitter * Upvote [my questions and answers on StackOverflow](http://stackoverflow.com/users/1305344/jacek-laskowski)
