目錄

一、前言

二、Hive select 完整語法樹

三、Hive select 操作演示

3.1 數(shù)據(jù)準(zhǔn)備

3.1.1 創(chuàng)建一張表

3.1.2 將數(shù)據(jù)load加載到t_usa_covid19表

3.1.3 再創(chuàng)建一張分區(qū)表

3.1.4 使用動(dòng)態(tài)分區(qū)插入數(shù)據(jù)

3.2 select 常用語法

3.2.1 查詢所有字段或者指定字段

3.2.2 查詢匹配正則表達(dá)式的所有字段

3.2.3 查詢當(dāng)前數(shù)據(jù)庫

3.2.4 查詢使用函數(shù)

3.2.5 使用函數(shù)

3.3 distinct關(guān)鍵字

3.3.1 查詢state字段并去重

3.3.2 多個(gè)字段distinct 整體去重

3.4 分區(qū)查詢、分區(qū)裁剪

3.5 GROUP BY

3.5.1 GROUP BY 概念

3.5.2 hive中 GROUP BY 使用限制

3.6 HAVING

3.7 limit

3.7.1 返回結(jié)果集的前5條

3.7.2 分頁查詢

3.8 HAVING與WHERE區(qū)別

3.9 select 語句中關(guān)鍵字順序總結(jié)

四、union?

4.1 操作演示

4.1.1 使用union查詢student_local和student_hdfs

4.1.2 使用ALL關(guān)鍵字會(huì)保留重復(fù)行

4.1.3 union之前的的表需要排序或者限制表的查詢數(shù)量

五、子查詢

5.1 where子句中子查詢

5.1.1 不相關(guān)子查詢

5.1.2 相關(guān)子查詢

六、CTE

6.1 操作演示

6.1.1 CTE結(jié)合insert使用

6.1.2 CTE 其他用法

七、join關(guān)聯(lián)查詢

7.1 hive join語法樹

關(guān)于語法樹中關(guān)鍵參數(shù)說明

7.2 join語法豐富化

7.2.1 隱式聯(lián)接表示法

7.2.2 非等值連接

7.3 hive join操作演示

7.3.1 數(shù)據(jù)準(zhǔn)備

7.3.2 加載數(shù)據(jù)到表中

7.3.3 inner join 內(nèi)連接

7.3.4?left join 左連接

7.3.5 right join 右連接

7.3.6 full outer join 全外連接

7.3.7 left semi join 左半開連接

7.3.8 cross join 交叉連接

7.3.9 關(guān)于 join使用 注意事項(xiàng)

八、寫在文末

---

一、前言

由于Hive是基于Hadoop的數(shù)據(jù)倉庫，是面向分析支持分析工具。將已有的結(jié)構(gòu)化數(shù)據(jù)文件映射成為表，然后提供SQL分析數(shù)據(jù)的能力。因此在Hive中常見的操作就是分析查詢select操作。

Hive早期是不支持update和delete語法的，因?yàn)镠ive所處理的數(shù)據(jù)都是已經(jīng)存在的的數(shù)據(jù)、歷史數(shù)據(jù)。后續(xù)Hive支持了相關(guān)的update和delete操作，不過有很多約束。詳見Hive事務(wù)的支持。

二、Hive select 完整語法樹

看hive的select語法樹，對(duì)于熟悉mysql的同學(xué)是不是覺得里面有些關(guān)鍵詞比較熟悉，在hive的select 語法中，尤其值得注意的就是from后面的內(nèi)容，表示從哪里獲取數(shù)據(jù)，可以是普通物理表、視圖、join結(jié)果或子查詢結(jié)果；

[WITH CommonTableExpression (, CommonTableExpression)*]
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 [offset,] rows];

表名和列名不區(qū)分大小寫（大小寫不敏感）

三、Hive select 操作演示

接下來通過實(shí)際的操作演示來體驗(yàn)下select語法的強(qiáng)大之處吧

3.1 數(shù)據(jù)準(zhǔn)備

在當(dāng)前的數(shù)據(jù)目錄下有數(shù)據(jù)數(shù)據(jù)文件，內(nèi)容如下，里面記錄了2021-01-28美國(guó)各個(gè)縣累計(jì)新冠確診病例數(shù)和累計(jì)死亡病例數(shù)；

