java spark 读取csv,spark 2.0使用json读取csv

I have a CSV file that looks like:

"a","b","c","{""x"":""xx"",""y"":""yy""}"

When I use java CSV reader (au.com.bytecode.opencsv.CSVParser), it manages to parse the string when I indicate defaultEscapeChar = '\u0000'

When I tried to read it with spark 2.2 CSV reader, it failed and wasn't able to split it to 4 columns. This is what I tried:

val df = spark.read.format("csv")

.option("quoteMode","ALL")

.option("quote", "\u0000")

.load("s3://...")

I also tries it with option("escape", "\u0000")

but with no luck.

Which CSV options I need to choose in order to parse this file correctly?

解决方案

You actually were close, right option is option("escape", "\"")

so given recent spark version (2.2+ or maybe even earlier), snippet below

import org.apache.spark.sql.{Dataset, SparkSession}

object CsvJsonMain {

def main(args: Array[String]): Unit = {

val spark = SparkSession.builder().appName("CsvJsonExample").master("local").getOrCreate()

import spark.sqlContext.implicits._

val csvData: Dataset[String] = spark.sparkContext.parallelize(List(

"""

|"a","b","c","{""x"":""xx"",""y"":""yy""}"

""".stripMargin)).toDS()

val frame = spark.read.option("escape", "\"").csv(csvData)

frame.show()

}

}

would produce

+---+---+---+-------------------+

|_c0|_c1|_c2| _c3|

+---+---+---+-------------------+

| a| b| c|{"x":"xx","y":"yy"}|

+---+---+---+-------------------+

The reason why spark fails to parse such csv out-of-the box is that default escape value is '\' symbol as could be seen on the line 91 at CSVOptions and it's obviously wouldn't work with default json quotes escaping.

The underlying reason why it used to work before spark 2.0 with databricks-csv library is that underlying csv engine used to be commons-csv and escape character defaulted to null would allow library to detect json and it's way of escaping. Since 2.0 csv functionality is part of the spark itself and using uniVocity CSV parser which doesn't provide such "magic" but apparently is faster.

P.S. Don't forget to specify escaping when writing csv files, if you want to preserve json data as it is.

frame.write.option("quoteAll","true").option("escape", "\"").csv("csvFileName")