What is the difference between order by, sort by, cluster by and distribute by in Hive?

Hive provides 3 options to order or sort the result of records – order by, sort by, cluster by and distribute by. Which option you choose has performance implications. So it is important to understand the difference between the options and choose the right one for the use case at hand.

ORDER BY <column-name>

Guarantees global ordering.

Only one reducer is used to perform this operation. 

Will not work for large datasets as you might end up with Out of Memory exceptions.

If successful, you will get a full sorted output across the dataset.

SORT BY <column-name>

If the column name is a stock symbol in a stocks dataset, multiple reducers can receive records for APPL symbol and you will find APPL symbol in multiple output files there by not ensuring global ordering but the contents in the output file from a reducer will be ordered.

Doesn’t give you global ordering

Content within each output file is sorted but doesn’t give you global ordering. 

Good for large datasets that do not require a global ordering.

CLUSTER BY <column-name>

If the column name is a stock symbol in a stocks dataset, multiple reducers can not receive APPL symbol and you will find APPL symbol in only output files from one reducer there by ensuring global ordering.

Gives you global ordering

Good for large datasets that require global ordering.

DISTRIBUTE BY <column-name> and SORT BY <column-name>

Same as CLUSTER BY