|
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薪水的员工详细信息。
下面给出的是SELECT查询的语法：
+------+--------------+-------------+-------------------+--------+
| 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 |
|
