How to use pivot in SQL (not as DataFrame grouping operator)?

user_3190018 0
Viewed 553

I have this dataframe and I was trying to convert this dataframe operation in to sql use pivot function 

  val df = Seq(
    (1, "a,b,c"),
    (2, "b,c")
  ).toDF("id", "page_path")
  df.createOrReplaceTempView("df")

  df.show()
  df
    .withColumn("splitted", split($"page_path", ","))
    .withColumn("exploded", explode($"splitted"))
    .groupBy("id")
    .pivot("exploded")
    .count().show

output produced : 

+---+----+---+---+
| id|   a|  b|  c|
+---+----+---+---+
|  1|   1|  1|  1|
|  2|null|  1|  1|
+---+----+---+---+

I saw this databricks link to use pivot function in sql way, I tried to applied and failed..

Anyone has any idea of applying pivot function in a sql way ?

I just tried this way, 

  spark.sql(
    """
      (select * from (Select id, exploded from ( select id, explode(split( page_path ,',')) as exploded from df )
      group by id, exploded )
      Pivot
      (id, exploded)   )
             """.stripMargin
  ).show

is producing the result like 

+---+--------+
| id|exploded|
+---+--------+
|  2|       c|
|  1|       c|
|  1|       b|
|  2|       b|
|  1|       a|
+---+--------+

not like the above output showed using dataframe operations

I tried this also

 spark.sql(
    """
      select * from  (  select * from df lateral view explode(split( page_path ,',')) as exploded )
          pivot (exploded)
             """.stripMargin
  ).show

Exception :

Exception in thread "main" org.apache.spark.sql.AnalysisException: Number of column aliases does not match number of columns. Number of column aliases: 1; number of columns: 3.; line 2 pos 21

breaking my head, was thinking whether is possible or not.

apache-spark-sql apache-spark scala sql
1 Answers

We don't have to mention groupBy clause using sql pivot as Spark implicitly performs groupBy.

From documentation:

An important idea about pivot is that it performs a grouped aggregation based on a list of implicit group-by columns together with the pivot column. The implicit group-by columns are columns from the FROM clause that do not appear in any aggregate function or as the pivot column.

In pivot we need to add aggregate query on the pivoted column and in for mention the required columns list that needed in final dataset.

Using Spark-sql Pivot from spark-2.4:

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

val df1: DataFrame = Seq((1, "a,b,c"),(2, "b,c")).toDF("id", "page_path")
df1.createOrReplaceTempView("df1")
spark.sql(
    """
      |Select * from
      |( select id, explode(split( page_path ,',')) as exploded from df )
      |pivot(count(exploded) for exploded in ('a','b','c')
      |)
   """.stripMargin)
.show

Result :

+---+----+---+---+
| id|   a|  b|  c|
+---+----+---+---+
|  1|   1|  1|  1|
|  2|null|  1|  1|
+---+----+---+---+

Using Dataframe api:

df1.withColumn("splitted", split($"page_path", ",")).
withColumn("exploded", explode($"splitted")).
groupBy("id").
pivot("exploded").
count().
show

Result : 

+---+----+---+---+
| id|   a|  b|  c|
+---+----+---+---+
|  1|   1|  1|  1|
|  2|null|  1|  1|
+---+----+---+---+
user_76326950
Related