Hive——Hive查詢

查詢語句文法：

[WITH CommonTableExpression (, CommonTableExpression)*]    (Note: Only available
 starting with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, select_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]

1、基本查詢（Select…From）

1.1 全表和特定列查詢

1．全表查詢

hive > select * from emp;

2．選擇特定列查詢

hive > select empno, ename from emp;

注意：

（1）SQL 語言大小寫不敏感。

（2）SQL 可以寫在一行或者多行

（3）關鍵字不能被縮寫也不能分行

（4）各子句一般要分行寫。

（5）使用縮進提高語句的可讀性。

1.2 列别名

1．重命名一個列

2．便于計算

3．緊跟列名，也可以在列名和别名之間加入關鍵字‘AS’

4．案例實操

查詢名稱和部門

hive > select ename AS name, deptno dn from emp;

1.3 算術運算符

運算符	描述
A+B	A和B 相加
A-B	A減去B
A*B	A和B 相乘
A/B	A除以B
A%B	A對B取餘
A&B	A和B按位取與
A\|B	A和B按位取或
A^B	A和B按位取異或
~A	A按位取反

查詢出所有員工的薪水後加1顯示。

hive > select sal +1 from emp;

1.4 常用函數

1．求總行數（count）

hive > select count(*) cnt from emp;

2．求工資的最大值（max）

hive > select max(sal) max_sal from emp;

3．求工資的最小值（min）

hive > select min(sal) min_sal from emp;

4．求工資的總和（sum）

hive > select sum(sal) sum_sal from emp;

5．求工資的平均值（avg）

hive > select avg(sal) avg_sal from emp;

1.5 Limit語句

典型的查詢會傳回多行資料。LIMIT子句用于限制傳回的行數。

hive > select * from emp limit 5;

2、Where語句

1．使用WHERE子句，将不滿足條件的行過濾掉

2．WHERE子句緊随FROM子句

3．案例實操

查詢出薪水大于1000的所有員工

hive > select * from emp where sal >1000;

2.1 比較運算符（Between/In/ Is Null）

1）下面表中描述了謂詞操作符，這些操作符同樣可以用于JOIN…ON和HAVING語句中。

操作符	支援的資料類型	描述
A=B	基本資料類型	如果A等于B則傳回TRUE，反之傳回FALSE
A<=>B	基本資料類型	如果A和B都為NULL，則傳回TRUE，其他的和等号（=）操作符的結果一緻，如果任一為NULL則結果為NULL
A<>B, A!=B	基本資料類型	A或者B為NULL則傳回NULL；如果A不等于B，則傳回TRUE，反之傳回FALSE
A<B	基本資料類型	A或者B為NULL，則傳回NULL；如果A小于B，則傳回TRUE，反之傳回FALSE
A<=B	基本資料類型	A或者B為NULL，則傳回NULL；如果A小于等于B，則傳回TRUE，反之傳回FALSE
A>B	基本資料類型	A或者B為NULL，則傳回NULL；如果A大于B，則傳回TRUE，反之傳回FALSE
A>=B	基本資料類型	A或者B為NULL，則傳回NULL；如果A大于等于B，則傳回TRUE，反之傳回FALSE
A [NOT] BETWEEN B AND C	基本資料類型	如果A，B或者C任一為NULL，則結果為NULL。如果A的值大于等于B而且小于或等于C，則結果為TRUE，反之為FALSE。如果使用NOT關鍵字則可達到相反的效果。
A IS NULL	所有資料類型	如果A等于NULL，則傳回TRUE，反之傳回FALSE
A IS NOT NULL	所有資料類型	如果A不等于NULL，則傳回TRUE，反之傳回FALSE
IN(數值1, 數值2)	所有資料類型	使用 IN運算顯示清單中的值
A [NOT] LIKE B	STRING 類型	B是一個SQL下的簡單正規表達式，如果A與其比對的話，則傳回TRUE；反之傳回FALSE。B的表達式說明如下：‘x%’表示A必須以字母‘x’開頭，‘%x’表示A必須以字母’x’結尾，而‘%x%’表示A包含有字母’x’,可以位于開頭，結尾或者字元串中間。如果使用NOT關鍵字則可達到相反的效果。
A RLIKE B, A REGEXP B	STRING 類型	B是一個正規表達式，如果A與其比對，則傳回TRUE；反之傳回FALSE。比對使用的是JDK中的正規表達式接口實作的，因為正則也依據其中的規則。例如，正規表達式必須和整個字元串A相比對，而不是隻需與其字元串比對。

