Error when running a query involving ROUND function in spark sql

Question

I am trying, in pyspark, to obtain a new column by rounding one column of a table to the precision specified, in each row, by another column of the same table, e.g., from the following table:

+--------+--------+
|    Data|Rounding|
+--------+--------+
|3.141592|       3|
|0.577215|       1|
+--------+--------+

I should be able to obtain the following result:

+--------+--------+--------------+
|    Data|Rounding|Rounded_Column|
+--------+--------+--------------+
|3.141592|       3|         3.142|
|0.577215|       1|           0.6|
+--------+--------+--------------+

In particular, I have tried the following code:

import pandas as pd
from pyspark.sql import SparkSession
from pyspark.sql.types import (
  StructType, StructField, FloatType, LongType, 
  IntegerType
)

pdDF = pd.DataFrame(columns=["Data", "Rounding"], data=[[3.141592, 3], 
   [0.577215, 1]])

mySchema = StructType([ StructField("Data", FloatType(), True), 
StructField("Rounding", IntegerType(), True)])

spark = (SparkSession.builder
    .master("local")
    .appName("column rounding")
    .getOrCreate())

df = spark.createDataFrame(pdDF,schema=mySchema)

df.show()

df.createOrReplaceTempView("df_table")


df_rounded = spark.sql("SELECT Data, Rounding, ROUND(Data, Rounding) AS Rounded_Column FROM df_table")

df_rounded .show()

but I get the following error:

raise AnalysisException(s.split(': ', 1)[1], stackTrace)
pyspark.sql.utils.AnalysisException: u"cannot resolve 'round(df_table.`Data`, df_table.`Rounding`)' due to data type mismatch: Only foldable Expression is allowed for scale arguments; line 1 pos 23;\n'Project [Data#0, Rounding#1, round(Data#0, Rounding#1) AS Rounded_Column#12]\n+- SubqueryAlias df_table\n   +- LogicalRDD [Data#0, Rounding#1], false\n"

Any help would be deeply appreciated :)

Answer 1

With spark sql , the catalyst throws out the following error in your run - Only foldable Expression is allowed for scale arguments

i.e @param scale new scale to be round to, this should be a constant int at runtime

ROUND only expect a Literal for the scale. you can try out writing custom code instead of spark-sql way.

EDIT:

With UDF,

val df = Seq(
  (3.141592,3),
  (0.577215,1)).toDF("Data","Rounding")

df.show()
df.createOrReplaceTempView("df_table")

import org.apache.spark.sql.functions._
def RoundUDF(customvalue:Double, customscale:Int):Double = BigDecimal(customvalue).setScale(customscale, BigDecimal.RoundingMode.HALF_UP).toDouble
spark.udf.register("RoundUDF", RoundUDF(_:Double,_:Int):Double)

val df_rounded = spark.sql("select Data, Rounding, RoundUDF(Data, Rounding) as Rounded_Column from df_table")
df_rounded.show()

Input:

    +--------+--------+
    |    Data|Rounding|
    +--------+--------+
    |3.141592|       3|
    |0.577215|       1|
    +--------+--------+

Output:

+--------+--------+--------------+
|    Data|Rounding|Rounded_Column|
+--------+--------+--------------+
|3.141592|       3|         3.142|
|0.577215|       1|           0.6|
+--------+--------+--------------+