Hive Data Retrieval Queries: Difference between CLUSTER BY, ORDER BY, and SORT BY

In short, for your questions:

  1. Does CLUSTER BY guarantee a global order?
    • No.
  2. DISTRIBUTE BY puts the same keys into same reducers but what about the adjacent keys?

The longer version:

To understand Hive, first you must understand Map Reduce. The relevant property of Map Reduce for this question is that when data arrives at the reducing stage, they would have always gone through shuffling and sorting on mapper or reducer before the reducing step. Then:

  • ORDER BY x: Data guarantees global ordering by x by using one reducer for the last reducing step. This will be slow for large outputs. It is however acceptable even for processing large datasets as long as you know at the last step the data size is small. (e.g. after filtering and aggregation)
  • SORT BY x: guarantees local ordering specifically by x, at each of N reducers. Global ordering is not guaranteed.
  • DISTRIBUTE BY x: guarantees that the rows of the same distribute key x goes to the same reducer but does NOT guarantees your desired ordering. Data at reducing stage is still sorted, but NOT necessarily by x.
  • CLUSTER BY x: This is the same as doing (DISTRIBUTE BY x and SORT BY x).

Please refer to the official wiki for detailed mechanism and syntax:

As mentioned in comments and other answers, previous answer is incorrect and the choice of words may add wrong nuances. The use of of the phrase overlapping-range is misleading since shuffling is done with a hash function, which is NOT necessarily by range. For instance depending on implementation hashing integers by even or odd number is valid, which is overlapping in terms of number range. DISTRIBUTE BY in this case only guarantees all even numbers goes to the same reducer, and all odd numbers goes to another.

The other answers have minor mistakes, or unrelated to the question, or too verbose.

Leave a Comment