2）案例實操

（1）查詢出薪水等于5000的所有員工

hive > select * from emp where sal =5000;

（2）查詢工資在500到1000的員工資訊

hive > select * from emp where sal between 500 and 1000;

（3）查詢comm為空的所有員工資訊

hive > select * from emp where comm is null;

（4）查詢工資是1500和5000的員工資訊

hive > select * from emp where sal IN (1500, 5000);

2.2 Like和RLike

1）使用LIKE運算選擇類似的值

2）選擇條件可以包含字元或數字:

% 代表零個或多個字元(任意個字元)。

_ 代表一個字元。

3）RLIKE子句是Hive中這個功能的一個擴充，其可以通過Java的正規表達式這個更強大的語言來指定比對條件。

4）案例實操

（1）查找以2開頭薪水的員工資訊

hive (default)> select * from emp where sal LIKE '2%';

（2）查找第二個數值為2的薪水的員工資訊

hive (default)> select * from emp where sal LIKE '_2%';

（3）查找薪水中含有2的員工資訊

hive (default)> select * from emp where sal RLIKE '[2]';

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

操作符	含義
AND	邏輯并
OR	邏輯或
NOT	邏輯否

案例實操

（1）查詢薪水大于1000，部門是30

hive > select * from emp where sal>1000 and deptno=30;

（2）查詢薪水大于1000，或者部門是30

hive > select * from emp where sal>1000 or deptno=30;

（3）查詢除了20部門和30部門以外的員工資訊

hive > select * from emp where deptno not IN(30, 20);

3、分組

3.1 Group By語句

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

案例實操：

（1）計算emp表每個部門的平均工資

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

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

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

3.2 Having語句

1．having與where不同點

（1）where針對表中的列發揮作用，查詢資料；having針對查詢結果中的列發揮作用，篩選資料。

（2）where後面不能寫分組函數，而having後面可以使用分組函數。

（3）having隻用于group by分組統計語句。

2．案例實操

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

求每個部門的平均工資

hive > select deptno, avg(sal) from emp group by deptno;

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

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

4、Join語句

4.1 等值Join

Hive支援通常的SQL JOIN語句，但是隻支援等值連接配接，不支援非等值連接配接。

案例實操

（1）根據員工表和部門表中的部門編号相等，查詢員工編号、員工名稱和部門編号；

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

4.2 表的别名

1．好處

（1）使用别名可以簡化查詢。

（2）使用表名字首可以提高執行效率。

2．案例實操

合并員工表和部門表

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

4.3 内連接配接

内連接配接：隻有進行連接配接的兩個表中都存在與連接配接條件相比對的資料才會被保留下來。

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

4.4 左外連接配接

左外連接配接：JOIN操作符左邊表中符合WHERE子句的所有記錄将會被傳回。

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

4.5 右外連接配接

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

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

4.6 滿外連接配接

滿外連接配接：将會傳回所有表中符合WHERE語句條件的所有記錄。如果任一表的指定字段沒有符合條件的值的話，那麼就使用NULL值替代。

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

4.7 多表連接配接

注意：連接配接 n個表，至少需要n-1個連接配接條件。例如：連接配接三個表，至少需要兩個連接配接條件。

資料準備

1700  Beijing
1800  London
1900  Tokyo

1．建立位置表

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

2．導入資料

hive > load data local inpath '/opt/module/datas/location.txt' into table default.location;

3．多表連接配接查詢

hive >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;

大多數情況下，Hive會對每對JOIN連接配接對象啟動一個MapReduce任務。本例中會首先啟動一個MapReduce job對表e和表d進行連接配接操作，然後會再啟動一個MapReduce job将第一個MapReduce job的輸出和表l;進行連接配接操作。

