Hive Order by operation

Common advanced queries in hive include:group BY, Order by, join, distribute by, sort by, cluster by, Union all. today we look at the order by operation, and order by indicates that some fields are sorted by the following syntax:

[Java]View PlainCopy

1. Select Col,col2 ...
2. From TableName
3. Where condition
4. Order by Col1,col2 [Asc|desc]

Attention:

(1):order by can be sorted by more than one column, sorted by dictionary by default.

(2):order BY is a global sort.

(3):The order by requires the reduce operation, and only one reduce, cannot be configured (because multiple reduce cannot complete the global sort).

The order by operation is subject to the following attributes:

[Java]View PlainCopy

1. Set hive.mapred.mode=nonstrict; (default value/defaults)
3. Set hive.mapred.mode=strict;

Note: If you use the ORDER BY statement in strict mode, you must add the Limit keyword to the statement because only a single reduce can be started when you execute an order by, and the execution time can be lengthy if the ordered result set is too large.

Let's take a look at the use of order by in one example:

The database has a employees table with the following data:

[Java]View PlainCopy

2. Hive> SELECT * FROM Employees;
4. Ok
6. lavimer 15000.0 [ "Li", "K1": 1.0, "K2": 2.0, "K3": 3.0}    { "street": "Dingnan", "City": 101}  2015-01-24  love  
8. Liao    18000.0 [ "Liu", "K4": Span class= "number" >2.0, "K5": 3.0, "K6": 6.0}    { "Dingnan", "City": 102 } 2015-01-24  love  
10. Zhang 19000.0 ["Xiao","Wen","Tian"] {"K7":7.0,"K8":8.0,"K8": 8.0} {"St Reet ":" Dingnan "," City ":" Ganzhou "," num ":103} 2015-01-

Now I want to sort by the second column (salary) in descending order:

[Java]View PlainCopy

2. Hive> SELECT * FROM Employees ORDER BY salary Desc;
4. The process of executing the MapReduce
6. Job 0:map: 1 Reduce: 1 Cumulative CPU: 2.62 sec HDFs Read: 415 HDFS Write: 245 SUCCESS
8. Total MapReduce CPU time spent: 2 seconds 620 msec
10. Ok
12. Zhang   19000.0 [ "K7": 7.0, "K8": 8.0} { " Street ": " Dingnan ", " Ganzhou ", "num": 103} 2015-01-< Span class= "number" >24&NBSP;&NBSP;LOVE&NBSP;&NBSP;
14. liao    18000.0 [ "K4": 2.0, "K5": 3.0, "K6": 6.0}    { "street": "Dingnan" , 102} 2015-01-24  love   
16. Lavimer 15000.0 ["Li","Lu","Wang"] {"K1":1.0,"K2":2.0,"K3":3.0} {" Street ":" Dingnan "," City ":" Ganzhou "," num ":101} 2015-01-
18. Time taken: 20.484 seconds
20. Hive>

The Hive.mapred.mode property at this time is:

[Java]View PlainCopy

1. Hive> set Hive.mapred.mode;
2. Hive.mapred.mode=nonstrict
3. Hive>

Now let's change it to strict and then use order by to query:

[Java]View PlainCopy

1. Hive> set hive.mapred.mode=strict;
2. Hive> SELECT * FROM Employees ORDER BY salary Desc;
3. Failed:error in semantic Analysis: 1:at strict mode, if ORDER by was specified, LIMIT must also be specifie D. Error encountered near token ' salary '
4. Hive>

Note: Queries must be prefixed with the Limit keyword in strict mode.

[Java]View PlainCopy

1. Hive> SELECT * FROM Employees ORDER BY salary desc limit 3;
2. Failed:error in semantic analysis:no partition predicate found for Alias "Employees" Table "Employees"

Note: Another thing to note is that the strict mode also restricts the query for partitioned tables, and the solution is to specify the partition

First take a look at the partition:

[Java]View PlainCopy

1. Hive> Show partitions employees;
2. Ok
3. Date_time=2015-01-24/type=love
4. Time taken: 0.096 seconds

In strict mode, use the ORDER by query first:

[Java]View PlainCopy

2. Hive> SELECT * FROM Employees where partition (date_time=' 2015-01-24 ', type=' love ') Order by salary desc limit 3;
4. Failed:parse error:line 1:cannot recognize input near ' partition ' (" date_time ' in expression s Pecification
8. hive                                                                                                                      
10. > select * FROM Employees where date_time=' 2015-01-24 ' and type=' love ' ORDER by salary desc limit 3;
14. Execute a mapreduce program
18. Total MapReduce CPU time spent: 3 seconds 510 msec
20. Ok
22. zhang   19000.0 [ "Xiao", "K7": 7.0, "K8": 8.0} { " Street ": " Dingnan ", " Ganzhou ", "num": 103} 2015-01-< Span class= "number" >24&NBSP;&NBSP;LOVE&NBSP;&NBSP;
24. Liao    18000.0 [ "Liu", "K4": Span class= "number" >2.0, "K5": 3.0, "K6": 6.0}    { "Dingnan", "City": 102 } 2015-01-24  love  
26. Lavimer 15000.0 ["Li","Lu","Wang"] {"K1":1.0,"K2":2.0,"K3":3.0} {" Street ":" Dingnan "," City ":" Ganzhou "," num ":101} 2015-01-
28. Time taken: 19.861 seconds
30. Hive>

Hive Order by operation