Rollup Cube and Pivot

Spark tutorial · PySpark.in

Rollup, Cube, and Pivot (Advanced Grouping)

Introduction

PySpark provides advanced grouping operations such as pivot, rollup, and cube that extend the capabilities of a basic groupBy. These operations allow for multi-dimensional aggregations and data transformations beyond simple single-level grouping:

Pivot: Reshapes the data by turning unique values of one column into new columns (like creating a pivot table in Excel), along with an aggregation to populate the values.
Rollup: Performs hierarchical multi-level aggregations, computing subtotals moving from the first specified grouping column to the last. This produces nested totals (including intermediate subtotals and a grand total) following the grouping hierarchy.
Cube: Computes all combinations of the grouping columns (like SQL CUBE), generating subtotals for every possible combination of the specified dimensions (including each individual column total and the overall total).

These tools are powerful for generating summary reports and multi-level breakdowns of data. In the sections below, we will explain each with examples for clarity.

Pivot (Reshaping Data)

A pivot operation is useful for transforming a “tall” DataFrame into a “wide” format for reporting or analysis. When you pivot a DataFrame, you specify one or more index columns (the groupBy keys), a pivot column whose unique values will become new columns, and an aggregation to fill the resulting cells. Essentially, pivot turns row values into column headings and aggregates the data accordingly (similar to a spreadsheet pivot table).

For example, imagine a sales DataFrame with columns: Product, Region, Sales. If we want each product to be a row and create separate columns for each region’s total sales, we can pivot on the Region column:

```

from pyspark.sql import SparkSession, functions as F

spark = SparkSession.builder.getOrCreate()

sales_data = [

("A", "East", 100),

("A", "West", 150),

("B", "East", 200),

("B", "West", 250),

("C", "East", 300),

("C", "West", 350)

]

sales_df = spark.createDataFrame(sales_data, ["Product", "Region", "Sales"])

# Pivot the data: group by Product, pivot on Region, aggregate by sum of Sales

pivot_df = sales_df.groupBy("Product").pivot("Region").sum("Sales")

pivot_df.show()

```

In this code, we group by Product, pivot on Region, and sum the Sales. The result will have each unique product as a row, and two new columns (East and West) because those are the unique Region values. The cell values are the sum of sales for that product in that region. For instance, Product A had 100 sales in East and 150 in West, so in the pivoted output the row for Product A will show East = 100 and West = 150. If the data had multiple entries per Product-Region, the sum("Sales") aggregation adds them up (you could also use other aggregations like average, min, max, etc.).

Why an aggregation is required: Pivoting in Spark requires an aggregation function because if there are multiple rows for the same Product-Region combination, Spark needs instructions on how to combine them into a single value. By specifying an aggregation (sum in this case), we tell Spark how to reduce potentially many rows into one. Without an aggregation, a pivot alone can’t resolve multiple values. In our example, if Product A had two records for East region, the sum would ensure those are added together for the East column.

Pivoting is typically used to convert data from a long format to a wide format for comparative analysis or reporting. After pivoting, you get a matrix-like result: one row per group (Product here) and one column per category value (Region here). This is different from rollup and cube, which add aggregated rows; pivot instead spreads values across new columns.

Rollup (Hierarchical Subtotals)

A rollup is a multi-level aggregation that creates hierarchical subtotals moving along a list of grouping columns. Think of it as computing subtotals at increasing levels of aggregation. If you group on (A, B) with a rollup, Spark will produce aggregations for: each combination of (A, B), each unique A (subtotal across all B for that A), and an overall total across all A. Rollup is useful when you have an inherent hierarchy in your grouping columns and you want totals at each level of the hierarchy.

For example, if we roll up by (Year, Quarter), the result would include:

Aggregated metrics for each Year-Quarter combination (the finest level, same as a normal groupBy on both),
A subtotal for each Year (aggregating all quarters in that year),
An overall total (aggregating all years).

