spark.sql and temp views

Going back and forth

orders.createOrReplaceTempView("orders")

top = spark.sql("""
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
""")

result = top.filter(F.col("total") > 1000).orderBy(F.desc("total"))

The result of spark.sql is an ordinary DataFrame: you can keep chaining DataFrame methods, register it as another view, or write it out. Nothing runs until an action, exactly as with DataFrame code.

Views and tables

A temp view stores no data. It is a name for a plan: querying it re-runs the plan, including reading the source, unless the DataFrame is cached.

Passing parameters safely

Building SQL with f-strings breaks on quotes in values and opens the door to SQL injection when values come from users or files. Spark 3.4+ supports named parameters:

# Fragile: breaks for O'Brien, and unsafe with untrusted input
spark.sql(f"SELECT * FROM orders WHERE customer = '{name}'")

# Safe: the value is passed separately
spark.sql("SELECT * FROM orders WHERE customer = :name AND amount > :min",
          args={"name": name, "min": 100})

# DataFrames can be passed straight in (Spark 3.4+)
spark.sql("SELECT * FROM {o} WHERE amount > 100", o=orders)

Same plan, same speed

SQL text and DataFrame calls are two front ends to the same engine. Both are parsed into a logical plan, optimised by CatalystCatalyst: Spark's query optimizer. It rewrites your DataFrame code into a faster equivalent plan before running it. Learn more → and executed the same way. explain() on either shows identical physical plans for equivalent queries. Choose by readability: SQL for analysts and long set-based logic, DataFrames when you build queries dynamically in Python (loops over columns, reusable functions, unit tests).

spark.sql("SELECT customer, SUM(amount) AS revenue FROM orders WHERE amount > 0 GROUP BY customer")

SELECT customer, SUM(amount) AS revenue FROM orders WHERE amount > 0 GROUP BY customer

orders.filter("amount > 0").groupBy("customer").agg(F.sum("amount").alias("revenue"))

Mixing them well

• F.expr("CASE WHEN ... END") and df.selectExpr("amount * 1.18 AS gross") embed SQL expressions inside DataFrame code.
• df.filter("amount > 100") accepts a SQL condition string.
• Register intermediate results as views to break a long SQL pipeline into readable steps, or use CTEs (WITH ... AS) inside one query.
• Use spark.table("db.orders") to read a catalog table as a DataFrame.

Common mistakes