apache hive - HiveQL Select Where - hive tutorial - hadoop hive - hadoop hive - hiveql
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql
What is HiveQL ?
- Hive is a datawarehouseing infrastructure for Hadoop. The primary responsibility is to provide data summarization, query and analysis.
- The best part of HIVE is that it supports SQL-Like access to structured data which is known as HiveQL (or HQL) as well as big data analysis with the help of MapReduce.
learn hive - hive tutorial - apache hive mapreduce hadoop - hive examples
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql
Mysql Vs Hive :
learn hive - hive tutorial - mysql vs hiveql - hive programs - hive examples
learn hive - hive tutorial - mysql vs hiveql - hive programs - hive examples
What is Select-Where in HiveQL ?
- Here, We explains how to use the SELECT statement with WHERE clause.
- SELECT statement is used to retrieve the data from a table. WHERE clause works similar to a condition.
- It filters the data using the condition and gives you a finite result.
- The built-in operators and functions generate an expression, which fulfils the condition.
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql
WHERE Clauses:
- A WHERE clause is used to filter the result set by using predicate operators and logical operators. Functions can also be used to compute the condition.
- List of Predicate Operators
- List of Logical Operators
- List of Functions
- Here’s an example query that uses a WHERE clause.
SELECT name FROM products WHERE name = 'stone of jordan';
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
Syntax:
- Given below is the syntax of the SELECT query:
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];
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
Example:
- Let us take an example for SELECT…WHERE clause.
- Assume we have the employee table as given below, with fields named Id, Name, Salary, Designation, and Dept.
- Generate a query to retrieve the employee details who earn a salary of more than Rs 30000.
| ID | Name | Salary | Designation | Dept |
|---|---|---|---|---|
| 1201 | Aarthi | 45000 | Technical manager | TP |
| 1202 | Boomi | 45000 | Proofreader | PR |
| 1203 | Dharsanya | 40000 | Technical writer | TP |
| 1204 | Harikka | 40000 | Hr Admin | HR |
| 1205 | Arun | 30000 | Op Admin | Admin |
- The following query retrieves the employee details using the above scenario:
hive> SELECT * FROM employee WHERE salary>30000;
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
- On successful execution of the query, you get to see the following response:
| ID | Name | Salary | Designation | Dept |
|---|---|---|---|---|
| 1201 | Aarthi | 45000 | Technical manager | TP |
| 1202 | Boomi | 45000 | Proofreader | PR |
| 1203 | Dharsanya | 40000 | Technical writer | TP |
| 1204 | Harikka | 40000 | Hr Admin | HR |
Hive JDBC Program:
learn hive - hive tutorial - apache hive - hive jdbc driver - hive examples
The JDBC program to apply where clause for the given example is as follows.
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;
public class WikitechyWhere {
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();
}
}
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
- Save the program in a file named WikitechyWhere.java
- Use the following commands to compile and execute this program.
$ javac WikitechyWhere.java
$ java WikitechyWhere
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
Output:
| ID | Name | Salary | Designation | Dept |
|---|---|---|---|---|
| 1201 | Aarthi | 45000 | Technical manager | TP |
| 1202 | Boomi | 45000 | Proofreader | PR |
| 1203 | Dharsanya | 40000 | Technical writer | TP |
| 1204 | Harikka | 40000 | Hr Admin | HR |
Filtering data using HIVEQL language :
SELECT * FROM status_updates WHERE status LIKE ‘michael jackson’
learn hive - hive tutorial - apache hive - hiveql select statement - hive examples
Figure out total number of status_updates in a given day---------------SELECT COUNT(1) FROM status_updates WHERE ds = ’2009-08-01’
learn hive - hive tutorial - apache hive - hiveql select statement where condition - hive examples
-
FROM (SELECT a.status, b.school, b.gender
FROM status_updates a JOIN profiles b
ON (a.userid = b.userid and
a.ds='2009-03-20' )
) subq1
INSERT OVERWRITE TABLE gender_summary
PARTITION(ds='2009-03-20')
SELECT subq1.gender, COUNT(1)
GROUP BY subq1.gender
INSERT OVERWRITE TABLE school_summary
PARTITION(ds='2009-03-20')
SELECT subq1.school, COUNT(1)
GROUP BY subq1.school
learn hive - hive tutorial - apache hive - hiveql joins and merge and group by statement - hive examples