cast("decimal(32,9)")正在将输入值0转换为科学值0E-9

Question

我使用一个非常基本的spark代码，以便将输入值"Qty“转换为输出变量，该变量将被插入到RDBMS中。然而，对于输入值"0"，spark cast("decimal(32,9)")将其转换为0E-9。下面是我的代码：

dfFinal = dfInput.withColumn("QtyOut",dfInput.col("Qty").cast("decimal(32,9)");
dfFinal.show();

当Qty的值为"0“时，输出为"0E-9”。然后我尝试使用"format_number“，它解决了这个问题，但随后将QtyOut转换为字符串，并为大于999的数字添加”，“。下面是实现该功能的代码

dfFinal = dfInput.withColumn("NewQtyOut",format_number(dfInput.col("curr_notl_amt"),9));

Answer 1

0和0E-9是一样的，所以我不知道你为什么要把它转换成0.000000000。

这就是对它的测试-

   val dfInput = spark.range(1).withColumn("Qty", col("id").cast(StringType))
    val processDF = dfInput.withColumn("QtyOut",dfInput.col("Qty").cast("decimal(32,9)"))
    processDF.show(false)
    processDF.printSchema()

    processDF.withColumn("NewQtyOut",format_number(processDF.col("QtyOut"),9)).show()
    processDF.withColumn("NewQtyOut",format_number(processDF.col("QtyOut"),9)).printSchema()

    processDF
      .withColumn("isTrue", when(col("QtyOut").equalTo(0), true).otherwise(false))
      .show(false)

输出-

+---+---+------+
|id |Qty|QtyOut|
+---+---+------+
|0  |0  |0E-9  |
+---+---+------+

root
 |-- id: long (nullable = false)
 |-- Qty: string (nullable = false)
 |-- QtyOut: decimal(32,9) (nullable = true)

+---+---+------+-----------+
| id|Qty|QtyOut|  NewQtyOut|
+---+---+------+-----------+
|  0|  0|  0E-9|0.000000000|
+---+---+------+-----------+

root
 |-- id: long (nullable = false)
 |-- Qty: string (nullable = false)
 |-- QtyOut: decimal(32,9) (nullable = true)
 |-- NewQtyOut: string (nullable = true)

+---+---+------+------+
|id |Qty|QtyOut|isTrue|
+---+---+------+------+
|0  |0  |0E-9  |true  |
+---+---+------+------+

请观察isTrue列。

编辑-1(基于注释)

使用我们想要的scale从科学记数法转换decimal -

 def bigDecimalFormatter(x: Double, y: Int): Double =
      BigDecimal(x).setScale(y, BigDecimal.RoundingMode.HALF_UP).toDouble

    val decimalFormatter = udf((decimal: Double, scale: Int) => bigDecimalFormatter(decimal, scale))
    processDF.select(decimalFormatter(col("QtyOut"), lit(9)),
      decimalFormatter(lit(1.1000000453E4), lit(5)))
      .show(false)

输出-

+--------------+--------------------+
|UDF(QtyOut, 9)|UDF(11000.000453, 5)|
+--------------+--------------------+
|0.0           |11000.00045         |
+--------------+--------------------+