上一篇：095-BigData-23Hive分區(qū)及DML操作

六、查詢

官方資料之查詢(select)

[WITH CommonTableExpression (, CommonTableExpression)*]    (Note: Only available starting with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, elect_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT number]

6.1 基本查詢（Select…From）

6.1.1 全表和特定列查詢
1）全表查詢
hive (default)> select * from emp;
2）選擇特定列查詢
hive (default)> select empno, ename from emp;
注意：
（1）SQL 語言大小寫不敏感。
（2）SQL 可以寫在一行或者多行
（3）關(guān)鍵字不能被縮寫也不能分行
（4）各子句一般要分行寫。
（5）使用縮進(jìn)提高語句的可讀性。

6.1.2 列別名
1）重命名一個(gè)列。
2）便于計(jì)算。
3）緊跟列名，也可以在列名和別名之間加入關(guān)鍵字‘AS’
4）案例實(shí)操
（1）查詢名稱和部門
hive (default)> select ename AS name, deptno dn from emp;

6.1.3 算術(shù)運(yùn)算符

image.png

案例實(shí)操
查詢出所有員工的薪水后加1顯示。
hive (default)> select sal +1 from emp;

6.1.4 常用函數(shù) (重點(diǎn))

1）求總行數(shù)（count）
hive (default)> select count(1) cnt from emp;
2）求工資的最大值（max）
hive (default)> select max(sal) max_sal from emp;
3）求工資的最小值（min）
hive (default)> select min(sal) min_sal from emp;
4）求工資的總和（sum）
hive (default)> select sum(sal) sum_sal from emp;
5）求工資的平均值（avg）
hive (default)> select avg(sal) avg_sal from emp;

6.1.5 Limit語句(實(shí)用重點(diǎn))

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

6.2 Where語句(實(shí)用重點(diǎn))

6.2 Where語句
1）使用WHERE子句，將不滿足條件的行過濾掉。
2）WHERE子句緊隨FROM子句。
3）案例實(shí)操
查詢出薪水大于1000的所有員工
hive (default)> select ename , sal from emp where sal > 1000;

6.2.1 比較運(yùn)算符（Between/In/ Is Null）
1）下面表中描述了謂詞操作符，這些操作符同樣可以用于JOIN…ON和HAVING語句中。

image.png

2）案例實(shí)操
（1）查詢出薪水等于5000的所有員工
hive (default)> select * from emp where sal =5000;
（2）查詢工資在500到1000的員工信息，注意between ... and 是閉區(qū)間
hive (default)> select * from emp where sal between 500 and 1000;
（3）查詢comm(獎(jiǎng)金)為空的所有員工信息
hive (default)> select * from emp where comm is null;
（4）查詢工資是1500和5000的員工信息
hive (default)> select * from emp where sal IN (1500, 5000);

6.2.2 Like和RLike
1）使用LIKE運(yùn)算選擇類似的值
2）選擇條件可以包含字符或數(shù)字:
% 代表零個(gè)或多個(gè)字符(任意個(gè)字符)。
_ 代表一個(gè)字符。
3）RLIKE子句是Hive中這個(gè)功能的一個(gè)擴(kuò)展，其可以通過Java的正則表達(dá)式這個(gè)更強(qiáng)大的語言來指定匹配條件。
4）案例實(shí)操
（1）查找以2開頭薪水的員工信息
hive (default)> select * from emp where sal LIKE '2%';
（2）查找第二個(gè)數(shù)值為2的薪水的員工信息
hive (default)> select * from emp where sal LIKE '_2%';
（3）查找薪水中含有2的員工信息
hive (default)> select * from emp where sal RLIKE '[2]';

6.2.3 邏輯運(yùn)算符（And/Or/Not）

image.png

案例實(shí)操
（1）查詢薪水大于1000，部門是30
hive (default)> select * from emp where sal>1000 and deptno=30;
（2）查詢薪水大于1000，或者部門是30
hive (default)> select * from emp where sal>1000 or deptno=30;
（3）查詢除了20部門和30部門以外的員工信息
hive (default)> select * from emp where deptno not IN(30, 20);

6.3 分組

