Apache Spark lab: learn DataFrames and Spark SQL on a CSV file, with the Spark UI

This lab gives each student a small Apache Spark of their own, with a CSV file of 24 orders already loaded as a DataFrame. Spark is a tool for working on tables of data by splitting the work into jobs, and a student asks it questions in two ways, with DataFrame code and with SQL, then opens the Spark UI to see what it did.

It is not an attack lab. Every copy has no route to the internet and none to any other student, so nothing can be downloaded into it and nothing in it can reach anybody else.

What students learn

How a DataFrame is a table that Spark reads from a file: its columns and the types Spark works out, then filter to keep some rows, groupBy and count to summarise them, and the same kind of question asked in SQL against a table of the same name. They see that both ways make the same kind of plan, and that every query that has to read the data becomes a job they can find in the Spark UI, under its Jobs tab and its SQL / DataFrame tab. It suits introductions to data engineering, big data and databases; check your own awarding body’s unit for the exact wording, none is quoted here.

Run it in your browser

The lab starts one service, spark, and waits until it has loaded the file, which takes about six seconds. It is Spark in local mode: one process that holds the driver and its worker threads, which is what fits a copy’s memory. There is no master and worker to start and stop. The terminal has no Spark of its own, so the service also answers on port 8000: curl sends a SQL query to /sql, or one DataFrame expression to /df, and prints the result as a table.

  1. Open the Container page. Open Build on the bar and choose Run a container. Anybody may open it, and the Git lab and the SQL lab start with no account. Every other lab needs a free account: create one, sign in, then press Start.
  2. Find the lab. The Sample labs card lists every sample. Find Apache Spark: DataFrames and SQL.
  3. Press Start. Start runs it as it is, in one press. Edit the file first opens the compose file so you can change it before you start.
  4. Open it. On the lab’s page, the lab bar has a link called Spark UI: press it to see the Spark UI in your browser, with its Jobs, Stages and SQL / DataFrame tabs. Then press Open terminal and type curl -s -d 'SELECT * FROM orders LIMIT 5' http://spark:8000/sql to see the first five orders. orders.count() sent to /df answers 24.

What to ask students to hand in

The result of a query they wrote themselves over the file, such as the total revenue per country (quantity times unit_price, largest first, which puts Japan on top with 717.5), the table it printed, and a screenshot of that query in the Spark UI. Then a sentence or two in their own words on what Spark did. Nothing is marked automatically: the sentence shows whether it was understood. For stronger work ask for the same answer both as DataFrame code and as SQL.

A basic lesson on this lab

Spark Basics is a small lesson worked in this lab. In it a student looks at the first rows, filters the UK orders, groups and counts the orders by country, asks for the revenue of each country in SQL, and then finds those queries in the Spark UI.

Free personal accounts have it in the Basics module, which is on Home, every college can copy it from the Library’s Basics folder, and a new college opens with it in a module called Lab Basics (Sample). It is deliberately small: it shows a teacher what a lesson worked in a lab is, and the lab does the rest. Copy it into a module of your own to change it.

The code on GitHub

The compose file has no Dockerfile: it runs one stock public image, the Apache Spark project’s own, pinned to a version, as an unprivileged user with 512 MB of memory. Apache Spark is licensed Apache-2.0 and is not distributed by us. The file also writes the CSV and the small query service when it starts, so there is nothing to build. The limits are honest ones: it is one Spark and not a cluster, the data is 24 rows so timings say nothing about real workloads, a query that needs far more memory than the copy has will fail, and the lab has no way out to the internet. Nothing is kept when the lab stops.

Set it as work

Say in the task what to hand in.

  1. Make the lab. In your module add a task and choose the Container lab card, then In a GitHub repository. Give https://github.com/mitsurutech/lab-spark with the branch main. The compose file is the default, compose.yaml. Fork the repository first if you want to change it, for example to use your own CSV file.
  2. Publish and try it. Publish pins the commit you were shown, so a later change in the repository does not alter a lab you have already set. Start your own copy first.
  3. Hand it out. Assign it like any task, with a due date if you want one, and say in the task what to do and what to hand in.
  4. Mark what is handed in. Open a student’s copy and the work they handed in, mark it, and send it back with a reason if it needs more.

Questions people ask

Is this a Spark cluster?
No. It is Spark in local mode, one process doing the work of a master and its workers, because a copy’s service has 512 MB and a worker alone needs more. It teaches the DataFrame and SQL side and not how work is spread over machines.
Why does the terminal use curl and not pyspark or spark-sql?
The lab’s terminal carries curl, psql and a few other clients, and no Spark and no Java. The Spark service answers a query sent over HTTP and prints the result, so curl is enough. On your own machine you can use pyspark with the same ideas.
Can students use their own data?
Not inside a copy, which has no way in for files. Fork the repository, put your own rows in the CSV in compose.yaml, and set the fork as the lab.