ORDER BY:ORDER BY is a global sort and is affected by Hive.mapred.mode. There are some limitations to using the:
1, in strict mode (HIVE.MAPRED.MODE=STRICT), must be used in conjunction with limit (? ）。
Cause: When executing the order BY, Hive uses a reducer, and if the query results are large, this reducer can be very laborious to execute, so you have to limit the number of query output results. After limit n, the data processed by Reducer has n * COUNT (map) data. 2, in the non-strict mode (Default, Hive.mapred.mode=nonstrict), can not be used with limit. Related settings: Set hive.mapred.mode=strict; Set hive.mapred.mode=nonstrict; Example:Select ID, name from the user order by name limit;
Sort by:to sort the data in each reducer,If more than one reducer executes the query, the queried data is partially sorted.limit n can also be used to reduce the amount of data processed by reducer. Related settings:set mapred.reduce.tasks=n;
ExampleHive uses 2 reducer to execute the query select col1, col2 from T1 sort by col1 ASC, col2 desc, then:
the output of a reducer is:
05
033691the output from another reducer is:04
031115Description: Sorts the data in the same reducer. Distribute by:when Hive executes a query, the partition results in Mr are partitioned according to Distributeby fields so that the same field data is processed by the same reducer. Related settings:
set mapred.reduce.tasks=n;
Examplethe data for a column is as follows:
X1
X2
X3
X4
X1
after this field is Distributeby,
the data in the Reducer1 are:
X1
X2
X1
the data in the Reducer2 are:
X3
X4
Note: The same value (x1) is handled by the same reducer, but the data is unordered. Cluster By:clusterby is a shortcut to Distributeby and sortby, partitioned and sorted according to the Clusterby field.
Example
the data for a column is as follows:
X1
X2
X3
X4
X1
after this field is Distributeby,
the data in the Reducer1 are:
X1
X1
X2
the data in the Reducer2 are:
X3
X4
Note: The same value (x1) is handled by the same reducer, and the data is ordered.
Hive sort order by sort by distribute by cluster
