1.基礎(chǔ)語法

1.1正則匹配

需要現(xiàn)開啟正則SET hive.support.quoted.identifiers = none;反引號(hào)不在解釋為其他含義，被解釋為正則表達(dá)式

--查詢匹配正則表達(dá)式的所有字段（c開頭的庫，任意表）
SET hive.support.quoted.identifiers = none; --反引號(hào)不在解釋為其他含義，被解釋為正則表達(dá)式
select `^c.*` from t_usa_covid19_p;
--查詢當(dāng)前數(shù)據(jù)庫
select current_database(); --省去from關(guān)鍵字
--查詢使用函數(shù)
select count(county) from t_usa_covid19_p;

1.2去重

1.3where篩選

• 因?yàn)閣here執(zhí)行順序在group by之前，所以不能使用聚合函數(shù)（可以使用如length()等普通非聚合函數(shù)）
• 分區(qū)查詢（分區(qū)裁剪）：where后面指定 分區(qū)=xxx，減少全表掃描

1.4group by聚合

select的值可以是被聚合字段，也可以是使用聚合函數(shù)后的屬性

2.DQL執(zhí)行順序

3.三種排序

3.1 order by全局排序

• order by 是對(duì)最終結(jié)果全局排序，因此當(dāng)?shù)讓邮褂?code>MapReduce引擎執(zhí)行的時(shí)候，只會(huì)有一個(gè)reducetask執(zhí)行。如果輸出的行數(shù)太大，會(huì)導(dǎo)致需要很長(zhǎng)的時(shí)間才能完成全局排序。

• order by默認(rèn)升序ASC，ASC順序的默認(rèn)空排序順序?yàn)镹ULLS FIRST，而DESC順序的默認(rèn)空排序順序?yàn)镹ULLS LAST。當(dāng)然，也可以手動(dòng)指定空值在前or在后

• 在嚴(yán)格模式set hive.mapred.mode=strict;下，order by必須配合limit使用，無論數(shù)據(jù)量多少

3.2 cluster by字段分組，單用時(shí)組內(nèi)正序排序

• 因?yàn)槭欠纸M，組內(nèi)排序，所以比order by更快，但無法全局排序（僅組內(nèi)有序）
• cluster by = （對(duì)同一字段）distribute by + sort by
• cluster by只能是組內(nèi)升序
  

3.2.1查看reduce task個(gè)數(shù)

• Hive SQL底層走M(jìn)R程序，因此cluster by就是有幾個(gè)reduce task就分成幾個(gè)部分
• 可以通過指令set mapreduce.job.reduces;查看reduce task的個(gè)數(shù)

3.2.2如何決定cluster by的分組個(gè)數(shù)

• 如果select的時(shí)候不指定set mapreduce.job.reduces=?;，Hive就會(huì)根據(jù)data size數(shù)據(jù)量大小自己決定reduce task的個(gè)數(shù)
• 如果select的時(shí)候手動(dòng)指定了set mapreduce.job.reduces=?;那設(shè)置幾就分為幾組

3.3distribute by+sort by任意指定分組與排序

3.3.1分組與排序字段不一致

3.3.2分組與排序字段一樣，且組內(nèi)升序排序

3.4小結(jié)

4.Union和Union All

5.任意級(jí)別子查詢

• Hive支持任意級(jí)別的子查詢（可以任意嵌套）
• 子查詢可以用AS指定別名，僅供本次DQL使用（子查詢不是CTE）
• WHERE IN可以支持

5.1任意級(jí)別嵌套

5.2 IN、NOT IN不相關(guān)子查詢–單列無關(guān)字段名

–不相關(guān)子查詢，相當(dāng)于IN、NOT IN,子查詢只能選擇一個(gè)列，避免歧義。

5.3 EXISTS、NOT EXISTS相關(guān)子查詢–可以引用父查詢

因?yàn)镋XISTS本身的篩選級(jí)別是整行，因此可以SELECT多個(gè)列，還能在子查詢中引用父查詢

6.CTE表達(dá)式：臨時(shí)結(jié)果集

• common table expression
• 僅針對(duì)本次SQL，CTE表達(dá)式不能單獨(dú)執(zhí)行
• 相比子查詢，CTE臨時(shí)表可以被多個(gè)DQL使用（減少額外的子查詢開銷）
• 以下SQL必須一次性執(zhí)行，CTE才能生效
  
  通過臨時(shí)結(jié)果集insert的兩種方式
  

7. JOIN的6種方式

在Hive中，當(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內(nèi)連接

• 可以省略inner
• 當(dāng)且僅當(dāng)兩個(gè)表都存在與連接條件相匹配的數(shù)據(jù)才會(huì)被保留

7.1.1早期的隱式連接

7.2 左右連接

• outer可以省略
• 左右連接一般是等價(jià)的（方向問題罷了），通常習(xí)慣用left join
• left join：以左表為基準(zhǔn)關(guān)聯(lián)右表，左表數(shù)據(jù)全部返回
• right join：以右表為基準(zhǔn)關(guān)聯(lián)左表，右表數(shù)據(jù)全部返回

7.3全外連接（全連接、外連接）

• full outer join 或者 full join
• 等價(jià)于：分別left join和rigth join，最后union distinct
  

7.4 左右半開連接（半開就是：內(nèi)連接的“一半”）

這里以左半開連接left semi join為例：

• 效果上看：等價(jià)于inner join之后只返回左表包含的列（join對(duì)數(shù)據(jù)進(jìn)行匹配篩選）例如：select tb1.a1, tb1.a2 from tb1 join tb2 on tb1.id = tb2.id，僅做篩選，不取tb2的列
• 效率比inner join更高

7.5交叉連接cross join

• 大表千萬不能用，這個(gè)是返回兩張表的笛卡爾積
• 即：沒有on條件的inner join

8.Hive種對(duì)多連表JOIN的MR優(yōu)化.

a join b…join c時(shí)，如果沒有貫穿3表的條件字段（兩個(gè)MR作業(yè)）

• MR作業(yè)1：把a(bǔ)和b表的匹配數(shù)據(jù)放入reduce緩存中（內(nèi)存，后續(xù)比較更快）
• MR作業(yè)2：通過reduce流式傳輸（占用更少內(nèi)存）與上述MR1產(chǎn)生的reduce緩存進(jìn)行比較（快）
• 我們不能讓內(nèi)存占用過高，因此誰的表最大，誰放最后走reduce流式傳輸，兩個(gè)小表放前面產(chǎn)生的reduce緩存占用更小

8.1一個(gè)or多個(gè)mr作業(yè)

注意最后一句話（join的底層mr執(zhí)行邏輯）

8.2小表驅(qū)動(dòng)大表

• 當(dāng)出現(xiàn)多個(gè)MR作業(yè)時(shí)（沒有貫穿多表的連接字段）
• 這里如果C表最大，那么把C表放在最后有助于減少reduce階段的緩存占用