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程序應(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