3.1.1 創(chuàng)建一張表

為了演示方便，這里提前創(chuàng)建一張新的數(shù)據(jù)庫；

drop table if exists t_usa_covid19;
CREATE TABLE t_usa_covid19(count_date string,county string,state string,fips int,cases int,deaths int)
row format delimited fields terminated by ",";

3.1.2 將數(shù)據(jù)load加載到t_usa_covid19表

load data local inpath '/usr/local/soft/selectdata/us-covid19-counties.dat' into table t_usa_covid19;

執(zhí)行上面的加載命令；

3.1.3 再創(chuàng)建一張分區(qū)表

基于count_date日期,state州進(jìn)行分區(qū)

CREATE TABLE if not exists t_usa_covid19_p(county string,fips int,cases int,deaths int)
partitioned by(count_date string,state string)
row format delimited fields terminated by ",";

3.1.4 使用動(dòng)態(tài)分區(qū)插入數(shù)據(jù)

將數(shù)據(jù)導(dǎo)入t_usa_covid19_p中

set hive.exec.dynamic.partition.mode = nonstrict;

insert into table t_usa_covid19_p partition (count_date,state)
select county,fips,cases,deaths,count_date,state from t_usa_covid19;

執(zhí)行完成后檢查hdfs文件目錄，可以看到分區(qū)目錄已經(jīng)存在了；

3.2 select 常用語法

下面對(duì)select涉及到的常用操作語法結(jié)合實(shí)際操作進(jìn)行詳細(xì)的說明。

3.2.1 查詢所有字段或者指定字段

select * from t_usa_covid19_p;
select county, cases, deaths from t_usa_covid19_p;

3.2.2 查詢匹配正則表達(dá)式的所有字段

使用正則表達(dá)式需要開啟如下參數(shù)

SET hive.support.quoted.identifiers = none; --反引號(hào)不在解釋為其他含義，被解釋為正則表達(dá)式；

3.2.3 查詢當(dāng)前數(shù)據(jù)庫

select current_database(); --省去from關(guān)鍵字

3.2.4 查詢使用函數(shù)

select count(county) from t_usa_covid19_p;

3.2.5 使用函數(shù)

select state,count(deaths) from t_usa_covid19_p ?group by state having count(deaths) > 100;

3.3 distinct關(guān)鍵字

在hive中使用select xx from .. 時(shí)相當(dāng)于是?select all ...，即返回所有匹配的行；

select state from t_usa_covid19_p;

如果數(shù)據(jù)有重復(fù)的話，就可以考慮使用distinct關(guān)鍵字進(jìn)行去重處理；

3.3.1 查詢state字段并去重

select distinct state from t_usa_covid19_p;

3.3.2 多個(gè)字段distinct 整體去重

select ?county,state from t_usa_covid19_p;
select distinct county,state from t_usa_covid19_p;
select distinct sex from student;

3.4 分區(qū)查詢、分區(qū)裁剪

針對(duì)Hive分區(qū)表，在查詢時(shí)可以指定分區(qū)查詢，減少全表掃描，也叫做分區(qū)裁剪；

所謂分區(qū)裁剪指：對(duì)分區(qū)表進(jìn)行查詢時(shí)，會(huì)檢查WHERE子句或JOIN中的ON子句中是否存在對(duì)分區(qū)字段的過濾，如果存在，則僅訪問查詢符合條件的分區(qū)，即裁剪掉沒必要訪問的分區(qū)

需求：找出來自加州，累計(jì)死亡人數(shù)大于1000的縣

state字段就是分區(qū)字段 進(jìn)行分區(qū)裁剪 避免全表掃描

由于分區(qū)表創(chuàng)建的時(shí)候指定了多個(gè)字段，這里還可以使用多分區(qū)裁剪

select * from t_usa_covid19_p where count_date = "2021-01-28" and state ="California" and deaths > 1000;

3.5 GROUP BY

3.5.1 GROUP BY 概念

使用過mysql的同學(xué)對(duì)group by 應(yīng)該不陌生，即分組的意思，hive中GROUP BY語句用于結(jié)合聚合函數(shù)，根據(jù)一個(gè)或多個(gè)列對(duì)結(jié)果集進(jìn)行分組；

