SQL queries on DataFrames in Apache Spark - Step-by-Step Execution

Concept Flow - SQL queries on DataFrames

Create SparkSession

↓

Load Data into DataFrame

↓

Create Temp View from DataFrame

↓

Write SQL Query as String

↓

Run spark.sql(query)

↓

Get Result DataFrame

↓

Show or Use Result

This flow shows how to run SQL queries on Spark DataFrames by creating a temporary view and querying it with spark.sql.

Execution Sample

Apache Spark

from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()
data = [(1, 'Alice'), (2, 'Bob'), (3, 'Cathy')]
df = spark.createDataFrame(data, ['id', 'name'])
df.createOrReplaceTempView('people')
result = spark.sql('SELECT id, name FROM people WHERE id > 1')
result.show()

This code creates a DataFrame, registers it as a temp view, runs a SQL query to select rows where id > 1, and shows the result.

Execution Table

Step	Action	Input/Condition	Output/Result
1	Create SparkSession	null	spark session object created
2	Create DataFrame from list	data = [(1, 'Alice'), (2, 'Bob'), (3, 'Cathy')]	DataFrame with 3 rows and columns 'id', 'name'
3	Create temp view 'people'	df.createOrReplaceTempView('people')	Temporary SQL view 'people' created
4	Run SQL query	SELECT id, name FROM people WHERE id > 1	Result DataFrame with rows where id=2 and id=3
5	Show result	result.show()	Output: id name 2 Bob 3 Cathy
6	End	No more steps	Execution complete

💡 All steps executed; SQL query filtered rows with id > 1

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
spark	null	SparkSession object	SparkSession object	SparkSession object	SparkSession object
df	null	DataFrame with 3 rows	DataFrame with 3 rows	DataFrame with 3 rows	DataFrame with 3 rows
people (temp view)	null	null	Temp view created	Temp view created	Temp view created
result	null	null	null	DataFrame with 2 rows (id>1)	DataFrame with 2 rows (id>1)

Key Moments - 3 Insights

Why do we need to create a temporary view before running SQL queries on a DataFrame?

The temporary view acts like a table in SQL. spark.sql() runs queries on SQL tables or views, not directly on DataFrames. See execution_table step 3 where the temp view is created.

What happens if the SQL query references a table name that does not exist?

spark.sql() will throw an error because it cannot find the table or view. You must create the temp view first as shown in step 3.

Does the original DataFrame change after running the SQL query?

No, the original DataFrame stays the same. The SQL query returns a new DataFrame with filtered or selected data, as shown in variable_tracker for 'result'.

Visual Quiz - 3 Questions

Look at the execution_table at step 4. What does the SQL query select?

A All rows where id is greater than 1

B All rows where id is less than or equal to 1

C All rows with name 'Alice'

D All rows without any filter

💡 Hint

Check the 'Input/Condition' column at step 4 in execution_table.

According to variable_tracker, what is the state of 'result' after step 4?

A null

B DataFrame with 2 rows where id > 1

C DataFrame with 3 rows

D Temporary view object

💡 Hint

Look at the 'result' row and the 'After Step 4' column in variable_tracker.

If we skip creating the temp view (step 3), what will happen when running the SQL query?

A The query will run successfully with no results

B The original DataFrame will be modified

C spark.sql() will throw an error about missing table

D The query will return all rows

💡 Hint

Refer to key_moments question about missing temp view.

Concept Snapshot

SQL queries on DataFrames in Spark:
- Create SparkSession
- Load data into DataFrame
- Create temp view with createOrReplaceTempView('viewName')
- Run SQL query with spark.sql('SELECT ... FROM viewName WHERE ...')
- Result is a new DataFrame
- Use show() to display results

Full Transcript

This visual execution shows how to run SQL queries on Spark DataFrames. First, a SparkSession is created. Then data is loaded into a DataFrame. The DataFrame is registered as a temporary SQL view. Next, a SQL query string is written and executed with spark.sql(). The query filters rows where id is greater than 1. The result is a new DataFrame with filtered rows. Finally, the result is displayed using show(). Variables like spark, df, and result change state as the code runs. Key points include the need to create a temp view before querying and that the original DataFrame does not change. The quizzes test understanding of query filtering, variable states, and error conditions.

Practice

1. What does the createOrReplaceTempView method do in Apache Spark?

easy

A. It registers a DataFrame as a temporary SQL table for querying.

B. It permanently saves the DataFrame to disk as a table.

C. It converts a SQL query into a DataFrame.

D. It deletes a temporary view from Spark.

Solution

1. Step 1: Understand the purpose of createOrReplaceTempView

   This method registers a DataFrame as a temporary table in Spark's SQL context.

2. Step 2: Recognize its use in SQL queries

   Once registered, you can run SQL queries on the DataFrame using spark.sql().

