35. SQL vs the DataFrame API — same engine

SQL and DataFrame code aren't two engines — they're two front ends to the same one. These two produce identical results:

orders.dropDuplicates().createOrReplaceTempView("o")

# SQL
sql_way = spark.sql("""
    SELECT category, ROUND(SUM(quantity * unit_price), 2) AS revenue
    FROM o GROUP BY category ORDER BY revenue DESC
""")

# DataFrame API
df_way = (
    orders.dropDuplicates()
    .withColumn("lt", F.col("quantity") * F.col("unit_price"))
    .groupBy("category")
    .agg(F.round(F.sum("lt"), 2).alias("revenue"))
    .orderBy(F.col("revenue").desc())
)

sql_way.show()
df_way.show()

Same numbers, same plan under the hood. Which you write is about readability and context, not capability.

Mixing both in one pipeline

Because spark.sql returns a DataFrame and any DataFrame can become a view, you can hop between them mid-pipeline — SQL for the gnarly join, DataFrame methods for the programmatic parts:

orders.dropDuplicates().createOrReplaceTempView("o")

# start in SQL...
base = spark.sql("SELECT category, quantity * unit_price AS line_total FROM o")

# ...finish in the DataFrame API
base.groupBy("category").agg(
    F.round(F.sum("line_total"), 2).alias("revenue")
).orderBy(F.col("revenue").desc()).show()

Use whichever expresses each step most clearly. A common split: SQL for set-based logic a SQL-fluent teammate will read, DataFrame API for anything you build up in a loop or parameterize from Python.

Your turn

Using spark.sql, return the top 5 most expensive distinct products: select product and unit_price from the view, order by unit_price descending, limit 5. Assign the DataFrame to result.

orders.dropDuplicates().createOrReplaceTempView("o")
result = spark.sql("...")  # <- SELECT product, unit_price FROM o ORDER BY unit_price DESC LIMIT 5
result.show()

orders.dropDuplicates().createOrReplaceTempView("o")
result = spark.sql("""
    SELECT product, unit_price
    FROM o
    ORDER BY unit_price DESC
    LIMIT 5
""")