前言

????????今天是中秋節(jié)，早上七點就醒了，干啥呢，大一開學后空教室緊缺，還不趁著假期來學校等啥呢。順便偷偷許個愿吧，希望在明年的這個時候，秋招不知道趕不趕得上，我希望拿幾個國獎，藍橋杯、中國大學生計算機設計大賽、挑戰(zhàn)杯、軟件杯... 。最大的愿望還是能夠早點找到一份心儀的工作！！！不說了，開卷！

Hive 查詢語句

????????查詢語句必然是 Hive 的重中之重，之前的 SQL 基礎也不是那么牢固，尤其是高級的 SQL 語句，這里需要惡補一下。

1、基本語法

每個關鍵字的順序不能顛倒。

SELECT [ALL | DISTINCT] 字段1, 字段2, ...FROM 表名[WHERE 條件][GROUP BY 字段]    --分組查詢[HAVING 字段]     --分組后過濾(group by 后只能用 having 不能再用 where)[ORDER BY 字段]    --排序[CLUSTER BY col_list| [DISTRIBUTE BY col_list] [SORT BY col_list]][LIMIT 頁數(shù)]    --分頁顯示

2、基本查詢

2.1、數(shù)據(jù)準備（Select…From）

創(chuàng)建文件 dept.txt、emp.txt、loc.txt。

dept.txt：

10	行政部	1700
20	財務部	1800
30	教學部	1900
40	銷售部	1700

emp.txt：

7369	張三	研發(fā)	800.00	30
7499	李四	財務	1600.00	20
7521	王五	行政	1250.00	10
7566	趙六	銷售	2975.00	40
7654	侯七	研發(fā)	1250.00	30
7698	馬八	研發(fā)	2850.00	30
7782	金九	\N	2450.0	30
7788	銀十	行政	3000.00	10
7839	小芳	銷售	5000.00	40
7844	小明	銷售	1500.00	40
7876	小李	行政	1100.00	10
7900	小元	講師	950.00	30
7902	小海	行政	3000.00	10
7934	小紅明	講師	1300.00	30

loc.txt：

1700	北京
1800	上海
1900	深圳

創(chuàng)建表

dept：

use default;
-- 創(chuàng)建部門表 在hdfs生成目錄: /user/hive/warehouse/dept
create table if not exists dept(deptno int, --部門編號dname string,   --部門名稱loc int --部門位置
)
row format delimited fields terminated by '\t';

emp：

-- 創(chuàng)建員工表 在hdfs生成目錄: /user/hive/warehouse/emp
create table if not exists emp(empno int,  --員工編號ename string,   --員工姓名job string, --員工崗位sal double, --員工工資deptno int  --部門編號
)
row format delimited fields terminated by '\t';

location：

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

導入數(shù)據(jù)

load data local inpath '/opt/module/hive-3.1.2/datas/dept.txt' into table dept;load data local inpath '/opt/module/hive-3.1.2/datas/emp.txt' into table emp;load data local inpath '/opt/module/hive-3.1.2/datas/loc.txt' into table location;

2.2、全表和特定列查詢

-- 查詢全表
select * from emp;
-- 查詢指定字段
select empno,ename from emp;

2.3、列別名

可以省去 as 。

-- 列別名 as 或者 直接字段后跟 別名
select empno as id,ename name from emp;

??2.3、limit 分頁顯示

-- 分頁顯示 limit(begin,len) begin從0開始算起 向下讀取len行
select * from emp limit 5;
select * from emp limit 0,3;

2.4、關系運算符（between、in、is Null）

這里只

-- 關系運算符
-- 查詢部門id為30或20的員工信息
select * from emp
where deptno in (30,20);
-- 比較運算符
select * from emp where sal =3000;
select * from emp where sal between 500 and 1000;
select * from emp where job is null;

2.5、邏輯運算符（and、or、not）

-- 查詢除了20部門和30部門以外的員工信息select * from emp where deptno not IN(30, 20);

?2.6、like 和 Rlike

like 可以用來進行 模糊匹配：

• % 代表零個或多個字符(任意個字符)。
• _ 代表一個字符。