注意：出現(xiàn)在GROUP BY中select_expr的字段：要么是GROUP BY分組的字段；要么是被聚合函數(shù)應(yīng)用的字段

3.5.2 hive中 GROUP BY 使用限制

上面提到：出現(xiàn)在GROUP BY中select_expr的字段：要么是GROUP BY分組的字段；要么是被聚合函數(shù)應(yīng)用的字段，主要原因是：避免出現(xiàn)一個(gè)字段多個(gè)值的歧義；

• 分組字段出現(xiàn)select_expr中，一定沒有歧義，因?yàn)榫褪腔谠撟侄畏纸M的，同一組中必相同；
• 被聚合函數(shù)應(yīng)用的字段，也沒歧義，因?yàn)榫酆虾瘮?shù)的本質(zhì)就是多進(jìn)一出，最終返回一個(gè)結(jié)果；

如下圖，左邊為原始表數(shù)據(jù)，使用group by進(jìn)行分組，基于category進(jìn)行分組，相同顏色的分在同一組中。 在select_expr中，如果出現(xiàn)category字段，則沒有問題，因?yàn)橥唤M中category值一樣，但是返回day就有問題了，day的結(jié)果不一樣；

需求：根據(jù)state州進(jìn)行分組

select state,deaths from t_usa_covid19_p where count_date = "2021-01-28" group by state;

注意：執(zhí)行上面的sql會(huì)報(bào)錯(cuò)，因?yàn)閐eaths不是分組字段 報(bào)錯(cuò)，而state是分組字段 可以直接出現(xiàn)在select_expr中；

group by 被聚合函數(shù)應(yīng)用

select state,sum(deaths) from t_usa_covid19_p where count_date = "2021-01-28" group by state;

3.6 HAVING

在SQL中增加HAVING子句原因是，WHERE關(guān)鍵字無法與聚合函數(shù)一起使用。HAVING子句可以讓我們篩選分組后的各組數(shù)據(jù),并且可以在Having中使用聚合函數(shù)，因?yàn)榇藭r(shí)where，group by已經(jīng)執(zhí)行結(jié)束，結(jié)果集已經(jīng)確定。

需求：統(tǒng)計(jì)死亡病例數(shù)大于10000的州；

思路：先where分組前過濾（此處是分區(qū)裁剪），再進(jìn)行g(shù)roup by分組， 分組后每個(gè)分組結(jié)果集確定 再使用having過濾

select state,sum(deaths)
from t_usa_covid19_p
where count_date = "2021-01-28"
group by state
having sum(deaths) > 10000;

也可以像下面這樣寫

select state,sum(deaths) as cnts
from t_usa_covid19_p
where count_date = "2021-01-28"
group by state
having cnts> 10000;

?這樣寫更好 即在group by的時(shí)候聚合函數(shù)已經(jīng)作用得出結(jié)果 having直接引用結(jié)果過濾 不需要再單獨(dú)計(jì)算一次了

執(zhí)行上面的sql，結(jié)果是一樣的，時(shí)間上來說，似乎更快一點(diǎn)；

3.7 limit

limit就是限制查詢返回結(jié)果的數(shù)量，有時(shí)候我們并不希望一次性返回所有數(shù)據(jù)時(shí)就可以使用limit，這個(gè)就比較簡(jiǎn)單；

3.7.1 返回結(jié)果集的前5條

select * from t_usa_covid19_p
where count_date = "2021-01-28"
? and state ="California"
limit 5;

3.7.2 分頁查詢

查詢第1~3條數(shù)據(jù)

3.8 HAVING與WHERE區(qū)別

不少同學(xué)在HAVING與where使用的時(shí)候有點(diǎn)懵，這里簡(jiǎn)單總結(jié)下：

• having是在分組后對(duì)數(shù)據(jù)進(jìn)行過濾；
• where是在分組前對(duì)數(shù)據(jù)進(jìn)行過濾；
• having后面可以使用聚合函數(shù)；
• where后面不可以使用聚合函數(shù)；

3.9 select 語句中關(guān)鍵字順序總結(jié)

在查詢過程中執(zhí)行順序：from > where > group（含聚合）> having >order > select；

1、聚合語句(sum,min,max,avg,count)要比having子句優(yōu)先執(zhí)行；