注意：為什麼不是表d和表l先進行連接配接操作呢？這是因為Hive總是按照從左到右的順序執行的。

5、排序

5.1 全局排序（Order By）

Order By：全局排序，一個MapReduce

1．使用 ORDER BY 子句排序

ASC（ascend）: 升序（預設）

DESC（descend）: 降序

2．ORDER BY 子句在SELECT語句的結尾

3．案例實操

（1）查詢員工資訊按工資升序排列

hive > select * from emp order by sal;

（2）查詢員工資訊按工資降序排列

hive > select * from emp order by sal desc;

5.2 按照别名排序

按照員工薪水的2倍排序

hive > select ename, sal*2 twosal from emp order by twosal;

5.3 多個列排序

按照部門和工資升序排序

hive > select ename, deptno, sal from emp order by deptno, sal ;

5.4 每個MapReduce内部排序（Sort By）

Sort By：每個MapReduce内部進行排序，對全局結果集來說不是排序。

1．設定reduce個數

hive > set mapreduce.job.reduces=3;

2．檢視設定reduce個數

hive > set mapreduce.job.reduces;

3．根據部門編号降序檢視員工資訊

hive > select * from emp sort by empno desc;

4．将查詢結果導入到檔案中（按照部門編号降序排序）

hive > insert overwrite local directory '/opt/module/datas/sortby-result' select * from emp sort by deptno desc;

5.5 分區排序（Distribute By）

Distribute By：類似MR中partition，進行分區，結合sort by使用。

注意，Hive要求DISTRIBUTE BY語句要寫在SORT BY語句之前。

對于distribute by進行測試，一定要配置設定多reduce進行處理，否則無法看到distribute by的效果。

案例實操：

（1）先按照部門編号分區，再按照員工編号降序排序。

hive > set mapreduce.job.reduces=3;

hive > insert overwrite local directory '/opt/module/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;

5.6 Cluster By

當distribute by和sorts by字段相同時，可以使用cluster by方式。

cluster by除了具有distribute by的功能外還兼具sort by的功能。但是排序隻能是倒序排序，不能指定排序規則為ASC或者DESC。

1）以下兩種寫法等價

hive > select * from emp cluster by deptno;

hive > select * from emp distribute by deptno sort by deptno;

注意：按照部門編号分區，不一定就是固定死的數值，可以是20号和30号部門分到一個分區裡面去。

6 分桶及抽樣查詢

6.1 分桶表資料存儲

分區針對的是資料的存儲路徑；分桶針對的是資料檔案。

分區提供一個隔離資料和優化查詢的便利方式。不過，并非所有的資料集都可形成合理的分區，特别是之前所提到過的要确定合适的劃分大小這個疑慮。

分桶是将資料集分解成更容易管理的若幹部分的另一個技術。

資料準備

2．建立分桶表時，資料通過子查詢的方式導入

（1）先建一個普通的stu表

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

（2）向普通的stu表中導入資料

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

（3）清空stu_buck表中資料

truncate table stu_buck;
select * from stu_buck;

（4）導入資料到分桶表，通過子查詢的方式

insert into table stu_buck select id, name from stu;

（5）需要設定一個屬性即可分桶成功

hive > set hive.enforce.bucketing=true;

hive > set mapreduce.job.reduces=-1;

hive > insert into table stu_buck select id, name from stu;

（6）查詢分桶的資料

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

6.2 分桶抽樣查詢

對于非常大的資料集，有時使用者需要使用的是一個具有代表性的查詢結果而不是全部結果。Hive可以通過對表進行抽樣來滿足這個需求。

查詢表stu_buck中的資料。

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

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

y必須是table總bucket數的倍數或者因子。hive根據y的大小，決定抽樣的比例。例如，table總共分了4份，當y=2時，抽取(4/2=)2個bucket的資料，當y=8時，抽取(4/8=)1/2個bucket的資料。

x表示從哪個bucket開始抽取，如果需要取多個分區，以後的分區号為目前分區号加上y。例如，table總bucket數為4，tablesample(bucket 1 out of 2)，表示總共抽取（4/2=）2個bucket的資料，抽取第1(x)個和第3(x+y)個bucket的資料。