3. Final Answer:

   It registers a DataFrame as a temporary SQL table for querying. -> Option A

4. Quick Check:

   Temporary view = SQL table for queries [OK]

Hint: Temp view means temporary SQL table for queries [OK]

Common Mistakes:

• Thinking it saves data permanently
• Confusing it with running SQL queries
• Assuming it deletes tables

2. Which of the following is the correct syntax to run a SQL query on a DataFrame registered as a temp view named people?

easy

A. spark.execute("SELECT * FROM people")

B. spark.query("SELECT * FROM people")

C. spark.sql("SELECT * FROM people")

D. spark.runSQL("SELECT * FROM people")

Solution

1. Step 1: Identify the correct method to run SQL queries

   The method to run SQL queries on Spark is spark.sql().

2. Step 2: Check the syntax for querying the temp view

   The query string should be passed inside spark.sql() as shown in spark.sql("SELECT * FROM people").

3. Final Answer:

   spark.sql("SELECT * FROM people") -> Option C

4. Quick Check:

   Use spark.sql() to run SQL queries [OK]

Hint: Use spark.sql() to run SQL queries on temp views [OK]

Common Mistakes:

• Using incorrect method names like spark.query
• Forgetting to use quotes around SQL query
• Confusing method names with other APIs

3. Given the following code, what will be the output of the SQL query?

data = [(1, "Alice", 29), (2, "Bob", 31), (3, "Cathy", 25)]
columns = ["id", "name", "age"]
df = spark.createDataFrame(data, columns)
df.createOrReplaceTempView("people")
result = spark.sql("SELECT name FROM people WHERE age > 28 ORDER BY age DESC")
result.show()

medium

A. [["Bob"], ["Cathy"]]

B. [["Alice"], ["Bob"]]

C. [["Cathy"]]

D. [["Bob"], ["Alice"]]

Solution

1. Step 1: Analyze the data and filter condition

   Rows with age > 28 are Alice (29) and Bob (31).

2. Step 2: Apply ORDER BY age DESC

   Ordering by age descending gives Bob (31) first, then Alice (29).

3. Final Answer:

   [["Bob"], ["Alice"]] -> Option D

4. Quick Check:

   Filter age > 28 and order desc = Bob, Alice [OK]

Hint: Filter then order by age descending to get correct order [OK]

Common Mistakes:

• Mixing ascending and descending order
• Including rows with age <= 28
• Confusing column names in SELECT

4. Identify the error in the following code snippet:

df.createOrReplaceTempView("employees")
result = spark.sql("SELECT * FROM employee WHERE salary > 50000")
result.show()

medium

A. The DataFrame was not created before creating temp view.

B. The temp view name is 'employees' but query uses 'employee'.

C. The SQL query syntax is incorrect.

D. The show() method cannot be used on SQL query results.

Solution

1. Step 1: Check temp view name and SQL query table name

   The temp view is named "employees" but the query uses "employee" (missing 's').

2. Step 2: Understand impact of name mismatch

   This causes a runtime error because the table "employee" does not exist.

3. Final Answer:

   The temp view name is 'employees' but query uses 'employee'. -> Option B

4. Quick Check:

   Temp view and query table names must match exactly [OK]

Hint: Check temp view and query table names match exactly [OK]

Common Mistakes:

• Assuming SQL syntax error instead of name mismatch
• Thinking show() can't be used on results
• Ignoring case sensitivity in table names

5. You have a DataFrame sales with columns product, region, and amount. You want to find the total sales amount per region but only for regions where total sales exceed 1000. Which SQL query on the temp view sales will achieve this?

hard

A. SELECT region, SUM(amount) AS total_sales FROM sales GROUP BY region HAVING total_sales > 1000

B. SELECT region, SUM(amount) AS total_sales FROM sales WHERE total_sales > 1000 GROUP BY region

C. SELECT region, SUM(amount) FROM sales GROUP BY region WHERE SUM(amount) > 1000

D. SELECT region, SUM(amount) AS total_sales FROM sales GROUP BY region WHERE total_sales > 1000

Solution

1. Step 1: Understand filtering on aggregated data

   Filtering on aggregated results requires the HAVING clause, not WHERE.

2. Step 2: Check correct use of HAVING with alias

   Using HAVING total_sales > 1000 after GROUP BY is correct as in SELECT region, SUM(amount) AS total_sales FROM sales GROUP BY region HAVING total_sales > 1000.

3. Final Answer:

   SELECT region, SUM(amount) AS total_sales FROM sales GROUP BY region HAVING total_sales > 1000 -> Option A

4. Quick Check:

   Use HAVING to filter grouped aggregates [OK]

Hint: Use HAVING, not WHERE, to filter grouped results [OK]

Common Mistakes:

• Using WHERE instead of HAVING for aggregates
• Placing WHERE after GROUP BY
• Not aliasing SUM(amount) before HAVING