Running SQL Queries on DataFrames

Analogy categoryAnalogies

Bridging DataFrames and SQL

Spark SQL lets you query a DataFrame using ordinary SQL text instead of chained method calls. You register a DataFrame as a temporary view with df.createOrReplaceTempView("orders"), then run spark.sql("SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id"). The call to spark.sql() itself returns a new DataFrame, so the SQL result can be piped straight back into .filter(), .join(), or .write() just like any other DataFrame.

🏏

Cricket analogy: It is like a scorer who can log a delivery either by tapping shorthand codes into a scoring app or by writing the ball-by-ball commentary in plain English — both end up updating the same match scorecard, just via a different input method.

The Catalyst Optimizer Unifies Both Paths

Whether you write df.groupBy("customer_id").sum("amount") or the equivalent SQL string, Spark parses both into the same unresolved logical plan, which the Catalyst optimizer then resolves against the catalog, optimizes with rules like predicate pushdown and constant folding, and finally converts into an identical physical plan for execution on the Tungsten engine. There is no performance penalty for choosing SQL over the DataFrame API, or vice versa — the choice is purely about which syntax best expresses the transformation for your team.

🏏

Cricket analogy: It is like DRS reviewing an lbw call from the on-field umpire's raised finger or from the third umpire's independent replay analysis — different review paths, but Hawk-Eye produces one authoritative final verdict either way.

Global vs Local Temporary Views

createOrReplaceTempView() registers a view scoped to the current SparkSession — it disappears once that session ends, and other sessions in the same application cannot see it. createGlobalTempView() instead registers the view under a special system database called global_temp, making it visible to every SparkSession sharing the same SparkContext for the lifetime of the application, but you must qualify references to it as global_temp.viewname in your SQL.

🏏

Cricket analogy: It is like a batter's personal net-practice notes that only they can see, versus a team's shared strategy whiteboard in the dressing room that every player on that tour can read until the series ends.

python

orders_df = spark.read.parquet("s3://data/orders/")

# Session-scoped: only visible to this SparkSession
orders_df.createOrReplaceTempView("orders")

# Application-scoped: visible from any session sharing the SparkContext
orders_df.createGlobalTempView("orders_global")

# Query with plain SQL
top_customers = spark.sql("""
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    WHERE order_date >= '2026-01-01'
    GROUP BY customer_id
    ORDER BY total_spent DESC
    LIMIT 10
""")

# Chain DataFrame API on the SQL result
top_customers.filter(top_customers.total_spent > 1000).show()

# Global views require the global_temp qualifier
spark.sql("SELECT * FROM global_temp.orders_global LIMIT 5").show()

Mixing SQL and the DataFrame API

There is no rule that says a pipeline must be pure SQL or pure DataFrame code — teams routinely use SQL for a complex multi-way join or window function where the declarative syntax is clearer, then switch to the DataFrame API for programmatic steps like looping over a list of columns to cast types. Because spark.sql() always returns a DataFrame, and any DataFrame can be re-registered as a view with createOrReplaceTempView(), you can hop between the two styles as many times as the pipeline needs.

🏏

Cricket analogy: It is like a captain who sets an aggressive slip cordon by verbal instruction for a specific over, then switches to hand signals for field adjustments the rest of the innings — whichever communication fits the moment.

Because spark.sql() output is a plain DataFrame, you can .explain() it just like any DataFrame API chain to inspect the physical plan — this is the fastest way to confirm that a SQL query and its DataFrame-API equivalent really do compile to the same plan.

Temporary views created with createOrReplaceTempView() are not persisted to the metastore and vanish when the SparkSession stops — they are not the same as managed tables created with saveAsTable(). If you need the view to survive across job runs, write it out as an actual table instead.

createOrReplaceTempView() registers a DataFrame as a session-scoped SQL view.
createGlobalTempView() registers a view visible across sessions under the global_temp database.
spark.sql() always returns a DataFrame, so SQL and DataFrame API code can be freely chained together.
SQL and DataFrame API queries compile to the same logical plan via the Catalyst optimizer, so there is no performance difference between them.
Global temp views must be referenced with the global_temp. prefix in SQL.
Temporary views are not persisted — use saveAsTable() for durable tables.
Use .explain() on any SQL result DataFrame to inspect its physical execution plan.