2、where子句在查詢過程中執(zhí)行優(yōu)先級(jí)別優(yōu)先于聚合語句(sum,min,max,avg,count)

下面用一句sql總結(jié)下：

select state,sum(deaths) as cnts

from t_usa_covid19_p

where count_date = "2021-01-28"

group by state

having cnts> 10000;

四、union?

使用過mysql的同學(xué)應(yīng)該對(duì)union不陌生，當(dāng)需要對(duì)跨表數(shù)據(jù)進(jìn)行組合，或者不方便通過關(guān)聯(lián)查詢得到滿足結(jié)果的數(shù)據(jù)集的時(shí)候，就可以使用mysql的union查詢，在hive中也提供了union的語法；

UNION用于將來自于多個(gè)SELECT語句的結(jié)果合并為一個(gè)結(jié)果集

語法

select_statement ?? ?

????????UNION [ALL | DISTINCT]

select_statement

????????UNION [ALL | DISTINCT]

select_statement ...;

關(guān)于hive中的union做幾點(diǎn)說明：

• 使用DISTINCT關(guān)鍵字與只使用UNION默認(rèn)值效果一樣，都會(huì)刪除重復(fù)行。1.2.0之前的Hive版本僅支持UNION ALL，在這種情況下不會(huì)消除重復(fù)的行；
• 使用ALL關(guān)鍵字，不會(huì)刪除重復(fù)行，結(jié)果集包括所有SELECT語句的匹配行（包括重復(fù)行）；
• 每個(gè)select_statement返回的列的數(shù)量和名稱必須相同；

4.1 操作演示

4.1.1 使用union查詢student_local和student_hdfs

select num,name from student_local
UNION
select num,name from student_hdfs;

使用DISTINCT關(guān)鍵字與使用UNION默認(rèn)值效果一樣，都會(huì)刪除重復(fù)行?

4.1.2 使用ALL關(guān)鍵字會(huì)保留重復(fù)行

select num,name from student_local
UNION ALL
select num,name from student_hdfs limit 5;

執(zhí)行上面的sql，如果兩個(gè)表有相同的數(shù)據(jù)將會(huì)保留重復(fù)行

4.1.3 union之前的的表需要排序或者限制表的查詢數(shù)量

如果要將ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTE BY或LIMIT應(yīng)用于單個(gè)SELECT，請(qǐng)將子句放在括住SELECT的括號(hào)內(nèi)，如下：

SELECT num,name FROM (select num,name from student_local LIMIT 2) ?subq1
UNION
SELECT num,name FROM (select num,name from student_hdfs LIMIT 3) subq2;

如果要將ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTE BY或LIMIT子句應(yīng)用于整個(gè)UNION結(jié)果，請(qǐng)將ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTE BY或LIMIT放在最后一個(gè)之后，如下：

select num,name from student_local
UNION
select num,name from student_hdfs
order by num desc;

執(zhí)行結(jié)果如下：

五、子查詢

在上面的union最后就用到了子查詢，在Hive0.12版本，僅在FROM子句中支持子查詢，關(guān)于子查詢的特點(diǎn)如下：

• 必須要給子查詢一個(gè)名稱，因?yàn)镕ROM子句中的每個(gè)表都必須有一個(gè)名稱；
• 子查詢返回結(jié)果中的列必須具有唯一的名稱；
• 子查詢返回結(jié)果中的列在外部查詢中可用，就像真實(shí)表的列一樣；
• 子查詢也可以是帶有UNION的查詢表達(dá)式；
• Hive支持任意級(jí)別的子查詢，也就是所謂的嵌套子查詢（這點(diǎn)與mysql略有差異）；
• Hive 0.13.0和更高版本中的子查詢名稱之前可以包含可選關(guān)鍵字AS；

from子句中子查詢（Subqueries）

SELECT num

FROM (

????????select num,name from student_local

? ? ?) tmp;

包含UNION ALL的子查詢的示例

SELECT t3.name
FROM (
? ? ? ? ?select num,name from student_local
? ? ? ? ?UNION distinct
? ? ? ? ?select num,name from student_hdfs
? ? ?) t3;

5.1 where子句中子查詢

從Hive 0.13開始，WHERE子句支持下述類型的子查詢；

