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.
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.
Practice what you learned
1. Which method registers a DataFrame as a view visible only within the current SparkSession?
2. What must you prefix a global temporary view's name with when querying it in SQL?
3. What type of object does spark.sql("SELECT ...") return?
4. How does the performance of a SQL query compare to an equivalent DataFrame API chain?
5. What happens to a session-scoped temporary view when the SparkSession that created it stops?
Was this page helpful?
You May Also Like
Window Functions in Spark SQL
Understand how to compute running totals, rankings, and moving averages across partitions of rows using Spark's window function API.
Reading and Writing Data Sources
Learn how Spark's DataFrameReader and DataFrameWriter APIs handle formats like Parquet, CSV, and JDBC, plus schema handling and partitioned writes.
User-Defined Functions (UDFs)
Understand when and how to extend Spark SQL with custom Python or Pandas UDFs, and why built-in functions should always be preferred when available.
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