Odtnhj

I'm trying to groupBy column name host and aggregate average of a column of type ArrayType(StringType) after type casting it to ArrayType(IntegerType).

It throws below error

 `cannot resolve `avg(variables)` due to datatype mismatch: function average requires numeric types, not ArrayType(IntegerType,true);

Input Data - sample Dataframe before grouping

|request|time |type |host |service | variables |
|REST |1542111483170|RESTFUL|KAFKA|www.google.com|[Duration, 7,Type] |
|REST |1542111486570|RESTFUL|KAFKA|www.google.com|[Duration, 9, Type]|

How to cast or handle ArrayType(StringType) to IntegerType i.e Column variables is of ArrayType(varaible.variable:String,varaible.value:String,varaible.TypeString) I want to convert 2nd value of Array varaible.value to Integer for Aggregation (Average calculation)?

Case Class:

 case class ServiceActivity(val request: String, val time: Long, val Type: String, val host: String, val service: String, val variables: Array[Variables])

 case class Variables(val variable: String, val value: String, val Type: String)

Code Below:

val report = df.select("*").where(array_contains(df("variables.variable"),"Duration"))
val intermediate = report.withColumn("variables", col(variables.value).cast(org.apache.spark.sql.types.ArrayType(org.apache.spark.sql.types.IntegerType,true)
intermediate.withColumn("duration",$"variables".getItem(2)).drop("variables").withColumnRenamed("duration","variables")

GroupBy Code(error):

 intermediate.groupBy(intermediate("host")).agg(Map("variables"->"avg"))

Any workarounds.

Thanks

Sorted out by splitting the Array and using concat_ws method

 val splitArray = ab.withColumn("Avg_Duration", concat_ws("", ab("variables.value")))

 splitArray.groupBy(splitArray("host")).agg(Map("Avg_Duration" -> "avg")).show(false)

Thank you