[Feature] Add an option to skip empty lines when reading CSV #10773

Search before asking

• I searched in the issues and found nothing similar.

Motivation

The CSV reader turns every empty or whitespace-only line into a row where all columns are NULL, while Spark's built-in CSV source drops these lines. This makes a CSV Format Table return extra rows that a Spark CSV table over the same files does not return, which gets in the way of moving an existing Spark (Hive metastore) CSV table to a Paimon Format Table.

Current behavior in CsvParser#parse (master d95fd18b48):

// empty line results in all null values
if (isNullOrWhitespaceOnly(line) || projectMapping.length == 0) {
    return emptyRow;
}

Spark 3.5 drops these lines before parsing them, and no option turns that off (UnivocityParser.parseIterator -> CSVExprUtils.filterCommentAndEmpty):

iter.filter(_.trim.nonEmpty)

We hit this while moving tab-separated, gzip-compressed log tables from Spark CSV tables to Paimon CSV Format Tables (Spark 3.5, Paimon 2.0). In one hour of logs, some free-text values contain line breaks, so a few records are split across several physical lines and some of those lines contain only a space. Reading the same 3 files gives:

Apart from these 6 rows, all other rows have the same per-column hashes in both readers.

Why blank lines cannot simply be skipped

Paimon's own CsvFormatWriter writes a blank line in some cases, and today those lines are read back correctly. I checked this on master by writing rows with CsvFormatWriter and reading them back with the CSV reader:

So unconditionally skipping blank lines would silently drop rows from single-column tables written by Paimon. A table with 2 or more columns is not affected, because an all-NULL row is written with delimiters and is never blank.

Solution

Add an opt-in CSV option that is off by default:

csv.skip-empty-lines = false   (default; current behavior)

When it is true, CsvParser / CsvFileReader skip lines that are empty or contain only whitespace, the same way Spark does. The default stays as it is today, so existing tables and round-trips are not affected. Users moving tables from Spark can enable the option per table. A few details:

• Whitespace follows StringUtils#isNullOrWhitespaceOnly, which is what the parser already uses, so the option only changes what happens to lines that already become all-NULL rows today.
• The header line (csv.include-header) and splitting by offset/length are not affected. Skipping happens per line after the line reader.
• Docs: add the option to the CSV format options table.

Alternatives I considered:

1. Skip blank lines only when the schema has more than 1 column, with no new option. This avoids the single-column round-trip problem, but it still changes read results for existing multi-column tables that contain blank lines.
2. Change the default to match Spark. This breaks the single-column round-trip shown above.

I prefer the opt-in option, but I'd like to hear whether the committers prefer another approach before I start.

Anything else?

• The current behavior comes from [csv] Csv Parser should not depends on jackson #6508 (the Jackson-free CsvParser). With the older Jackson-based reader, blank lines were not skipped either, so Paimon has never matched Spark here.

Are you willing to submit a PR?

• I'm willing to submit a PR!