Spark : Failed to send boolean column containg null value to Oracle

user_14992414 0
Viewed 870

I have a dataframe that contains a Boolean column (TRUE,FALSE,NULL). When i send this dataframe to an existing table in Oracle 19c using Spark2 Scala jdbc, it FAILS with the error : java.sql.SQLException: Invalid column type: 16.

But, when they aren't a NULL values in the dataframe, the task is accomplished as successfully. In addition, if I insert a NULL value into the Boolean field directly in Oracle, i have no problem (this is just for testing NULL value directly in Oracle).

The way that I write the Data in Oracle Using Spark is :

df.write.mode(SaveMode.Append).jdbc(url, tableName, connectionProperties)

DataFrame that i can write in Oracle:

val df = Seq(("d1",TRUE), ("d2",FALSE)).toDF("firstColumn","secondColumn")

DataFrame that I can't write in Oracle :

val df = Seq(("d1",TRUE), ("d2",FALSE),("d3",null)).toDF("fistColumn","secondColumn")

And here is some information about the destination column in Oracle :

  • DataType : NUMBER(1,0)
  • NULLABLE : YES
  • Data_Default : (null)
apache-spark-sql apache-spark scala oracle
1 Answers

I've faced the same issue recently, and I have came across with a solution that I want to share with you. As you well noticed, Oracle JDBC transforms the boolean type into a NUMBER(1,0). Therefore, we will perform that operation ourselves in Spark. To better illustrate it, I'll show you a very simple example (it's in Python, I haven't ever used Scala, I'm sorry, but I think you'll understand it fine):

df = spark.createDataFrame([['Alice', True], ['Bob', False], ['Charlie', None]], 'name STRING, married BOOLEAN')
df.show()
df.printSchema()

Here we have a DataFrame with a boolean column with three values: true, false and null:

enter image description here

Now we're going to cast the "married" column from boolean to integer:

df = df.withColumn("married", df['married'].cast("integer"))

And here is the result:

enter image description here

Now the Oracle JDBC won't complain anymore about the data type (I've tested it myself and it worked fine, I hope it's the same for you). Good luck!

user_31271830
Related