Pyspark cast error

[anandreddy23]

Hi All,

hive> create table UK ( a decimal(10,2)) ;

hive> create table IN ( a decimal(10,5)) ;

hive> create view T as select a from UK union all select a from IN ;

above all statements executes successfully in Hive and return results when select statement is executed. However, when select statement executed from python using pyspark I get error saying "Cannot up cast a from decimal(10,2) to decimal(10,5)".

Ideally view looks for same datatype and also this work fine in its source data db(hive). This has become a show stopper and Cculd you please help me with a possible  solution to fix this in pyspark please ? 

Thanks in advance,

Anand.

#pyspark

[Kaniz_Fatma]

Hi @anandreddy23, The error you're encountering, "Cannot upcast a from decimal(10,2) to decimal(10,5)", is typically due to an attempt to cast a decimal value with lower precision to a decimal value with higher accuracy. 

In PySpark, the casting operation is strict and doesn't allow upcasting to a higher precision decimal. This is to prevent any potential data loss from such an operation. You can try to handle this error in the following ways:

1. Change the precision of your target decimal to match the source decimal precision.

2. If you need to increase the accuracy of your decimal, you may need to cast to a different type (like float or double) and then cast to the desired decimal precision.

[anandreddy23]

Hi @Kaniz_Fatma , Many thanks for your quick response.

I am sorry as I can't change the datatype or match the decimals. However, my doubt is again Hive is the DB where the view is created and queries without any errors, why should spark have to look into scale & precision if datatypes match ? We were told spark is framework that speeds up reading and processing data using multiple nodes within cluster, but wasn't aware that it would use its own SQL execution plan and its rules are different from underlying database