一：order by
order by会对输入做全局排序，因此只有一个Reducer(多个Reducer无法保证全局有序)，然而只有一个Reducer，会导致当输入规模较大时，消耗较长的计算时间。关于order by的详细介绍请参考这篇文章：Hive Order by操作。
二：sort by
sort by不是全局排序，其在数据进入reducer前完成排序，因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只会保证每个reducer的输出有序，并不保证全局有序。sort by不同于order by，它不受Hive.mapred.mode属性的影响，sort by的数据只能保证在同一个reduce中的数据可以按指定字段排序。使用sort by你可以指定执行的reduce个数(通过set mapred.reduce.tasks=n来指定)，对输出的数据再执行归并排序，即可得到全部结果。
语法
下面给出的是ORDER BY子句的语法：
SELECT [ALL | DISTINCT] select_expr, select_expr, ... FROM table_reference [WHERE where_condition] [GROUP BY col_list] [HAVING having_condition] [ORDER BY col_list]] [LIMIT number];
示例
让我们举个SELECT ... ORDER BY子句的例子。假设员工表，如下Id, Name, Salary, Designation, 和 Dept 的字段，生成一个查询用于检索员工的详细信息。
+------+--------------+-------------+-------------------+--------+ | ID | Name | Salary | Designation | Dept | +------+--------------+-------------+-------------------+--------+ |1201 | Gopal | 45000 | Technical manager | TP | |1202 | Manisha | 45000 | Proofreader | PR | |1203 | Masthanvali | 40000 | Technical writer | TP | |1204 | Krian | 40000 | Hr Admin | HR | |1205 | Kranthi | 30000 | Op Admin | Admin | +------+--------------+-------------+-------------------+--------+
下面是使用上述业务情景查询检索员工详细信息：
hive> SELECT Id, Name, Dept FROM employee ORDER BY DEPT;
查询结果
+------+--------------+-------------+-------------------+--------+ | ID | Name | Salary | Designation | Dept | +------+--------------+-------------+-------------------+--------+ |1205 | Kranthi | 30000 | Op Admin | Admin | |1204 | Krian | 40000 | Hr Admin | HR | |1202 | Manisha | 45000 | Proofreader | PR | |1201 | Gopal | 45000 | Technical manager | TP | |1203 | Masthanvali | 40000 | Technical writer | TP | +------+--------------+-------------+-------------------+--------+
JDBC 程序
下面是JDBC程序应用给定Order By子句的例子。
import java.sql.SQLException;import java.sql.Connection;import java.sql.ResultSet;import java.sql.Statement;import java.sql.DriverManager;public class HiveQLOrderBy {
private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";
public static void main(String[] args) throws SQLException {
// Register driver and create driver instance
Class.forName(driverName);
// get connection
Connection con = DriverManager.getConnection("jdbc:hive://localhost:10000/userdb", "", "");
// create statement
Statement stmt = con.createStatement();
// execute statement
Resultset res = stmt.executeQuery("SELECT * FROM employee ORDER BY DEPT;");
System.out.println(" ID \t Name \t Salary \t Designation \t Dept ");
while (res.next()) {
System.out.println(res.getInt(1) + " " + res.getString(2) + " " + res.getDouble(3) + " " + res.getString(4) + " " + res.getString(5));
}
con.close();
}}输出
ID Name Salary Designation Dept 1205 Kranthi 30000 Op Admin Admin 1204 Krian 40000 Hr Admin HR 1202 Manisha 45000 Proofreader PR 1201 Gopal 45000 Technical manager TP 1203 Masthanvali 40000 Technical writer TP 1204 Krian 40000 Hr Admin HR
#sort by
实例
hive (hive)> select * from user; OK id name 1 lavimer 2 liaozhongmin 3 liaozemin
使用sort by按id降序排列：
hive (hive)> select * from user sort by id desc; //MapReduce... Execution completed successfully Mapred Local Task Succeeded . Convert the Join into MapJoin OK id name 3 liaozemin 2 liaozhongmin 1 lavimer Time taken: 3.828 seconds
感觉本站内容不错，读后有收获？小额赞助,鼓励网站分享出更好的教程
上一篇：activity生命周期总结
下一篇：hive sql之group by
^