Introduction

This lesson focuses on executing SQL queries within Apache Spark using the Spark SQL module. We will explore how to utilize a DataFrame, specifically the Employee DataFrame, to run SQL queries including basic SQL operations such as SELECT, WHERE, and ORDER BY. Understanding the query execution flow in Spark will also be emphasized, along with handling query results as DataFrames. By the end of this lesson, you will be equipped with the knowledge necessary to manipulate and query data effectively in Spark.

Writing SELECT, WHERE, ORDER BY Queries

SELECT and WHERE

To select particular columns and filter the rows, use SELECT and WHERE as shown:

Dataset<Row> selectedEmployees = spark.sql("SELECT Name, Department FROM temp_view_employees WHERE Salary > 60000");
selectedEmployees.show();

ORDER BY

To sort your data in ascending or descending order, use ORDER BY:

Dataset<Row> orderedBySalary = spark.sql("SELECT * FROM temp_view_employees ORDER BY Salary DESC");
orderedBySalary.show();

Query Execution Flow in Spark

When you execute a query using spark.sql(), Spark handles the query in multiple stages:

1. Parsing: The SQL query is parsed into a logical plan.
2. Optimization: Spark applies various optimizations to the logical plan.
3. Physical Planning: The optimized plan is converted into one or more physical execution plans.
4. Execution: Spark executes the plan to compute the result.

Handling Query Results as DataFrames

The result of a spark.sql() query is a DataFrame. You can further manipulate this DataFrame or convert it into other formats such as a CSV file or an in-memory collection.

Dataset<Row> resultDF = spark.sql("SELECT * FROM temp_view_employees WHERE Age < 35");
resultDF.show();

// Converting to an Array or Collection
Row[] resultArray = (Row[]) resultDF.collect();

Creating Temporary Views

Temporary Views in Spark SQL can be created from DataFrames and can be queried using SQL. These views are tied to a session.

employeesDF.createOrReplaceTempView("temp_view_employees");
Dataset<Row> tempViewResults = spark.sql("SELECT * FROM temp_view_employees WHERE Department = 'Engineering'");
tempViewResults.show();