Spark SQL

You already know SQL, and so do most people who use your data. Spark SQL runs a plain SQL query on DataFrames. Spark turns the query into the same plan as the matching methods, so neither style is faster and you can mix them; a data engineer often gets a query from an analyst and has to plug it into a pipeline built with methods.

Give the DataFrame a name: SQL can't see Python variables. df.createOrReplaceTempView("name") registers a temporary view: a name SQL can use after FROM. It copies no rows and lasts as long as the Spark session; OrReplace means that running the line again points the name at the new DataFrame instead of failing because the name is taken.

Run the query: spark.sql(query) returns a DataFrame like any other. It computes nothing yet, DataFrame methods such as orderBy keep working on it, and an action such as show() runs the whole chain. For example, the order lines per status:

orders.createOrReplaceTempView("orders")
by_status = spark.sql("SELECT status, COUNT(*) AS line_count FROM orders GROUP BY status")
by_status.orderBy("status").show()

Here the query is written as in the SQL path: query = ( … ) holds one string piece per clause, each ending with a space, so the pieces join into one query. Your program shows the revenue per category, biggest first, the same numbers you built with groupBy and agg; then the check prints the columns of revenue:

+--------+-------+
|category|revenue|
+--------+-------+
|   gifts|  20750|
|    home|  16809|
| kitchen|  12682|
|  office|   6737|
+--------+-------+

['category', 'revenue']

Your task: the starting code reads orders.csv, keeps the paid lines in paid and leaves query = ( ) empty. Name paid as the view "paid", add the SELECT, FROM and GROUP BY pieces, run the query with spark.sql into revenue, and show it sorted biggest first with orderBy. A Check takes about 10 seconds here, because Spark first starts a Java program.

Assignment
Follow the Coding Tutorial to name paid for SQL, build the revenue-per-category query one clause at a time, run it with spark.sql and show the result sorted.

Hint
The name you give the view is the name after FROM. revenue is a DataFrame, so you sort it with the orderBy method and F.desc("revenue"), then call show().