Hive Order by Vs Sort by

Today we will discuss about how and where we can use Order by and Sort by clause in Hive

ORDER BY:

è Forces all the data to go into the same reducer node, by doing this, Order by ensure that entire dataset is totally ordered

è Uses a single reducer to guarantee total order in output

Drawbacks:

è Single reducer will take a long time to sort very large outputs

Sort By:

è Sort the rows based on the given columns per reducer. If there are more than one reducer, then the output per reducer will be sorted

Drawbacks:

If we have more than one reducer, then order of total output is not guaranteed to be sorted.

Let’s take one simple example. Currently Dept. table has following data

First will try to run the Order by query by setting reducer count as 2

If you see above screenshot all the data got sorted based on deptno column in Ascending order.

Now will try to run Sort by command.

We can clearly see that individual reducer level results are sorted but not at complete data set level.

However, sometimes we do not require total ordering. For example, suppose you have a table called user_action_table where each row has user_id, action, and time. Your goal is to order them by time per user_id and in this situation, we can use Sort By clause