Rlike 是 Hive 對like的擴展，使它可以通過Java的正則表達式這個更強大的語言來指定匹配條件。

-- 查找名字以A開頭的員工信息
select * from emp where ename LIKE ‘A%’; hiveselect * from emp where ename RLIKE ‘^A’;

---

3、分組

3.1、group by

GROUP BY語句通常會和聚合函數(shù)一起使用，按照一個或者多個列隊結果進行分組，然后對每個組執(zhí)行聚合操作。

注意：使用聚合函數(shù)必須使用 group by！

在 Hive 中，當你在查詢語句中使用聚合函數(shù)（如sum()）時，你需要使用group by子句來對數(shù)據(jù)進行分組。這是因為聚合函數(shù)會對每個組的數(shù)據(jù)進行操作，而不是對整個數(shù)據(jù)集進行操作。

-- 計算沒個部門的平均工資
select t.deptno, avg(t.sal) from emp t group by t.deptno;   --用時27s--計算每個部門中每個崗位的最高薪水
select t.deptno,t.job,max(t.sal) from emp t group by t.deptno,t.job;    --用時22s

3.2、having

如果我們要對分組后的結果進行條件過濾，這時候不能使用 where ，需要使用 having。

-- 使用 where 對grou by的結果進行再次過濾
select job,cnt from
(select job,count(*) cnt from emp group by job)t1
where cnt>=2;-- 上面的寫法太復雜了 所以有了 having
select job,count(*) cnt from emp having cnt>=2;-- 查詢平均工資>1000的部門id
select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal > 1000;

4、Join 語句

4.1、內連接

返回兩張表中滿足關聯(lián)條件的行，拼接成一張寬表（因為兩張表橫向合并，字段增加）

-- 內連接 (返回兩張表的所有能關聯(lián)上(滿足e.deptno = d.deptno)的行)
-- 根據(jù)部門編號查詢出員工的部門名稱
select e.empno,e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;

4.2、左外連接

把左表的全部行和右表進行拼接，右表如果不滿足拼接條件，則拼接的部分補 NULL。

當執(zhí)行左外連接時，Hive 會將左表的每一行與右表中滿足關聯(lián)條件的行進行匹配。如果右表中存在匹配的行，則返回左表和右表中匹配行的組合。如果右表中不存在匹配的行，則返回左表的行，右表的部分將用 NULL 值填充。

-- 左外連接 (返回左表的全部行)
select e.empno,e.ename,d.deptno from emp e left join dept d on e.deptno = d.deptno;

4.3、右外連接

把右表的全部行和左表進行拼接，左表如果不滿足拼接條件，則拼接的部分補 NULL。

-- 右外連接 (返回右表的全部行)
select e.empno,e.ename,d.deptno from emp e right join dept d on e.deptno = d.deptno;

4.4、滿外連接

返回左表和右表中所有的行，以及兩者之間滿足連接條件的匹配行。如果某一側的表中沒有匹配的行，則返回NULL值。

-- 滿外連接
select e.empno,e.ename,d.deptno from emp e full join dept d on e.deptno = d.deptno;

4.5、多表連接

大多數(shù)情況下，Hive會對每對join連接對象啟動一個MapReduce任務。本例中會首先啟動一個MapReduce job對表e和表d進行連接操作，然后會再啟動一個MapReduce job將第一個MapReduce job的輸出和表l進行連接操作。

-- 用emp表的deptno 關聯(lián)dept表的deptno字段,再用dept表的loc字段關聯(lián)location表的loc字段
select * from emp e
join dept don e.deptno = d.deptno
join location l
on d.loc = l.loc;

4.6、笛卡爾集

笛卡爾集會在下面條件下產生

1. 省略連接條件
2. 連接條件無效
3. 所有表中的所有行互相連接

--笛卡爾集 (把a表每一行數(shù)據(jù)和b表每一行數(shù)據(jù)關聯(lián)到一起) 不要輕易使用
--     a  |  1    => (a,1)(b,1)(c,1)
--     b  |  2    => (a,2)(b,2)(c,2)
--     c  |  3    => (a,3)(b,3)(c,3)
-- 結果總行數(shù): a行數(shù)*b行數(shù), 3*3=9行
select empno,dname from emp,dept;

