Spark将csv列中的空值视为空数据类型 [英] Spark treating null values in csv column as null datatype

问题描述

我的spark应用程序读取一个csv文件，然后使用sql将其转换为其他格式，然后将结果数据帧写入另一个csv文件中.

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.

例如，我输入的csv如下:

For example, I have input csv as follows:

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

我的转变是:

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

(我无法回答为什么将null用作SecondaryLocationId，这是业务用例)现在spark无法找出SecondaryLocationId的数据类型，并在架构中返回null并在写入输出csv时抛出错误 CSV数据源不支持null数据类型.

(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.

下面是printSchema()和我正在使用的写选项.

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)

有没有一种方法可以默认使用数据类型(例如字符串)?顺便说一句，我可以通过将 null 替换为空string('')来使其工作，但这不是我想要的.

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.

推荐答案

使用lit(null):导入org.apache.spark.sql.functions.{lit，udf}

示例:

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))

或使用这样的UDF:

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)

重新发布零323代码.

reposting zero323 code.

现在让我们讨论第二个问题

Now lets discuss your second 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? "

答案:

在这种情况下，您可以使用选项

In this case you can use option

Databricks Scala样式指南不同意null应该总是被禁止使用Scala代码，并说:对于性能敏感的代码，最好使用null而不是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."

示例:

+------+
|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

这篇关于Spark将csv列中的空值视为空数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！