HiveQl_Part1

HiveQl_Part1

HiveQl_Part1

1.) 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. hive> select * from emp1 where code>500; 1281 Shawn Architect 7890 1481 10 IXZ 1381 Jacob Admin 4560 1481 20 POI 1481 flink Mgr 9580 1681 10 IXZ 1581 Richard Developer 1000 1681 40 LKJ 1681 Mira Mgr 5098 1481 10 IKZ 1781 John Developer 6500 1681 10 IXZ 2.) ORDER BY This chapter explains how to use the ORDER BY clause in a SELECT statement. The ORDER BY clause is used to retrieve the details based on one column and sort the result set by ascending or descending order. hive> select id,name from emp1 order by rank; OK 1781 John 1681 Mira 1481 flink 1281 Shawn 1381 Jacob 1581 Richard 3.) GROUP BY This chapter explains the details of GROUP BY clause in a SELECT statement. The GROUP BY clause is used to group all the records in a result set using a particular collection column. It is used to query a group of records. SELECT role,count(*) as count_role FROM emp1 GROUP BY role; Admin 1 Architect 1 Developer 2 Mgr 2 4.) HAVING we can Filter With Having In GroupBy SELECT role,count(*) as count_role FROM emp1 GROUP BY role having count_role > 1; Developer 2 Mgr 2