topic Re: How to prevent spark-csv from adding quotes to JSON string in dataframe in Data Engineering

How to prevent spark-csv from adding quotes to JSON string in dataframe

mlm — Mon, 02 Nov 2015 18:43:25 GMT

I have a sql dataframe with a column that has a json string in it (e.g. {"key":"value"}). When I use spark-csv to save the dataframe it changes the field values to be "{""key"":""valule""}". Is there a way to turn that off?

Re: How to prevent spark-csv from adding quotes to JSON string in dataframe

PohlPosition — Fri, 06 Nov 2015 18:25:37 GMT

Try creating a custom schema that represents that column as a JSONObject and applying that schema when you create the DataFrame

Re: How to prevent spark-csv from adding quotes to JSON string in dataframe

vida — Fri, 06 Nov 2015 18:29:25 GMT

I was able to turn that off by setting the quote option to be a single white space. The problem with this is I am not sure how you can espace strings can contain your delimiter - "," - or whatever you set that too. If you are sure none of your strings have the delimiting character, you should be fine.

(df
  .repartition(1)
  .write
  .format("com.databricks.spark.csv")
  .option("header", "true")
  .option("quote", " ")
  .save("/FileStore/test"))

Re: How to prevent spark-csv from adding quotes to JSON string in dataframe

chaotic3quilibr — Thu, 30 Mar 2017 22:58:21 GMT

Yes. The way to turn off the default escaping of the double quote character (") with the backslash character (\), you must add an .option() method call with just the right parameters after the .write() method call. The goal of the option() method call is to change how the csv() method "finds" instances of the "quote" character. To do this, you must change the default of what a "quote" actually means; i.e. change the character sought from being a double quote character (") to a Unicode "\u0000" character (essentially providing the Unicode NUL character which won't ever occur within a well formed JSON document).

val dataFrame =
  spark.sql("SELECT * FROM some_table_with_a_json_column")
val unitEmitCsv =
  dataframe
    .write
    .option("header", true)
    .option("quote", "\u0000") //magic is happening here
    .csv("/FileStore/temp.csv")

This was only one of several lessons I learned attempting to work with Apache Spark and emitting .csv files. For more information and context on this, please see the blog post I wrote titled "Example Apache Spark ETL Pipeline Integrating a SaaS".

Re: How to prevent spark-csv from adding quotes to JSON string in dataframe

chaotic3quilibr — Thu, 30 Mar 2017 23:00:36 GMT

Your answer is actually not only incorrect, it causes the JSON content to become corrupt. So, while it might have solved a highly specific problem you had at the time you were doing this, it isn't a general solution. I have come up with a general solution which I cover in my own answer to this question.

Re: How to prevent spark-csv from adding quotes to JSON string in dataframe

AshleyPan — Thu, 14 Jun 2018 18:11:44 GMT

Do quote or escape options only work with "Write" instead of "read"? Our source files contain doube quotes. We'd like to add backsplash (escape) in front each double quote before converting the values from out dataframes to json outputs.