HIVE查詢語法案例

查詢語句語法：

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [HAVING col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT number]

1、基本查詢（Select…From）

1）全表和特定列查詢

（0）數據準備

（0）原始數據

dept:

10	ACCOUNTING	1700
20	RESEARCH	1800
30	SALES	1900
40	OPERATIONS	1700


emp：

7369	SMITH	CLERK	7902	1980-12-17	800.00		20
7499	ALLEN	SALESMAN	7698	1981-2-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-2-22	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-4-2	2975.00		20
7654	MARTIN	SALESMAN	7698	1981-9-28	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1981-5-1	2850.00		30
7782	CLARK	MANAGER	7839	1981-6-9	2450.00		10
7788	SCOTT	ANALYST	7566	1987-4-19	3000.00		20
7839	KING	PRESIDENT		1981-11-17	5000.00		10
7844	TURNER	SALESMAN	7698	1981-9-8	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-5-23	1100.00		20
7900	JAMES	CLERK	7698	1981-12-3	950.00		30
7902	FORD	ANALYST	7566	1981-12-3	3000.00		20
7934	MILLER	CLERK	7782	1982-1-23	1300.00		10

（1）建立部門表

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

（2）建立員工表

create table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string, 
sal double, 
comm double,
deptno int)
row format delimited fields terminated by '\t'；

（3）匯入數據

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

（1）全表查詢

hive (default)> select * from emp;

（2）選擇特定列查詢

hive (default)> select empno, ename from emp;

注意：

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

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

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

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

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

2）列別名

（1）重新命名一個列

（2）便於計算

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

（4）案例實操

查詢名稱和部門

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

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 (default)> select sal +1 from emp;

4）常用函數

(1）求總行數（count）

hive (default)> select count(*) 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;

5)Limit語句

典型的查詢會返回多行數據。LIMIT子句用於限制返回的行數。

hive (default)> select * from emp limit 5;  //查詢前面5條數據
hive (default)> select * from emp limit 2,3;  // 表示查詢第三條到第五條的數據

6）Where語句

（1）使用WHERE子句，將不滿足條件的行過濾掉

（2）WHERE子句緊隨FROM子句

（3）案例實操

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

hive (default)> select * from emp where sal >1000;

注意：where子句中不能使用欄位別名。

7）比較運算子（Between/In/ Is Null）

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

操作符	支援的數據型別	描述
A=B	基本數據型別	如果A等於B則返回TRUE，反之返回FALSE
A<=>B	基本數據型別	如果A和B都爲NULL，則返回TRUE，如果一邊爲NULL，返回False
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是基於java的正則表達式，如果A與其匹配，則返回TRUE；反之返回FALSE。匹配使用的是JDK中的正則表達式介面實現的，因爲正則也依據其中的規則。例如，正則表達式必須和整個字串A相匹配，而不是隻需與其字串匹配。

（2）案例實操

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

hive (default)> select * from emp where sal =5000;

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

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

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

hive (default)> select * from emp where comm is null;

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

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

8） Like和RLike

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

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

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

_ 代表一個字元。

（3）RLIKE子句

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

（4）案例實操

（1）查詢名字以A開頭的員工資訊

hive (default)> select * from emp where ename LIKE 'A%';

hive (default)> select * from emp where ename RLIKE '^A';

（2）查詢名字中第二個字母爲A的員工資訊

hive (default)> select * from emp where ename LIKE '_A%';

hive (default)> select * from emp where ename RLIKE '^.A';

（3）查詢名字中帶有A的員工資訊

hive (default)> select * from emp where ename  LIKE '%A%';

hive (default)> select * from emp where ename  RLIKE '[A]';

9）邏輯運算子（And/Or/Not）

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

（1）案例實操

（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);

2、分組

1）Group By語句

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

（1）案例實操：

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

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

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

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

2）Having語句

（1）having與where不同點

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

（2）having只用於group by分組統計語句。

（2）案例實操

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

求每個部門的平均工資

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

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

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

 avg_sal > 2000;

3、 Join語句

1）等值Join

Hive支援通常的SQL JOIN語句，但是隻支援等值連線，不支援非等值連線。（這個版本支援）

（1）案例實操

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

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

2）表的別名

（1）好處

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

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

（2）案例實操

合併員工表和部門表

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

3）內連線

內連線：只有進行連線的兩個表中都存在與連線條件相匹配的數據纔會被保留下來。

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

