Spark SQL Basics
Overview
Learn the basics of Spark SQL: how to run standard ANSI SQL queries directly on your files and catalog tables.
What You Will Learn
In this lesson, you will learn:
- Spark SQL Interface: Executing SQL commands via
spark.sql(). - Table DDL: Creating, dropping, and inspecting catalog tables.
- Catalyst Optimization: How Spark SQL queries are optimized under the hood.
Detailed Concept Explanation
Spark SQL is a Spark module that integrates relational database processing with Spark's functional programming API. It allows you to run SQL queries directly on DataFrames or registered tables.
When you run a query using spark.sql("SELECT ..."), the string is parsed by Spark's query compiler. It generates a logical execution plan, which the Catalyst Optimizer optimizes before converting it into physical tasks executed across the cluster executors.
Code Examples
Input Dataset Preview
Below is the users table stored in the catalog:
| name | age |
|---|---|
| Alice | 22 |
| Bob | 30 |
Python (PySpark) Implementation
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName("SparkSQL").getOrCreate()
data = [("Alice", 22), ("Bob", 30)]
df = spark.createDataFrame(data, ["name", "age"])
df.createOrReplaceTempView("people")
# Run standard SQL query
older_people = spark.sql("SELECT name, age FROM people WHERE age >= 25")
older_people.show()
Expected Output
+----+---+
|name|age|
+----+---+
| Bob| 30|
+----+---+
Execution Plan Diagram (Python & Scala)
Scala Implementation
import org.apache.spark.sql.SparkSession
val spark = SparkSession.builder().appName("SQLScala").getOrCreate()
import spark.implicits._
val df = Seq(("Alice", 22), ("Bob", 30)).toDF("name", "age")
df.createOrReplaceTempView("people")
val result = spark.sql("SELECT name, age FROM people WHERE age >= 25")
result.show()
Expected Output
+----+---+
|name|age|
+----+---+
| Bob| 30|
+----+---+
SQL Implementation
-- Direct query syntax on registered catalog table
SELECT COUNT(*) AS total_count
FROM people
WHERE age >= 25;
Expected Output
Executing this SQL query returns:
| total_count |
|---|
| 1 |
Common Mistakes
- Assuming SQL is Slower Than DataFrames: Writing raw SQL queries and assuming they run slower than DataFrame code. Spark translates both raw SQL strings and DataFrame code into the exact same physical execution plan, meaning they run at identical speeds.
Best Practices
- Leverage SQL for Complex Joins: Use Spark SQL when writing complex, multi-table joins. It is often easier to read and maintain than chained DataFrame code.
Interview Perspective
Does writing queries in Spark SQL perform slower than using the DataFrame API?
No. Spark SQL and the DataFrame API share the same Catalyst Optimizer. Chained DataFrame transformations and raw SQL query strings are compiled into the exact same physical execution plan, yielding identical performance.