Spark Pivot to wide column

Question

Technology: Spark 3.0.3 with Scala 2.12.10

I'm trying to pivot a spark dataframe having 238 million records (Parquet files total 1.1GB ) registered as spark tempView with 4 columns namely: (timestamp, asset, tag, value)

I had to pivot the dataframe on the tag column, so I fetched the distinct values of the tag and passed them into the in clause.

SQL Query Used:

SELECT
   * 
FROM
   (
      select
         timestamp,
         tag,
         value 
      from
         temp_table 
      group by
         timestamp,
         tag,
         value 
   )
   pivot( AVG(value) for tag IN 
   (
      tagval_1,
      tagval_2,
      ...,
      tagval_4000
   )
)

I've close to 4000 distinct values for the tag column. Configuration used to run the Query:

Spark Driver: 5GB

Spark Executor: 14 Executors of 7GB memory and 2 cores each.

I always end up getting JVM memory overhead exception. Hence I kept on increasing the driver's memory to 5GB each time, eventually at 30GB memory the pivot query ran and it took whopping 25 mins to finish the job.

I'm doing something wrong? Is the resource usage justified? How can a total of 1.1GB of raw files take so many resources for pivoting?

Any help would be appreciated.

Spark Pivot to wide column

0 Answers