HiveQL SELECT Where
Hive 查询语言 (HiveQL) 是 Hive 用于处理和分析 Metastore 中结构化数据的查询语言。本章说明如何使用带有 WHERE 子句的 SELECT 语句。
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 子句为例。假设我们有如下所示的员工表，其中包含名为 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.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
