HiveQL - Select-Where

Hive 查询语言 (HiveQL) 是 Hive 用于处理和分析 Metastore 中的结构化数据的查询语言。本章介绍如何使用带有 WHERE 子句的 SELECT 语句。

SELECT 语句用于从表中检索数据。WHERE 子句的工作方式类似于条件。它使用条件过滤数据并为您提供有限的结果。内置运算符和函数生成满足条件的表达式。

语法

下面给出了 SELECT 查询的语法：

SELECT [ALL | DISTINCT] select_expr, select_expr, ... 
FROM table_reference 
[WHERE where_condition] 
[GROUP BY col_list] 
[HAVING having_condition] 
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]] 
[LIMIT number];

示例

让我们以 SELECT…WHERE 子句为例。假设我们有如下所示的员工表，其字段名为 Id、Name、Salary、Designation 和 Dept。生成查询以检索薪水超过 30000 卢比的员工详细信息。

+------+--------------+-------------+-------------------+--------+
| 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 * FROM employee WHERE salary>30000;

成功执行查询后，您将看到以下响应：

+------+--------------+-------------+-------------------+--------+
| 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     |
+------+--------------+-------------+-------------------+--------+

JDBC 程序

针对给定示例应用 where 子句的 JDBC 程序如下。

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveQLWhere {
   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 WHERE salary>30000;");
      
      System.out.println("Result:");
      System.out.println(" ID 	 Name 	 Salary 	 Designation 	 Dept ");
      
      while (res.next()) {
         System.out.println(res.getInt(1) + " " + res.getString(2) + " " + res.getDouble(3) + " " + res.getString(4) + " " + res.getString(5));
      }
      con.close();
   }
}

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

$ javac HiveQLWhere.java
$ java HiveQLWhere

输出：

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