HiveQL Select語句與Where子句

Hive查詢語言(HiveQL)是一種查詢語言,Hive處理在Metastore分析結(jié)構(gòu)化數(shù)據(jù)。本章介紹了如何使用SELECT語句的WHERE子句。

SELECT語句用來從表中檢索的數(shù)據(jù)。 WHERE子句中的工作原理類似于一個(gè)條件。它使用這個(gè)條件過濾數(shù)據(jù)，并返回給出一個(gè)有限的結(jié)果。內(nèi)置運(yùn)算符和函數(shù)產(chǎn)生一個(gè)表達(dá)式，滿足以下條件。

語法

下面給出的是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];

示例

讓我們舉個(gè)例子SELECT ... WHERE子句。假設(shè)employee表有如下 Id, Name, Salary, Designation, 和 Dept等字段，生成一個(gè)查詢檢索超過30000薪水的員工詳細(xì)信息。

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

下面的查詢檢索使用上述業(yè)務(wù)情景的員工詳細(xì)信息：

hive> SELECT * FROM employee WHERE salary>30000;

成功執(zhí)行查詢后，能看到以下回應(yīng)：

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

在JDBC程序應(yīng)用，其中針對(duì)給定的例子如下子句。

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 {
   
      // Register driver and create driver instance
      Class.forName(driverName);
      
      // get connection
      Connection con = DriverManager.getConnection("jdbc:hive://localhost:10000/userdb", "", "");
      
      // create statement
      Statement stmt = con.createStatement();
      
      // execute statement
      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();
   }
}

保存程序在一個(gè)名為HiveQLWhere.java文件。使用下面的命令來編譯和執(zhí)行這個(gè)程序。

$ 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