title: Spark Scala: Querying same table multiple times
body: <p>I am trying to query multiple columns from the same table (bigTable) to generate some aggregated columns (column1_sum, column2_sum, column3_count). In the end, I join all the columns together to form one table.</p>
  
  <p><strong>Code below</strong></p>
  
  <pre><code>val t1 = bigTable
              .filter($"column10" === value1)
              .groupBy("key1","key2")
              .agg(sum("column1") as "column1_sum")
  
  val t2 = bigTable
              .filter($"column11"===1)
              .filter($"column10" === value1)
              .groupBy("key1","key2")
              .agg(sum("column2") as "column2_sum")
  
  val t3 = bigTable
              .filter($"column10" === value3)
              .groupBy("key1","key2")
              .agg(countDistinct("column3") as "column3_count")
  
  tAll
              .join(t1,Seq("key1","key2"),"left_outer")
              .join(t2,Seq("key1","key2"),"left_outer")
              .join(t3,Seq("key1","key2"),"left_outer")
  </code></pre>
  
  <p><strong>Issues with the above code</strong></p>
  
  <p>bigTable is a huge table (it runs into millions of rows). So, querying it multiple times is not efficient. The query is taking a lot of time to run.</p>
  
  <p>Any ideas on how I could achieve the same output in a more efficient way? Is there a way to query the bigTable lesser number of times?</p>
  
  <p>Thanks a lot in advance.</p>