topic Re: How do I create a single CSV file from multiple partitions in Databricks / Spark? in Data Engineering

How do I create a single CSV file from multiple partitions in Databricks / Spark?

rlgarris — Wed, 02 Dec 2015 18:26:01 GMT

Using sparkcsv to write data to dbfs, which I plan to move to my laptop via standard s3 copy commands.

The default for spark csv is to write output into partitions. I can force it to a single partition, but would really like to know if there is a generic way to do this.

In a hadoop file system, I'd simply run something like

hadoop fs -getmerge /user/hadoop/dir1/ ./myoutput.txt

Any equivalent from within the databricks platform?

Re: How do I create a single CSV file from multiple partitions in Databricks / Spark?

rlgarris — Wed, 02 Dec 2015 18:26:21 GMT

If the data isn't more than a few GB then you can coalesce your dataset prior to writing it out.

Something like:

df.coalesce(1).write.format("com.databricks.spark.cvs").save("...path...")

then copy to a single file using a dbutils.fs command:

dbutils.fs.cp("...path...", "..path.. ..csv")

Re: How do I create a single CSV file from multiple partitions in Databricks / Spark?

rlgarris — Wed, 02 Dec 2015 18:26:37 GMT

Thanks Richard. That is useful for single files. I'll add it to our local docs. I ended up writing a shell script that downloads all parts and merges them locally, so that can remain an option for people with larger files.

Re: How do I create a single CSV file from multiple partitions in Databricks / Spark?

ChrisJohnson — Fri, 19 Aug 2016 00:34:47 GMT

Any tips if the data is more than a few GB? Obviously the concern is a call to coalesce will bring all data into drive memory.

Re: How do I create a single CSV file from multiple partitions in Databricks / Spark?

chaotic3quilibr — Fri, 31 Mar 2017 19:18:33 GMT

THIS IS TERRIBLE ADVICE. DO NOT USE the DataFrame methods of .coalesce(1) or .repartition(1) except for very small data sets. Instead, use the hdfs merge mechanism via FileUtils.copyMerge(). This solution on StackOverflow correctly identifies how to do this:

http://stackoverflow.com/a/41785085/501113

Re: How do I create a single CSV file from multiple partitions in Databricks / Spark?

chaotic3quilibr — Fri, 31 Mar 2017 19:21:21 GMT

Please look this StackOverflow answer for the most effective way to use the HDFS FileUtils.copyMerge() command:

http://stackoverflow.com/a/41785085/501113

Re: How do I create a single CSV file from multiple partitions in Databricks / Spark?

chaotic3quilibr — Fri, 31 Mar 2017 19:21:58 GMT

Please look this StackOverflow answer for the most effective way to use the HDFS FileUtils.copyMerge() command:

http://stackoverflow.com/a/41785085/501113

Re: How do I create a single CSV file from multiple partitions in Databricks / Spark?

JosiahYoder — Thu, 27 Jul 2017 20:10:18 GMT

If you can fit all the data into RAM on one worker (and thus can use .coalesce(1)), you can use dbfs to find and move the resulting CSV file:

val fileprefix= "/mnt/aws/path/file-prefix"

dataset .coalesce(1)

.write

//.mode("overwrite") // I usually don't use this, but you may want to.

.option("header", "true") .option("delimiter","\t") .csv(fileprefix+".tmp")

val partition_path = dbutils.fs.ls(fileprefix+".tmp/") .filter(file=>file.name.endsWith(".csv"))(0).path

dbutils.fs.cp(partition_path,fileprefix+".tab")

dbutils.fs.rm(fileprefix+".tmp",recurse=true)

If your file does not fit into RAM on the worker, you may want to consider chaoticequilibrium's suggestion to use FileUtils.copyMerge(). I have not done this, and don't yet know if is possible or not, e.g., on S3.

Sources:

Stack Overflow: Writing single CSV file

Re: How do I create a single CSV file from multiple partitions in Databricks / Spark?

JosiahYoder — Thu, 27 Jul 2017 20:11:25 GMT

I'm really miffed that my formatting of the code disappears when I commit the edit.

Re: How do I create a single CSV file from multiple partitions in Databricks / Spark?

JosiahYoder — Thu, 27 Jul 2017 20:14:38 GMT

See my embellishment of this answer, filling out the ...s in the "...path...":

Re: How do I create a single CSV file from multiple partitions in Databricks / Spark?

RandyBonnette — Wed, 03 Jul 2019 11:09:13 GMT

You need to set the recursive setting on the copy command. Matthew Gascoyne explained it in detail in one of his posts:

When trying to copy a folder from one location to another in Databricks you may write my paper tasks and run into the below message

Re: How do I create a single CSV file from multiple partitions in Databricks / Spark?

ChristianHomber — Tue, 21 Jan 2020 11:50:40 GMT

Without access to bash it would be highly appreciated if an option within databricks (e.g. via dbfsutils) existed.

Re: How do I create a single CSV file from multiple partitions in Databricks / Spark?

Rampatel5 — Fri, 25 Dec 2020 04:59:37 GMT

Is FileUtils.copyMerge() is supported in databricks in DBFS?