1、Select

hive (default)> select * from XX; //全表查詢    
hive (default)> select 列名赖草，列名 from XX; //特定列查詢

注意：
（1）SQL 語言大小寫不敏感。
（2）SQL 可以寫在一行或者多行
（3）關(guān)鍵字不能被縮寫也不能分行
（4）各子句一般要分行寫剪个。
（5）使用縮進提高語句的可讀性秧骑。

列別名

重命名一個列，便于計算扣囊。緊跟列名乎折，也可以在列名和別名之間加入關(guān)鍵字‘AS’
查詢名稱和部門
hive (default)> select ename AS name, deptno dn from emp;

算術(shù)運算符

常用函數(shù)

求總行數(shù)（count）
hive (default)> select count(*) cnt from emp;

求工資的最大值（max）
hive (default)> select max(sal) max_sal from emp;

Snipaste_2020-06-08_16-06-56.png

求工資的最小值（min）
hive (default)> select min(sal) min_sal from emp;

求工資的總和（sum）
hive (default)> select sum(sal) sum_sal from emp;

求工資的平均值（avg）
hive (default)> select avg(sal) avg_sal from emp;

比較運算符

Limit

典型的查詢會返回多行數(shù)據(jù)。LIMIT 子句用于限制返回的行數(shù)侵歇。
hive (default)> select * from emp limit 5;

where

使用WHERE 子句骂澄，將不滿足條件的行過濾掉
WHERE 子句緊隨 FROM 子句
hive (default)> select * from emp where sal >1000;

Like、RLike

查找以 2 開頭薪水的員工信息
hive (default)> select * from emp where sal LIKE '2%';
查找第二個數(shù)值為 2 的薪水的員工信息
hive (default)> select * from emp where sal LIKE '_2%';
查找薪水中含有 2 的員工信息
hive (default)> select * from emp where sal RLIKE '[2]';

Snipaste_2020-06-08_16-19-33.png

邏輯運算符（And/Or/Not）

查詢薪水大于 1000惕虑，部門是 30
hive (default)> select * from emp where sal>1000 and deptno=30;
查詢薪水大于 1000坟冲，或者部門是 30
hive (default)> select * from emp where sal>1000 or deptno=30;
查詢除了 20 部門和 30 部門以外的員工信息
hive (default)> select * from emp where deptno not IN(30, 20);

Snipaste_2020-06-08_16-20-17.png

分組

Group By

GROUP BY 語句通常會和聚合函數(shù)一起使用，按照一個或者多個列隊結(jié)果進行分組溃蔫， 然后對每個組執(zhí)行聚合操作
（1）計算emp 表每個部門的平均工資

hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;

image.png

（2）計算emp 每個部門中每個崗位的最高薪水

hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job;

image.png

Having語句

having 只用于 group by 分組統(tǒng)計語句

求每個部門的平均薪水大于 2000 的部門

select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

image.png

Join

Hive 支持通常的 SQL JOIN 語句健提，但是只支持等值連接，不支持非等值連接伟叛。
根據(jù)員工表和部門表中的部門編號相等私痹，查詢員工編號、員工名稱和部門名稱
//取出 e.empno, e.ename, d.deptno, d.dname四列痪伦。命名為e侄榴，join dept，命名為d网沾， jion條件on e.deptno = d.deptno

hive (default)> select e.empno, e.ename, d.deptno, d.dname from emp e join dept d
on e.deptno = d.deptno;

Snipaste_2020-06-08_18-07-37.png

表別名

好處：
使用別名可以簡化查詢。
使用表名前綴可以提高執(zhí)行效率
合并員工表和部門表

select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno= d.deptno;

內(nèi)連接：只有進行連接的兩個表中都存在與連接條件相匹配的數(shù)據(jù)才會被保留下來蕊爵。

hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d 
on e.deptno= d.deptno;

左外連接：JOIN 操作符左邊表中符合WHERE 子句的所有記錄將會被返回辉哥。

hive (default)> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

右外連接：JOIN 操作符右邊表中符合WHERE 子句的所有記錄將會被返回。

hive (default)> select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

全（滿）外連接：將會返回所有表中符合 WHERE 語句條件的所有記錄。如果任一表的指定字段沒有符合條件的值的話醋旦，那么就使用 NULL 值替代恒水。

hive (default)> select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

多表連接

實踐

create table if not exists default.location( loc int,
loc_name string
)
row format delimited fields terminated by '\t';

load data local inpath '/home/data/loc.txt' into table location;

Snipaste_2020-06-08_21-50-17.png

多表連接查詢

SELECT e.ename, d.deptno, l.loc_name 
FROM    emp e
JOIN    dept d
ON  d.deptno = e.deptno 
JOIN    location l
ON  d.loc = l.loc;

join.png

大多數(shù)情況下，Hive 會對每對 JOIN 連接對象啟動一個 MapReduce 任務(wù)饲齐。
本例中會首先啟動一個 MapReduce job 對表e 和表 d 進行連接操作钉凌，
然后會再啟動一個 MapReduce job 將第一個 MapReduce job 的輸出和表 l;進行連接操作。
注意：為什么不是表 d 和表 l 先進行連接操作呢捂人？這是因為 Hive 總是按照從左到右的順序執(zhí)行的御雕。

排序

全局排序(Order By)

Order By：全局排序，一個 Reducer

使用 ORDER BY 子句排序
ASC（ascend）: 升序（默認(rèn)）
DESC（descend）: 降序

ORDER BY 子句在SELECT 語句的結(jié)尾
案例實操:
查詢員工信息按工資升序排列
select * from emp order by sal;
查詢員工信息按工資降序排列
select * from emp order by sal desc;
按照員工薪水的 2 倍排序
select ename, sal*2 twosal from emp order by twosal;
按照部門和工資升序排序
select ename, deptno, sal from emp order by deptno, sal ;

orderby.png

每個 MapReduce 內(nèi)部排序（Sort By）

Sort By：每個 Reducer 內(nèi)部進行排序滥搭，對全局結(jié)果集來說不是排序酸纲，多個reduce個數(shù)

設(shè)置reduce 個數(shù)
set mapreduce.job.reduces=3;

根據(jù)部門編號降序查看員工信息
select * from emp sort by empno desc;

分區(qū)排序（Distribute By）

Distribute By：類似 MR 中 partition，進行分區(qū)瑟匆，結(jié)合 sort by 使用闽坡。
注意，Hive 要求 DISTRIBUTE BY 語句要寫在SORT BY 語句之前愁溜。
對于 distribute by 進行測試疾嗅，一定要分配多 reduce 進行處理，否則無法看到 distribute by的效果冕象。

先按照部門編號分區(qū)代承，再按照工資降序排序

set mapreduce.job.reduces=3;

insert overwrite local directory '/home/out/dis-by'
select * from emp distribute by deptno sort by sal desc;

Snipaste_2020-06-08_22-20-29.png

Cluster By

當(dāng) distribute by 和 sorts by 字段相同時，可以使用 cluster by 方式交惯。

cluster by 除了具有 distribute by 的功能外還兼具 sort by 的功能次泽。
但是排序只能是升序排序，不能指定排序規(guī)則為 ASC 或者 DESC席爽。

select * from emp cluster by deptno;
同上
select * from emp distribute by deptno sort by deptno;