5.1.1 不相關(guān)子查詢

該子查詢不引用父查詢中的列，可以將查詢結(jié)果視為IN和NOT IN語句的常量；

執(zhí)行子查詢，其結(jié)果不被顯示，而是傳遞給外部查詢，作為外部查詢的條件使用。

如下面的sql：

SELECT *
FROM student_hdfs
WHERE student_hdfs.num IN (select num from student_local limit 2);

5.1.2 相關(guān)子查詢

子查詢引用父查詢中的列，子查詢的WHERE子句中支持對(duì)父查詢的引用；

如下面的sql：

SELECT A
FROM T1
WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2.Y);

六、CTE

CTE是一個(gè)臨時(shí)結(jié)果集，該結(jié)果集是從WITH子句中指定的簡(jiǎn)單查詢派生而來的，緊接在SELECT或INSERT關(guān)鍵字之前，關(guān)于cte的使用：

• CTE僅在單個(gè)語句的執(zhí)行范圍內(nèi)定義；
• CTE可以在 SELECT，INSERT， CREATE TABLE AS SELECT或CREATE VIEW AS SELECT語句中使用；

在hive的select語法樹中所處的位置如下圖：

6.1 操作演示

select語句中的CTE

with q1 as (select num,name,age from student where num = 95002)
select *
from q1;

from風(fēng)格的cte

with q1 as (select num,name,age from student where num = 95002)
from q1
select *;

鏈?zhǔn)斤L(fēng)格的cte

with q1 as ( select * from student where num = 95002),q2 as ( select num,name,age from q1)
select * from (select num from q2) a;

使用union的cte查詢

with q1 as (select * from student where num = 95002),q2 as (select * from student where num = 95004)
select * from q1 union all select * from q2;

6.1.1 CTE結(jié)合insert使用

可以直接將cte中的查詢結(jié)果插入到另一張表中，如下操作：

#創(chuàng)建一張待插入數(shù)據(jù)的表
create table s1 like student;#使用cte語法插入數(shù)據(jù)
with q1 as ( select * from student where num = 95002)
from q1
insert overwrite table s1
select *;#查詢數(shù)據(jù)
select * from s1;

6.1.2 CTE 其他用法

創(chuàng)建表來源于一個(gè)查詢結(jié)果集

create table s2 as
with q1 as ( select * from student where num = 95002)
select * from q1;

使用cte創(chuàng)建視圖

create view v1 as
with q1 as ( select * from student where num = 95002)
select * from q1;

七、join關(guān)聯(lián)查詢

使用mysql時(shí)，經(jīng)常涉及到多張表的關(guān)聯(lián)查詢，相信大家并不陌生，在Hive中也提供了多種join查詢，當(dāng)下版本3.1.2總共支持6種join語法，它們分別是：

• inner join（內(nèi)連接）；
• left join（左連接）；
• right join（右連接）；
• full outer join（全外連接）；
• left semi join（左半開連接）；
• cross join（交叉連接，也叫做笛卡爾乘積）；

7.1 hive join語法樹

join_table:

????????table_reference [INNER] JOIN table_factor [join_condition]

?????????| table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition

?????????| table_reference LEFT SEMI JOIN table_reference join_condition

?????????| table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10)

join_condition:

????????ON expression

關(guān)于語法樹中關(guān)鍵參數(shù)說明

table_reference

是join查詢中使用的表名，也可以是子查詢別名（查詢結(jié)果當(dāng)成表參與join）

table_factor

與table_reference相同,是聯(lián)接查詢中使用的表名,也可以是子查詢別名

join_condition

join查詢關(guān)聯(lián)的條件，如果在兩個(gè)以上的表上需要連接，則使用AND關(guān)鍵字

7.2 join語法豐富化

Hive中join語法從面世開始其實(shí)并不豐富，不像在RDBMS中那么靈活，從Hive 0.13.0開始，支持隱式聯(lián)接表示法（請(qǐng)參閱HIVE-5558）。允許FROM子句連接以逗號(hào)分隔的表列表，而省略JOIN關(guān)鍵字，從Hive 2.2.0開始，支持ON子句中的復(fù)雜表達(dá)式，支持不相等連接（請(qǐng)參閱HIVE-15211和HIVE-15251）。在此之前，Hive不支持不是相等條件的聯(lián)接條件。

