BrevFeed
Sign in

34. Temp views and `spark.sql`

📖 Reading · 3 min
💡 Every code box below is live — edit it and hit Run.

Everything you've written as DataFrame code can also be written as SQL — same engine, same result. To run SQL against a DataFrame, first register it as a temporary view, then call spark.sql(...):

orders.dropDuplicates().createOrReplaceTempView("orders_v")

spark.sql("""
    SELECT category, ROUND(SUM(quantity * unit_price), 2) AS revenue
    FROM orders_v
    GROUP BY category
    ORDER BY revenue DESC
""").show()

createOrReplaceTempView("name") makes the DataFrame queryable as a table called name. spark.sql(...) returns a DataFrame — so you can keep chaining DataFrame methods on the result.

Views are session-scoped (and per-run here)

A temp view lives in your SparkSession. In this course every Run starts a fresh session, so each code box must register the views it uses — you'll see createOrReplaceTempView at the top of every SQL example. (On a real cluster you register once and query many times.)

SQL joins, exactly as you'd expect

Register both sides and join in plain SQL:

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

spark.sql("""
    SELECT o.order_id, o.product, c.name, c.city
    FROM o
    JOIN c ON o.customer_id = c.customer_id
    LIMIT 5
""").show()

Your turn

Register deduplicated orders as a view and use spark.sql to return each country and its order count, ordered by count descending. Assign the resulting DataFrame to result.

orders.dropDuplicates().createOrReplaceTempView("orders_v")
result = spark.sql("...")  # <- SELECT country, COUNT(*) AS count FROM orders_v GROUP BY country ORDER BY count DESC
result.show()

orders.dropDuplicates().createOrReplaceTempView("orders_v")
result = spark.sql("""
    SELECT country, COUNT(*) AS count
    FROM orders_v
    GROUP BY country
    ORDER BY count DESC
""")
← Previous Next →