Shulou(Shulou.com)06/01 Report--

This article is about how Hive implements queries. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. Query

Official demonstration case: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select

1.1 complete syntax SELECT [ALL | DISTINCT] Field 1, Field 2, Field 3...--all is the default table is all checked out Distinct stands for de-repeating query (which column can be exact) FROM table_reference-from which table to look up [WHERE where_condition]-filter condition [GROUP BY col_list]-grouped with so-and-so fields (there can be multiple fields) [HAVING col_list]-filter some data after grouping [ORDER BY col_list]-global ranking Order [DISTRIBUTE BY col_list] [SORT BY col_list]-Partition, And sort [CLUSTER BY col_list]-- partition sort [LIMIT number]-- limit the number of rows output (page flip)

SQL execution order: from

1.3.The grouping 1.3.1, group byGROUP BY statements are usually used with aggregate functions, grouping according to one or more queue results, and then performing aggregation operations on each group. Select t.deptno, t.job, max (t.sal) max_sal from emp t group by t.deptno, t.job.Note: after using group by, the fields followed by select can only be followed by group by. 1.3.2. Having--having differs from where-(1) the grouping aggregate function cannot be written after where, while the grouping aggregate function can be used after having. -- (2) having is only used for group by grouping statistics statements. Select deptno, avg (sal) avg_sal from empgroup by deptnohaving avg_sal > 20001.4, join on1.4.1, inner join-- only data matching the join condition in both tables of the join will be retained select e.empno, e.ename, d.deptno from emp e (inner) join dept d on e.deptno = d.deptno 1.4.2, left outer join-all records in the table on the left side of the JOIN operator that match the WHERE clause will be returned to select e.join, d.dname, d.locfrom emp eleft join dept don e.deptno=d.deptno 1.4.3, right outer join-- all records in the table on the right side of the JOIN operator that match the WHERE clause will be returned select e.join, d.*from emp eright join dept don e.deptno=d.deptno1.4.4, full outer join-will return all records in all tables that meet the criteria of the WHERE statement-- method 1: select. Join, d.*from dept dfull join emp eon d.deptnopende.deptnowub-method 2: select e.empno, e.ename D.dnamefrom dept dleft join emp eon d.deptno=e.deptnounion allselect e.empno, e.ename, d.dnamefrom dept dright join emp eon d.deptno=e.deptno--union vertical splicing of two tables can deduplicate the same data-- union all vertical splicing of two tables directly does not remove duplicates 1.5, sort 1.5.1, order by-- global sorting There is only one Reducer--asc ascending order (default)-desc reverse select * from emporder by sal desc1.5.2, sort by & distribute by--distribute by (and sort by) partition by department number, and then sort by employee number in descending order. / / set the number of reduce set mapreduce.job.reduces=3;-- default-1insert overwrite local directory'/ opt/module/hive/datas/distribute-result'select * from emp distribute by deptno sort by empno desc; Note:-- the partition rule of distribute by is to divide the number of hash codes in the partition field with the number of reduce, and then divide the remainder into one zone. -- Hive requires DISTRIBUTE BY statements to be written before SORT BY statements. 1.5.3. Cluster by-- when the distribute by and sort by fields are the same, you can use cluster by select * from emp cluster by deptno;select * from emp distribute by deptno sort by deptno; Note:-- cluster by not only has the function of distribute by, but also has the function of sort by. But the sort can only be sorted in ascending order, and the sort rules cannot be specified as ASC or DESC1.6, Multidimensional aggregation 1.6.1, grouping sets--group by a-dint b grouping sets (ameme b), c)-- equivalent to (group by a-Min b) union (group by c) select region,school,class,count (1) from schoolgroup by region,school,class grouping sets (region,school,class) +-+ | region | school | class | _ c3 | +-+ | NULL | NULL | Class every three years | 5 | | NULL | NULL | Class 1 Tank | 6 | | NULL | NULL | big data Class 1 | 4 | NULL | NULL | Primary School Class 1 | 4 | NULL | NULL | Master Class 1 | 4 | NULL | Baoan Middle School | NULL | 4 | NULL | King Canyon | NULL | 10 | NULL | Huangtian Primary School | NULL | 4 | NULL | Longhua Primary School | NULL | 5 | Baoan District | NULL | NULL | 8 | | King's area | | | NULL | NULL | 10 | | Longhua District | NULL | NULL | 5 | +-+ 1.6.2, With cube--group by a Union-- is equivalent to union-- group by null,a,b,c,ab,ac,bc,abcselect region,class, school,count (1) from school group by region,class, school with cube after various combinations of group by. 1.6.3, with rollup--group by a BBJ c with rollup-- is equivalent to union-group by null,a,ab,abcselect region,class, school,count (1) from school group by region,class, school with rollup Thank you for reading! This is the end of the article on "how to query Hive". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.