4）左外連線

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

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

5）右外連線

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

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

6）滿外連線

滿外連線：將會返回所有表中符合WHERE語句條件的所有記錄。如果任一表的指定欄位沒有符合條件的值的話，那麼就使用NULL值替代。

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

7）多表連線

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

數據準備在/opt/module/hive/datas/下：vim location.txt

1700	Beijing
1800	London
1900	Tokyo

（1）建立位置表

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

（2）匯入數據

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

（3）多表連線查詢

hive (default)>SELECT e.ename, d.dname, 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總是按照從左到右的順序執行的。

8）笛卡爾積

（1）笛卡爾集會在下面下麪條件下產生

（1）省略連線條件

（2）連線條件無效

（3）所有表中的所有行互相連線

（2）案例實操

hive (default)> select empno, dname from emp, dept;

4、排序

1）全域性排序（Order By）

Order By：全域性排序，只有一個Reducer

（1）使用ORDER BY子句排序

ASC（ascend）: 升序（預設）

DESC（descend）: 降序

（2）ORDER BY子句在SELECT語句的結尾

（3）案例實操

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

hive (default)> select * from emp order by sal;

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

hive (default)> select * from emp order by sal desc;

2）按照別名排序

按照員工薪水的2倍排序

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

3）多個列排序

按照部門和工資升序排序

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

4）每個Reduce內部排序（Sort By）

Sort By：對於大規模的數據集order by的效率非常低。在很多情況下，並不需要全域性排序，此時可以使用sort by。

Sort by爲每個reducer產生一個排序檔案。每個Reducer內部進行排序，對全域性結果集來說不是排序。

（mr根據每個鍵的雜湊值而分配到不同的reducer）

（1）設定reduce個數

hive (default)> set mapreduce.job.reduces=3;

（2）檢視設定reduce個數

hive (default)> set mapreduce.job.reduces;

（3）根據部門編號降序檢視員工資訊

hive (default)> select * from emp sort by deptno desc;

（4）將查詢結果匯入到檔案中（按照部門編號降序排序）

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

5）分割區（Distribute By）

Distribute By：在有些情況下，我們需要控制某個特定行應該到哪個reducer，通常是爲了進行後續的聚集操作。distribute by 子句可以做這件事。distribute by類似MR中partition（自定義分割區），進行分割區，結合sort by使用。

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

（1）案例實操：

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

hive (default)> set mapreduce.job.reduces=3;
hive (default)> insert overwrite local directory '/opt/module/hive/datas/distribute-result' select * from emp distribute by deptno sort by sal desc;

注意：

Ø distribute by的分割區規則是根據分割區欄位的hash碼與reduce的個數進行模除後，餘數相同的分到一個區。

Ø Hive要求DISTRIBUTE BY語句要寫在SORT BY語句之前。

Ø 演示完以後mapreduce.job.reduces的值要設定回1，否則下面下麪分割區or分桶表load跑mr的時候有可能會報錯

6）分割區排序（Cluster By）

當distribute by和sort by欄位相同時，可以使用cluster by方式。

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

（1）以下兩種寫法等價

hive (default)> select * from emp cluster by deptno;
hive (default)> select * from emp distribute by deptno sort by deptno;

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

1、基本查詢（Select…From）

1）全表和特定列查詢

（0）數據準備

（1）全表查詢

（2）選擇特定列查詢

2）列別名

3） 算術運算子

4）常用函數

(1）求總行數（count）

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

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

(4）求工資的總和（sum）

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

5)Limit語句

6）Where語句

7）比較運算子（Between/In/ Is Null）

8） Like和RLike

9）邏輯運算子（And/Or/Not）

2、分組

1）Group By語句

2）Having語句

3、 Join語句

1） 等值Join

2）表的別名

3） 內連線

4）左外連線

5）右外連線

6）滿外連線

7）多表連線

8）笛卡爾積

4、排序

1）全域性排序（Order By）

2）按照別名排序

3）多個列排序

4）每個Reduce內部排序（Sort By）

（1）設定reduce個數

（2）檢視設定reduce個數

（3）根據部門編號降序檢視員工資訊

（4）將查詢結果匯入到檔案中（按照部門編號降序排序）

5）分割區（Distribute By）

（1）案例實操：

6）分割區排序（Cluster By）

3）算術運算子

1）等值Join

3）內連線