HiveQL Select-Where语句

Hive查询语言（HiveQL）是用于处理和分析Metastore中的结构化数据的查询语言。本章解释了如何使用SELECT语句和WHERE子句。

SELECT语句用于从表中检索数据。WHERE子句类似于一个条件。它使用条件过滤数据并给出有限的结果。内置运算符和函数生成一个表达式，满足条件。

阅读更多：Hive 教程

语法

下面是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。生成一个查询以检索工资超过30,000卢比的员工详细信息。

+------+--------------+-------------+-------------------+--------+
| 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 \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();
   }
}

将程序保存到名为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