What is the difference between sort and orderBy functions in Spark

Question

What is the difference between sort and orderBy spark DataFrame?

scala> zips.printSchema
root
 |-- _id: string (nullable = true)
 |-- city: string (nullable = true)
 |-- loc: array (nullable = true)
 |    |-- element: double (containsNull = true)
 |-- pop: long (nullable = true)
 |-- state: string (nullable = true)

Below commands produce same result:

zips.sort(desc("pop")).show
zips.orderBy(desc("pop")).show

Answer 1

OrderBy is just an alias for the sort function.

From the Spark documentation:

  /**
   * Returns a new Dataset sorted by the given expressions.
   * This is an alias of the `sort` function.
   *
   * @group typedrel
   * @since 2.0.0
   */
  @scala.annotation.varargs
  def orderBy(sortCol: String, sortCols: String*): Dataset[T] = sort(sortCol, sortCols : _*)

Answer 2

They are NOT the SAME.

The SORT BY clause is used to return the result rows sorted within each partition in the user specified order. When there is more than one partition SORT BY may return result that is partially ordered.

Reference :https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-sortby.html

The ORDER BY clause is used to return the result rows in a sorted manner in the user specified order. Unlike the SORT BY clause, this clause guarantees a total order in the output.

Reference : https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-orderby.html

Answer 3

sort() function sorts the output in each bucket by the given columns on the file system. It does not guaranty the order of output data. Whereas The orderBy() happens in two phase .

First inside each bucket using sortBy() then entire data has to be brought into a single executer for over all order in ascending order or descending order based on the specified column. It involves high shuffling and is a costly operation. But as

The sort() operation happen inside each an individual bucket and is a light weight operation.

Here is a example:

Preparing data

>>> listOfTuples = [(16,5000),(10,3000),(13,2600),(19,1800),(11,4000),(17,3100),(14,2500),(20,2000)]
>>> tupleRDD = sc.parallelize(listOfTuples,2)
>>> tupleDF = tupleRDD.toDF(["Id","Salary"])

The data looks like :

>>> tupleRDD.glom().collect()
[[(16, 5000), (10, 3000), (13, 2600), (19, 1800)], [(11, 4000), (17, 3100), (14, 2500), (20, 2000)]]
>>> tupleDF.show()
+---+------+
| Id|Salary|
+---+------+
| 16|  5000|
| 10|  3000|
| 13|  2600|
| 19|  1800|
| 11|  4000|
| 17|  3100|
| 14|  2500|
| 20|  2000|
+---+------+

Now the sort operation will be

>>> tupleDF.sort("id").show()
+---+------+
| Id|Salary|
+---+------+
| 10|  3000|
| 11|  4000|
| 13|  2600|
| 14|  2500|
| 16|  5000|
| 17|  3100|
| 19|  1800|
| 20|  2000|
+---+------+

See, the order is not as expected. Now if we see the orederBy operation :

>>> tupleDF.orderBy("id").show()
+---+------+
| Id|Salary|
+---+------+
| 10|  3000|
| 11|  4000|
| 13|  2600|
| 14|  2500|
| 16|  5000|
| 17|  3100|
| 19|  1800|
| 20|  2000|
+---+------+

It maintains the overall order of data.