🔬 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.

Same plan, different syntax

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.

Three things to know about temp views
  • ▸ 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 SparkSession object. 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.

spark_sql_demo.py Lesson 08 · SouthBank India · verified output below
verified output
=== SouthBank India — Spark SQL Demo === -- Credit vs Debit by City -- +---------+--------+-----+-------+ | city|txn_type|count| total| +---------+--------+-----+-------+ |Bangalore| credit| 1|73500.0| |Bangalore| debit| 1|18000.0| | Delhi| credit| 1|95000.0| | Delhi| debit| 2|34000.0| | Mumbai| credit| 3|91400.0| | Mumbai| debit| 2|13000.0| +---------+--------+-----+-------+ -- Net Position per Account -- +----------+-------+ |account_id|net_inr| +----------+-------+ | ACC002|61000.0| | ACC003|55500.0| | ACC001|52000.0| | ACC005|31400.0| | ACC004|-5000.0| +----------+-------+ -- Large Debits (>10 000 ₹) via DataFrame filter, then SQL -- +------+----------+----------+---------+ |txn_id|account_id|amount_inr| city| +------+----------+----------+---------+ |TXN009| ACC002| 22000.0| Delhi| |TXN007| ACC003| 18000.0|Bangalore| |TXN002| ACC002| 12000.0| Delhi| +------+----------+----------+---------+

🗄️ 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.

Why create the filtered view? The DataFrame filter expression stays in Python where it is easy to unit-test. Once the data is clean, SQL provides a readable summary that a risk analyst can audit without knowing PySpark. Both steps compile into one physical plan — Spark does not execute the filter and then run the SQL separately.

⚖️ 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.

createOrReplaceGlobalTempView()

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

Priya on Rohan's workflow — SouthBank India

"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

Using a view after the session expired
Temp views live in the SparkSession, not on disk. If the session times out or is garbage-collected (common in notebooks that are left idle), the next 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.
Mixing table aliases incorrectly in SQL subqueries
Spark SQL follows standard scoping rules: an alias defined in a subquery is not visible in the outer query unless selected. A common mistake is referencing an inner alias in the outer 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.
SQL string templates without parameterization
Building SQL queries by concatenating user-supplied strings — 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

Question 1 of 3
createOrReplaceTempView() registers a view — where does it live?
Question 2 of 3
Which component compiles both Spark SQL strings and DataFrame API calls into a physical execution plan?
Question 3 of 3
Can you mix DataFrame operations and SQL in the same pipeline?
0
/ 3
Catalyst approved.

You now know how to mix SQL and DataFrame APIs in the same Spark pipeline.

🎤 Interview Q&A

Medium What is the Spark Catalyst optimizer?
+

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.

Easy What is the difference between a temp view and a global temp view in Spark?
+

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.

Hard How does Spark SQL push predicates down to the data source?
+

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) and when(col("col") > x, "A").otherwise("B") (PySpark) produce the same Catalyst expression — use whichever your team reads more easily.
Africa & India's Best AI Academy
🤖 AI-first 🎓 Placement Assistance

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 →