Spark/Hive converting decimal to null value if we specify the precision
more than available precision in file. Below example give you details. I
am not sure why its converting into Null.
Note: You need to trim string before casting to decimal
Table data with col1 and col2 columns
val r = sqlContext.sql("select col2 from nd2629.test")
+-------------+
| col2|
+-------------+
| 1.00|
| 2.0|
| 123.798|
| 123456.67777|
+-------------+
val r = sqlContext.sql("select CAST(TRIM(col2) as decimal(10,4)) from
nd2629.test")
+-----------+
| _c0|
+-----------+
| 1.0000|
| 2.0000|
| 123.7980|
|123456.6778|
+-----------+
val r = sqlContext.sql("select CAST(TRIM(col2) as decimal(10,5)) from
nd2629.test")
+---------+
| _c0|
+---------+
| 1.00000|
| 2.00000|
|123.79800|
| null|
+---------+
you need to specify the precision value as max precision value for column -1
in above case max precision is 5 (123456.*67777*) so we should specify
decimal(10,5)
Thank you,
Naresh
On Tue, Jan 30, 2018 at 8:48 PM, Arnav kumar <akns2...@gmail.com> wrote:
> Hi Experts
>
> I am trying to convert a string with decimal value to decimal in Spark Sql
> and load it into Hive/Sql Server.
>
> In Hive instead of getting converted to decimal all my values are coming
> as null.
>
> In Sql Server instead of getting decimal values are coming without
> precision
>
> Can you please let me know if this is any kind of limitation
>
> Here is my code
>
>
> //select the required columns from actual data frame
> val query ="""select eventId,
> cast(eventData.latitude as Decimal(10,10)) as Latitude,
> cast(eventData.longitude as Decimal(10,10)) as Longitude from event"""
>
> //creating event data frame
> val eventTableDF = sparkSession.sql(query)
> //printing the schema for debugging purpose
> eventTableDF.printSchema()
>
> root
> |-- eventId: string (nullable = true)
> |-- Latitude: decimal(10,10) (nullable = true)
> |-- Longitude: decimal(10,10) (nullable = true)
>
>
>
> val eventTableDF = sparkSession.sql(query)
> import sparkSession.implicits._
> eventTableDF.write.mode(org.apache.spark.sql.SaveMode.
> Append).insertInto(eventTable)
>
>
>
>
>
> With Best Regards
> Arnav Kumar
>
>
>
>
