So, this issue has been talked about quite a bit, but it was supposed to be fixed in Spark 2.2.0 which was just released. From what I can tell, the wholeLine option is supposed to fix this issue, however I am having no luck. The issue is that when there is a line break in a value, say like, a notes column that has a bunch of text in it, and there's a line break in those notes, Spark treats it as a new row.
Here is the snippet I'm using to read the data from the CSV.
sparkSession.read
.option("header", "true")
.option("delimiter", ",")
.option("multiLine", "true")
.option("inferSchema", true)
.option("wholeFile", "true")
.option("quoteMode", "ALL")
.option("parserLib", "univocity")
.csv(csvFilePath)
As you can tell, I'm using every option under the sun, but I'm still getting the wrong output. The output I get is below, those are all supposed to be unique integers, but they're being polluted with data from other columns, from another row. I've changed the data a little but you can see there is string data in the column with the IDs.
+--------------------+
| ID|
+--------------------+
| 3876849|
| 3876872|
| consistent|
| 4/30|
|,blah blah blah ...|
| 3876876|
| 3876878|
| 5-6 days ago"|
| 3876879|
| 3876880|
|blah blah blah bl...|
| 3876893|
|*blah blah blah ...|
| 3876900|
| 3876904|
+--------------------+
This has me really stumped, for good measure I'm including my build.sbt as well.
name := "MyApp"
organization := "com.test"
version := "1.0-SNAPSHOT"
scalaVersion := "2.11.8"
libraryDependencies ++= Seq(
"org.apache.spark" % "spark-core_2.11" % "2.2.0" % "provided",
"org.apache.spark" % "spark-sql_2.11" % "2.2.0" % "provided",
"org.apache.spark" % "spark-hive_2.11" % "2.2.0" % "provided",
"org.elasticsearch" % "elasticsearch-spark-20_2.11" % "5.5.0",
"org.apache.logging.log4j" % "log4j-core" %"2.8.2"
)
assemblyMergeStrategy in assembly := {
case PathList("META-INF", xs @ _*) => MergeStrategy.discard
case x => MergeStrategy.first
}
assemblyOption in assembly := (assemblyOption in assembly).value.copy(includeScala = false)