查詢(xún)基本語(yǔ)法
[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]
數(shù)據(jù)準(zhǔn)備

create database test;
use test;

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

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

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

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

create table emp_sex(
name string, 
dept_id string, 
sex string) 
row format delimited fields terminated by "\t";

create table movie_info(
movie string, 
category array<string>) 
row format delimited fields terminated by "\t"
collection items terminated by ",";

create table business(
name string, 
orderdate string,
cost int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

create table score(
name string,
subject string, 
score int) 
row format delimited fields terminated by "\t";


load data local inpath '/opt/module/datas/dept.txt' into table test.dept;
load data local inpath '/opt/module/datas/emp.txt' into table test.emp;
load data local inpath '/opt/module/datas/location.txt' into table test.location;
load data local inpath '/opt/module/datas/student.txt' into table stu;
load data local inpath '/opt/module/datas/emp_sex.txt' into table emp_sex;
load data local inpath '/opt/module/datas/movie.txt' into table movie_info;
load data local inpath "/opt/module/datas/business.txt" into table business;

dept.txt內(nèi)容如下

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

emp.txt內(nèi)容如下

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

location.txt內(nèi)容如下

1700    Beijing
1800    London
1900    Tokyo

student.txt內(nèi)容如下

6       zhao`
7       jun
8       feng
9       xiang
10      bin

movie.txt內(nèi)容如下

無(wú)雙  劇情,動(dòng)作,犯罪
找到你 劇情
影   劇情,動(dòng)作,古裝
嗝嗝老師 Hichki 劇情,喜劇
李茶的姑媽   喜劇
胖子行動(dòng)隊(duì)   喜劇,動(dòng)作
西虹市首富   喜劇
黃金兄弟    動(dòng)作,犯罪

business內(nèi)容如下

jack    2018-01-01  10
tony    2018-01-02  15
jack    2018-02-03  23
tony    2018-01-04  29
jack    2018-01-05  46
jack    2018-04-06  42
tony    2018-01-07  50
jack    2018-01-08  55
mart    2018-04-08  62
mart    2018-04-09  68
neil    2018-05-10  12
mart    2018-04-11  75
neil    2018-06-12  80
mart    2018-04-13  94

基本查詢(xún)

全表和特定列查詢(xún)

-- 全表查詢(xún)
select * from emp;
select * from dept;
-- 選擇特定列查詢(xún)
 select dptno, dname from dept;

注意

1. SQL 語(yǔ)言大小寫(xiě)不敏感。
2. SQL 可以寫(xiě)在一行或者多行
3. 關(guān)鍵字不能被縮寫(xiě)也不能分行
4. 各子句一般要分行寫(xiě)姥份。
5. 使用縮進(jìn)提高語(yǔ)句的可讀性唧席。

列別名

重命名一個(gè)列钾军，便于計(jì)算，緊跟列名窗怒，也可以在列名和別名之間加入關(guān)鍵字‘AS’

select dptno as no, dname name from dept;

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

select sal, sal * 1.3 from emp;

常用函數(shù)

-- 求總行數(shù)（count）
select count(empno) from emp;
-- 求工資的最大值（max）
select max(sal) from emp;
-- 求工資的最小值（min）
select min(sal) from emp;
-- 求工資的總和（sum）
select sum(sal) from emp;
-- 求工資的平均值（avg）
select avg(sal) from emp;

limit語(yǔ)句

典型的查詢(xún)會(huì)返回多行數(shù)據(jù)映跟。LIMIT子句用于限制返回的行數(shù)。

select * from emp limit 5;

Where語(yǔ)句

使用WHERE子句扬虚，將不滿(mǎn)足條件的行過(guò)濾掉努隙，WHERE子句緊隨FROM子句

 select * from emp where sal > 2000;

比較運(yùn)算符

下表描述了謂詞操作符，這些操作符同樣可以用于JOIN…ON和HAVING語(yǔ)句中

-- 查詢(xún)出薪水等于5000的所有員工
select * from emp where sal = 5000;
-- 查詢(xún)工資在500到1000的員工信息
select * from emp where sal between 500 and 1000;
-- 查詢(xún)comm為空的所有員工信息
select * from emp where comm is null;
-- 查詢(xún)工資是1500和5000的員工信息
select * from emp where sal in(1500,5000);
select * from emp where sal = 1500 or sal = 5000;

Like和Rlike

使用LIKE運(yùn)算選擇類(lèi)似的值，選擇條件可以包含字符或數(shù)字匾竿，% 代表零個(gè)或多個(gè)字符(任意個(gè)字符)瓦宜，_ 代表一個(gè)字符。
RLIKE子句是Hive中這個(gè)功能的一個(gè)擴(kuò)展岭妖，其可以通過(guò)Java的正則表達(dá)式這個(gè)更強(qiáng)大的語(yǔ)言來(lái)指定匹配條件临庇。

-- 查找以2開(kāi)頭薪水的員工信息
select * from emp where sal like '2%';
-- 查找第二個(gè)數(shù)值為2的薪水的員工信息
select * from emp where sal like'_2%';
--查找薪水中含有2的員工信息

邏輯運(yùn)算符

-- 查詢(xún)薪水大于1000，部門(mén)是30
select * from emp where sal > 1000 and deptno = 30;
-- 查詢(xún)薪水大于1000昵慌，或者部門(mén)是30
select * from emp where sal > 1000 or deptno = 30;
-- 查詢(xún)除了20部門(mén)和30部門(mén)以外的員工信息
select * from emp where deptno not in (20,30);

分組

Group By語(yǔ)句

GROUP BY語(yǔ)句通常會(huì)和聚合函數(shù)一起使用假夺，按照一個(gè)或者多個(gè)列隊(duì)結(jié)果進(jìn)行分組，然后對(duì)每個(gè)組執(zhí)行聚合操作废离。

-- 計(jì)算emp表每個(gè)部門(mén)的平均工資
select avg(sal) from emp group by deptno;
-- 計(jì)算emp每個(gè)部門(mén)中每個(gè)崗位的最高薪水
select max(sal) from emp group by deptno, job;

Having語(yǔ)句

having與where不同點(diǎn)

1. where針對(duì)表中的列發(fā)揮作用侄泽，查詢(xún)數(shù)據(jù)；having針對(duì)查詢(xún)結(jié)果中的列發(fā)揮作用蜻韭，篩選數(shù)據(jù)。
2. where后面不能寫(xiě)分組函數(shù)柿扣，而having后面可以使用分組函數(shù)肖方。
3. having只用于group by分組統(tǒng)計(jì)語(yǔ)句。

-- 部門(mén)的平均薪水大于2000的部門(mén)
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

Join語(yǔ)句

等值join

Hive支持通常的SQL JOIN語(yǔ)句未状，但是只支持等值連接俯画，不支持非等值連接。

-- 查詢(xún)員工編號(hào)司草、員工名稱(chēng)艰垂、部門(mén)編號(hào)和部門(mén)名稱(chēng)
select e.empno, e.ename,d.deptno,d.dname from emp e join dept d on e.deptno = d.deptno;

表的別名

好處

1. 使用別名可以簡(jiǎn)化查詢(xún)
2. 使用表名前綴可以提高執(zhí)行效率

內(nèi)連接

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

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

左外連接

左外連接：JOIN操作符左邊表中符合WHERE子句的所有記錄將會(huì)被返回

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

右外連接

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

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

滿(mǎn)外連接

滿(mǎn)外連接：將會(huì)返回所有表中符合WHERE語(yǔ)句條件的所有記錄。如果任一表的指定字段沒(méi)有符合條件的值的話(huà)埋虹，那么就使用NULL值替代

多表鏈接

注意：連接 n個(gè)表猜憎，至少需要n-1個(gè)連接條件。例如：連接三個(gè)表搔课，至少需要兩個(gè)連接條件胰柑。

select e.empno,e.ename,d.deptno,d.dname,l.loc_name from emp e join dept d on e.deptno = d.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í)行的踩官。

笛卡爾積

笛卡爾積會(huì)在下面條件下產(chǎn)生

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

鏈接謂詞不支持or

-- 錯(cuò)誤的sql
select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno
= d.deptno or e.ename=d.ename;

排序

全局排序order by

Order By：全局排序却桶，一個(gè)MapReduce
ASC（ascend）: 升序（默認(rèn)），DESC（descend）: 降序
ORDER BY 子句在SELECT語(yǔ)句的結(jié)尾

-- 查詢(xún)員工信息按工資升序排列
select * from emp order by sal;
-- 查詢(xún)員工信息按工資降序排列
select * from emp order by sal desc;
-- 按照員工薪水的2倍排序
select sal, sal * 2 doublesal from emp order by doublesal;
-- 按照部門(mén)和工資升序排序
select * from emp order by deptno,sal;

內(nèi)部排序sort by

Sort By蔗牡，每個(gè)MapReduce內(nèi)部進(jìn)行排序颖系，對(duì)全局結(jié)果集來(lái)說(shuō)不是排序

-- 設(shè)置reduce個(gè)數(shù)
set mapreduce.job.reduces=3;
-- 根據(jù)部門(mén)編號(hào)降序查看員工信息
select * from emp sort by deptno desc;
-- 將查詢(xún)結(jié)果導(dǎo)入到文件中（按照部門(mén)編號(hào)降序排序）
insert overwrite local directory '/opt/module/datas/sort_result' row format delimited fields terminated by '\t' select * from emp sort by deptno desc;

分區(qū)排序distribute by

Distribute By,類(lèi)似MR中partition，進(jìn)行分區(qū)蛋逾，結(jié)合sort by使用
注意集晚，Hive要求DISTRIBUTE BY語(yǔ)句要寫(xiě)在SORT BY語(yǔ)句之前

-- 先按照部門(mén)編號(hào)分區(qū)，再按照員工編號(hào)降序排序
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;

Cluster By

當(dāng)distribute by和sorts by字段相同時(shí)区匣，可以使用cluster by方式偷拔。
cluster by除了具有distribute by的功能外還兼具sort by的功能。但是排序只能是倒序排序亏钩，不能指定排序規(guī)則為ASC或者DESC

insert overwrite local directory '/opt/module/datas/cluster_result' row format delimited fields terminated by '\t' select * from emp cluster by deptno;

分桶及抽樣查詢(xún)

分區(qū)針對(duì)的是數(shù)據(jù)的存儲(chǔ)路徑莲绰；分桶針對(duì)的是數(shù)據(jù)文件。
分區(qū)提供一個(gè)隔離數(shù)據(jù)和優(yōu)化查詢(xún)的便利方式姑丑。不過(guò)蛤签，并非所有的數(shù)據(jù)集都可形成合理的分區(qū)，特別是之前所提到過(guò)的要確定合適的劃分大小這個(gè)疑慮
分桶是將數(shù)據(jù)集分解成更容易管理的若干部分的另一個(gè)技術(shù)

分桶表數(shù)據(jù)存儲(chǔ)

-- 設(shè)置相關(guān)參數(shù)
set hive.enforce.bucketing=true;
set mapreduce.job.reduces=-1;
-- 通過(guò)查詢(xún)的方式插入數(shù)據(jù)
insert into table stu_buck select * from stu;
-- 查詢(xún)數(shù)據(jù)
select * from stu_buck;

分桶表數(shù)據(jù)存儲(chǔ).png

分桶表數(shù)據(jù)查詢(xún).png

分桶抽樣查詢(xún)

對(duì)于非常大的數(shù)據(jù)集栅哀，有時(shí)用戶(hù)需要使用的是一個(gè)具有代表性的查詢(xún)結(jié)果而不是全部結(jié)果震肮。Hive可以通過(guò)對(duì)表進(jìn)行抽樣來(lái)滿(mǎn)足這個(gè)需求。
查詢(xún)表stu_buck中的數(shù)據(jù)

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

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ù)。
x表示從哪個(gè)bucket開(kāi)始抽取谈火，如果需要取多個(gè)分區(qū)侈询，以后的分區(qū)號(hào)為當(dāng)前分區(qū)號(hào)加上y。例如堆巧，table總bucket數(shù)為4妄荔，tablesample(bucket 1 out of 2)泼菌，表示總共抽取（4/2=）2個(gè)bucket的數(shù)據(jù)啦租，抽取第1(x)個(gè)和第4(x+y)個(gè)bucket的數(shù)據(jù)哗伯。
注意：x的值必須小于等于y的值，否則

分桶抽樣查詢(xún)x小于y.png

其他常用查詢(xún)函數(shù)

空字段賦值

NVL：給值為NULL的數(shù)據(jù)賦值篷角，它的格式是NVL( string1, replace_with)焊刹。它的功能是如果string1為NULL，則NVL函數(shù)返回replace_with的值恳蹲，否則返回string1的值虐块，如果兩個(gè)參數(shù)都為NULL ，則返回NULL

select nvl(comm, -1) from emp;
select nvl(comm, mgr) from emp;
select nvl(comm, ename) from emp;

CASE WHEN

求出不同部門(mén)男女各多少人嘉蕾。結(jié)果如下

A  2  2
B  4  2

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;

結(jié)果如下

case when then else end.png

行轉(zhuǎn)列

CONCAT(string A/col, string B/col…)：返回輸入字符串連接后的結(jié)果贺奠，支持任意個(gè)輸入字符串;
CONCAT_WS(separator, str1, str2,...)：它是一個(gè)特殊形式的 CONCAT()。第一個(gè)參數(shù)剩余參數(shù)間的分隔符错忱。分隔符可以是與剩余參數(shù)一樣的字符串儡率。如果分隔符是 NULL，返回值也將為 NULL以清。這個(gè)函數(shù)會(huì)跳過(guò)分隔符參數(shù)后的任何 NULL 和空字符串儿普。分隔符將被加到被連接的字符串之間;
COLLECT_SET(col)：函數(shù)只接受基本數(shù)據(jù)類(lèi)型，它的主要作用是將某字段的值進(jìn)行去重匯總掷倔，產(chǎn)生array類(lèi)型字段眉孩。
需求，把部門(mén)和性別相同的人歸類(lèi)到一起勒葱，結(jié)果如下

 A,男  唐僧|孫悟空|豬八戒|沙悟凈
B,女  玉兔精|蜘蛛精
B,男  金角大王|銀角大王

select temp.id_sex,concat_ws('|',collect_set(temp.name)) from(
select name, dept_id, sex, concat(dept_id, ',', sex) as id_sex from emp_sex 
)temp
group by temp.id_sex;

列轉(zhuǎn)行

EXPLODE(col)：將hive一列中復(fù)雜的array或者map結(jié)構(gòu)拆分成多行浪汪。
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解釋?zhuān)河糜诤蛃plit, explode等UDTF一起使用，它能夠?qū)⒁涣袛?shù)據(jù)拆成多行數(shù)據(jù)凛虽，在此基礎(chǔ)上可以對(duì)拆分后的數(shù)據(jù)進(jìn)行聚合
需求吟宦，將電影分類(lèi)中的數(shù)組數(shù)據(jù)展開(kāi)

select movie,cate
from movie_info
lateral view explode(category) tableAlias as cate;

窗口函數(shù)

相關(guān)函數(shù)說(shuō)明

OVER()：指定分析函數(shù)工作的數(shù)據(jù)窗口大小，這個(gè)數(shù)據(jù)窗口大小可能會(huì)隨著行的變而變化
CURRENT ROW：當(dāng)前行
n PRECEDING：往前n行數(shù)據(jù)
n FOLLOWING：往后n行數(shù)據(jù)
UNBOUNDED：起點(diǎn)涩维，UNBOUNDED PRECEDING 表示從前面的起點(diǎn)，UNBOUNDED FOLLOWING表示到后面的終點(diǎn)
LAG(col,n)：往前第n行數(shù)據(jù)
LEAD(col,n)：往后第n行數(shù)據(jù)
NTILE(n)：把有序分區(qū)中的行分發(fā)到指定數(shù)據(jù)的組中袁波，各個(gè)組有編號(hào)瓦阐，編號(hào)從1開(kāi)始，對(duì)于每一行篷牌，NTILE返回此行所屬的組的編號(hào)睡蟋。注意：n必須為int類(lèi)型

需求

1. 查詢(xún)?cè)?018年4月份購(gòu)買(mǎi)過(guò)的顧客及總?cè)藬?shù)
2. 查詢(xún)顧客的購(gòu)買(mǎi)明細(xì)及月購(gòu)買(mǎi)總額
3. 上述的場(chǎng)景,要將cost按照日期進(jìn)行累加
4. 查詢(xún)顧客上次的購(gòu)買(mǎi)時(shí)間
5. 查詢(xún)前20%時(shí)間的訂單信息

對(duì)應(yīng)sql

-- 查詢(xún)?cè)?018年4月份購(gòu)買(mǎi)過(guò)的顧客及總?cè)藬?shù)
select name,count(*) over()
from business
where orderdate like '2018-04-%'
group by name;
-- 查詢(xún)顧客的購(gòu)買(mǎi)明細(xì)及月購(gòu)買(mǎi)總額
select name, orderdate, cost, sum(cost) over(distribute by month(orderdate))
from business;
-- 查詢(xún)顧客的購(gòu)買(mǎi)明細(xì)及月購(gòu)買(mǎi)總額，將cost按照日期進(jìn)行累加
select name, orderdate, cost, 
sum(cost) over() as sumcost1,--所有行相加
sum(cost) over(distribute by month(orderdate)) as sumcost2, --按月分組枷颊，組內(nèi)數(shù)據(jù)相加
sum(cost) over(distribute by month(orderdate) sort by orderdate) as sumcost3, -- 按月分組戳杀，組內(nèi)數(shù)據(jù)累加
sum(cost) over(distribute by month(orderdate) sort by orderdate rows between unbounded preceding and current row) as sumcost4, --和sumcost3一樣,由起點(diǎn)到當(dāng)前行的聚合
sum(cost) over(distribute by month(orderdate) sort by orderdate rows between 1 preceding and current row) as sumcost5, -- 當(dāng)前行和前一行做集合
sum(cost) over(distribute by month(orderdate) sort by orderdate rows between 1 preceding and 1 following) as sumcost6, -- 當(dāng)前行前面一行该面，當(dāng)前行，當(dāng)前行后面一行做聚合
sum(cost) over(distribute by month(orderdate) sort by orderdate rows between current row and unbounded following) as sumcost7 -- 當(dāng)前行及后面所有行
from business;
-- 查詢(xún)顧客上次購(gòu)買(mǎi)時(shí)間
select name, orderdate, cost,
lag(orderdate,1) over(distribute by name sort by orderdate) prev_time
from business;
select name, orderdate, cost,
lag(orderdate,1,'1900-01-01') over(distribute by name sort by orderdate) prev_time
from business;
-- 查詢(xún)前20%時(shí)間的訂單信息
select * from
(select name, orderdate, cost, ntile(5) over(sort by orderdate) as sorted from business
)tmp
where tmp.sorted = 1;

Rank

相關(guān)函數(shù)說(shuō)明

RANK() 排序相同時(shí)會(huì)重復(fù)信卡，總數(shù)不會(huì)變
DENSE_RANK()排序相同時(shí)會(huì)重復(fù)隔缀，總數(shù)會(huì)減少
ROW_NUMBER() 會(huì)根據(jù)順序計(jì)算

需求

計(jì)算每門(mén)學(xué)科成績(jī)排名

對(duì)應(yīng)sql

select name, subject, score,
rank() over(distribute by subject sort by score desc) as rk,
dense_rank() over(distribute by subject sort by score desc) as dr,
row_number() over(distribute by subject sort by score desc) as rn
from score;