Running SQL Against DataFrames

df.createOrReplaceTempView("lines") names a DataFrame for SQL, and spark.sql() returns a DataFrame. Up to the end of Partitioning and Caching, listings start with Loading Order History's tables loaded as orders, lines, customers and books, each also registered as a temporary view of that name.

SQL with named parameters, returning a DataFrameJavaScript

import datetime
top = spark.sql("""
    SELECT b.title, sum(l.qty) AS copies, sum(l.qty * l.unit_price) AS revenue
    FROM lines l JOIN books b ON l.book_id = b.id
    WHERE l.status = :status AND l.order_ts >= :since
    GROUP BY b.title ORDER BY revenue DESC LIMIT 3""",
    args={"status": "delivered", "since": datetime.date(2026, 1, 1)})
top.show(truncate=False)
print(type(top).__name__, top.where("copies > 50000").count())     # an ordinary DataFrame

Output

+------------------------+------+----------+
|title                   |copies|revenue   |
+------------------------+------+----------+
|Patterns of the Deep Web|67828 |2679206.00|
|Salt and Saffron        |85708 |2056992.00|
|The Quiet Harbor        |133290|1998017.10|
+------------------------+------+----------+
DataFrame 3

Pass values through args (:name markers, or ? with a list), never by formatting strings: they arrive as typed literals, so SQL injection is impossible. Use a datetime.date here: a naive datetime.datetime is read in the machine's time zone, which on this UTC+8 host moved the cut-off back eight hours on a first run.

MENU