The rollup follows the order of columns given: it first varies all levels of the first column with all of the second, then provides a subtotal for the first column, and so on, finally culminating in a grand total. Rollup is essentially a shortcut for specifying multiple grouping sets in a hierarchy (Spark’s rollup is actually translated into grouping sets under the hood.

When Spark displays rollup results, it uses NULL in place of a value for a grouping column to indicate an “all” or subtotal at that level. For instance, a row with Year = 2023 and Quarter = NULL would represent the total for year 2023 across all quarters.

Cube (All Combinations of Subtotals)

A cube is similar to a rollup, but goes beyond hierarchy – it computes all possible combinations of the grouping columns. This is useful for fully exploring multi-dimensional data. If you cube on (X, Y), you will get:

Aggregations for each combination of X and Y (like a normal groupBy on both),
Subtotals for each X (aggregating across all Y values for that X),
Subtotals for each Y (aggregating across all X values for that Y),
A grand total (aggregating across all X and all Y).

In other words, cube will produce results for every subset of the grouping keys. With two keys, rollup gave us N+1 = 3 grouping sets (if N=2, combinations were (X,Y), (X), ()), whereas cube gives 2^N = 4 grouping sets ((X,Y), (X), (Y), ()). For three grouping columns, cube would produce 2^3 = 8 combinations, and so on. Cube is like asking for every possible subtotal breakdown by the given dimensions, which is why it can result in many more rows than a rollup. As with rollup, Spark will use NULL in place of a column value to indicate an aggregation across “all” values of that column.

Cube is powerful for creating comprehensive summary tables in one go. For example, cubing by (Year, Region) on a sales dataset would give you:

Sales by each Year-Region combination,
Total sales for each Year (across all regions),
Total sales for each Region (across all years),
Total sales overall.

Example: Rollup vs. Cube (with PySpark code)

To illustrate the difference between a standard groupBy, a rollup, and a cube, consider a simple DataFrame with two columns x and y:

```

from pyspark.sql import SparkSession, functions as F

spark = SparkSession.builder.getOrCreate()

data = [("foo", 1), ("foo", 2), ("bar", 2), ("bar", 2)]

df = spark.createDataFrame(data, ["x", "y"])

df.show()

```

This data contains four records. Let’s see how groupBy, rollup, and cube aggregations compare when we count the records in each group:

1. GroupBy on (x, y) – This will give the count for each exact (x,y) combination present in the data (no subtotals, just the distinct groups in the data):

```

df.groupBy("x", "y").count().show()

```

Output:

+-----+---+-----+

| x | y |count|

+-----+---+-----+

| foo | 1 | 1|

| foo | 2 | 1|

| bar | 2 | 2|

+-----+---+-----+

Explanation: We have three distinct (x,y) pairs in the data: (“foo”,1) appears once, (“foo”,2) appears once, and (“bar”,2) appears twice. GroupBy only reports these existing combinations. There are no subtotals or totals beyond these groups.

2. Rollup on (x, y) – This will produce the above groups plus subtotals for each x and an overall total:

```

df.rollup("x", "y").count().show()

```

Output:

+-----+----+-----+

| x | y |count|

+-----+----+-----+

| foo | 1 | 1|

| foo | 2 | 1|

| bar | 2 | 2|

| foo |NULL| 2| -- subtotal for x = 'foo' (1+1)

| bar |NULL| 2| -- subtotal for x = 'bar' (2 records)

|NULL|NULL| 4| -- grand total (all records)

+-----+----+-----+

Explanation: The rollup includes all the group-level rows from groupBy, and then adds:

("foo", NULL) with count 2, which is the total count of records where x = "foo" (summing the two foo rows).
("bar", NULL) with count 2, the total for x = "bar".
(NULL, NULL) with count 4, the grand total of all records.

Here NULL in the output indicates "all values" for that grouping column. For example, x = foo, y = NULL is an aggregation of all records with x = foo (regardless of y). Rollup did not produce rows for (NULL, 1) or (NULL, 2) because rollup only goes through the hierarchy of x then y. Once it collapsed x entirely (to produce the grand total), it doesn’t produce subtotals for y alone. In summary, rollup yielded: all (x,y) combinations, subtotals by x, and an overall total.

3. Cube on (x, y) – This will produce all the groupBy results plus subtotals for each x, each y, and the overall total (all combination of dimensions):

```

df.cube("x", "y").count().show()

```

Output:

+-----+----+-----+

| x | y |count|

+-----+----+-----+

| foo | 1 | 1|

| foo | 2 | 1|

| bar | 2 | 2|

| foo |NULL| 2| -- subtotal for x = 'foo'

| bar |NULL| 2| -- subtotal for x = 'bar'

|NULL| 1 | 1| -- subtotal for y = 1 (across all x)

|NULL| 2 | 3| -- subtotal for y = 2 (across all x)

|NULL|NULL| 4| -- grand total (all records)

+-----+----+-----+

Explanation: Cube includes every possible subtotal. In addition to the rollup results (subtotals by x and the grand total), we also see:

(NULL, 1) with count 1, which is the total records where y = 1 (across all x values).
(NULL, 2) with count 3, the total records where y = 2 (across all x).

Cube is effectively doing a groupBy on all combinations of the given fields: by (x,y), by just x, by just y, and by neither (overall total). In this small example, cube produced 8 rows (which is 2^2 combinations), whereas rollup produced 6 rows.

Interpreting NULLs: In both rollup and cube outputs, NULL serves as a placeholder meaning "all values" for that column. For instance, in the cube output, the row (NULL, 2) denotes all x with y=2, and (foo, NULL) denotes all y with x='foo'. The row (NULL, NULL) is the grand total over all x and y. When using these in practice, you might use the grouping_id() function or check for NULLs to identify which level of aggregation a row represents – but the key takeaway here is understanding how the extra rows represent subtotals.

Differences and Usage

groupBy("A", "B"): Aggregates only at the full combination of the specified keys. You get one row per unique (A,B) in the data, with no higher-level totals. This is the default behavior without rollup or cube – just like a standard SQL GROUP BY on those columns.
rollup("A", "B"): Aggregates at multiple levels following the hierarchy of columns. You get:
- Rows for each (A,B) combination (like groupBy).
- Subtotal rows for each unique A (with B collapsed to all, indicated by NULL for B).
- An overall total row (A collapsed to all as well).
  Rollup is useful when you need cumulative subtotals. For example, df.rollup("Year", "Quarter").agg(func) would yield quarterly results, yearly totals, and a grand total. The number of grouping combinations from a rollup with N columns is N+1 (including the grand total).
cube("A", "B"): Aggregates at all possible combinations of the columns. You get:
- Rows for each (A,B) combination.
- Subtotals for each A (with B = NULL).
- Subtotals for each B (with A = NULL).
- A grand total (A and B = NULL).
  Cube is useful for comprehensive multi-dimensional analysis, where you want every breakdown. With N columns, cube generates 2^N combinations of grouping sets, which can be powerful but also potentially large, so use cube judiciously if N is large.
pivot("Column"): This is not for adding subtotal rows, but rather for transforming data layout. Pivot turns distinct values of the given column into new columns in the result. It’s ideal for creating cross-tabulations or matrix-style summaries. Pivot requires specifying an aggregation because multiple source rows might map to the same output cell. Use pivot when you want to spread data across columns for comparison (for example, sales by region as separate columns, survey responses as separate columns, etc.), rather than adding summary rows.

Rollup Cube and Pivot