SQL: A Quick Review
When working with data, a solid knowledge of SQL is required to help think about your problem in terms of DataFrame transformations.
Have a quick scan through the topics that you're not too familiar with:
- Joins and Unions
- Aggregate Functions (GROUP BY)
- Window Functions
- What’s so great about them anyways? (See next slide)
- Indexes & Query Plans
A Deep Dive on Window Functions
- What limitations have you faced in your analytics when using GROUP BY in the past?
- How can you do rankings per each category efficiently?
- How can you get the entire row of the best-selling product? (imagine you want to see additional attributes of this product)
It is important to make sure you understand everything about window function semantics.
- partitionBy
- similar to
groupBy(but doesn’t reduce/aggregate information down to a single row)
- similar to
- orderBy
- Just FYI, you can
orderBy(F.col(“myColumn”).desc())for descending order.
- Just FYI, you can
- rowsBetween
- You should look up the default arguments for different window functions (e.g. lag, lead, first, last, max, min, row_number)
- rangeBetween Can you explain the difference between rowsBetween and rangeBetween?
These are all also concepts in SQL and not special to Spark.
Exercise: Windows
The next exercises uses Windows for several critical sections and we find that people are more successful in completing the exercises if they are familiarised with Windows. Follow the instructions here for the Windows Walkthrough.