Hadoop Spark 1.4.1 - sort multiple CSV files and save sorted result in 1 output file

Question

I have 3 files in HDFS and would like to use most efficient way to sort them first on 1st column and then on 2nd column and store sorted result back to a new file on HDFS using Scala (or Python) in Spark 1.4.1:
hdfs:///test/2016/file.csv
hdfs:///test/2015/file.csv
hdfs:///test/2014/file.csv

Files look like this (no header):
hdfs:///test/2016/file.csv
127,56,abc
125,56,abc
121,56,abc

hdfs:///test/2016/file.csv
126,66,abc
122,56,abc
123,46,abc

hdfs:///test/2016/file.csv
122,66,abc
128,56,abc
123,16,abc

Sorted output want to save to HDFS:
hdfs:///test/output/file.csv
121,56,abc
122,56,abc
122,66,abc
123,16,abc
123,46,abc
125,56,abc
126,66,abc
127,56,abc
128,56,abc

I am very new to Spark and so far I only know how to load file:
val textFile = sc.textFile("hdfs:///test/2016/file.csv")

Tried to read on internet on how to sort but not clear on what libraries should work for this case (CSV files) and this version of Spark (1.4.1) and how to use them.. Please help, Joe

Answer 1

I'd suggest to use databricks csv library for reading and writing csvs: https://github.com/databricks/spark-csv

As I had no access to hdfs now, this example uses the filesystem, but should also work when used with an hdfs path.

import org.apache.spark.sql.SQLContext
import org.apache.spark.{SparkContext, SparkConf}
import org.apache.spark.sql.functions._  // needed for ordering the dataframe

object StackoverflowTest {

  def main(args: Array[String]) {
    // basic spark init
    val conf = new SparkConf().setAppName("Data Import from CSV")
    val sc = new SparkContext(conf)
    val sqlContext = new SQLContext(sc)

    // first we load every file from the data directory that starts with 'file'
    val storeDf = sqlContext.read
      .format("com.databricks.spark.csv")
      .option("inferSchema", "true")
      .load("data/file*")

    // then we sort it and write to an output
    storeDf
      .orderBy("C0", "C1")  // default column names
      .repartition(1)   // in order to have 1 output file
      .write
      .format("com.databricks.spark.csv")
      .save("data/output")
  }

}

The result will be written to data/output/part-00000 as csv. Hope this helps.

Answer 2

val textFile = sc.textFile("hdfs:///test/*/*.csv")
                 .map( _.split(",",-1) match { case Array(col1, col2, col3) => (col1, col2, col3) })
                 .sortBy(_._1)
                 .map(_._1+","+_._2+","+_._3)
                 .saveAsTextFile("hdfs:///testoutput/output/file.csv")

You will want to save in a different folder, otherwise the files you produce will be reused when you run it again.