UDF's vs Spark sql vs column expressions performance optimization

Question

I understand that UDFs are a complete blackbox to Spark and no attempt will be made to optimize it. But will the usage of Column type and its functions listed in: (https://spark.apache.org/docs/2.1.0/api/scala/index.html#org.apache.spark.sql.Column)
make the function "eligible" for Catalyst Optimizer?.

For example, UDF to create a new column by adding 1 to existing column

val addOne = udf( (num: Int) => num + 1 )
df.withColumn("col2", addOne($"col1"))

The same Function, using Column type:

def addOne(col1: Column) = col1.plus(1)
df.withColumn("col2", addOne($"col1"))

or

spark.sql("select *, col1 + 1 from df")

will there be any difference in performance between them?

Yosi Dahari Yosi Dahari · Accepted Answer · 2017-08-03T19:09:12

Over a simple in-memory set of 6 records, the 2nd and 3rd options yield relatively the same performance of ~70 miliseconds, which is much better than the first (using UDF - 0.7 seconds):

val addOne = udf( (num: Int) => num + 1 )
val res1 = df.withColumn("col2", addOne($"col1"))
res1.show()
//df.explain()

def addOne2(col1: Column) = col1.plus(1)
val res2 = df.withColumn("col2", addOne2($"col1"))
res2.show()
//res2.explain()

val res3 = spark.sql("select *, col1 + 1 from df")
res3.show()

Timeline: First two stages are for UDF option, next two for the second option, and last two for spark SQL:

In all three approaches, the shuffle writes was exactly the same (354.0 B) whereas the major difference in the duration was executor compute time when using UDF:

UDF's vs Spark sql vs column expressions performance optimization

2 Answers