How to cast an array of struct in a spark dataframe using selectExpr?

Question

How to cast an array of struct in a spark dataframe ?

Let me explain what I am trying to do via an example. We'll start by creating a dataframe Which contains an array of rows and nested rows. My Integers are not casted yet in the dataframe, and they're created as strings :

import org.apache.spark.sql._
import org.apache.spark.sql.types._
val rows1 = Seq(
  Row("1", Row("a", "b"), "8.00", Seq(Row("1","2"), Row("12","22"))),
  Row("2", Row("c", "d"), "9.00", Seq(Row("3","4"), Row("33","44")))
)

val rows1Rdd = spark.sparkContext.parallelize(rows1, 4)

val schema1 = StructType(
  Seq(
    StructField("id", StringType, true),
    StructField("s1", StructType(
      Seq(
        StructField("x", StringType, true),
        StructField("y", StringType, true)
      )
    ), true),
    StructField("d", StringType, true),
    StructField("s2", ArrayType(StructType(
      Seq(
        StructField("u", StringType, true),
        StructField("v", StringType, true)
      )
    )), true)
  )
)

val df1 = spark.createDataFrame(rows1Rdd, schema1)

Here's the schema of the created dataframe :

       df1.printSchema
       root
       |-- id: string (nullable = true)
       |-- s1: struct (nullable = true)
       |    |-- x: string (nullable = true)
       |    |-- y: string (nullable = true)
       |-- d: string (nullable = true)
       |-- s2: array (nullable = true)
       |    |-- element: struct (containsNull = true)
       |    |    |-- u: string (nullable = true)
       |    |    |-- v: string (nullable = true)

What I want to do is to cast all the strings which can be an integer, to an integer. I tried to do the following but it didn't work:

df1.selectExpr("CAST (id AS INTEGER) as id",
  "STRUCT (s1.x, s1.y) AS s1",
  "CAST (d AS DECIMAL) as d",
  "Array (Struct(CAST (s2.u AS INTEGER), CAST (s2.v AS INTEGER))) as s2").show()

I was having the following exception :

cannot resolve 'CAST(`s2`.`u` AS INT)' due to data type mismatch: cannot cast array<string> to int; line 1 pos 14;

Anyone has the right query to cast all the values to INTEGER ? I'll be grateful.

Thanks a lot,

zero323 · Accepted Answer · 2018-07-31 22:07:52Z

9

You should match a full structure:

val result = df1.selectExpr(
  "CAST(id AS integer) id",
  "s1",
  "CAST(d AS decimal) d",
  "CAST(s2 AS array<struct<u:integer,v:integer>>) s2"
)

which should give you following schema:

result.printSchema

root
 |-- id: integer (nullable = true)
 |-- s1: struct (nullable = true)
 |    |-- x: string (nullable = true)
 |    |-- y: string (nullable = true)
 |-- d: decimal(10,0) (nullable = true)
 |-- s2: array (nullable = true)
 |    |-- element: struct (containsNull = true)
 |    |    |-- u: integer (nullable = true)
 |    |    |-- v: integer (nullable = true)

and data:

result.show

+---+-----+---+----------------+
| id|   s1|  d|              s2|
+---+-----+---+----------------+
|  1|[a,b]|  8|[[1,2], [12,22]]|
|  2|[c,d]|  9|[[3,4], [33,44]]|
+---+-----+---+----------------+

edited Jul 31, 2018 at 22:07

answered Jul 31, 2018 at 19:34

zero323

331k108 gold badges981 silver badges958 bronze badges

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

2 Comments

Zambonilli Over a year ago

For those looking for how to do this just using dataframes straight up, you just cast the array the same way as the selectExpr. E.G. result1 = df1.withColumn("s2", F.col("s2").cast("array<struct<u:integer,v:integer>>"))

Nipun Over a year ago

What if I need only to convert u to integer and do not need to include v at all ?

Collectives™ on Stack Overflow

How to cast an array of struct in a spark dataframe using selectExpr?

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