7.2.1 隱式聯(lián)接表示法

SELECT *
FROM table1 t1, table2 t2, table3 t3
WHERE t1.id = t2.id AND t2.id = t3.id AND t1.zipcode = '02535';

7.2.2 非等值連接

SELECT a.* FROM a JOIN b ON (a.id = b.id)
SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)
SELECT a.* FROM a LEFT OUTER JOIN b ON (a.id <> b.id)

7.3 hive join操作演示

7.3.1 數(shù)據(jù)準(zhǔn)備

提前創(chuàng)建3張表并且加載數(shù)據(jù)到表中

--table1: 員工表
CREATE TABLE employee(id int,name string,deg string,salary int,dept string) row format delimited
fields terminated by ',';--table2:員工家庭住址信息表
CREATE TABLE employee_address (id int,hno string,street string,city string
) row format delimited
fields terminated by ',';--table3:員工聯(lián)系方式信息表
CREATE TABLE employee_connection (id int,phno string,email string
) row format delimited
fields terminated by ',';

執(zhí)行上面的sql完成表的創(chuàng)建

7.3.2 加載數(shù)據(jù)到表中

load data local inpath '/usr/local/soft/hivedata/employee.txt' into table employee;

load data local inpath '/usr/local/soft/hivedata/employee_address.txt' into table employee_address;

load data local inpath '/usr/local/soft/hivedata/employee_connection.txt' into table employee_connection;

7.3.3 inner join 內(nèi)連接

• 內(nèi)連接是最常見的一種連接，它也被稱為普通連接，其中inner可以省略：inner join == join ；
• 只有進(jìn)行連接的兩個(gè)表中都存在與連接條件相匹配的數(shù)據(jù)才會(huì)被留下來；

員工表與員工住址表關(guān)聯(lián)查詢

select e.id,e.name,e_a.city,e_a.street
from employee e inner join employee_address e_a
on e.id =e_a.id;

等價(jià)于

select e.id,e.name,e_a.city,e_a.street
from employee e join employee_address e_a
on e.id =e_a.id;

等價(jià)于 隱式連接表示法

select e.id,e.name,e_a.city,e_a.street
from employee e , employee_address e_a
where e.id =e_a.id;

7.3.4?left join 左連接

• left join中文叫做是左外連接(Left Outer Join)或者左連接，其中outer可以省略，left outer join是早期的寫法；
• left join的核心就在于left左。左指的是join關(guān)鍵字左邊的表，簡(jiǎn)稱左表；
• 通俗解釋：join時(shí)以左表的全部數(shù)據(jù)為準(zhǔn)，右邊與之關(guān)聯(lián)；左表數(shù)據(jù)全部返回，右表關(guān)聯(lián)上的顯示返回，關(guān)聯(lián)不上的顯示null返回；

員工表和員工聯(lián)系方式表關(guān)聯(lián)查詢

select e.id,e.name,e_conn.phno,e_conn.email
from employee e left join employee_connection e_conn
on e.id =e_conn.id;

等價(jià)于查詢?left outer join

select e.id,e.name,e_conn.phno,e_conn.email
from employee e left outer join ?employee_connection e_conn
on e.id =e_conn.id;

7.3.5 right join 右連接

• right join中文叫做是右外連接(Right Outer Jion)或者右連接，其中outer可以省略；
• right join的核心就在于Right右。右指的是join關(guān)鍵字右邊的表，簡(jiǎn)稱右表；
• 通俗解釋：join時(shí)以右表的全部數(shù)據(jù)為準(zhǔn)，左邊與之關(guān)聯(lián)；右表數(shù)據(jù)全部返回，左表關(guān)聯(lián)上的顯示返回，關(guān)聯(lián)不上的顯示null返回；
• 很明顯，right join和left join之間很相似，重點(diǎn)在于以哪邊為準(zhǔn)，也就是一個(gè)方向的問題；

員工表和員工聯(lián)系方式表關(guān)聯(lián)查詢

select e.id,e.name,e_conn.phno,e_conn.email
from employee e right join employee_connection e_conn
on e.id =e_conn.id;

等價(jià)于 right outer join

