Join in Spark returns duplicates implicit data types don't match

user_4115050 0
Viewed 613

I am getting duplicates when joining on two dataframes where one key is a decimal and the other is a string. It seems that Spark is converting the decimal to a string which results in a scientific notation expression, but then shows the original result in decimal form just fine. I found a workaround by converting to string directly, but this seems dangerous as duplicates are created without warning. Is this a bug? How can I detect when this is happening?

Here's an demo in pyspark on Spark 2.4:

>>> from pyspark.sql.functions import *
>>> from pyspark.sql.types import *
>>> df1 = spark.createDataFrame([('a', 9223372034559809871), ('b', 9223372034559809771)], ['group', 'id_int'])
>>> df1=df1.withColumn('id',col('id_int').cast(DecimalType(38,0)))
>>>
>>> df1.show()
+-----+-------------------+-------------------+
|group|             id_int|                 id|
+-----+-------------------+-------------------+
|    a|9223372034559809871|9223372034559809871|
|    b|9223372034559809771|9223372034559809771|
+-----+-------------------+-------------------+

>>>
>>> df2= spark.createDataFrame([(1, '9223372034559809871'), (2, '9223372034559809771')], ['value', 'id'])
>>> df2.show()
+-----+-------------------+
|value|                 id|
+-----+-------------------+
|    1|9223372034559809871|
|    2|9223372034559809771|
+-----+-------------------+

>>>
>>> df1.join(df2, ["id"]).show()
+-------------------+-----+-------------------+-----+
|                 id|group|             id_int|value|
+-------------------+-----+-------------------+-----+
|9223372034559809871|    a|9223372034559809871|    1|
|9223372034559809871|    a|9223372034559809871|    2|
|9223372034559809771|    b|9223372034559809771|    1|
|9223372034559809771|    b|9223372034559809771|    2|
+-------------------+-----+-------------------+-----+

>>> df1.dtypes
[('group', 'string'), ('id_int', 'bigint'), ('id', 'decimal(38,0)')]
apache-spark join types
1 Answers

It's happenning because of the values (very very large) in the joining keys:

I tweaked the values in the joining condition and it's giving me the proper results :

from pyspark.sql.types import *
    
df1 = spark.createDataFrame([('a', 9223372034559809871), ('b', 9123372034559809771)],
                            ['group', 'id_int'])

df1=df1.withColumn('id',col('id_int').cast(DecimalType(38,0)))

df2= spark.createDataFrame([(1, '9223372034559809871'), (2, '9123372034559809771')],
                           ['value', 'id'])

df1.join(df2, df1["id"]==df2["id"],"inner").show()
user_162062730
Related