Hive查询语言(HiveQL)是Hive的一种查询语言,用于处理和分析Metastore中的结构化数据.本章介绍如何将SELECT语句与WHERE子句一起使用.
SELECT语句用于从表中检索数据. WHERE子句的作用类似于条件.它使用条件过滤数据并为您提供有限的结果.内置的运算符和函数生成一个满足条件的表达式.
下面给出了SELECT查询的语法:/p>
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 | +------+--------------+-------------+-------------------+--------+
为给定示例应用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