select e.id,e.name,e_conn.phno,e_conn.email
from employee e right outer join employee_connection e_conn
on e.id =e_conn.id;

7.3.6 full outer join 全外連接

• full outer join 等價(jià) full join ?,中文叫做全外連接或者外連接；
• 包含左、右兩個(gè)表的全部行，不管另外一邊的表中是否存在與它們匹配的行；
• 在功能上：等價(jià)于對(duì)這兩個(gè)數(shù)據(jù)集合分別進(jìn)行左外連接和右外連接，然后再使用消去重復(fù)行的操作將上述兩個(gè)結(jié)果集合并為一個(gè)結(jié)果集；

員工和員工住址表關(guān)聯(lián)查詢

select e.id,e.name,e_a.city,e_a.street
from employee e full outer join employee_address e_a
on e.id =e_a.id;

等價(jià)于

select e.id,e.name,e_a.city,e_a.street
from employee e full ?join employee_address e_a
on e.id =e_a.id;

7.3.7 left semi join 左半開連接

• 左半開連接（LEFT SEMI JOIN）會(huì)返回左邊表的記錄，前提是其記錄對(duì)于右邊的表滿足ON語句中的判定條件；
• 從效果上來看有點(diǎn)像inner join之后只返回左表的結(jié)果；

員工和員工住址表關(guān)聯(lián)查詢

select *
from employee e left semi join employee_address e_addr
on e.id =e_addr.id;

相當(dāng)于 inner join,但是只返回左表全部數(shù)據(jù)， 只不過效率高一些

select e.*
from employee e inner join employee_address e_addr
on e.id =e_addr.id;

7.3.8 cross join 交叉連接

• 交叉連接cross join，將會(huì)返回被連接的兩個(gè)表的笛卡爾積，返回結(jié)果的行數(shù)等于兩個(gè)表行數(shù)的乘積。對(duì)于大表來說，cross join慎用；
• 在SQL標(biāo)準(zhǔn)中定義的cross join就是無條件的inner join。返回兩個(gè)表的笛卡爾積,無需指定關(guān)聯(lián)鍵；
• 在HiveSQL語法中，cross join 后面可以跟where子句進(jìn)行過濾，或者on條件過濾；

下列A、B、C 執(zhí)行結(jié)果相同，但是效率不一樣：

--A:
select a.*,b.* from employee a,employee_address b where a.id=b.id;

--B:
select * from employee a cross join employee_address b on a.id=b.id;
select * from employee a cross join employee_address b where a.id=b.id;

--C:
select * from employee a inner join employee_address b on a.id=b.id;

一般不建議使用方法A和B，因?yàn)槿绻蠾HERE子句的話，往往會(huì)先生成兩個(gè)表行數(shù)乘積的行的數(shù)據(jù)表然后才根據(jù)WHERE條件從中選擇。因此，如果兩個(gè)需要求交集的表太大，將會(huì)非常非常慢，不建議使用。

7.3.9 關(guān)于 join使用 注意事項(xiàng)

• 允許使用復(fù)雜的聯(lián)接表達(dá)式,支持非等值連接；
• 同一查詢中可以連接2個(gè)以上的表；
• 如果每個(gè)表在聯(lián)接子句中使用相同的列，則Hive將多個(gè)表上的聯(lián)接轉(zhuǎn)換為單個(gè)MR作業(yè)；
• join時(shí)的最后一個(gè)表會(huì)通過reducer流式傳輸，并在其中緩沖之前的其他表，因此，將大表放置在最后有助于減少reducer階段緩存數(shù)據(jù)所需要的內(nèi)存；
• 在join的時(shí)候，可以通過語法STREAMTABLE提示指定要流式傳輸?shù)谋?。如果省略STREAMTABLE提示，則Hive將流式傳輸最右邊的表；
• join在WHERE條件之前進(jìn)行；
• 如果除一個(gè)要連接的表之外的所有表都很小，則可以將其作為僅map作業(yè)執(zhí)行（mapjoin）；

八、寫在文末

查詢操作對(duì)于Hive來說，不管是日常開發(fā)還是生產(chǎn)運(yùn)維，都具有重要的意義，因此熟練掌握Hive的查詢相關(guān)的語法和操作非常重要，希望對(duì)看到的伙伴有用。