Spark SQL change format of the number

Question

After show command spark prints the following:

+-----------------------+---------------------------+
|NameColumn             |NumberColumn               |
+-----------------------+---------------------------+
|name                   |4.3E-5                     |
+-----------------------+---------------------------+

Is there a way to change NumberColumn format to something like 0.000043?

Answer 1

you can use format_number function as

import org.apache.spark.sql.functions.format_number
df.withColumn("NumberColumn", format_number($"NumberColumn", 5))

here 5 is the decimal places you want to show

As you can see in the link above that the format_number functions returns a string column

format_number(Column x, int d)
Formats numeric column x to a format like '#,###,###.##', rounded to d decimal places, and returns the result as a string column.

If your don't require , you can call regexp_replace function which is defined as

regexp_replace(Column e, String pattern, String replacement)
Replace all substrings of the specified string value that match regexp with rep.

and use it as

import org.apache.spark.sql.functions.regexp_replace
df.withColumn("NumberColumn", regexp_replace(format_number($"NumberColumn", 5), ",", ""))

Thus comma (,) should be removed for large numbers.

Answer 2

You can use cast operation as below:

val df = sc.parallelize(Seq(0.000043)).toDF("num")    

df.createOrReplaceTempView("data")
spark.sql("select CAST (num as DECIMAL(8,6)) from data")

adjust the precision and scale accordingly.