🔬 Two APIs, one optimizer
PySpark gives you two ways to query data: the SQL string API and the DataFrame method API. It is tempting to think of these as different engines — they are not. Both compile down to the same logical plan and are then optimised by the same component: the Catalyst optimizer.
spark.sql("SELECT city, COUNT(*) FROM txn GROUP BY city") and df.groupBy("city").count() produce an identical logical plan. Catalyst applies the same rule-based and cost-based transformations to both before handing them to the physical planner. Use whichever is more readable for the task — there is no performance difference between the two forms for the same query.
This means you can mix them freely in the same pipeline. A common pattern is to do complex filtering in the DataFrame API (where you have full Python expressiveness) and then pass the filtered result to a SQL query for the aggregation, which colleagues who know SQL can read and audit.
| API | Syntax example | Compiled by |
|---|---|---|
| Spark SQL | spark.sql("SELECT ...") |
Catalyst optimizer → physical plan |
| DataFrame API | df.groupBy(...).agg(...) |
Catalyst optimizer → same physical plan |
📋 createOrReplaceTempView
Before you can query a DataFrame with spark.sql(), you need to give it a name. That is what createOrReplaceTempView() does: it registers the DataFrame as a named virtual table inside the current SparkSession.
- ▸ In-memory only. The view is never written to disk. It is a pointer to the DataFrame's execution plan stored in the session's catalog. Closing the SparkSession destroys it.
- ▸ Session-scoped. The view is only visible to SQL queries run through the same
SparkSessionobject. Other SparkSessions (including ones in other threads) cannot see it. - ▸ OrReplace semantics. If a view with the same name already exists in this session, it is silently replaced. This is useful in notebooks where you re-run cells — no need to drop the view first.
The view does not copy the data. It stores only the logical plan. When you run spark.sql("SELECT ... FROM transactions"), Spark looks up "transactions" in the session catalog, retrieves the plan, merges it with the SQL query, and then optimises and executes the combined plan in a single pass.
💻 SouthBank India — Spark SQL demo
SouthBank India processes ten transactions across five accounts and three cities. Rohan uses three different query patterns to analyse the data: a standard GROUP BY, a conditional aggregate with CASE WHEN, and a mixed approach that filters in the DataFrame API before switching to SQL.
🗄️ SQL inside Spark — the three queries explained
Query 1 — GROUP BY with aggregate functions
The first query groups by city and txn_type, then counts rows and sums the amount. This is standard ANSI SQL. You can copy this query into any SQL tool and it reads identically — that is the point. Mumbai has three credits totalling ₹91,400 but only two debits totalling ₹13,000, making it a strongly credit-positive city in this dataset.
Query 2 — CASE WHEN inside an aggregate
SQL conditional aggregation: a CASE WHEN expression inside SUM() flips debits to negative so the sum represents the net position. ACC004 has a net of -₹5,000 because it only has one debit transaction and no credits. This pattern is sometimes called a "pivot without pivoting" — you can compute multiple metrics in one pass without joining or grouping twice.
Query 3 — DataFrame filter then SQL
This is the mixed pattern. The DataFrame API's filter() is used to push a compound predicate (type equals "debit" AND amount exceeds ₹10,000) in Python. The filtered result is registered as a second temp view called large_debits. The SQL query then selects and sorts from that view. The result contains only three transactions: TXN009 (₹22,000), TXN007 (₹18,000), and TXN002 (₹12,000). The ₹8,000 and ₹5,000 debits are correctly excluded.
⚖️ When to use SQL vs the DataFrame API
Both APIs compile to the same plan, so the choice is about readability and team workflow, not performance. Here is a practical guide.
| Situation | Prefer | Reason |
|---|---|---|
| Complex multi-table queries | SQL | Easier to read and debug; analysts can review without knowing PySpark |
| Dynamic column selection | DataFrame | Column list determined at runtime from a Python variable; hard to express in SQL strings safely |
| Existing SQL knowledge on team | SQL | Lower learning curve; existing skills transfer directly |
| Building reusable transform functions | DataFrame | Functions accept DataFrames as arguments; easier to unit-test with chispa |
| Window functions | Either | Syntax is nearly identical; OVER (PARTITION BY ... ORDER BY ...) in SQL vs Window.partitionBy(...).orderBy(...) in DataFrame |
🌐 Global temp views
A regular temp view lives only in the SparkSession that created it. If you have multiple SparkSessions in the same Spark application (uncommon but possible, for example in some Spark Structured Streaming setups), you can share a view across all of them using a global temporary view.
Call df.createOrReplaceGlobalTempView("my_table") to register a global view. Access it in SQL by prefixing the database name: spark.sql("SELECT * FROM global_temp.my_table"). The global temp view persists as long as the Spark application is alive — it survives individual SparkSession restarts within the same application.
In practice, most pipelines use a single SparkSession per job and never need global temp views. They are most useful in notebooks that create multiple SparkSessions for isolation, or in long-running Spark applications with multiple logical stages that need to share a reference dataset.
💡 Rohan's SQL-first pattern
"At SouthBank India, Rohan writes all exploratory queries in SQL first, then converts the final version to DataFrame API for the production pipeline where testability matters more. SQL lets him iterate in a notebook quickly — he can paste the query into a shared doc for the risk team to review. Once it is signed off, he wraps the same logic in a Python function that takes a DataFrame and returns a DataFrame, adds chispa assertions, and ships it. The logic is identical; the form changes."
This pattern has two practical benefits. First, SQL strings are easy to share with non-engineers for review and approval. Second, converting a working SQL query to the DataFrame API is a straightforward mechanical translation — you are not changing the logic, just the syntax. The risk of introducing bugs in the translation is low because the SQL version serves as a reference implementation to test against.
🔀 CASE WHEN vs when()
SQL's CASE WHEN and PySpark's F.when() express the same conditional logic. Both compile to the same physical plan node in Catalyst.
| API | Syntax | Notes |
|---|---|---|
| SQL | CASE WHEN col > x THEN 'A' ELSE 'B' END | Standard ANSI SQL; readable by anyone who knows SQL |
| PySpark | when(col("col") > x, "A").otherwise("B") | Chainable; can be stored in a Python variable and reused |
Use SQL's CASE WHEN when writing queries that analysts will read. Use F.when().otherwise() when building a reusable transform function that computes the column expression programmatically (for example, when the threshold x is passed as a function argument). Both produce the same Catalyst expression tree.
⚠️ Common mistakes
spark.sql("SELECT ... FROM my_view") throws AnalysisException: Table or view not found. Fix: call createOrReplaceTempView() again on the DataFrame before re-running the query, or add a session keep-alive.WHERE clause. If you see AnalysisException: Resolved attribute(s) ... missing from child, check that every column you reference in the outer query is actually selected from the subquery or derived table.spark.sql(f"SELECT * FROM {user_input}") — is a SQL injection risk. If user_input is "txn; DROP TABLE transactions", the second statement could execute. Always validate and whitelist dynamic identifiers, or use parameterised expressions with the DataFrame API for untrusted input.🧠 Quiz — 3 questions
createOrReplaceTempView() registers a view — where does it live?You now know how to mix SQL and DataFrame APIs in the same Spark pipeline.
🎤 Interview Q&A
Catalyst is the query optimizer built into Spark SQL. It works in four phases. First, it parses the SQL string or DataFrame operations into an unresolved logical plan — a tree of nodes representing operations like filter, project, and aggregate. Second, it resolves column names and types against the schema catalog to produce a resolved logical plan. Third, it applies a library of rule-based transformations — predicate pushdown, constant folding, column pruning — to produce an optimised logical plan. Fourth, it generates multiple physical plans (for example, sort-merge join vs broadcast hash join) and selects the cheapest one using cost estimates. Both spark.sql() and the DataFrame API feed into Catalyst at the resolved logical plan stage, which is why they produce identical execution plans for equivalent queries.
A temp view (created with createOrReplaceTempView()) is scoped to the SparkSession that created it. It lives only in that session's catalog and is destroyed when the session is closed. A global temp view (created with createOrReplaceGlobalTempView()) is scoped to the Spark application — it is accessible from any SparkSession within the same running Spark app and must be referenced with the global_temp. prefix, for example SELECT * FROM global_temp.my_table. In most production jobs there is only one SparkSession per application, so global temp views are rarely needed. They are most useful in notebooks that create multiple sessions, or in Structured Streaming applications that maintain long-running sessions.
Predicate pushdown is a Catalyst optimisation that moves filter conditions as close to the data source as possible, reducing the amount of data read into Spark's memory. When Catalyst encounters a filter on a column that the data source supports filtering on natively — for example a Parquet file's row-group statistics, a JDBC table's WHERE clause, or a Delta table's data skipping index — it translates the filter condition into a data-source-level filter and passes it through the DataSource V2 API's pushFilters() method. The data source then applies the filter during I/O, so only matching rows are returned to the Spark executor. For Parquet, this uses column statistics (min/max per row group) to skip entire row groups. For JDBC, Spark generates a SQL WHERE clause appended to the scan query. The net effect is that a filter like WHERE city = 'Mumbai' on a partitioned Parquet table might read 30% of the data instead of 100% — with zero change to the query. You can verify whether pushdown happened by calling df.explain(True) and looking for PushedFilters in the physical plan output.
✅ Key takeaways
spark.sql()and the DataFrame API compile to the same Catalyst logical plan — there is no performance difference between the two forms for equivalent queries.createOrReplaceTempView()registers a DataFrame as a named virtual table in the current SparkSession's catalog — it lives in memory only and is destroyed when the session closes.- You can mix both APIs freely in the same pipeline: filter in the DataFrame API for testability, then register a temp view and query with SQL for readability.
CASE WHEN col > x THEN 'A' ELSE 'B' END(SQL) andwhen(col("col") > x, "A").otherwise("B")(PySpark) produce the same Catalyst expression — use whichever your team reads more easily.
From SQL to production-grade Spark pipelines
Join the Best AI Program: The AI-Era Data Engineer — 5 months, fully online, with placement assistance. Week 1 is free.
Explore The AI-Era Data Engineer →