6.3.1 Group By語句
GROUP BY語句通常會(huì)和聚合函數(shù)一起使用，按照一個(gè)或者多個(gè)列隊(duì)結(jié)果進(jìn)行分組，然后對(duì)每個(gè)組執(zhí)行聚合操作。
案例實(shí)操：
（1）計(jì)算emp表每個(gè)部門的平均工資
hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
（2）計(jì)算emp每個(gè)部門中每個(gè)崗位的最高薪水
hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job;

6.3.2 Having語句
1）having與where不同點(diǎn)
（1）where針對(duì)表中的列發(fā)揮作用，查詢數(shù)據(jù)；having針對(duì)查詢結(jié)果中的列發(fā)揮作用，篩選數(shù)據(jù)。
（2）where后面不能寫分組函數(shù)，而having后面可以使用分組函數(shù)。
（3）having只用于group by分組統(tǒng)計(jì)語句。
2）案例實(shí)操：
（1）求每個(gè)部門的平均薪水大于2000的部門
求每個(gè)部門的平均工資
hive (default)> select deptno, avg(sal) from emp group by deptno;
求每個(gè)部門的平均薪水大于2000的部門
hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

6.4 Join語句

6.4.1 等值Join

Hive支持通常的SQL JOIN語句，但是只支持等值連接，不支持非等值連接。

案例實(shí)操

（1）根據(jù)員工表和部門表中的部門編號(hào)相等，查詢員工編號(hào)、員工名稱和部門編號(hào)；

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

同樣與

select e.empno,e.ename,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;

6.4.2 表的別名
1）好處
（1）使用別名可以簡(jiǎn)化查詢。
（2）使用表名前綴可以提高執(zhí)行效率。
2）案例實(shí)操
合并員工表和部門表
hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

6.4.3 內(nèi)連接
內(nèi)連接：只有進(jìn)行連接的兩個(gè)表中都存在與連接條件相匹配的數(shù)據(jù)才會(huì)被保留下來。
hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;
6.4.4 左外連接
左外連接：JOIN操作符左邊表中符合WHERE子句的所有記錄將會(huì)被返回。
hive (default)> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;
6.4.5 右外連接
右外連接：JOIN操作符右邊表中符合WHERE子句的所有記錄將會(huì)被返回。
hive (default)> select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;
6.4.6 滿外連接
滿外連接：將會(huì)返回所有表中符合WHERE語句條件的所有記錄。如果任一表的指定字段沒有符合條件的值的話，那么就使用NULL值替代。
hive (default)> select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;
6.4.7 多表連接
注意：連接 n個(gè)表，至少需要n-1個(gè)連接條件。例如：連接三個(gè)表，至少需要兩個(gè)連接條件。
0）數(shù)據(jù)準(zhǔn)備

1）創(chuàng)建位置表

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

2）導(dǎo)入數(shù)據(jù)
hive (default)> load data local inpath '/opt/module/datas/location.txt' into table default.location;
3）多表連接查詢distinct
hive (default)>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;
大多數(shù)情況下，Hive會(huì)對(duì)每對(duì)JOIN連接對(duì)象啟動(dòng)一個(gè)MapReduce任務(wù)。本例中會(huì)首先啟動(dòng)一個(gè)MapReduce job對(duì)表e和表d進(jìn)行連接操作，然后會(huì)再啟動(dòng)一個(gè)MapReduce job將第一個(gè)MapReduce job的輸出和表l;進(jìn)行連接操作。
注意：為什么不是表d和表l先進(jìn)行連接操作呢？這是因?yàn)镠ive總是按照從左到右的順序執(zhí)行的。

6.4.8 笛卡爾積
1）笛卡爾集會(huì)在下面條件下產(chǎn)生:
（1）省略連接條件
（2）連接條件無效
（3）所有表中的所有行互相連接
2）案例實(shí)操
hive (default)> select empno, deptno from emp, dept;
FAILED: SemanticException Column deptno Found in more than One Tables/Subqueries
6.4.9 連接謂詞中不支持or
hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno or e.ename=d.ename; 錯(cuò)誤的

6.5 排序

