Pyspark Sql writing DF to text and preserve headers

user_8958754 0
Viewed 905

I have a spark sql dataframe, I want to write out to text, unit separated. I understand the process is to first concat_ws then coalesce(1) to a single cell df so it can be written like so.

outputDF = df.select(concat_ws('\x1f',*df.columns))
outputDF = outputDF.coalesce(1)

print('PRINTING PREVIEW')
outputDF.show(truncate=0)

outputDF.write.format("text").option("header","true").mode("overwrite").save(s3bucket_somewhere)

I have a show() step which, gives me the output that I expect, something like :

+----------------------------------------------------------------  
----------------------------------------------------------------  
----------------------------------------------------------------  
+
|concat_ws( , name, age,color)|
+----------------------------------------------------------------  
----------------------------------------------------------------  
----------------------------------------------------------------  
+
|adam 30 red | 
|bob 22 blue | 

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

only showing top 20 rows

But when viewing the file written to S3 it doesn't include the header.Even although I set it in the write options it seems to not be included. I have tried using alias during the concat_ws step but no luck.

pyspark apache-spark-sql apache-spark
1 Answers

Text file format does not support the use of headers. You can write a CSV file instead, which will give essentially the same file as the text file, only that the file extension is csv rather than txt.

e.g.

outputDF = df.select(concat_ws('\x1f',*df.columns))

outputDF.coalesce(1).write.format("csv").option("header","true").mode("overwrite").save(s3bucket_somewhere)

Or you can skip the concat_ws step and write to a csv directly.

e.g.

df.coalesce(1).write.format("csv").option("header","true").option("sep", "\x1f").mode("overwrite").save(s3bucket_somewhere)
user_141657300
Related