Spark SQL create an array with array values in a column

Question

I have a column of type integer arrays:

 case class Testing(name: String, age: Int, salary: Double, array: Array[Int])

  val x = sc.parallelize(Array(
     Testing(null, 21, 905.33, Array(1,2,3)),
     Testing("Noelia", 26, 1130.60, Array(3,2,1)),
     Testing("Pilar", 52,  1890.85, Array(3,3,3)),
     Testing("Roberto", 31, 1450.14, Array(1,0,0))
   ))

  // Convert RDD to a DataFrame 
  val df = sqlContext.createDataFrame(x) 

  // For SQL usage we need to register the table
   df.registerTempTable("df")

I want to create an array whose elements are the values of the column "array". How to do this in Spark SQL?

 sqlContext.sql("SELECT [array] from df").show

 [ [1,2,3], [3,2,1], [3,3,3], [1,0,0]]

I think you are missing code. Assuming you really are talking about Spark SQL, you need a step where you create a DataFrame. — David Griffin
– David Griffin, Commented Mar 21, 2016 at 16:12

David Griffin · Accepted Answer · 2016-03-21 17:02:13Z

1

Assuming as you imply that you have a DataFrame named df, and that the Array() values are in a column named array this should do the trick.

df.select($"array").rdd.map{
  row =>  row.getList[Int](0).toArray
}.collect()

Of if you want to do it through sqlContext.sql:

sqlContext.sql("SELECT array FROM df").rdd.map{
  row =>  row.getList[Int](0).toArray
}.collect()

edited Mar 21, 2016 at 17:02

answered Mar 21, 2016 at 16:24

David Griffin

14k5 gold badges49 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

nest Over a year ago

There some way to do this through a function udf ?

David Griffin Over a year ago

If so it would do exactly this same logic in a udf. One way or another you have to convert a DataFrame to an Array[Array[Int]]. Which means DataFrame -> RDD[Row] -> RDD[Array[Int]] -> Array[Array[Int]]

nest Over a year ago

Sorry but I'm new and I do not see how . Say I want something like: sqlContext.sql("select agg(array1, array2, array3) from df"). As I can define a function like this?

David Griffin Over a year ago

You can do sqlContext.sql("SELECT array[0] as array1, array[1] as array2, array[2] as array3 FROM df") -- does that help?

nest Over a year ago

No, I want to pass the array you've created with your code in a UDF funtion , as I do that?

Collectives™ on Stack Overflow

Spark SQL create an array with array values in a column

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related