spark 2.0 read csv with json

user_4039014 0
Viewed 5222

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?

apache-spark csv scala
2 Answers

I'm on Spark 1.6 and using Spark CSV as an external JAR but this works for me:

sqlContext.read.format("com.databricks.spark.csv")
          .option("quoteMode", "ALL")
          .option("delimiter", ",")
          .load("file")
          .show

+---+---+---+-------------------+
| C0| C1| C2|                 C3|
+---+---+---+-------------------+
|  a|  b|  c|{"x":"xx","y":"yy"}|
+---+---+---+-------------------+

EDIT : Looks like Spark CSV is intelligent enough

sc.textFile("file").collect
res7: Array[String] = Array(a,b,c,"{""x"":""xx"",""y"":""yy""}")

scala> sqlContext.read.format("com.databricks.spark.csv").load("file").show
+---+---+---+-------------------+
| C0| C1| C2|                 C3|
+---+---+---+-------------------+
|  a|  b|  c|{"x":"xx","y":"yy"}|
+---+---+---+-------------------+


scala> sqlContext.read.format("com.databricks.spark.csv").option("quoteMode", "ALL").load("file").show
+---+---+---+-------------------+
| C0| C1| C2|                 C3|
+---+---+---+-------------------+
|  a|  b|  c|{"x":"xx","y":"yy"}|
+---+---+---+-------------------+
user_72245970
Related