topic Re: data.write.format('com.databricks.spark.csv') added additional quotation marks in Data Engineering

data.write.format('com.databricks.spark.csv') added additional quotation marks

WenLin — Mon, 06 Jun 2016 18:40:17 GMT

0favorite

I am using the following code (pyspark) to export my data frame to csv:

data.write.format('com.databricks.spark.csv').options(delimiter="\t", codec="org.apache.hadoop.io.compress.GzipCodec").save('s3a://myBucket/myPath')

Note that I use

delimiter="\t"

, as I don't want to add additional quotation marks around each field. However, when I checked the output csv file, there are still some fields which are enclosed by quotation marks. e.g.

abcdABCDAAbbcd ....

1234_3456ABCD  ...

"-12345678AbCd"...

It seems that the quotation mark appears when the leading character of a field is "-". Why is this happening and is there a way to avoid this? Thanks!

Re: data.write.format('com.databricks.spark.csv') added additional quotation marks

WenLin — Mon, 06 Jun 2016 22:45:28 GMT

I also tried to use quoteMode = "NONE" , but doesn't work either

Re: data.write.format('com.databricks.spark.csv') added additional quotation marks

SiddSingal — Thu, 09 Jun 2016 04:40:29 GMT

Could you possible supply some Python code that creates a small DataFrame that demonstrates this behavior?

Re: data.write.format('com.databricks.spark.csv') added additional quotation marks

chaotic3quilibr — Fri, 31 Mar 2017 00:37:47 GMT

The way to turn off the default escaping of the double quote character (") with the backslash character (\) - i.e. to avoid escaping for all characters entirely, 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 as it is emitting the content. 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 assuming it won't ever occur within the document).

Here's Scala code achieving the effect. The second to last line (ending with magic is happening here) is the critical line and looks exactly the same in Python (as it does here in Scala).

val dataFrame =
  spark.sql("SELECT * FROM some_table_with_odd_characters_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".