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