Hive : SORT BY vs ORDER BY vs DISTRIBUTE BY vs CLUSTER BY

In Apache Hive, It’s always a matter of confusion that how SORT BY,  ORDER BY, DISTRIBUTE BY and CLUSTER BY differs. I have compiled a set of differences between these based on attributes like  how will final output look like and ordering of data in output –

SORT BY

Sort By vs Order By vs Group By vs Cluster By in Hive
Sort By vs Order By vs Group By vs Cluster By in Hive

Hive uses the columns in SORT BY to sort the rows before feeding the rows to a reducer. The sort order will be dependent on the column types. If the column is of numeric type, then the sort order is also in numeric order. If the column is of string type, then the sort order will be lexicographical order.

Ordering : It orders data at each of ‘N’ reducers , but each reducer can have overlapping ranges of data.

Outcome : N or more sorted files with overlapping ranges.

Let’s understand with an example :-

The query had 2 reducers, and the output of each is:

Reducer 1 :

0 5
0 3
3 6
9 1

Reducer 2 :

0 4
0 3
1 1
2 5

As, we can see, each reducer output is ordered but total ordering is missing , since we end up with multiple outputs per reducer.

ORDER BY

This is similar to ORDER BY in SQL Language.

In Hive, ORDER BY guarantees total ordering of data, but for that it has to be passed on to a single reducer, which is normally unacceptable and therefore in strict mode, hive makes it compulsory to use LIMIT with ORDER BY so that reducer doesn’t get overburdened.

Ordering : Total Ordered data.

Outcome : Single output i.e. fully ordered.

For example :

Reducer :

0  5
0  4
0  3
0  3
1   1
2   5
3   6
9   1

DISTRIBUTE BY

Hive uses the columns in Distribute By to distribute the rows among reducers. All rows with the same Distribute By columns will go to the same reducer.

It ensures each of N reducers gets non-overlapping ranges of column, but doesn’t sort the output of each reducer. You end up with N or more unsorted files with non-overlapping ranges.

Example ( taken directly from Hive wiki ):-

We are Distributing By x on the following 5 rows to 2 reducer:

x1
x2
x4
x3
x1

Reducer 1 got

x1
x2
x1

Reducer 2 got

x4
x3

Note that all rows with the same key x1 is guaranteed to be distributed to the same reducer (reducer 1 in this case), but they are not guaranteed to be clustered in adjacent positions.

CLUSTER BY

Cluster By is a short-cut for both Distribute By and Sort By.

CLUSTER BY x ensures each of N reducers gets non-overlapping ranges, then sorts by those ranges at the reducers.

Ordering : Global ordering between multiple reducers.

Outcome : N or more sorted files with non-overlapping ranges.

For the same example as above , if we use Cluster By x, the two reducers will further sort rows on x:

Reducer 1 got

x1
x1
x2

Reducer 2 got

x3
x4

Instead of specifying Cluster By, the user can specify Distribute By and Sort By, so the partition columns and sort columns can be different.

References : –

[1] http://stackoverflow.com/questions/13715044/hive-cluster-by-vs-order-by-vs-sort-by

[2] https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy#LanguageManualSortBy-SyntaxofOrderBy

You may also be interested in some other BigData posts –

 

 

  • Pingback: Hive Strict Mode - Saurz{Code}()

  • kratibadjatya

    Nice explanation..

  • David

    good article! :)

    btw can I ask something?

    what is the relation between these quries?

    1. create table x clustered by id sorted by id, time
    >> when does clustwred by sorted works.
    is it ok if not clustered sorted inserted because this will cluster and sort at query time, but this will take more time?

    2. insert into x select * from y cluster by id
    sort by id, time
    >> if I inserted clusterd and sorted data, The query 1 time will be shorten ?

    3. select * from x cluster by id
    sort by id , time

    really confusing. .
    ㅡㅡㅡㅡ