While we discuss about aggregation we should be aware of Group By clause, so I am not going to go deep on that now. Here we will discuss about grouping sets, rollup and cubes in sparks which are very less explored.

Lets create a data frame that will help us to understand Multi dimensional Aggregation:

Let’s create a sample input set to perform spark aggregate functions.

import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.functions._

object Multi_Dimensional_Aggregates {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession.builder()
      .appName("Spark example")
      .master("local[*]")
      .config("option", "some-value")
      .getOrCreate()
    import spark.implicits._
    val input = Seq((100, "computer", 75), (100, "maths", 90), (100, "science", 70), (100, "history", 50),
      (200, "computer", 50), (200, "maths", 35), (200, "science", 90), (200, "history", 78),
      (300, "computer", 92), (300, "maths", 67), (300, "science", 71), (300, "history", 65),
      (400, "computer", 56), (400, "maths", 77), (400, "science", 42), (400, "history", 37)
    )
    var inputDataframe = input.toDF("id", "subject", "marks")
    inputDataframe.show()
    
  }
}

• — -+ — — — — + — — -+
  | id| subject|marks|
  + — -+ — — — — + — — -+
  |100|computer| 75|
  |100| maths| 90|
  |100| science| 70|
  |100| history| 50|
  |200|computer| 50|
  |200| maths| 35|
  |200| science| 90|
  |200| history| 78|
  |300|computer| 92|
  |300| maths| 67|
  |300| science| 71|
  |300| history| 65|
  |400|computer| 56|
  |400| maths| 77|
  |400| science| 42|
  |400| history| 37|
  + — -+ — — — — + — — -+

Rollup:

Lets first write the function, see the output and understand Rollup:

val rollupDataframe= inputDataframe.rollup("id","subject")
  .agg(avg("marks") as "avg_marks")
    .orderBy("id")
rollupDataframe.show(30,false)

• — — + — — — — + — — — — -+
  |id |subject |avg_marks|
  + — — + — — — — + — — — — -+
  |null|null |65.3125 |
  |100 |null |71.25 |
  |100 |computer|75.0 |
  |100 |history |50.0 |
  |100 |science |70.0 |
  |100 |maths |90.0 |
  |200 |history |78.0 |
  |200 |science |90.0 |
  |200 |computer|50.0 |
  |200 |maths |35.0 |
  |200 |null |63.25 |
  |300 |null |73.75 |
  |300 |science |71.0 |
  |300 |maths |67.0 |
  |300 |history |65.0 |
  |300 |computer|92.0 |
  |400 |maths |77.0 |
  |400 |computer|56.0 |
  |400 |null |53.0 |
  |400 |history |37.0 |
  |400 |science |42.0 |
  + — — + — — — — + — — — — -+
• Rollup computes the aggregate at the hierarchy level of the columns. Here it assumes that hierarchy starts at id and drill downs to subject.
• First row gives us the average(marks) considering all the input rows hence ID and Subject has NULL values. Average marks of all the students in the class across the subject is 65.3125
• Row 2,11,12,19 gives the average marks of each students, so the ID will have the student Id and the subject column has NULL value since it gives the average of each student.
• Rest all rows will just give the value for each ID and subject.
• Rollup offers a shorthand of group by and just gives us the aggregation based on the columns defined in the query. We can also write the above query using GROUP BY but it will be clumsy. Example below

val group1=inputDataframe.groupBy().agg(avg("marks") as "avg_marks")
  .select(lit(null) as "id" ,lit(null) as "subject" ,$"avg_marks")

val group2=inputDataframe.groupBy("id","subject").agg(avg("marks") as "avg_marks")

val group3=inputDataframe.groupBy("id").agg(avg("marks") as "avg_marks")
  .select($"id",lit(null) as "subject", $"avg_marks")

val groupByRollupAlternateDf=group1.union(group2).union(group3).orderBy("id")

This will also give the same output as Rollup. You can clearly see that Rollup query is very simple, concise and clear and does not need multiple statements.

Rollup with Grouping_id:

