What you will do
Apache Spark is a tool for working on tables of data too big for one program to hold, by splitting the work into jobs. A DataFrame is Spark's name for a table, and you can ask it questions with code or with SQL. Your lab has a small Spark and a file of 24 orders. You will look at it, filter it, group and count it, ask the same kind of question in SQL, and then find the work in the Spark UI.
Before you start
Note
Press Start the lab on the bar at the top of this lesson and wait until it says it is running. There is one container, spark, and the terminal reaches it by that name on port 8000. The Spark UI link opens its web page, port 4040. Each Run button below types its command into your lab's terminal, one after another in the same session. To watch them, or to type your own, press Open terminal on the bar.
1. Look at the data
Spark read orders.csv into a table called orders. This SQL question asks for five rows of it: the answer is a table whose first row is order 1001, Ada, from the UK, two keyboards at 25.0. The curl sends your question to the lab's query service and prints what comes back.
curl -s -d 'SELECT * FROM orders LIMIT 5' http://spark:8000/sqlShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
2. Filter it with a DataFrame
The same table is a DataFrame called orders, and filter keeps the rows that match. This keeps the UK orders: the answer is a table of seven rows. The second command counts all the rows instead, and prints 24.
curl -s -d 'orders.filter(orders.country == "UK")' http://spark:8000/dfShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
curl -s -d 'orders.count()' http://spark:8000/dfShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
3. Group and count
groupBy gathers the rows that share a country and count counts each group. The answer has four rows: UK 7, Japan 6, France 6 and Spain 5, though they may come in a different order, because Spark does not promise one until you ask for it.
curl -s -d 'orders.groupBy("country").count()' http://spark:8000/dfShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
4. Ask in SQL
The same kind of question in SQL: the revenue of each country is quantity times unit price, added up, largest first. The answer has Japan on top with 717.5, then the UK with 670.0, France 495.0 and Spain 490.0. Spark turns the SQL and the DataFrame code into the same kind of plan.
curl -s -d 'SELECT country, round(sum(quantity * unit_price), 2) AS revenue FROM orders GROUP BY country ORDER BY revenue DESC' http://spark:8000/sqlShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
5. See the work in the Spark UI
Open Spark UI in your browser, from the link on the bar at the top of this lesson. The Jobs tab lists the work Spark did for your questions, and the SQL / DataFrame tab lists each query with the plan it was run as. Run a step again and watch a new one appear. This is what you screenshot when you hand in your own query.
For teachers
Tip
This lesson is a small example of a lesson worked in a lab: the steps and the Run buttons are the lesson, and the lab is each student's own copy. The lab is one service and nothing here marks itself: the student hands in the result of a query of their own, a screenshot of it in the Spark UI and a sentence on what Spark did. It is Spark in local mode, not a cluster, and the data is a 24-row file, so it teaches the DataFrame and SQL side and not scale. Copy it into your own module and change it, or build one from scratch with the Lab menu in the lesson builder.