Spark treating null values in csv column as null datatype

Question

My spark application reads a csv file, transforms it to a different format with sql and writes the result dataframe to a different csv file.

For example, I have input csv as follows:

Id|FirstName|LastName|LocationId
1|John|Doe|123
2|Alex|Doe|234

My transformation is:

Select Id, 
       FirstName, 
       LastName, 
       LocationId as PrimaryLocationId,
       null as SecondaryLocationId
from Input

(I can't answer why the null is being used as SecondaryLocationId, it is business use case) Now spark can't figure out the datatype of SecondaryLocationId and returns null in the schema and throws the error CSV data source does not support null data type while writing to output csv.

Below are printSchema() and write options I am using.

root
     |-- Id: string (nullable = true)
     |-- FirstName: string (nullable = true)
     |-- LastName: string (nullable = true)
     |-- PrimaryLocationId: string (nullable = false)
     |-- SecondaryLocationId: null (nullable = true)

dataFrame.repartition(1).write
      .mode(SaveMode.Overwrite)
      .option("header", "true")
      .option("delimiter", "|")
      .option("nullValue", "")
      .option("inferSchema", "true")
      .csv(outputPath)

Is there a way to default to a datatype (such as string)? By the way, I can get this to work by replacing null with empty string('') but that is not what I want to do.

Solution

use lit(null): import org.apache.spark.sql.functions.{lit, udf}

Example:

import org.apache.spark.sql.functions.{lit, udf}

case class Record(foo: Int, bar: String)
val df = Seq(Record(1, "foo"), Record(2, "bar")).toDF

val dfWithFoobar = df.withColumn("foobar", lit(null: String))


scala> dfWithFoobar.printSchema
root
|-- foo: integer (nullable = false)
|-- bar: string (nullable = true)
|-- foobar: null (nullable = true)
and it is not retained by the csv writer. If it is a hard requirement you 
 can cast column to the specific type (lets say String):

import org.apache.spark.sql.types.StringType
df.withColumn("foobar", lit(null).cast(StringType))

or use an UDF like this:

val getNull = udf(() => None: Option[String]) // Or some other type

df.withColumn("foobar", getNull()).printSchema

root
 |-- foo: integer (nullable = false)
 |-- bar: string (nullable = true)
 |-- foobar: string (nullable = true)

reposting zero323 code.

Now lets discuss your second question

Question :

"This is only when I know which columns will be treated as null datatype. When a large number of files are being read and applied various transformations on, then I wouldn't know or is there a way I might know which fields are null treated? "

Ans :

In this case you can use option

The Databricks Scala style guide does not agree that null should always be banned from Scala code and says: “For performance sensitive code, prefer null over Option, in order to avoid virtual method calls and boxing.”

Example :

+------+
|number|
+------+
|     1|
|     8|
|    12|
|  null|
+------+


val actualDf = sourceDf.withColumn(
  "is_even",
  when(
    col("number").isNotNull, 
    isEvenSimpleUdf(col("number"))
  ).otherwise(lit(null))
)

actualDf.show()
+------+-------+
|number|is_even|
+------+-------+
|     1|  false|
|     8|   true|
|    12|   true|
|  null|   null|
+------+-------+

• https://medium.com/@mrpowers/dealing-with-null-in-spark-cfdbb12f231e
• https://github.com/vaquarkhan/scala-style-guide

Comments

[tturner]

This is only when I know which columns will be treated as null datatype. When a large number of files are being read and applied various transformations on, then I wouldn't know or is there a way I might know which fields are null treated?