6.5.1 全局排序（Order By）
Order By：全局排序，一個(gè)MapReduce
1）使用 ORDER BY 子句排序
ASC（ascend）: 升序（默認(rèn)）
DESC（descend）: 降序
2）ORDER BY 子句在SELECT語句的結(jié)尾。
3）案例實(shí)操
（1）查詢員工信息按工資升序排列
hive (default)> select * from emp order by sal;
（2）查詢員工信息按工資降序排列
hive (default)> select * from emp order by sal desc;
6.5.2 按照別名排序
按照員工薪水的2倍排序
hive (default)> select ename, sal*2 twosal from emp order by twosal;
6.5.3 多個(gè)列排序
按照部門和工資升序排序
hive (default)> select ename, deptno, sal from emp order by deptno, sal ;
注：這個(gè)是先按部門號(hào)排序，部門號(hào)相同，按薪水升序排序
6.5.4 每個(gè)MapReduce內(nèi)部排序（Sort By）
Sort By：每個(gè)MapReduce內(nèi)部進(jìn)行排序，分區(qū)規(guī)則按照key的hash來運(yùn)算，（區(qū)內(nèi)排序）對(duì)全局結(jié)果集來說不是排序。
1）設(shè)置reduce個(gè)數(shù)
hive (default)> set mapreduce.job.reduces=3;
2）查看設(shè)置reduce個(gè)數(shù)
hive (default)> set mapreduce.job.reduces;
3）根據(jù)部門編號(hào)降序查看員工信息
hive (default)> select * from emp sort by empno desc;
4）將查詢結(jié)果導(dǎo)入到文件中（按照部門編號(hào)降序排序）
hive (default)> insert overwrite local directory '/opt/module/datas/emp.txt' row format delimited fields terminated by '\t' select * from emp sort by deptno desc;

6.5.5 分區(qū)排序（Distribute By）
Distribute By：類似MR中partition，進(jìn)行分區(qū)，結(jié)合sort by使用。
注意，Hive要求DISTRIBUTE BY語句要寫在SORT BY語句之前。
對(duì)于distribute by進(jìn)行測(cè)試，一定要分配多reduce進(jìn)行處理，否則無法看到distribute by的效果。

案例實(shí)操：
（1）先按照部門編號(hào)分區(qū)，再按照員工編號(hào)降序排序。
hive (default)> set mapreduce.job.reduces=3;
hive (default)> insert overwrite local directory '/opt/module/datas/distribute-result' row format delimited fields terminated by '\t' select * from emp distribute by deptno sort by empno desc;
6.5.6 Cluster By
當(dāng)distribute by和sorts by字段相同時(shí)，可以使用cluster by方式。
cluster by除了具有distribute by的功能外還兼具sort by的功能。但是排序只能是倒序排序，不能指定排序規(guī)則為ASC或者DESC。
1）以下兩種寫法等價(jià)
hive (default)> insert overwrite local directory '/root/emp3' row format delimited fields terminated by '\t' select * from emp cluster by deptno;
hive (default)> select * from emp distribute by deptno sort by deptno;
注意：按照部門編號(hào)分區(qū)，不一定就是固定死的數(shù)值，可以是20號(hào)和30號(hào)部門分到一個(gè)分區(qū)里面去。

6.6 分桶及抽樣查詢

6.6.1 分桶表數(shù)據(jù)存儲(chǔ)
分區(qū)針對(duì)的是數(shù)據(jù)的存儲(chǔ)路徑；分桶針對(duì)的是數(shù)據(jù)文件。
分區(qū)提供一個(gè)隔離數(shù)據(jù)和優(yōu)化查詢的便利方式。不過，并非所有的數(shù)據(jù)集都可形成合理的分區(qū)，特別是之前所提到過的要確定合適的劃分大小這個(gè)疑慮。
分桶是將數(shù)據(jù)集分解成更容易管理的若干部分的另一個(gè)技術(shù)。
1）先創(chuàng)建分桶表，通過直接導(dǎo)入數(shù)據(jù)文件的方式
（0）數(shù)據(jù)準(zhǔn)備

（1）創(chuàng)建分桶表

