Explode (transpose?) multiple columns in Spark SQL table

by

Spark >= 2.4

You can skip zip udf and use arrays_zip function:

df.withColumn("vars", explode(arrays_zip($"varA", $"varB"))).select(
  $"userId", $"someString",
  $"vars.varA", $"vars.varB").show

Spark < 2.4

What you want is not possible without a custom UDF. In Scala you could do something like this:

val data = sc.parallelize(Seq(
    """{"userId": 1, "someString": "example1",
        "varA": [0, 2, 5], "varB": [1, 2, 9]}""",
    """{"userId": 2, "someString": "example2",
        "varA": [1, 20, 5], "varB": [9, null, 6]}"""
))

val df = spark.read.json(data)

df.printSchema
// root
//  |-- someString: string (nullable = true)
//  |-- userId: long (nullable = true)
//  |-- varA: array (nullable = true)
//  |    |-- element: long (containsNull = true)
//  |-- varB: array (nullable = true)
//  |    |-- element: long (containsNull = true)

Now we can define zip udf:

import org.apache.spark.sql.functions.{udf, explode}

val zip = udf((xs: Seq[Long], ys: Seq[Long]) => xs.zip(ys))

df.withColumn("vars", explode(zip($"varA", $"varB"))).select(
   $"userId", $"someString",
   $"vars._1".alias("varA"), $"vars._2".alias("varB")).show

// +------+----------+----+----+
// |userId|someString|varA|varB|
// +------+----------+----+----+
// |     1|  example1|   0|   1|
// |     1|  example1|   2|   2|
// |     1|  example1|   5|   9|
// |     2|  example2|   1|   9|
// |     2|  example2|  20|null|
// |     2|  example2|   5|   6|
// +------+----------+----+----+

With raw SQL:

sqlContext.udf.register("zip", (xs: Seq[Long], ys: Seq[Long]) => xs.zip(ys))
df.registerTempTable("df")

sqlContext.sql(
  """SELECT userId, someString, explode(zip(varA, varB)) AS vars FROM df""")

More Related Contents:

  1. How to select the first row of each group?
  2. Querying Spark SQL DataFrame with complex types
  3. Concatenate columns in Apache Spark DataFrame
  4. Spark Window Functions – rangeBetween dates
  5. Spark SQL window function with complex condition
  6. Does SparkSQL support subquery?
  7. Including null values in an Apache Spark Join
  8. Spark sql queries vs dataframe functions
  9. How to get other columns when using Spark DataFrame groupby?
  10. get TopN of all groups after group by using Spark DataFrame
  11. Why using a UDF in a SQL query leads to cartesian product?
  12. How to aggregate over rolling time window with groups in Spark
  13. What is the difference between cube, rollup and groupBy operators?
  14. Difference between === null and isNull in Spark DataDrame
  15. HIVE select count(*) non null returns higher value than select count(*)
  16. SPARK SQL – case when then
  17. How do I get a SQL row_number equivalent for a Spark RDD?
  18. Find last day of a month in Hive
  19. How to measure the execution time of a query on Spark
  20. Spark replacement for EXISTS and IN
  21. Array Intersection in Spark SQL
  22. Can I pass column name as input parameter in SQL stored Procedure
  23. Foreign Key to non-primary key
  24. “IN” clause limitation in Sql Server
  25. Meaning of square brackets [] in MS-SQL table designer? [duplicate]
  26. Postgres not using index when index scan is much better option
  27. Check if current date is between two dates Oracle SQL
  28. SQL query that gives distinct results that match multiple columns
  29. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  30. is there a PRODUCT function like there is a SUM function in Oracle SQL?

Leave a Comment