[TOC] ## 語法結構 ~~~ SELECT [ALL | DISTINCT] select_expr, select_expr, ... FROM table_reference [WHERE where_condition] [GROUP BY col_list [HAVING condition]] [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list] ] [LIMIT number] ~~~ ## 案例 ### 查詢時創建外部表 ~~~ create external table student_ext(Sno int,Sname string,Sex string,Sage int,Sdept string) row format delimited fields terminated by ',' location '/stu'; ~~~ ### where和分組 ~~~ //where查詢 select * from student_ext where sno=95020; //分組 select sex,count(*) from student_ext group by sex; ~~~ ### 分區和排序 ~~~ //分區,排序,但是這個只有1個reduce,沒意義,cluster by后面可以跟多個 select * from student_ext cluster by sex; ~~~ ### 設置reduce ~~~ //設置4個reduce //這樣每個reduce自己內部會排序 hive> set mapred.reduce.tasks=4; hive> create table tt_1 as select * from student_ext cluster by sno; //查看結果,這個tt_1文件夾下面有4個文件 dfs -cat /user/hive/warehouse/db1.db/tt_1/000000_0; //這個結果和上面一樣,分成4個reduce create table tt_2 as select * from student_ext distribute by sno sort by sno; //排序可以按照其他方式排序 create table tt_3 as select * from student_ext distribute by sno sort by sage; ~~~ # 排序 ## 全局排序 order By全局排序,一個reduce asc升序 desc降序 ## sortBy 每個mapreduce內部排序 對全局來說不是排序 ## DistributeBy 分區排序 類似MR中的partition,進行分區,結合sort by使用 注意:hive要求DistributeBy語句要寫在sortby語句之前 ## ClusterBy 當DistributeBy和sortBy字段相同時,可以使用ClusterBy方式. **ClusterBy除了DistributeBy的功能外還有sortby功能.但是排序只能是倒排序,不能指定排序規則是ASC和DESC**