4.7、聯(lián)合（union、union all）

縱向拼接，要求必須字段數(shù)相同，字段類型相同。

-- 聯(lián)合union
-- join 是橫向拼接(形成寬表,增加了字段) 而 union是縱向拼接(增加表的數(shù)據(jù),也就是兩張表的大部分字段的個數(shù)和類型必須一致)
-- union去重，union all不去重
select * from emp
where deptno = 30
union
select * from emp
where deptno = 20;

---

5、排序

5.1、全局排序（Order By）

語法：

select * from 表名 order by 字段 [asc | desc];

asc：升序（默認）

desc：降序

-- 1.全局排序 order by
-- asc: 升序 desc:降序
select * from emp order by sal;

????????我們在實際開發(fā)中，order by 其實是一個比較危險的操作，因為我們一個 order by 操作的底層中，Map 可能是多個 Map 任務，但是 Reduce 任務默認只有一個。這樣的話，如果我們這張表對應的數(shù)據(jù)源非常大，那么 Reduce 任務的壓力可想而知。

????????實際開發(fā)中，我們更多的時候并不需要整個結果排好序的數(shù)據(jù)，而往往要的是前幾個或者后幾個數(shù)據(jù)，所以我們的 order by 經常是配合 limit 來使用的。這樣的性能往往是最好的，因為假如有100w條數(shù)據(jù)，我們只需要前100個升序的結果，那么我們就可以讓 Reduce 任務只拉取每個 Map 任務的前 100 條數(shù)據(jù)即可。

select * form 表名 order by 字段 limit 100;

5.2、每個Reducer內部排序（Sort By）

作用：指定排序字段。

????????對于很大規(guī)模的數(shù)據(jù)，order by 可以保證所有的數(shù)據(jù)結果保存在一個文件并全局有序，但是很多時候，我們并不需要全局排序，此時可以使用 sort by。

????????sort by 為每個 Reduce 任務產生一個排序文件，只能保證每個 Reduce 任務的結果有序，而不是全局有序。

設置 reduce 個數(shù)

-- 設置reduce 任務數(shù)量為 3
set mapreduce.job.reduces = 3;

查看 reduce 個數(shù)

set mapreduce.job.reduces;

測試

-- 根據(jù)員工薪資進行降序排序
select * from emp sort by sal desc ;

運行結果：?

我們的數(shù)據(jù)并不是全局有序，而是分為了3塊（reduce 任務個數(shù)），各自局部有序。

這里，我在 reduce 任務數(shù)為 3 的情況下又測試了一遍 order by，發(fā)現(xiàn)結果是全局有序了，說明有兩個 reduce 任務沒有開啟。?

將查詢結果導出到文件中

-- 格式化導出
insert overwrite local directory '/opt/module/hive-3.1.2/datas/sortby-result'
row format delimited fields terminated by '\t'
select * from emp sort by sal;

?運行結果：

可以看到，一共導出了3個文件，分別內部有序。

5.3、分區(qū)（Distribute By）

作用：指定分區(qū)字段

我們 hadoop 默認的分區(qū)規(guī)則如下：

public int getPartition(K key, V value, int numReduceTasks) {return (key.hashCode() & 2147483647) % numReduceTasks;}

這里，我們指定我們的 Reduce 任務數(shù)為 3，這樣理論應該產生 3 個分區(qū)：

insert overwrite local directory '/opt/module/hive-3.1.2/datas/distributeby-result'
row format delimited fields terminated by '\t'
select * from emp distribute by sal;

運行結果：?

5.4、分區(qū)排序（Cluster By）

如果我們的分區(qū)字段（distribute by）和排序字段（sort by）是同一個字段的時候，我們可以簡寫為 cluster by 。但是排序只能是升序排序，不能指定排序規(guī)則為ASC或者DESC。

select * from emp cluster by deptno;

相當于

select * from emp
sort by deptno
distribute by deptno;