注意：x的值必須小于等于y的值，否則

FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck

7、其他常用查詢函數

7.1 空字段指派

函數說明

NVL：給值為NULL的資料指派，它的格式是NVL( string1, replace_with)。它的功能是如果string1為NULL，則NVL函數傳回replace_with的值，否則傳回string1的值，如果兩個參數都為NULL ，則傳回NULL。

2.資料準備：采用員工表

3.查詢：如果員工的comm為NULL，則用-1代替

hive > select nvl(comm,-1) from emp;
OK
_c0
20.0
300.0
500.0
-1.0
1400.0
-1.0
-1.0
-1.0
-1.0
0.0
-1.0
-1.0
-1.0
-1.0

4.查詢：如果員工的comm為NULL，則用上司id代替

hive > select nvl(comm,mgr) from emp;
OK
_c0
20.0
300.0
500.0
7839.0
1400.0
7839.0
7839.0
7566.0
NULL
0.0
7788.0
7698.0
7566.0

7.2 CASE WHEN

1. 資料準備

name	dept_id	sex
悟空	A	男
大海	A	男
宋宋	B	男
鳳姐	A	女
婷姐	B	女
婷婷	B	女

2．需求

求出不同部門男女各多少人。結果如下：

A     2       1
B     1       2

3．建立本地emp_sex.txt，導入資料

[root@master datas]$ vi emp_sex.txt

悟空 A 男
大海 A 男
宋宋 B 男
鳳姐 A 女
婷姐 B 女
婷婷 B 女

4．建立hive表并導入資料

create table emp_sex(
    name string,
    dept_id string,
    sex string
)
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/datas/emp_sex.txt' into table emp_sex;

5．按需求查詢資料

select
  dept_id,
  sum(case sex when '男' then 1 else 0 end) male_count,
  sum(case sex when '女' then 1 else 0 end) female_count
from
  emp_sex
group by
  dept_id;

7.2 行轉列

1．相關函數說明

CONCAT(string A/col, string B/col…)：傳回輸入字元串連接配接後的結果，支援任意個輸入字元串;

CONCAT_WS(separator, str1, str2,...)：它是一個特殊形式的 CONCAT()。第一個參數為剩餘參數間的分隔符。分隔符可以是與剩餘參數一樣的字元串。如果分隔符是 NULL，傳回值也将為 NULL。這個函數會跳過分隔符參數後的任何 NULL 和空字元串。分隔符将被加到被連接配接的字元串之間;

COLLECT_SET(col)：函數隻接受基本資料類型，它的主要作用是将某字段的值進行去重彙總，産生array類型字段。

2．資料準備

name	constellation	blood_type
孫悟空	白羊座	A
大海	射手座	A
宋宋	白羊座	B
豬八戒	白羊座	A
鳳姐	射手座	A

3．需求

把星座和血型一樣的人歸類到一起。結果如下：

射手座,A            大海|鳳姐
白羊座,A            孫悟空|豬八戒
白羊座,B            宋宋

4．建立本地constellation.txt，導入資料

[root@master datas]$ vim constellation.txt
孫悟空 白羊座 A
大海  射手座 A
宋宋  白羊座 B
豬八戒   白羊座 A
鳳姐  射手座 A

5．建立hive表并導入資料

create table person_info(
    name string, 
    constellation string, 
    blood_type string
) 
row format delimited fields terminated by "\t";
load data local inpath “/opt/module/datas/person_info.txt” into table person_info;

6．按需求查詢資料

select
    t1.base,
    concat_ws('|', collect_set(t1.name)) name
from
    (select
        name,
        concat(constellation, ",", blood_type) base
    from
        person_info) t1
group by
    t1.base;

7.3 列轉行

1．函數說明

EXPLODE(col)：将hive一列中複雜的array或者map結構拆分成多行。

LATERAL VIEW

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解釋：用于和split, explode等UDTF一起使用，它能夠将一列資料拆成多行資料，在此基礎上可以對拆分後的資料進行聚合。