1.基礎語法

1.1正則匹配

需要現開啟正則SET hive.support.quoted.identifiers = none;反引號不在解釋為其他含義，被解釋為正則表達式

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

1.2去重

1.3where篩選

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

1.4group by聚合

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

2.DQL執行順序

3.三種排序

3.1 order by全局排序

• order by 是對最終結果全局排序，因此當底層使用MapReduce引擎執行的時候，只會有一個reducetask執行。如果輸出的行數太大，會導致需要很長的時間才能完成全局排序。

• order by默認升序ASC，ASC順序的默認空排序順序為NULLS FIRST，而DESC順序的默認空排序順序為NULLS LAST。當然，也可以手動指定空值在前or在后

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

3.2 cluster by字段分組，單用時組內正序排序

• 因為是分組，組內排序，所以比order by更快，但無法全局排序（僅組內有序）
• cluster by = （對同一字段）distribute by + sort by
• cluster by只能是組內升序
  

3.2.1查看reduce task個數

• Hive SQL底層走MR程序，因此cluster by就是有幾個reduce task就分成幾個部分
• 可以通過指令set mapreduce.job.reduces;查看reduce task的個數

3.2.2如何決定cluster by的分組個數

• 如果select的時候不指定set mapreduce.job.reduces=?;，Hive就會根據data size數據量大小自己決定reduce task的個數
• 如果select的時候手動指定了set mapreduce.job.reduces=?;那設置幾就分為幾組

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

3.3.1分組與排序字段不一致

3.3.2分組與排序字段一樣，且組內升序排序

3.4小結

4.Union和Union All

5.任意級別子查詢

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

5.1任意級別嵌套

5.2 IN、NOT IN不相關子查詢–單列無關字段名

–不相關子查詢，相當于IN、NOT IN,子查詢只能選擇一個列，避免歧義。

5.3 EXISTS、NOT EXISTS相關子查詢–可以引用父查詢

因為EXISTS本身的篩選級別是整行，因此可以SELECT多個列，還能在子查詢中引用父查詢

6.CTE表達式：臨時結果集

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

7. JOIN的6種方式

在Hive中，當下版本3.1.2總共支持6種join語法。分別是:

• inner join(內連接)、
• left join(左連接)、right join(右連接)
• full outer join(全外連接)、left semi join(左半開連接) 、cross join(交叉連接，也叫做笛卡爾乘積)。

7.1內連接

• 可以省略inner
• 當且僅當兩個表都存在與連接條件相匹配的數據才會被保留

7.1.1早期的隱式連接

7.2 左右連接

• outer可以省略
• 左右連接一般是等價的（方向問題罷了），通常習慣用left join
• left join：以左表為基準關聯右表，左表數據全部返回
• right join：以右表為基準關聯左表，右表數據全部返回

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

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

7.4 左右半開連接（半開就是：內連接的“一半”）

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

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

7.5交叉連接cross join

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

8.Hive種對多連表JOIN的MR優化.

a join b…join c時，如果沒有貫穿3表的條件字段（兩個MR作業）

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

8.1一個or多個mr作業

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

8.2小表驅動大表

• 當出現多個MR作業時（沒有貫穿多表的連接字段）
• 這里如果C表最大，那么把C表放在最后有助于減少reduce階段的緩存占用