1 Number of partitions in a RDD

We consider a set of CSV files that contain temperature measurements over several years. Each line has the following content: year,month,day,hour,minute,second,temperature.

These files are stored at the following location: hdfs://sar01:9000/data/temperatures/.

Here is the detail for each file:

• File temperatures_86400.csv contains one measurement per day between 1980 and 2018.
• File temperatures_2880.csv contains one measurement every 2880 seconds between 1980 and 2018.
• File temperatures_86.csv contains one measurement every 86 seconds for the year 1980 alone.
• File temperatures_10.csv contains one measurement every 10 seconds between 1980 - 2018.

2 Using the DataFrame API to compute the average temperatures

You’re now going to implement a Spark program to compute the average temperatures by using the Spark DataFrame API.

Go through the following steps:

• Copy the code template avg_temperatures_df.py to your home folder by executing the following command:

• The result of the computation will be stored in the folder temperatures_*.df.out under your HDFS folder bdiaspark2024/bdiaspark2024_XX.

3 Computing averages with SQL

You’re now going to implement the computation of the yearly average temperatures by using SQL on Spark DataFrames.

4 Using the DataFrame API on large files

We now consider the files stored under hdfs://sar01:9000/data/sales/.

These files contain tabular data related to the sale of products in a chain of stores. We consider two tables: store_sales and customer. In the first table we find information about each sale, such as the identifier of the product sold, the identifier of the buyer, the quantity of purchased product and the price paid by the customer. For this table, we have 4 files, which only differ in size:

• store_sales_1_4.100.dat: contains 9.5 GiB of data.

• store_sales_1_4.200.dat: contains 19 GiB of data.

• store_sales_1_4.400.dat: contains 38 GiB of data.

• store_sales_1_4.800.dat: contains 77 GiB of data.

In table customer we find data about customers, such as first and last names and birth dates. We only have one file for this table:

• customer_10000.dat: contains 8.3 GiB of data.

We want to test the performances of the DataFrame API on the following queries (WARNING. you must write a code that uses DataFrame functions, not SQL!):

• Query Q1: returns the number of clients. This corresponds to the following SQL query:

SELECT count(*) 
FROM customer

Query Q2: returns the price of the most expensive product. This corresponds to the following SQL query:

SELECT max(ss_list_price) 
FROM store_sales

Query Q3: returns the amount of money spent by each client. This corresponds to the following SQL query:

SELECT ss_customer_sk, SUM(ss_net_paid_inc_tax) as amountSpent 
FROM store_sales
GROUP BY ss_customer_sk

Query Q4: Query Q3 + sort the result so that the client that spent the most money appears on the top. This corresponds to the following SQL query:

SELECT ss_customer_sk, SUM(ss_net_paid_inc_tax) as amountSpent 
FROM store_sales
GROUP BY ss_customer_sk
ORDER BY amountSpent DESC

Query Q5: Query Q4 + join with the table customer to get the first and last name of the customers. This corresponds to the following SQL query:

SELECT c.c_first_name, c.c_last_name, SUM(ss_net_paid_inc_tax) as amountSpent 
FROM store_sales s JOIN customer c ON s.ss_customer_sk = c.c_customer_sk 
GROUP BY ss_customer_sk
ORDER BY amountSpent DESC

cp ~cpu_quercini/spark-sql-templates/dataframe_api_benchmark.py .