HiveQL series is already the last part in Hive series, maybe after I finish HiveQL, I will do a few more articles about Hive to answer some questions and frequently asked questions about Hive, some tutorials and resources. useful …. more. Let’s look forward to it!
1. Select-Where
Hive Query Language – Hive Query Language (HiveQL) – is a query language for Hive to conduct and analyze structured data in a Metastore. This section explains how to use a SELECT statement with a WHERE clause. The SELECT statement is often used to retrieve data from a table. The WHERE clause works similarly to a condition. It filters data using conditions and returns you a limited result. Built-in operators and functions create an expression that satisfies the condition.
Syntax
Given below is the syntax of the SELECT query:
1 2 3 4 5 6 7 8 | 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]; |
Example
We will take an example of a SELECT … WHERE clause. Assume that we have employee table as below, together with fields: Id, Name, Salary, Designation, and Dept. Create a query to get details about employees who pay more than 3000Rs.
1 2 3 4 5 6 7 8 9 10 | +------+--------------+-------------+-------------------+--------+ | 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 | +------+--------------+-------------+-------------------+--------+ |
The following query retrieves the employee’s details using the following condition:
1 2 | hive> SELECT * FROM employee WHERE salary>30000; |
On successful execution of the query, you will receive the following response:
1 2 3 4 5 6 7 8 9 | +------+--------------+-------------+-------------------+--------+ | 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 Program JDBC Program to apply where clause to the given example as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | 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(); } } |
Save the program in a file called HiveQLWhere.java. Use the following command to compile and execute the program:
1 2 3 | $ javac HiveQLWhere.java $ java HiveQLWhere |
Output:
1 2 3 4 5 6 | 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 |
. The next topic will be SELECT … ORDER BY.