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

在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 {

  // 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();

}
}

保存程序在一個名爲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