You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by ur...@apache.org on 2026/09/13 23:47:08 UTC
(spark) branch branch-4.x updated: [SPARK-59477][SQL] Add CSV write option treatNullAsEmptyString
This is an automated email from the ASF dual-hosted git repository.
uros-b pushed a commit to branch branch-4.x
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-4.x by this push:
new aa0cbc92b570 [SPARK-59477][SQL] Add CSV write option treatNullAsEmptyString
aa0cbc92b570 is described below
commit aa0cbc92b5705a763ae3bc129ece67b8873202d3
Author: Dejan Krakovic <de...@databricks.com>
AuthorDate: Sun Sep 13 23:46:47 2026 +0000
[SPARK-59477][SQL] Add CSV write option treatNullAsEmptyString
### What changes were proposed in this pull request?
Adds a per-write CSV option `treatNullAsEmptyString` that overrides the
session-level `spark.sql.legacy.nullValueWrittenAsQuotedEmptyStringCsv` config
for a single write.
- When set to `false`, nulls are written as a bare, unquoted empty token while
empty strings stay quoted (`""`), so downstream consumers can tell a null
apart from an actual empty string.
- When set to `true`, nulls are written through the `emptyValue` (a quoted
empty string `""` by default).
- When unset (including an explicit `null` value), the session config decides
the behavior, so existing writes are unaffected.
The option is parsed via the existing `getBool` helper, so a non-boolean value
raises the structured `paramIsNotBooleanValueError` naming the option rather
than a bare error. It only takes effect when `nullValue` is left at its default
(empty string); a non-empty `nullValue` is written verbatim, making the option
a no-op, matching how the SQL config it overrides already composes with
`nullValue`.
### Why are the changes needed?
The behavior of writing a null vs. an empty string in CSV is currently only
controllable through the session-level
`spark.sql.legacy.nullValueWrittenAsQuotedEmptyStringCsv` config. A workload
that needs to differentiate null from an empty string in its output cannot do
so per-write without changing the session/workspace default, which is not
always settable. A per-write option lets a single write opt into the
differentiation without affecting other writes.
### Does this PR introduce _any_ user-facing change?
Yes, it adds a new CSV write option `treatNullAsEmptyString`. Behavior is
unchanged when the option is not set (the session config continues to decide),
so existing writes are unaffected.
### How was this patch tested?
Added unit tests in `CSVSuite` covering: the option winning over the session
config for both `true`/`false`, the no-op behavior when a non-empty `nullValue`
is set, explicit-null handling (falls back to the session config), and the
structured error raised for a non-boolean value. Also updated the
`validate CSV Options` test for the new option.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Isaac
This pull request and its description were written by Isaac.
Closes #58767 from dejankrak-db/csv-treat-null-as-empty-string.
Authored-by: Dejan Krakovic <de...@databricks.com>
Signed-off-by: Uros Bojanic <22...@users.noreply.github.com>
(cherry picked from commit 66737e0482008f1eba28583cdc73d9bee54ac61c)
Signed-off-by: Uros Bojanic <22...@users.noreply.github.com>
---
docs/sql-data-sources-csv.md | 6 ++
.../apache/spark/sql/catalyst/csv/CSVOptions.scala | 20 ++++++
.../sql/catalyst/csv/UnivocityGenerator.scala | 9 ++-
.../sql/execution/datasources/csv/CSVSuite.scala | 77 +++++++++++++++++++++-
4 files changed, 109 insertions(+), 3 deletions(-)
diff --git a/docs/sql-data-sources-csv.md b/docs/sql-data-sources-csv.md
index 6fe3dbd83af7..7063685fdfed 100644
--- a/docs/sql-data-sources-csv.md
+++ b/docs/sql-data-sources-csv.md
@@ -144,6 +144,12 @@ Data source options of CSV can be set via:
<td>Sets the string representation of a null value. Since 2.0.1, this <code>nullValue</code> param applies to all supported types including the string type.</td>
<td>read/write</td>
</tr>
+ <tr>
+ <td><code>treatNullAsEmptyString</code></td>
+ <td></td>
+ <td>Controls how null values are written when <code>nullValue</code> is left at its default (empty string). When <code>true</code>, a null is written through <code>emptyValue</code> (a quoted empty string <code>""</code> by default), which makes it indistinguishable from an actual empty string. When <code>false</code>, a null is written as a bare, unquoted empty token, so it can be told apart from an empty string. When unset, the write follows the session default. Setting a non-empty [...]
+ <td>write</td>
+ </tr>
<tr>
<td><code>nanValue</code></td>
<td>NaN</td>
diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/csv/CSVOptions.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/csv/CSVOptions.scala
index 16c953aac77c..359b28c261eb 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/csv/CSVOptions.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/csv/CSVOptions.scala
@@ -280,6 +280,25 @@ class CSVOptions(
*/
val emptyValueInWrite = emptyValue.getOrElse("\"\"")
+ /**
+ * Whether a null value should be written as an empty string (which, given the default
+ * `emptyValue`, means a quoted empty string `""`) rather than as a bare, unquoted empty token.
+ *
+ * When set, this per-write option overrides the SQL config
+ * `spark.sql.legacy.nullValueWrittenAsQuotedEmptyStringCsv`. This lets a single write
+ * differentiate null from an actual empty string without changing the session-level default.
+ * When unset (including an explicit `null` value), the SQL config decides the behavior.
+ *
+ * Note this only takes effect when `nullValue` is left at its default (empty string): a
+ * non-empty `nullValue` is always written verbatim, so this option has no observable effect,
+ * matching how the SQL config it overrides already composes with `nullValue`.
+ */
+ val treatNullAsEmptyString: Option[Boolean] =
+ parameters.get(TREAT_NULL_AS_EMPTY_STRING) match {
+ case None | Some(null) => None
+ case Some(_) => Some(getBool(TREAT_NULL_AS_EMPTY_STRING))
+ }
+
/**
* A string between two consecutive JSON records.
*/
@@ -428,6 +447,7 @@ object CSVOptions extends DataSourceOptions {
val INPUT_BUFFER_SIZE = newOption("inputBufferSize")
val COLUMN_NAME_OF_CORRUPT_RECORD = newOption(DataSourceOptions.COLUMN_NAME_OF_CORRUPT_RECORD)
val NULL_VALUE = newOption("nullValue")
+ val TREAT_NULL_AS_EMPTY_STRING = newOption("treatNullAsEmptyString")
val NAN_VALUE = newOption("nanValue")
val POSITIVE_INF = newOption("positiveInf")
val NEGATIVE_INF = newOption("negativeInf")
diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/csv/UnivocityGenerator.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/csv/UnivocityGenerator.scala
index 1b5c32459942..4485494c11f2 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/csv/UnivocityGenerator.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/csv/UnivocityGenerator.scala
@@ -66,8 +66,13 @@ class UnivocityGenerator(
case TimeFormatter.defaultPattern => TimeFormatter.getFractionFormatter()
case customPattern => TimeFormatter(customPattern, isParsing = false)
}
- private val nullAsQuotedEmptyString =
- SQLConf.get.getConf(SQLConf.LEGACY_NULL_VALUE_WRITTEN_AS_QUOTED_EMPTY_STRING_CSV)
+ // The `treatNullAsEmptyString` write option, when set, overrides the session-level
+ // `spark.sql.legacy.nullValueWrittenAsQuotedEmptyStringCsv` config. This lets a single write
+ // choose whether a null is written as an empty string (a quoted empty string `""` with the
+ // default `emptyValue`) or as a bare, unquoted empty token, without changing the session
+ // default.
+ private val nullAsQuotedEmptyString = options.treatNullAsEmptyString.getOrElse(
+ SQLConf.get.getConf(SQLConf.LEGACY_NULL_VALUE_WRITTEN_AS_QUOTED_EMPTY_STRING_CSV))
private val binaryFormatter = ToStringBase.getBinaryFormatter
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/csv/CSVSuite.scala b/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/csv/CSVSuite.scala
index 0d0249fbad54..c698b351718e 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/csv/CSVSuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/csv/CSVSuite.scala
@@ -868,6 +868,80 @@ abstract class CSVSuite
}
}
+ test("treatNullAsEmptyString write option wins over " +
+ "spark.sql.legacy.nullValueWrittenAsQuotedEmptyStringCsv for both values") {
+ // The written bytes are what a downstream consumer relies on to tell null from an actual
+ // empty string. Note Spark's own default CSV reader cannot distinguish them: it maps both a
+ // bare token and a quoted "" back to null (nullSafeDatum treats datum == nullValue ("") as
+ // null), so we assert on the raw file text rather than a read round trip.
+ // The option must win regardless of the session-level legacy config, so a single write can
+ // differentiate null from an actual empty string without changing the session default.
+ Seq("true", "false").foreach { confVal =>
+ withSQLConf(SQLConf.LEGACY_NULL_VALUE_WRITTEN_AS_QUOTED_EMPTY_STRING_CSV.key -> confVal) {
+ // treatNullAsEmptyString = false: null is a bare unquoted token, empty string stays "".
+ withTempPath { path =>
+ Seq(("Tesla", null: String, ""))
+ .toDF("make", "comment", "blank")
+ .write
+ .option("treatNullAsEmptyString", "false")
+ .csv(path.getCanonicalPath)
+ checkAnswer(spark.read.text(path.getCanonicalPath), Row("Tesla,,\"\""))
+ }
+ // treatNullAsEmptyString = true: null is written as the (quoted) empty value, so it is
+ // no longer distinguishable from an actual empty string.
+ withTempPath { path =>
+ Seq(("Tesla", null: String, ""))
+ .toDF("make", "comment", "blank")
+ .write
+ .option("treatNullAsEmptyString", "true")
+ .csv(path.getCanonicalPath)
+ checkAnswer(spark.read.text(path.getCanonicalPath), Row("Tesla,\"\",\"\""))
+ }
+ }
+ }
+ }
+
+ test("treatNullAsEmptyString is a no-op when a non-empty nullValue is set") {
+ // A non-empty nullValue is written verbatim, so the option has no observable effect,
+ // matching how the SQL config it overrides composes with nullValue.
+ Seq("true", "false").foreach { optVal =>
+ withTempPath { path =>
+ Seq(("Tesla", null: String, ""))
+ .toDF("make", "comment", "blank")
+ .write
+ .option("nullValue", "NULL")
+ .option("treatNullAsEmptyString", optVal)
+ .csv(path.getCanonicalPath)
+ checkAnswer(spark.read.text(path.getCanonicalPath), Row("Tesla,NULL,\"\""))
+ }
+ }
+ }
+
+ test("treatNullAsEmptyString handles null and invalid values") {
+ // An explicit null value behaves as if the option were absent (session config decides).
+ withTempPath { path =>
+ Seq(("Tesla", null: String, ""))
+ .toDF("make", "comment", "blank")
+ .write
+ .option("treatNullAsEmptyString", null)
+ .csv(path.getCanonicalPath)
+ checkAnswer(spark.read.text(path.getCanonicalPath), Row("Tesla,,\"\""))
+ }
+ // A non-boolean value raises a structured error naming the option.
+ checkError(
+ exception = intercept[SparkException] {
+ withTempPath { path =>
+ Seq(("Tesla", null: String, ""))
+ .toDF("make", "comment", "blank")
+ .write
+ .option("treatNullAsEmptyString", "yes")
+ .csv(path.getCanonicalPath)
+ }
+ },
+ condition = "_LEGACY_ERROR_TEMP_2147",
+ parameters = Map("paramName" -> "treatNullAsEmptyString"))
+ }
+
test("save csv with compression codec option") {
withTempDir { dir =>
val csvDir = new File(dir, "csv").getCanonicalPath
@@ -3342,7 +3416,7 @@ abstract class CSVSuite
}
test("validate CSV Options") {
- assert(CSVOptions.getAllOptions.size == 43)
+ assert(CSVOptions.getAllOptions.size == 44)
// Please add validation on any new CSV options here
assert(CSVOptions.isValidOption("header"))
assert(CSVOptions.isValidOption("inferSchema"))
@@ -3372,6 +3446,7 @@ abstract class CSVSuite
assert(CSVOptions.isValidOption("inputBufferSize"))
assert(CSVOptions.isValidOption("columnNameOfCorruptRecord"))
assert(CSVOptions.isValidOption("nullValue"))
+ assert(CSVOptions.isValidOption("treatNullAsEmptyString"))
assert(CSVOptions.isValidOption("nanValue"))
assert(CSVOptions.isValidOption("positiveInf"))
assert(CSVOptions.isValidOption("negativeInf"))
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org