cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

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

mlm
New Contributor

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?

5 REPLIES 5

PohlPosition
Databricks Employee
Databricks Employee

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

vida
Databricks Employee
Databricks Employee

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"))

chaotic3quilibr
New Contributor III

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.

chaotic3quilibr
New Contributor III

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".

AshleyPan
New Contributor II

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.

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group