Spark-SQL : Access array elements storing within a cell in a data frame

Question

root
 |
 |-- dogs: array (nullable = true)
 |    |-- element: struct (containsNull = true)
 |    |    |-- _1: struct (nullable = true)
 |    |    |    |-- name: string (nullable = true)
 |    |    |    |-- color: string (nullable = true)
 |    |    |    |-- sources: array (nullable = true)
 |    |    |    |    |-- element: string (containsNull = true)
 |    |    |-- _2: age (nullable = true)

Which shows below with data.select("dogs").show(2,False)

+---------------------------------------------------------------------------------+
|names                                                                            |
+---------------------------------------------------------------------------------+
|[[[Max,White,WrappedArray(SanDiego)],3], [[Spot,Black,WrappedArray(SanDiego)],2]]|
|[[[Michael,Black,WrappedArray(SanJose)],1]]                                      |
+---------------------------------------------------------------------------------+
only showing top 2 rows

I am wondering if it is possible to access the array elements in each cell? For example, I want to retrieve (Max, white), (Spot, Black) and (Michael, Black) from the dogs column.

In additional, I would like to expand the rows with n elements to n rows if possible.

Thanks!

Possible duplicate of Querying Spark SQL DataFrame with complex types — zero323
– zero323, Commented Apr 25, 2016 at 19:06
It is the same question in scala-spark, though Edamame seems to be working with pyspark code. Not sure how SO should organize these (especially given their similarity), but the pyspark equivalent answer is below. — David
– David, Commented Apr 25, 2016 at 19:24

David · Accepted Answer · 2016-04-25 20:07:51Z

3

You can use explode as below to get access to a dataframe with each row being a record from the array.

data.registerTempTable("data")
dataExplode = sqlContext.sql("select explode(dogs) as dog from data")
dataExplode.show()

Then, you can use select to obtain just the columns you are interested in.

edited Apr 25, 2016 at 20:07

answered Apr 25, 2016 at 19:18

David

11.6k4 gold badges44 silver badges46 bronze badges

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

2 Comments

David Over a year ago

@Edamame sorry, there was a typo in the code. I forgot the quotes around "data" in registerTempTable. I edited the code, hopefully it works for you now

Myles Baker Over a year ago

explode() is useful when working with nested datatypes in dataframes.

Collectives™ on Stack Overflow

Spark-SQL : Access array elements storing within a cell in a data frame

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related