HiveQL Select-Where

Hive Select-Where

Hive查询语言（HiveQL）是Hive处理和分析Metastore中结构化数据的查询语言。本章说明如何将SELECT语句与WHERE子句一起使用。

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子句为例。假设我们有如下所示的employee表，其中的字段名为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.hive.jdbc.HiveDriver";
   
   public static void main(String[] args) throws SQLException {
      try {
         // Register driver and create driver instance
         Class.forName(driverName);
         
         // get connection
         Connection con = DriverManager.getConnection("jdbc:hive2://localhost:10000/userdb", "", "");
         
         // create statement
         Statement stmt = con.createStatement();
         
         // execute statement
         Resultset res = stmt.execute("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();
      } catch (Exception e) {
         System.out.println(e.getMessage());
      }     
   }
}

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

HiveQL Select-Where

Hive Select-Where

句法