How to alias a column in Spark SQL struct

Question

sparkSession.sql("select struct(col1,col2) as myStruct from table1")

return the dataframe with following schema

root
 |-- myStruct : struct (nullable = false)
 |    |-- col1: string (nullable = true)
 |    |-- col2: string (nullable = true)

But I need col1 as myCol1 and col2 as myCol2?

When I use as keyword inside struct function, it fails

sparkSession.sql("select struct(col1 as myCol1,col2 as myCol2) as myStruct from table1")

gives the below error message

mismatched input 'as' expecting {')', ','}(line 1, pos 19)

How to get column alias in struct field?

Which version of Spark do you use? I just tried on 2.3 and works for me fine: scala> sql("select struct(user as l, movie as p) as mystruct from table").printSchema root |-- mystruct: struct (nullable = false) | |-- l: integer (nullable = true) | |-- p: integer (nullable = true) — Tomasz Krol

NamrataK NamrataK · Accepted Answer · 2018-08-16T09:00:47

You can try this in Spark 2.1.0 on the created DF

val newDF = oldDF.withColumn("MyCol",struct($"myCol.col1".alias("myCol1"),$"myCol.col2".alias("myCol2"))).drop("myCol").withColumnRenamed("MyCol","myCol")

How to alias a column in Spark SQL struct

1 Answers