Since Rollup offers aggregation on each column that is specified in the query it may be difficult to understand based on which grouping columns the row is aggregated. So we can specify grouping_id as below.

val rollupWithGroupingDf= inputDataframe.rollup("id","subject")
    .agg(avg("marks") as "avg_marks" ,grouping_id() as "gid")
.select("id","subject","marks","gid").orderBy("gid")

  rollupWithGroupingDf.show(30, false)

• — — + — — — — + — — — — -+ — -+
  |id |subject |avg_marks|gid|
  + — — + — — — — + — — — — -+ — -+
  |200 |history |78 |0 |
  |300 |science |71 |0 |
  |400 |maths |77 |0 |
  |200 |maths |35 |0 |
  |400 |science |42 |0 |
  |400 |history |37 |0 |
  |200 |computer|50 |0 |
  |300 |history |65 |0 |
  |300 |computer|92 |0 |
  |200 |science |90 |0 |
  |100 |history |50 |0 |
  |100 |science |70 |0 |
  |100 |maths |90 |0 |
  |100 |computer|75 |0 |
  |300 |maths |67 |0 |
  |400 |computer|56 |0 |
  |300 |null |295 |1 |
  |400 |null |212 |1 |
  |200 |null |253 |1 |
  |100 |null |285 |1 |
  |null|null |1045 |3 |
  + — — + — — — — + — — — — -+ — -+
• Here in the above example grouping_id 0 says that the grouping columns are id and subject. grouping_id 1 says that grouping columns are based on the id and grouping id 3 says that grouping is done on all the columns.

Cube:

Cube function helps us aggregates across all columns in the specified group. It helps us calculate the subtotal and grand total across all the columns specified within the cube clause. Cube does not assume any hierarchy and just produces result across all the possible columns defined.

val cubeDf=inputDataframe.cube("id", "subject").agg(avg("marks") as "avg_marks")
  .orderBy("id")

cubeDf.show(30, false)

• — — + — — — — + — — — — -+
  |id |subject |avg_marks|
  + — — + — — — — + — — — — -+
  |null|history |57.5 |
  |null|computer|68.25 |
  |null|maths |67.25 |
  |null|science |68.25 |
  |null|null |65.3125 |
  |100 |science |70.0 |
  |100 |null |71.25 |
  |100 |history |50.0 |
  |100 |computer|75.0 |
  |100 |maths |90.0 |
  |200 |history |78.0 |
  |200 |maths |35.0 |
  |200 |science |90.0 |
  |200 |null |63.25 |
  |200 |computer|50.0 |
  |300 |history |65.0 |
  |300 |computer|92.0 |
  |300 |science |71.0 |
  |300 |null |73.75 |
  |300 |maths |67.0 |
  |400 |maths |77.0 |
  |400 |computer|56.0 |
  |400 |null |53.0 |
  |400 |history |37.0 |
  |400 |science |42.0 |
  + — — + — — — — + — — — — -+
• Here in comparison with the rollup results, first 3 rows gives us the aggregate of the subject and hence id is NULL.

Please refer to the github link for the code https://github.com/Samuel-William/ProgrammingExamples.git

Please clap if you liked this article and please add any comments if you did not like something.

GIT is a version control tool, GIT nowadays is used as version control tool in most of the companies. This article is written with the understanding that you have already installed git and you just want to know the basic GIT commands.

Open the GIT bash from the specific directory where you need to create, Here I have opened git bash from the BigData Repository.

If you have any questions or feedback please give your comments. It will be very much helpful to me.

GIT Commands:-

To Create a GIT Branch, - git init <branch_name>

To know whether you are in GIT Repo— git status

If it is not a git repo, it will show as below

If you are right on the git repo, it will show as below

Create a file on git repo- touch <name_of_file> Example: touch HelloWorld

Add a file in GIT- git add <name_of_file> Example: git add HelloWorld

git add is like a basket which you carry while you shopping in a grocery store, you can select the groceries and drop that in the basket and before you go to the counter you can filter out what you want and what you do not, so generally git add is like a staging area.