Introduction

In this lesson, we will explore data manipulation techniques in Apache Spark using Scala, focusing on aggregation, and grouping operations. We will start by creating an Employee DataFrame to work with, followed by creating a temporary view for SQL queries.

GROUP BY and Aggregate Functions

Grouping data is crucial when you want to perform operations on aggregated values. With GROUP BY, you can create summaries of data based on one or more columns. Let's calculate the average salary for each department:

val avgSalaryByDept = spark.sql("SELECT Department, AVG(Salary) AS AvgSalary FROM temp_view_employees GROUP BY Department")
avgSalaryByDept.show()

+-----------+----------+
|Department | AvgSalary|
+-----------+----------+
|    Finance|   65000.0|
|        HR |   50000.0|
|Engineering|   75000.0|
+-----------+----------+

Here, we have grouped employees by their department and calculated the average salary for each group. Aggregate functions such as SUM, COUNT, and AVG help us extract meaningful insights from our data.

Using the HAVING Clause

The HAVING clause is typically used with GROUP BY to filter groups based on aggregate values. For instance, suppose we want to find departments where the average salary is greater than 60000:

val highAvgSalaryDept = spark.sql("SELECT Department, AVG(Salary) AS AvgSalary FROM temp_view_employees GROUP BY Department HAVING AVG(Salary) > 60000")
highAvgSalaryDept.show()

+-----------+----------+
|Department | AvgSalary|
+-----------+----------+
|    Finance|   65000.0|
|Engineering|   75000.0|
+-----------+----------+

This retrieves only those departments that have an average salary exceeding 60000. It is essential for ensuring that the data analysis focuses on relevant subsets.

Summary

In this lesson, we explored how to create a DataFrame and make it accessible for SQL operations by creating a view. We learned to filter data using the WHERE clause, aggregate data using GROUP BY along with functions such as SUM, AVG, and COUNT, and apply the HAVING clause to filter grouped results. These operations enable effective data analysis and insight extraction from large datasets, forming the backbone of data engineering tasks in Spark.