pyspark - why this differing column nullable configurations error?

user_1232087 0
Viewed 151

Question: Why the following code is complaining about differing column nullable configurations of Hiring_Date column in df vs. SQL Table. What I may be missing here? The error occurs at line df3.write where df3 is supposed to be written to a SQL table

ERROR: ava.sql.SQLException: Spark Dataframe and SQL Server table have differing column nullable configurations at column index 0 DF col Hiring_Date nullable config is true Table col Hiring_Date nullable config is false

Remarks: From I understand (and it has worked in my other scripts), once you define a data type of a column in pyspark and set its null values to some value, that column in df is not nullable.

df = spark.read.csv("myDataFile.txt", sep="|", header="true", inferSchema="false")
            
df1 = df.select( *[ F.when(F.col(column).isNull(),'').otherwise(F.col(column)).alias(column) for column in df.columns])
            
df2 = df1.withColumn("Hiring_Date", df1.Hiring_Date.cast(TimestampType())) \
.withColumn("Hiring_Fee", df1.Hiring_Fee.cast(DoubleType()))
            
df3 = df2.fillna( {'Hiring_Fee' : 0, 'Hiring_Date': '1753-01-01 00:00:00.000'} )
    try:
        df3.write \
        .format("com.microsoft.sqlserver.jdbc.spark") \
        .mode("append") \
        .option("url", url) \
        .option("dbtable", table_name) \
        .option("user", myUserName) \
        .option("password", myPassword) \
        .save()
    except ValueError as error :
        print("Connector write failed", error)

SQL Server Table definition:

CREATE TABLE HR_History(
    Hiring_Date datetime NOT NULL,
    Hiring_Fee float NOT NULL
)
pyspark apache-spark azure-sql-database sql-server python
1 Answers

Can you paste some sample Data, i have taken some dummy Data and replicating your code.

>>> df = spark.read.csv("/Path to/sample1.csv", sep="|", header="true", inferSchema="false")
>>> df.show()
+------------+----------+
|Payment_Date|Hiring_Fee|
+------------+----------+
|  11-10-2022|     89296|
|  12-10-2022|     67760|
|        null|    879798|
+------------+----------+


>>> df1 = df.select( *[ F.when(F.col(column).isNull(),'').otherwise(F.col(column)).alias(column) for column in df.columns])
>>> df1.show()
+------------+----------+
|Payment_Date|Hiring_Fee|
+------------+----------+
|  11-10-2022|     89296|
|  12-10-2022|     67760|
|        null|    879798|
+------------+----------+

# you see in the df1 null is still there, it is not replaced. 

>>> df2 = df1.withColumn("Hiring_Date", to_timestamp("Payment_Date")) \
... .withColumn("Hiring_Fee", df1.Hiring_Fee.cast('double'))
>>> df2.show()
+------------+----------+-----------+
|Payment_Date|Hiring_Fee|Hiring_Date|
+------------+----------+-----------+
|  11-10-2022|   89296.0|       null|
|  12-10-2022|   67760.0|       null|
|        null|  879798.0|       null|
+------------+----------+-----------+

Here Hiring Date col is having all null values.

This could be due to i have taken dummy Data , but you have to check whether there are null somewhere in your df1

user_189201940
Related