Running SQL Queries on Spark
Spark SQL lets you query DataFrames using standard ANSI SQL syntax through spark.sql(), giving analysts and engineers a familiar interface on top of the same distributed execution engine that powers the DataFrame API. Under the hood, a SQL string and an equivalent chain of DataFrame method calls compile down to the exact same optimized physical plan, so neither approach has an inherent performance advantage over the other.
Cricket analogy: It's like two commentators describing the same delivery differently, one saying 'good length outside off, edged to slip' (SQL) and the other narrating frame by frame through replay angles (DataFrame API), yet both are describing the identical ball that was bowled.
Registering Tables and Views
Before you can query a DataFrame with SQL, it needs to be registered as a temporary view using df.createOrReplaceTempView('table_name'), which makes it queryable via spark.sql() for the duration of the SparkSession, or as a global temp view accessible across sessions in the same application. Spark can also connect to a persistent metastore, such as Hive's, to query permanent tables that outlive any single job.
Cricket analogy: It's like a scorer announcing a player's name so the commentary team can refer to them by name for the rest of the match (temp view), versus that player being permanently registered in the national team database for every future series (metastore table).
-- PySpark: register a DataFrame as a temp view, then query it with SQL
df = spark.read.parquet("s3://data/orders/")
df.createOrReplaceTempView("orders")
result = spark.sql("""
SELECT region, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY region
ORDER BY total_amount DESC
""")
result.show()
-- Equivalent DataFrame API call, compiles to the same physical plan
from pyspark.sql import functions as F
result2 = (
df.filter(F.col("order_date") >= "2026-01-01")
.groupBy("region")
.agg(F.count("*").alias("order_count"), F.sum("amount").alias("total_amount"))
.orderBy(F.desc("total_amount"))
)
The Catalyst Optimizer
Every SQL query or DataFrame call is compiled through four phases inside Catalyst: analysis, which resolves column and table references against the catalog; logical optimization, which applies rules like predicate pushdown and constant folding; physical planning, which generates candidate execution strategies and picks one using a cost model; and code generation, which produces optimized JVM bytecode via whole-stage code generation for the final plan.
Cricket analogy: It's like a captain's selection process: first checking which players are actually fit and available (analysis), then trimming the squad by dropping clearly weaker options (logical optimization), then choosing the best batting order from remaining candidates by matchup data (physical planning), and finally briefing the exact game plan to execute (code generation).
Call df.explain(mode='formatted') or df.explain(True) to see all four Catalyst phases for any query. Comparing the 'Parsed Logical Plan' to the 'Optimized Logical Plan' is a quick way to see exactly which rules, like predicate pushdown, Catalyst applied to your specific query.
SQL API vs. DataFrame API: Choosing Between Them
Because SQL strings and DataFrame method chains compile to the same physical plan, the choice between them is mostly about team ergonomics: SQL is often easier for analysts already fluent in it and integrates cleanly with BI tools over Spark's Thrift server, while the DataFrame API is easier to unit test, refactor, and compose with control flow in a general-purpose language like Python or Scala. Many production pipelines mix both freely within the same job.
Cricket analogy: It's like a captain choosing between calling a well-known set field like 'standard off-side field' by name (SQL) versus positioning each fielder individually by hand (DataFrame API); both place the same fielders, but the named call is faster to communicate to an experienced team.
User-defined functions (UDFs), whether SQL UDFs or Python UDFs registered via spark.udf.register(), are opaque to Catalyst: the optimizer cannot see inside them to apply rules like predicate pushdown, and Python UDFs additionally pay a serialization cost moving data between the JVM and a Python process. Prefer Spark's built-in SQL functions whenever an equivalent exists.
- spark.sql() runs standard SQL against DataFrames registered as temp views, compiling to the same physical plan as equivalent DataFrame method calls.
- createOrReplaceTempView() registers a DataFrame for SQL access within a session; Spark can also connect to a persistent metastore like Hive's.
- Catalyst compiles queries through four phases: analysis, logical optimization, physical planning, and code generation.
- df.explain() exposes all four plan stages, useful for verifying optimizations like predicate pushdown actually applied.
- Choosing SQL vs. the DataFrame API is mostly about ergonomics and tooling, not performance, since both compile to the same plan.
- UDFs are opaque to Catalyst and block optimizations like predicate pushdown, so built-in functions should be preferred when available.
Practice what you learned
1. What must you do before querying a DataFrame with spark.sql()?
2. Which of these is NOT one of Catalyst's four query compilation phases?
3. Why is choosing between the SQL API and the DataFrame API mostly a matter of ergonomics rather than performance?
4. Why do UDFs typically hurt Spark SQL query performance compared to built-in functions?
Was this page helpful?
You May Also Like
DataFrames Basics
How Spark's DataFrame API represents distributed, schema-aware tabular data and why it outperforms hand-written RDD code.
Schemas and Data Types
How Spark defines DataFrame structure with StructType schemas and a rich type system, and why explicit schemas matter in production.
Transformations and Actions
How Spark's lazy execution model splits operations into transformations that build a plan and actions that trigger real computation.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics
ProgrammingPowerShell Study Notes
Programming · 30 topics