HiveQL Select-Order By子句

本章介绍如何在 SELECT 语句中使用 ORDER BY 子句。ORDER BY 子句用于基于一列检索详细信息，并按升序或降序对结果集进行排序。

阅读更多：Hive 教程

语法

下面给出 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 程序

以下是应用给定示例的 Order By 子句的 JDBC 程序。

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 {

      // 注册驱动并创建驱动实例
      Class.forName(driverName);

      // 获取连接
      Connection con = DriverManager.getConnection("jdbc:hive://localhost:10000/userdb", "", "");

      // 创建语句
      Statement stmt = con.createStatement();

      // 执行语句
      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();
   }
}

将程序保存为名为 HiveQLOrderBy.java 的文件。使用以下命令编译并执行程序。

$ javac HiveQLOrderBy.java
$ java HiveQLOrderBy

输出:

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