Introduction

In this lesson, we will explore data manipulation techniques in Apache Spark using PySpark, 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:

from pyspark.sql import SparkSession

# Create Spark session
spark = SparkSession.builder.appName("PySparkAggregation").getOrCreate()

# Sample data
data = [("John", "Engineering", 70000),
        ("Jane", "Finance", 60000),
        ("Doe", "HR", 50000),
        ("Smith", "Engineering", 80000),
        ("Kim", "Finance", 70000)]

# Column names
columns = ["Name", "Department", "Salary"]

# Create DataFrame
df = spark.createDataFrame(data, columns)

# Create a temporary view
df.createOrReplaceTempView("temp_view_employees")

# SQL query to get average salary by department
avg_salary_by_dept = spark.sql("SELECT Department, AVG(Salary) AS AvgSalary FROM temp_view_employees GROUP BY Department")
avg_salary_by_dept.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:

# SQL query to get departments with average salary greater than 60000
high_avg_salary_dept = spark.sql("SELECT Department, AVG(Salary) AS AvgSalary FROM temp_view_employees GROUP BY Department HAVING AVG(Salary) > 60000")
high_avg_salary_dept.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.