[jira] [Commented] (SPARK-37604) Change emptyValueInRead's effect to that any fields matching this string will be set as "" when reading csv files
[
https://issues.apache.org/jira/browse/SPARK-37604?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17461525#comment-17461525
]
Wei Guo commented on SPARK-37604:
-
Well, I think your explanation is clearly and reasonable and it convinced me.
So I'll close this issue and the PR related. Thank you!
> Change emptyValueInRead's effect to that any fields matching this string will
> be set as "" when reading csv files
> -
>
> Key: SPARK-37604
> URL: https://issues.apache.org/jira/browse/SPARK-37604
> Project: Spark
> Issue Type: Improvement
> Components: SQL
>Affects Versions: 2.4.0, 3.2.0
>Reporter: Wei Guo
>Priority: Major
> Attachments: empty_test.png
>
>
> The csv data format is imported from databricks
> [spark-csv|https://github.com/databricks/spark-csv] by issue SPARK-12833 with
> PR [10766|https://github.com/apache/spark/pull/10766] .
> {*}For the nullValue option{*}, according to features described in spark-csv
> readme file, it's designed as:
> {noformat}
> When reading files:
> nullValue: specifies a string that indicates a null value, any fields
> matching this string will be set as nulls in the DataFrame
> When writing files:
> nullValue: specifies a string that indicates a null value, nulls in the
> DataFrame will be written as this string.
> {noformat}
> For example, when writing:
> {code:scala}
> Seq(("Tesla", null:String)).toDF("make", "comment").write.option("nullValue",
> "NULL").csv(path){code}
> The saved csv file is shown as:
> {noformat}
> Tesla,NULL
> {noformat}
> When reading:
> {code:scala}
> spark.read.option("nullValue", "NULL").csv(path).show()
> {code}
> The parsed dataframe is shown as:
> ||make||comment||
> |Tesla|null|
> We can find that null columns in dataframe can be saved as "NULL" strings in
> csv files and {color:#00875a}*"NULL" strings in csv files can be parsed as
> null columns*{color} in dataframe. That is:
> {noformat}
> When writing, convert null(in dataframe) to nullValue(in csv)
> When reading, convert nullValue or nothing(in csv) to null(in dataframe)
> {noformat}
> But actually, the option nullValue in depended component univocity's
> {*}_CommonSettings_{*}, is designed as that:
> {noformat}
> when reading, if the parser does not read any character from the input, the
> nullValue is used instead of an empty string.
> when writing, if the writer has a null object to write to the output, the
> nullValue is used instead of an empty string.{noformat}
> {*}There is a difference when reading{*}. In univocity, nothing content will
> be convert to nullValue strings. But In Spark, we finally convert nothing
> content or nullValue strings to null in *_UnivocityParser_ _nullSafeDatum_*
> method:
> {code:java}
> private def nullSafeDatum(
> datum: String,
> name: String,
> nullable: Boolean,
> options: CSVOptions)(converter: ValueConverter): Any = {
> if (datum == options.nullValue || datum == null) {
> if (!nullable) {
> throw QueryExecutionErrors.foundNullValueForNotNullableFieldError(name)
> }
> null
> } else {
> converter.apply(datum)
> }
> } {code}
>
> From now, we start to talk about emptyValue.
> {*}For the emptyValue option{*}, we add a emptyValueInRead option for
> reading and a emptyValueInWrite option for writing. I found that Spark keeps
> the same behaviors for emptyValue with univocity, that is:
> {noformat}
> When reading, if the parser does not read any character from the input, and
> the input is within quotes, the empty is used instead of an empty string.
> When writing, if the writer has an empty String to write to the output, the
> emptyValue is used instead of an empty string.{noformat}
> For example, when writing:
> {code:scala}
> Seq(("Tesla", "")).toDF("make", "comment").write.option("emptyValue",
> "EMPTY").csv(path){code}
> The saved csv file is shown as:
> {noformat}
> Tesla,EMPTY {noformat}
> When reading:
> {code:scala}
> spark.read.option("emptyValue", "EMPTY").csv(path).show()
> {code}
> The parsed dataframe is shown as:
> ||make||comment||
> |Tesla|EMPTY|
> We can find that empty columns in dataframe can be saved as "EMPTY" strings
> in csv files, *{color:#de350b}but "EMPTY" strings in csv files can not be
> parsed as empty columns{color}* in dataframe. That is:
> {noformat}
> When writing, convert "" empty(in dataframe) to emptyValue(in csv)
> When reading, convert "\"\"" quoted empty strings to emptyValue(in dataframe)
> {noformat}
>
> There is an obvious difference between nullValue and emptyValue in read
> handling. For nullValue, we will convert nothing or nullValue strings to null
> in dataframe, but for emptyValue, we just try to convert "\"\""(quoted empty
> strings) to emptyValue
[jira] [Commented] (SPARK-37604) Change emptyValueInRead's effect to that any fields matching this string will be set as "" when reading csv files
[
https://issues.apache.org/jira/browse/SPARK-37604?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17461495#comment-17461495
]
Max Gekk commented on SPARK-37604:
--
1. We don't declare that the behavior with the options *emptyValue* and
*nullValue* should be the same while reading CSV files. Even the options have
similar names, though.
2. The comment for emptyValueInRead states clearly that an empty input string
should be converted to the specified string in the option. There is no
contradiction, I believe.
I don't see any issues here so far that's why I asked you for a real use case
when you need to convert some non-empty string to the empty one.
> Change emptyValueInRead's effect to that any fields matching this string will
> be set as "" when reading csv files
> -
>
> Key: SPARK-37604
> URL: https://issues.apache.org/jira/browse/SPARK-37604
> Project: Spark
> Issue Type: Improvement
> Components: SQL
>Affects Versions: 2.4.0, 3.2.0
>Reporter: Wei Guo
>Priority: Major
> Attachments: empty_test.png
>
>
> The csv data format is imported from databricks
> [spark-csv|https://github.com/databricks/spark-csv] by issue SPARK-12833 with
> PR [10766|https://github.com/apache/spark/pull/10766] .
> {*}For the nullValue option{*}, according to features described in spark-csv
> readme file, it's designed as:
> {noformat}
> When reading files:
> nullValue: specifies a string that indicates a null value, any fields
> matching this string will be set as nulls in the DataFrame
> When writing files:
> nullValue: specifies a string that indicates a null value, nulls in the
> DataFrame will be written as this string.
> {noformat}
> For example, when writing:
> {code:scala}
> Seq(("Tesla", null:String)).toDF("make", "comment").write.option("nullValue",
> "NULL").csv(path){code}
> The saved csv file is shown as:
> {noformat}
> Tesla,NULL
> {noformat}
> When reading:
> {code:scala}
> spark.read.option("nullValue", "NULL").csv(path).show()
> {code}
> The parsed dataframe is shown as:
> ||make||comment||
> |Tesla|null|
> We can find that null columns in dataframe can be saved as "NULL" strings in
> csv files and {color:#00875a}*"NULL" strings in csv files can be parsed as
> null columns*{color} in dataframe. That is:
> {noformat}
> When writing, convert null(in dataframe) to nullValue(in csv)
> When reading, convert nullValue or nothing(in csv) to null(in dataframe)
> {noformat}
> But actually, the option nullValue in depended component univocity's
> {*}_CommonSettings_{*}, is designed as that:
> {noformat}
> when reading, if the parser does not read any character from the input, the
> nullValue is used instead of an empty string.
> when writing, if the writer has a null object to write to the output, the
> nullValue is used instead of an empty string.{noformat}
> {*}There is a difference when reading{*}. In univocity, nothing content will
> be convert to nullValue strings. But In Spark, we finally convert nothing
> content or nullValue strings to null in *_UnivocityParser_ _nullSafeDatum_*
> method:
> {code:java}
> private def nullSafeDatum(
> datum: String,
> name: String,
> nullable: Boolean,
> options: CSVOptions)(converter: ValueConverter): Any = {
> if (datum == options.nullValue || datum == null) {
> if (!nullable) {
> throw QueryExecutionErrors.foundNullValueForNotNullableFieldError(name)
> }
> null
> } else {
> converter.apply(datum)
> }
> } {code}
>
> From now, we start to talk about emptyValue.
> {*}For the emptyValue option{*}, we add a emptyValueInRead option for
> reading and a emptyValueInWrite option for writing. I found that Spark keeps
> the same behaviors for emptyValue with univocity, that is:
> {noformat}
> When reading, if the parser does not read any character from the input, and
> the input is within quotes, the empty is used instead of an empty string.
> When writing, if the writer has an empty String to write to the output, the
> emptyValue is used instead of an empty string.{noformat}
> For example, when writing:
> {code:scala}
> Seq(("Tesla", "")).toDF("make", "comment").write.option("emptyValue",
> "EMPTY").csv(path){code}
> The saved csv file is shown as:
> {noformat}
> Tesla,EMPTY {noformat}
> When reading:
> {code:scala}
> spark.read.option("emptyValue", "EMPTY").csv(path).show()
> {code}
> The parsed dataframe is shown as:
> ||make||comment||
> |Tesla|EMPTY|
> We can find that empty columns in dataframe can be saved as "EMPTY" strings
> in csv files, *{color:#de350b}but "EMPTY" strings in csv files can not be
> parsed as empty columns{color}* in dataframe. That is:
> {noformat}
> When writing, convert "" empty(in dataframe) to emptyValue(in csv)
[jira] [Commented] (SPARK-37604) Change emptyValueInRead's effect to that any fields matching this string will be set as "" when reading csv files
[
https://issues.apache.org/jira/browse/SPARK-37604?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17461393#comment-17461393
]
Wei Guo commented on SPARK-37604:
-
As the consideration of Hyukjin Kwon in the PR related, if we worry about
making a breaking change, we can add a new option to support it.
> Change emptyValueInRead's effect to that any fields matching this string will
> be set as "" when reading csv files
> -
>
> Key: SPARK-37604
> URL: https://issues.apache.org/jira/browse/SPARK-37604
> Project: Spark
> Issue Type: Improvement
> Components: SQL
>Affects Versions: 2.4.0, 3.2.0
>Reporter: Wei Guo
>Priority: Major
> Attachments: empty_test.png
>
>
> The csv data format is imported from databricks
> [spark-csv|https://github.com/databricks/spark-csv] by issue SPARK-12833 with
> PR [10766|https://github.com/apache/spark/pull/10766] .
> {*}For the nullValue option{*}, according to features described in spark-csv
> readme file, it's designed as:
> {noformat}
> When reading files:
> nullValue: specifies a string that indicates a null value, any fields
> matching this string will be set as nulls in the DataFrame
> When writing files:
> nullValue: specifies a string that indicates a null value, nulls in the
> DataFrame will be written as this string.
> {noformat}
> For example, when writing:
> {code:scala}
> Seq(("Tesla", null:String)).toDF("make", "comment").write.option("nullValue",
> "NULL").csv(path){code}
> The saved csv file is shown as:
> {noformat}
> Tesla,NULL
> {noformat}
> When reading:
> {code:scala}
> spark.read.option("nullValue", "NULL").csv(path).show()
> {code}
> The parsed dataframe is shown as:
> ||make||comment||
> |Tesla|null|
> We can find that null columns in dataframe can be saved as "NULL" strings in
> csv files and {color:#00875a}*"NULL" strings in csv files can be parsed as
> null columns*{color} in dataframe. That is:
> {noformat}
> When writing, convert null(in dataframe) to nullValue(in csv)
> When reading, convert nullValue or nothing(in csv) to null(in dataframe)
> {noformat}
> But actually, the option nullValue in depended component univocity's
> {*}_CommonSettings_{*}, is designed as that:
> {noformat}
> when reading, if the parser does not read any character from the input, the
> nullValue is used instead of an empty string.
> when writing, if the writer has a null object to write to the output, the
> nullValue is used instead of an empty string.{noformat}
> {*}There is a difference when reading{*}. In univocity, nothing content will
> be convert to nullValue strings. But In Spark, we finally convert nothing
> content or nullValue strings to null in *_UnivocityParser_ _nullSafeDatum_*
> method:
> {code:java}
> private def nullSafeDatum(
> datum: String,
> name: String,
> nullable: Boolean,
> options: CSVOptions)(converter: ValueConverter): Any = {
> if (datum == options.nullValue || datum == null) {
> if (!nullable) {
> throw QueryExecutionErrors.foundNullValueForNotNullableFieldError(name)
> }
> null
> } else {
> converter.apply(datum)
> }
> } {code}
>
> From now, we start to talk about emptyValue.
> {*}For the emptyValue option{*}, we add a emptyValueInRead option for
> reading and a emptyValueInWrite option for writing. I found that Spark keeps
> the same behaviors for emptyValue with univocity, that is:
> {noformat}
> When reading, if the parser does not read any character from the input, and
> the input is within quotes, the empty is used instead of an empty string.
> When writing, if the writer has an empty String to write to the output, the
> emptyValue is used instead of an empty string.{noformat}
> For example, when writing:
> {code:scala}
> Seq(("Tesla", "")).toDF("make", "comment").write.option("emptyValue",
> "EMPTY").csv(path){code}
> The saved csv file is shown as:
> {noformat}
> Tesla,EMPTY {noformat}
> When reading:
> {code:scala}
> spark.read.option("emptyValue", "EMPTY").csv(path).show()
> {code}
> The parsed dataframe is shown as:
> ||make||comment||
> |Tesla|EMPTY|
> We can find that empty columns in dataframe can be saved as "EMPTY" strings
> in csv files, *{color:#de350b}but "EMPTY" strings in csv files can not be
> parsed as empty columns{color}* in dataframe. That is:
> {noformat}
> When writing, convert "" empty(in dataframe) to emptyValue(in csv)
> When reading, convert "\"\"" quoted empty strings to emptyValue(in dataframe)
> {noformat}
>
> There is an obvious difference between nullValue and emptyValue in read
> handling. For nullValue, we will convert nothing or nullValue strings to null
> in dataframe, but for emptyValue, we just try to convert "\"\""(quoted empty
> strings) to emptyValue
[jira] [Commented] (SPARK-37604) Change emptyValueInRead's effect to that any fields matching this string will be set as "" when reading csv files
[
https://issues.apache.org/jira/browse/SPARK-37604?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17461390#comment-17461390
]
Wei Guo commented on SPARK-37604:
-
In short, for null values, we can save null values in dataframe as "NULL"
strings in csv files, and read back "NULL" strings as null values with the same
nullValue option("NULL"). But for empty values, if we save empty values in
dataframe as "EMPTY" strings in csv files, we can not read back "EMPTY" strings
as empty values with the same emptyValue("EMPTY"), we finally get "EMPTY"
strings. [~maxgekk]
> Change emptyValueInRead's effect to that any fields matching this string will
> be set as "" when reading csv files
> -
>
> Key: SPARK-37604
> URL: https://issues.apache.org/jira/browse/SPARK-37604
> Project: Spark
> Issue Type: Improvement
> Components: SQL
>Affects Versions: 2.4.0, 3.2.0
>Reporter: Wei Guo
>Priority: Major
> Attachments: empty_test.png
>
>
> The csv data format is imported from databricks
> [spark-csv|https://github.com/databricks/spark-csv] by issue SPARK-12833 with
> PR [10766|https://github.com/apache/spark/pull/10766] .
> {*}For the nullValue option{*}, according to features described in spark-csv
> readme file, it's designed as:
> {noformat}
> When reading files:
> nullValue: specifies a string that indicates a null value, any fields
> matching this string will be set as nulls in the DataFrame
> When writing files:
> nullValue: specifies a string that indicates a null value, nulls in the
> DataFrame will be written as this string.
> {noformat}
> For example, when writing:
> {code:scala}
> Seq(("Tesla", null:String)).toDF("make", "comment").write.option("nullValue",
> "NULL").csv(path){code}
> The saved csv file is shown as:
> {noformat}
> Tesla,NULL
> {noformat}
> When reading:
> {code:scala}
> spark.read.option("nullValue", "NULL").csv(path).show()
> {code}
> The parsed dataframe is shown as:
> ||make||comment||
> |Tesla|null|
> We can find that null columns in dataframe can be saved as "NULL" strings in
> csv files and {color:#00875a}*"NULL" strings in csv files can be parsed as
> null columns*{color} in dataframe. That is:
> {noformat}
> When writing, convert null(in dataframe) to nullValue(in csv)
> When reading, convert nullValue or nothing(in csv) to null(in dataframe)
> {noformat}
> But actually, the option nullValue in depended component univocity's
> {*}_CommonSettings_{*}, is designed as that:
> {noformat}
> when reading, if the parser does not read any character from the input, the
> nullValue is used instead of an empty string.
> when writing, if the writer has a null object to write to the output, the
> nullValue is used instead of an empty string.{noformat}
> {*}There is a difference when reading{*}. In univocity, nothing content will
> be convert to nullValue strings. But In Spark, we finally convert nothing
> content or nullValue strings to null in *_UnivocityParser_ _nullSafeDatum_*
> method:
> {code:java}
> private def nullSafeDatum(
> datum: String,
> name: String,
> nullable: Boolean,
> options: CSVOptions)(converter: ValueConverter): Any = {
> if (datum == options.nullValue || datum == null) {
> if (!nullable) {
> throw QueryExecutionErrors.foundNullValueForNotNullableFieldError(name)
> }
> null
> } else {
> converter.apply(datum)
> }
> } {code}
>
> From now, we start to talk about emptyValue.
> {*}For the emptyValue option{*}, we add a emptyValueInRead option for
> reading and a emptyValueInWrite option for writing. I found that Spark keeps
> the same behaviors for emptyValue with univocity, that is:
> {noformat}
> When reading, if the parser does not read any character from the input, and
> the input is within quotes, the empty is used instead of an empty string.
> When writing, if the writer has an empty String to write to the output, the
> emptyValue is used instead of an empty string.{noformat}
> For example, when writing:
> {code:scala}
> Seq(("Tesla", "")).toDF("make", "comment").write.option("emptyValue",
> "EMPTY").csv(path){code}
> The saved csv file is shown as:
> {noformat}
> Tesla,EMPTY {noformat}
> When reading:
> {code:scala}
> spark.read.option("emptyValue", "EMPTY").csv(path).show()
> {code}
> The parsed dataframe is shown as:
> ||make||comment||
> |Tesla|EMPTY|
> We can find that empty columns in dataframe can be saved as "EMPTY" strings
> in csv files, *{color:#de350b}but "EMPTY" strings in csv files can not be
> parsed as empty columns{color}* in dataframe. That is:
> {noformat}
> When writing, convert "" empty(in dataframe) to emptyValue(in csv)
> When reading, convert "\"\"" quoted empty strings to emptyValue(in dataframe)
>
[jira] [Commented] (SPARK-37604) Change emptyValueInRead's effect to that any fields matching this string will be set as "" when reading csv files
[
https://issues.apache.org/jira/browse/SPARK-37604?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17460144#comment-17460144
]
Wei Guo commented on SPARK-37604:
-
For codes:
{code:scala}
val data = Seq(("Tesla", "")).toDF("make", "comment")
data.write.option("emptyValue", "EMPTY").csv("/Users/guowei19/work/test_empty")
{code}
The csv file's content is as:
{noformat}
Tesla,EMPTY
{noformat}
(cat part-0-f0ed9c50-b1bf-4db9-9964-38fbf411e29c-c000.csv)
When I read it back to dataframe:
{code:scala}
spark.read.option("emptyValue",
"EMPTY").schema(data.schema).csv("/Users/guowei19/work/test_empty").show()
{code}
I want the column *comment* is "" rather a "EMPTY" string.
!image-2021-12-16-01-57-55-864.png|width=424,height=173!
> Change emptyValueInRead's effect to that any fields matching this string will
> be set as "" when reading csv files
> -
>
> Key: SPARK-37604
> URL: https://issues.apache.org/jira/browse/SPARK-37604
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 2.4.0, 3.2.0
>Reporter: Wei Guo
>Priority: Major
> Attachments: empty_test.png, image-2021-12-16-01-57-55-864.png
>
>
> The csv data format is imported from databricks
> [spark-csv|https://github.com/databricks/spark-csv] by issue SPARK-12833 with
> PR [10766|https://github.com/apache/spark/pull/10766] .
> {*}For the nullValue option{*}, according to features described in spark-csv
> readme file, it's designed as:
> {noformat}
> When reading files:
> nullValue: specifies a string that indicates a null value, any fields
> matching this string will be set as nulls in the DataFrame
> When writing files:
> nullValue: specifies a string that indicates a null value, nulls in the
> DataFrame will be written as this string.
> {noformat}
> For example, when writing:
> {code:scala}
> Seq(("Tesla", null:String)).toDF("make", "comment").write.option("nullValue",
> "NULL").csv(path){code}
> The saved csv file is shown as:
> {noformat}
> Tesla,NULL
> {noformat}
> When reading:
> {code:scala}
> spark.read.option("nullValue", "NULL").csv(path).show()
> {code}
> The parsed dataframe is shown as:
> ||make||comment||
> |Tesla|null|
> We can find that null columns in dataframe can be saved as "NULL" strings in
> csv files and {color:#00875a}*"NULL" strings in csv files can be parsed as
> null columns*{color} in dataframe. That is:
> {noformat}
> When writing, convert null(in dataframe) to nullValue(in csv)
> When reading, convert nullValue or nothing(in csv) to null(in dataframe)
> {noformat}
> But actually, the option nullValue in depended component univocity's
> {*}_CommonSettings_{*}, is designed as that:
> {noformat}
> when reading, if the parser does not read any character from the input, the
> nullValue is used instead of an empty string.
> when writing, if the writer has a null object to write to the output, the
> nullValue is used instead of an empty string.{noformat}
> {*}There is a difference when reading{*}. In univocity, nothing content will
> be convert to nullValue strings. But In Spark, we finally convert nothing
> content or nullValue strings to null in *_UnivocityParser_ _nullSafeDatum_*
> method:
> {code:java}
> private def nullSafeDatum(
> datum: String,
> name: String,
> nullable: Boolean,
> options: CSVOptions)(converter: ValueConverter): Any = {
> if (datum == options.nullValue || datum == null) {
> if (!nullable) {
> throw QueryExecutionErrors.foundNullValueForNotNullableFieldError(name)
> }
> null
> } else {
> converter.apply(datum)
> }
> } {code}
>
> From now, we start to talk about emptyValue.
> {*}For the emptyValue option{*}, we add a emptyValueInRead option for
> reading and a emptyValueInWrite option for writing. I found that Spark keeps
> the same behaviors for emptyValue with univocity, that is:
> {noformat}
> When reading, if the parser does not read any character from the input, and
> the input is within quotes, the empty is used instead of an empty string.
> When writing, if the writer has an empty String to write to the output, the
> emptyValue is used instead of an empty string.{noformat}
> For example, when writing:
> {code:scala}
> Seq(("Tesla", "")).toDF("make", "comment").write.option("emptyValue",
> "EMPTY").csv(path){code}
> The saved csv file is shown as:
> {noformat}
> Tesla,EMPTY {noformat}
> When reading:
> {code:scala}
> spark.read.option("emptyValue", "EMPTY").csv(path).show()
> {code}
> The parsed dataframe is shown as:
> ||make||comment||
> |Tesla|EMPTY|
> We can find that empty columns in dataframe can be saved as "EMPTY" strings
> in csv files, *{color:#de350b}but "EMPTY" strings in csv files can not be
> parsed as empty columns{color}* in