create table stu_buck1(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';

（2）查看表結(jié)構(gòu)
hive (default)> desc formatted stu_buck;
Num Buckets: 4
（3）導(dǎo)入數(shù)據(jù)到分桶表中
hive (default)> load data local inpath '/opt/module/datas/s/student.txt' into table stu_buck;
（4）查看創(chuàng)建的分桶表中是否分成4個(gè)桶
發(fā)現(xiàn)并沒有分成4個(gè)桶。是什么原因呢？
2）創(chuàng)建分桶表時(shí)，數(shù)據(jù)通過子查詢的方式導(dǎo)入
（1）先建一個(gè)普通的stu表

create table stu(id int, name string)
row format delimited fields terminated by '\t';

（2）向普通的stu表中導(dǎo)入數(shù)據(jù)

load data local inpath '/opt/module/datas/s/student.txt' into table stu;

（3）清空stu_buck表中數(shù)據(jù)

truncate table stu_buck;
select * from stu_buck;

（4）導(dǎo)入數(shù)據(jù)到分桶表，通過子查詢的方式

insert into table stu_buck
select id, name from stu;

（5）發(fā)現(xiàn)還是只有一個(gè)分桶
（6）需要設(shè)置一個(gè)屬性

hive (default)> set hive.enforce.bucketing=true;
hive (default)> set mapreduce.job.reduces=-1;
hive (default)> insert into table stu_buck1
select id, name from stu;

（7）查詢分桶的數(shù)據(jù)

hive (default)> select * from stu_buck;
OK
stu_buck.id     stu_buck.name
1001    ss1
1005    ss5
1009    ss9
1012    ss12
1016    ss16
1002    ss2
1006    ss6
1013    ss13
1003    ss3
1007    ss7
1010    ss10
1014    ss14
1004    ss4
1008    ss8
1011    ss11
1015    ss15

6.6.2 分桶抽樣查詢
對(duì)于非常大的數(shù)據(jù)集，有時(shí)用戶需要使用的是一個(gè)具有代表性的查詢結(jié)果而不是全部結(jié)果。Hive可以通過對(duì)表進(jìn)行抽樣來滿足這個(gè)需求。
查詢表stu_buck中的數(shù)據(jù)。

hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);

注：tablesample是抽樣語句，語法：TABLESAMPLE(BUCKET x OUT OF y) 。

hive (default)> select * from stu_buck tablesample(bucket 1 out of 3 on id);

不是桶數(shù)的倍數(shù)或者因子也可以，但是不推薦。
y必須是table總bucket數(shù)的倍數(shù)或者因子。hive根據(jù)y的大小，決定抽樣的比例。例如，table總共分了4份，當(dāng)y=2時(shí)，抽取(4/2=)2個(gè)bucket的數(shù)據(jù)，當(dāng)y=8時(shí)，抽取(4/8=)1/2個(gè)bucket的數(shù)據(jù)。

hive (default)> select * from stu_buck tablesample(bucket 1 out of 2 on id);

x表示從哪個(gè)bucket開始抽取。例如，table總bucket數(shù)為4，tablesample(bucket 4 out of 4)，表示總共抽?。?/4=）1個(gè)bucket的數(shù)據(jù)，抽取第4個(gè)bucket的數(shù)據(jù)。

hive (default)> select * from stu_buck tablesample(bucket 1 out of 8 on id);

注意：x的值必須小于等于y的值，否則
FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck

6.6.3 數(shù)據(jù)塊抽樣

Hive提供了另外一種按照百分比進(jìn)行抽樣的方式，這種是基于行數(shù)的，按照輸入路徑下的數(shù)據(jù)塊百分比進(jìn)行的抽樣。

hive (default)> select * from stu tablesample(0.1 percent) ;

提示：這種抽樣方式不一定適用于所有的文件格式。另外，這種抽樣的最小抽樣單元是一個(gè)HDFS數(shù)據(jù)塊。因此，如果表的數(shù)據(jù)大小小于普通的塊大小128M的話，那么將會(huì)返回所有行。

下一篇：097-BigData-25Hive函數(shù)壓縮存儲(chǔ)格式