Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to export data from Spark SQL to CSV

This command works with HiveQL:

insert overwrite directory '/data/home.csv' select * from testtable; 

But with Spark SQL I'm getting an error with an org.apache.spark.sql.hive.HiveQl stack trace:

java.lang.RuntimeException: Unsupported language features in query:     insert overwrite directory '/data/home.csv' select * from testtable 

Please guide me to write export to CSV feature in Spark SQL.

like image 808
shashankS Avatar asked Aug 11 '15 09:08

shashankS


People also ask

How do I save a Spark file to CSV?

In Spark, you can save (write/extract) a DataFrame to a CSV file on disk by using dataframeObj. write. csv("path") , using this you can also write DataFrame to AWS S3, Azure Blob, HDFS, or any Spark supported file systems.

Does Spark support CSV?

Spark SQL provides spark. read(). csv("file_name") to read a file or directory of files in CSV format into Spark DataFrame, and dataframe.


2 Answers

You can use below statement to write the contents of dataframe in CSV format df.write.csv("/data/home/csv")

If you need to write the whole dataframe into a single CSV file, then use df.coalesce(1).write.csv("/data/home/sample.csv")

For spark 1.x, you can use spark-csv to write the results into CSV files

Below scala snippet would help

import org.apache.spark.sql.hive.HiveContext // sc - existing spark context val sqlContext = new HiveContext(sc) val df = sqlContext.sql("SELECT * FROM testtable") df.write.format("com.databricks.spark.csv").save("/data/home/csv") 

To write the contents into a single file

import org.apache.spark.sql.hive.HiveContext // sc - existing spark context val sqlContext = new HiveContext(sc) val df = sqlContext.sql("SELECT * FROM testtable") df.coalesce(1).write.format("com.databricks.spark.csv").save("/data/home/sample.csv") 
like image 122
sag Avatar answered Oct 07 '22 15:10

sag


Since Spark 2.X spark-csv is integrated as native datasource. Therefore, the necessary statement simplifies to (windows)

df.write   .option("header", "true")   .csv("file:///C:/out.csv") 

or UNIX

df.write   .option("header", "true")   .csv("/var/out.csv") 

Notice: as the comments say, it is creating the directory by that name with the partitions in it, not a standard CSV file. This, however, is most likely what you want since otherwise your either crashing your driver (out of RAM) or you could be working with a non distributed environment.

like image 20
Boern Avatar answered Oct 07 '22 15:10

Boern