1.Hive原理

Hive是構(gòu)建在Hadoop上的數(shù)據(jù)倉(cāng)庫(kù)軟件框架肉微，支持使用SQL來(lái)讀匾鸥，寫(xiě)和管理大規(guī)模數(shù)據(jù)集合。Hive入門(mén)非常簡(jiǎn)單碉纳，功能非常強(qiáng)大勿负，所以非常流行。

通常來(lái)說(shuō)劳曹，Hive只支持?jǐn)?shù)據(jù)查詢和加載奴愉，但后面的版本也支持了插入，更新和刪除以及流式api铁孵。Ｈive具有目前Hadoop上最豐富最全的SQL語(yǔ)法锭硼，也擁有最慢最穩(wěn)定的執(zhí)行。是目前Hadoop上幾乎標(biāo)準(zhǔn)的ETL和數(shù)據(jù)倉(cāng)庫(kù)工具蜕劝。

Hive這個(gè)特點(diǎn)與其它AdHoc查詢工具如Impala(拉式獲取數(shù)據(jù)\利用內(nèi)存\漸進(jìn)輸出結(jié)果)檀头，Spark SQL或者Presto有著應(yīng)用場(chǎng)景的區(qū)別，也就是雖然都是即席查詢工具岖沛，前者適用與穩(wěn)定作業(yè)執(zhí)行暑始，調(diào)度以及ETL，或者更傾向于交戶式烫止。一個(gè)典型的場(chǎng)景是分析師使用Impala去探測(cè)數(shù)據(jù)蒋荚，驗(yàn)證想法，并把數(shù)據(jù)產(chǎn)品部署在Hive上執(zhí)行馆蠕。

1.1Hadoop基本原理

在我們講Hive原理和查詢優(yōu)化前，讓我們先回顧一下Hadoop基本原理惊奇。

Hadoop是一個(gè)分布式系統(tǒng)互躬，有HDFS和Yarn。HDFS用于執(zhí)行存儲(chǔ)颂郎，Yarn用于資源調(diào)度和計(jì)算吼渡。ＭapReduce是跑在Yarn上的一種計(jì)算作業(yè)，此外還有Spark等乓序。

Hive通常意義上來(lái)說(shuō)寺酪，是把一個(gè)SQL轉(zhuǎn)化成一個(gè)分布式作業(yè)坎背，如MapReduce，Spark或者Tez寄雀。無(wú)論Hive的底層執(zhí)行框架是MapReduce得滤、Spark還是Tez，其原理基本都類似盒犹。

而目前懂更，由于MapReduce穩(wěn)定，容錯(cuò)性好急膀，大量數(shù)據(jù)情況下使用磁盤(pán)沮协，能處理的數(shù)據(jù)量大，所以目前Hive的主流執(zhí)行框架是MapReduce卓嫂，但性能相比Spark和Tez也就較低慷暂，等下講到Group By和JOIN原理時(shí)會(huì)解釋這方面的原因。

目前的Hive除了支持在MapReduce上執(zhí)行晨雳，還支持在Spark和Tez 上執(zhí)行呜呐。我們以MapReduce為例來(lái)說(shuō)明的Hive的原理。先回顧一下 MapReduce 原理悍募。

兩個(gè)Mapper各自輸入一塊數(shù)據(jù)蘑辑，由鍵值對(duì)構(gòu)成，對(duì)它進(jìn)行加工（加上了個(gè)字符n）坠宴，然后按加工后的數(shù)據(jù)的鍵進(jìn)行分組洋魂，相同的鍵到相同的機(jī)器。這樣的話，第一臺(tái)機(jī)器分到了鍵nk1和nk3，第二臺(tái)機(jī)器分到了鍵nk2辕羽。

接下來(lái)再在這些Reducers上執(zhí)行聚合操作（這里執(zhí)行的是是count）澡罚，輸出就是nk1出現(xiàn)了2次，nk3出現(xiàn)了1次吱窝，nk2出現(xiàn)了3次。從全局上來(lái)看，MapReduce就是一個(gè)分布式的GroupBy的過(guò)程心剥。

從上圖可以看到，Global Shuffle左邊背桐，兩臺(tái)機(jī)器執(zhí)行的是Map优烧。Global Shuffle右邊，兩臺(tái)機(jī)器執(zhí)行的是Reduce链峭。所以Hive畦娄，實(shí)際上就是一個(gè)編譯器，一個(gè)翻譯機(jī)。把SQL翻譯成MapReduce之類的作業(yè)熙卡。

1.2Hive架構(gòu)

下面這個(gè)舊一點(diǎn)的圖片來(lái)自Facebook

從架構(gòu)圖上可以很清楚地看出Hive和Hadoop（MapReduce杖刷，HDFS）的關(guān)系。

Hive是最上層驳癌，即客戶端層或者作業(yè)提交層滑燃。?

MapReduce/Yarn是中間層，也就是計(jì)算層喂柒。?

HDFS是底層不瓶，也就是存儲(chǔ)層。

從Facebook的圖上可以看出灾杰，Hive主要有QL蚊丐，MetaStore和Serde三大核心組件構(gòu)成。QL就是編譯器艳吠，也是Hive中最核心的部分麦备。Serde就是Serializer和Deserializer的縮寫(xiě)，用于序列化和反序列化數(shù)據(jù)昭娩，即讀寫(xiě)數(shù)據(jù)凛篙。MetaStore對(duì)外暴露Thrift API，用于元數(shù)據(jù)的修改栏渺。比如表的增刪改查呛梆，分區(qū)的增刪改查，表的屬性的修改磕诊，分區(qū)的屬性的修改等填物。

1.3Hive的數(shù)據(jù)模型

Hive的數(shù)據(jù)存儲(chǔ)在HDFS上，基本存儲(chǔ)單位是表或者分區(qū)霎终，Hive內(nèi)部把表或者分區(qū)稱作SD滞磺，即Storage Descriptor。一個(gè)SD通常是一個(gè)HDFS路徑莱褒，或者其它文件系統(tǒng)路徑击困。SD的元數(shù)據(jù)信息存儲(chǔ)在Hive MetaStore中，如文件路徑广凸，文件格式阅茶，列，數(shù)據(jù)類型炮障，分隔符目派。Hive默認(rèn)的分格符有三種，分別是^A胁赢、^B和^C，即ASCii碼的1、2和3智末，分別用于分隔列谅摄，分隔列中的數(shù)組元素，和元素Key-Value對(duì)中的Key和Value系馆。

還記得大明湖畔暴露Thrift API的MetaStore么送漠？嗯，是她由蘑，就是它闽寡！所有的數(shù)據(jù)能不能認(rèn)得出來(lái)全靠它！

Hive的核心是Driver尼酿，Driver的核心是SemanticAnalyzer爷狈。 Hive實(shí)際上是一個(gè)SQL到Hadoop作業(yè)的編譯器。 Hadoop上最流行的作業(yè)就是MapReduce裳擎，當(dāng)然還有其它涎永，比如Tez和Spark。Hive目前支持ＭapReduce, Tez, Spark三種作業(yè)鹿响，其原理和剛才回顧的MapReduce過(guò)程類似羡微，只是在執(zhí)行優(yōu)化上有區(qū)別。

1.4Hive的編譯流程

Hive作業(yè)的執(zhí)行過(guò)程實(shí)際上是SQL翻譯成作業(yè)的過(guò)程惶我？那么妈倔，它是怎么翻譯的？

一條SQL绸贡，進(jìn)入的Hive盯蝴。經(jīng)過(guò)上述的過(guò)程，其實(shí)也是一個(gè)比較典型的編譯過(guò)程變成了一個(gè)作業(yè)恃轩。

首先结洼，Driver會(huì)輸入一個(gè)字符串SQL，然后經(jīng)過(guò)Parser變成AST（abstract syntax tree 抽象語(yǔ)法樹(shù)）叉跛，這個(gè)變成AST的過(guò)程是通過(guò)Antlr（antlr是指可以根據(jù)輸入自動(dòng)生成語(yǔ)法樹(shù)并可視化的顯示出來(lái)的開(kāi)源語(yǔ)法分析器）來(lái)完成的松忍，也就是Anltr根據(jù)語(yǔ)法文件來(lái)將SQL變成AST。

AST進(jìn)入SemanticAnalyzer（核心）變成QB筷厘，也就是所謂的QueryBlock鸣峭。一個(gè)最簡(jiǎn)的查詢塊，通常來(lái)講酥艳，一個(gè)From子句會(huì)生成一個(gè)QB摊溶。生成QB是一個(gè)遞歸過(guò)程，生成的 ＱＢ經(jīng)過(guò)GenLogicalPlan過(guò)程充石，變成了一個(gè)Operator圖莫换，也是一個(gè)有向無(wú)環(huán)圖。

OP DAG經(jīng)過(guò)邏輯優(yōu)化器，對(duì)這個(gè)圖上的邊或者結(jié)點(diǎn)進(jìn)行調(diào)整拉岁，順序修訂坷剧，變成了一個(gè)優(yōu)化后的有向無(wú)環(huán)圖。這些優(yōu)化過(guò)程可能包括謂詞下推（Predicate Push Down）喊暖，分區(qū)剪裁（Partition Prunner）惫企，關(guān)聯(lián)排序（Join Reorder）等等

經(jīng)過(guò)了邏輯優(yōu)化，這個(gè)有向無(wú)環(huán)圖還要能夠執(zhí)行陵叽。所以有了生成物理執(zhí)行計(jì)劃的過(guò)程狞尔。GenTasks。Ｈive的作法通常是碰到需要分發(fā)的地方巩掺，切上一刀偏序，生成一道MapReduce作業(yè)。如Group By切一刀锌半，Join切一刀禽车，Distribute By切一刀，Distinct切一刀刊殉。

這么很多刀砍下去之后殉摔，剛才那個(gè)邏輯執(zhí)行計(jì)劃，也就是那個(gè)邏輯有向無(wú)環(huán)圖记焊，就被切成了很多個(gè)子圖逸月，每個(gè)子圖構(gòu)成一個(gè)結(jié)點(diǎn)。這些結(jié)點(diǎn)又連成了一個(gè)執(zhí)行計(jì)劃圖遍膜，也就是Task Tree.

把這些個(gè)Task Tree 還可以有一些優(yōu)化碗硬，比如基于輸入選擇執(zhí)行路徑，增加備份作業(yè)等瓢颅。進(jìn)行調(diào)整恩尾。這個(gè)優(yōu)化就是由Physical Optimizer來(lái)完成的。經(jīng)過(guò)Physical Optimizer挽懦，這每一個(gè)結(jié)點(diǎn)就是一個(gè)MapReduce作業(yè)或者本地作業(yè)翰意，就可以執(zhí)行了。

這就是一個(gè)SQL如何變成MapReduce作業(yè)的過(guò)程信柿。要想觀查這個(gè)過(guò)程的最終結(jié)果冀偶，可以打開(kāi)Hive，輸入Explain ＋ 語(yǔ)句渔嚷，就能夠看到进鸠。

1.5group by的執(zhí)行任務(wù)

Hive最重要的部分是Group By和Join。下面分別講解一下：

首先是Group By

例如我們有一條SQL語(yǔ)句：

INSERT INTO TABLE pageid_age_sum

SELECT pageid, age, count(1)

FROM pv_users

GROUP BY pageid, age;

把每個(gè)網(wǎng)頁(yè)的閱讀數(shù)按年齡進(jìn)行分組統(tǒng)計(jì)形病。由于前面介紹了客年，MapReduce就是一個(gè)Group By的過(guò)程霞幅，這個(gè)SQL翻譯成MapReduce就是相對(duì)簡(jiǎn)單的。

我們?cè)贛ap端搀罢，每一個(gè)Map讀取一部分表的數(shù)據(jù)蝗岖，通常是64M或者256M侥猩，然后按需要Group By的Key分發(fā)到Reduce端榔至。經(jīng)過(guò)Shuffle Sort，每一個(gè)Key再在Reduce端進(jìn)行聚合（這里是Count)欺劳，然后就輸出了最終的結(jié)果唧取。

1.6 distinct的執(zhí)行任務(wù)

值得一提的是，Distinct在實(shí)現(xiàn)原理上與Group By類似划提。當(dāng)Group By遇上 Distinct……例如：SELECT pageid, COUNT(DISTINCT userid) FROM page_view GROUP BY pageid

Hive 實(shí)現(xiàn)成MapReduce的原理如下：

也就是說(shuō)Map分發(fā)到Reduce的時(shí)候枫弟，會(huì)使用pageid和userid作為聯(lián)合分發(fā)鍵，再去聚合（Count）鹏往，輸出結(jié)果淡诗。

介紹了這么多原理，重點(diǎn)還是為了使用伊履，為了適應(yīng)場(chǎng)景和業(yè)務(wù)韩容，為了優(yōu)化。從原理上可以看出唐瀑，當(dāng)遇到Group By的查詢時(shí)群凶，會(huì)按Group By 鍵進(jìn)行分發(fā)？如果鍵很多哄辣，撐爆了機(jī)器會(huì)怎么樣请梢？

對(duì)于Impala，或Spark力穗，為了快毅弧，key在內(nèi)存中，爆是經(jīng)常的当窗。爆了就失敗了够坐。對(duì)于Hive，Key在硬盤(pán)超全，本身就比Impala, Spark的處理能力大上幾萬(wàn)倍咆霜。但……不幸的是，硬盤(pán)也有可能爆嘶朱。

1.7 join的執(zhí)行任務(wù)

例如這樣一個(gè)查詢：INSERT INTO TABLE pv_users

SELECT pv.pageid, u.age

FROM page_view pv JOIN user u ON (pv.userid = u.userid);

把訪問(wèn)和用戶表進(jìn)行關(guān)聯(lián)蛾坯，生成訪問(wèn)用戶表。Hive的Join也是通過(guò)MapReduce來(lái)完成的疏遏。

就上面的查詢脉课，在ＭapReduce的Join的實(shí)現(xiàn)過(guò)程如下：

Ｍap端會(huì)分別讀入各個(gè)表的一部分?jǐn)?shù)據(jù)救军，把這部分?jǐn)?shù)據(jù)進(jìn)行打標(biāo)，例如pv表標(biāo)1倘零，user表標(biāo)2.

Map讀取是分布式進(jìn)行的唱遭。標(biāo)完完后分發(fā)到Reduce端，Reduce 端根據(jù)Join Key呈驶，也就是關(guān)聯(lián)鍵進(jìn)行分組拷泽。然后按打的標(biāo)進(jìn)行排序，也就是圖上的Shuffle Sort袖瞻。

在每一個(gè)Reduce分組中司致，Key為111的在一起，也就是一臺(tái)機(jī)器上聋迎。同時(shí)脂矫，pv表的數(shù)據(jù)在這臺(tái)機(jī)器的上端，user表的數(shù)據(jù)在這臺(tái)機(jī)器的下端霉晕。

這時(shí)候庭再，Reduce把pv表的數(shù)據(jù)讀入到內(nèi)存里，然后逐條與硬盤(pán)上user表的數(shù)據(jù)做Join就可以了牺堰。

從這個(gè)實(shí)現(xiàn)可以看出拄轻，我們?cè)趯?xiě)Hive Join的時(shí)候，應(yīng)該盡可能把小表（分布均勻的表）寫(xiě)在左邊萌焰，大表（或傾斜表）寫(xiě)在右邊哺眯。這樣可以有效利用內(nèi)存和硬盤(pán)的關(guān)系，增強(qiáng)Hive的處理能力扒俯。

同時(shí)由于使用Join Key進(jìn)行分發(fā)奶卓， Hive也只支持等值Join，不支持非等值Join撼玄。由于Join和Group By一樣存在分發(fā)夺姑，所以也同樣存在著傾斜的問(wèn)題。所以Join也要對(duì)抗傾斜數(shù)據(jù)掌猛，提升查詢執(zhí)行性能盏浙。

1.8 Map join的執(zhí)行任務(wù)

通常，有一種執(zhí)行非忱蟛纾快的Join叫Map Join 废膘。

手動(dòng)的Map Join SQL如下（pv是小表）：

INSERT INTO TABLE pv_users

SELECT /*+ MAPJOIN(pv) */ pv.pageid, u.age

FROM page_view pv JOIN user u

ON (pv.userid = u.userid);

還是剛才的例子，用Map Join執(zhí)行

Map Join通常只適用于一個(gè)大表和一個(gè)小表做關(guān)聯(lián)的場(chǎng)景慕蔚，例如事實(shí)表和維表的關(guān)聯(lián)丐黄。

原理如上圖，用戶可以手動(dòng)指定哪個(gè)表是小表孔飒，然后在客戶端把小表打成一個(gè)哈希表序列化文件的壓縮包灌闺，通過(guò)分布式緩存均勻分發(fā)到作業(yè)執(zhí)行的每一個(gè)結(jié)點(diǎn)上艰争。然后在結(jié)點(diǎn)上進(jìn)行解壓，在內(nèi)存中完成關(guān)聯(lián)桂对。

Map Join全過(guò)程不會(huì)使用Reduce甩卓，非常均勻，不會(huì)存在數(shù)據(jù)傾斜問(wèn)題蕉斜。默認(rèn)情況下逾柿，小表不應(yīng)該超過(guò)25M。在實(shí)際使用過(guò)程中蛛勉，手動(dòng)判斷是不是應(yīng)該用Map Join太麻煩了鹿寻，而且小表可能來(lái)自于子查詢的結(jié)果。

Hive有一種稍微復(fù)雜一點(diǎn)的機(jī)制诽凌，叫Auto Map Join

還記得原理中提到的物理優(yōu)化器？Physical Optimizer么坦敌？它的其中一個(gè)功能就是把Join優(yōu)化成Auto Map Join

圖上左邊是優(yōu)化前的侣诵，右邊是優(yōu)化后的

優(yōu)化過(guò)程是把Join作業(yè)前面加上一個(gè)條件選擇器ConditionalTask和一個(gè)分支。左邊的分支是MapJoin狱窘，右邊的分支是Common Join(Reduce Join)

看看左邊的分支是不是和我們上上一張圖很像杜顺？

這個(gè)時(shí)候，我們?cè)趫?zhí)行的時(shí)候蘸炸，就由這個(gè)Conditional Task 進(jìn)行實(shí)時(shí)路徑選擇躬络，遇到小于25兆走左邊，大于25兆走右邊搭儒。所謂穷当，男的走左邊，女的走右邊淹禾，人妖走中間馁菜。

在比較新版的Hive中，Auto Mapjoin是默認(rèn)開(kāi)啟的铃岔。如果沒(méi)有開(kāi)啟汪疮，可以使用一個(gè)開(kāi)關(guān)， set hive.auto.convert.join=true 開(kāi)啟毁习。

當(dāng)然智嚷，Join也會(huì)遇到和上面的Group By一樣的傾斜問(wèn)題。

Ｈive 也可以通過(guò)像Group By一樣兩道作業(yè)的模式單獨(dú)處理一行或者多行傾斜的數(shù)據(jù)纺且。

hive 中設(shè)定

set hive.optimize.skewjoin = true;

set hive.skewjoin.key = skew_key_threshold （default = 100000）

其原理是就在Reduce Join過(guò)程盏道，把超過(guò)十萬(wàn)條的傾斜鍵的行寫(xiě)到文件里，回頭再起一道Join單行的Map Join作業(yè)來(lái)單獨(dú)收拾它們隆檀。最后把結(jié)果取并集就是了摇天。如上圖所示粹湃。

1.9Hive適合做什么？

由于多年積累泉坐，Hive比較穩(wěn)定为鳄，幾乎是Hadoop上事實(shí)的SQL標(biāo)準(zhǔn)。 Ｈive適合離線ETL腕让，適合大數(shù)據(jù)離線Ad-Hoc查詢孤钦。適合特大規(guī)模數(shù)據(jù)集合需要精確結(jié)果的查詢。對(duì)于交互式Ad-Hoc查詢纯丸，通常還會(huì)有別的解決方案偏形，比如Impala, Presto等等。

特大規(guī)模的離線數(shù)據(jù)處理觉鼻，尤其是大表關(guān)聯(lián)俊扭，特大規(guī)模數(shù)據(jù)聚集，很適合使用Hive坠陈。講了這么多原理萨惑，最重要的還是應(yīng)用，還是創(chuàng)造價(jià)值仇矾。

對(duì)Hive來(lái)說(shuō)庸蔼，數(shù)據(jù)量再大，都不怕贮匕。數(shù)據(jù)傾斜姐仅，是大難題。但有很多優(yōu)化方法和業(yè)務(wù)改進(jìn)方法可以避過(guò)刻盐。Hive執(zhí)行穩(wěn)定掏膏，函數(shù)多，擴(kuò)展性強(qiáng)隙疚，數(shù)據(jù)吞吐量大壤追，了解原理，有助于用好和選型供屉。

2.SQL優(yōu)化

HIVE優(yōu)化可以從兩方面入手行冰，減少計(jì)算和加速計(jì)算。

減少計(jì)算包括：減少數(shù)據(jù)量伶丐、數(shù)據(jù)復(fù)用悼做，避免重復(fù)計(jì)算、多粒度逐步聚合和業(yè)務(wù)裁剪哗魂。

加速計(jì)算包括：使用內(nèi)存計(jì)算肛走、并發(fā)計(jì)算。

2.1 limit優(yōu)化

limit不啟用優(yōu)化的情況

a.語(yǔ)句中帶有join录别，group by朽色，列as別名邻吞，order by，where等的葫男，不啟用優(yōu)化抱冷。

b.子查詢中的limit不會(huì)做優(yōu)化，會(huì)掃描所有數(shù)據(jù)梢褐，掃描完了以后取limit指定的條數(shù)旺遮。

c. 查詢視圖 limit不會(huì)啟用優(yōu)化

?對(duì)于a這種情況，都會(huì)用MR處理盈咳，并且優(yōu)化會(huì)比較復(fù)雜耿眉，因?yàn)間roup by，order by這樣的鱼响，必須掃描所有數(shù)據(jù)鸣剪，要保證結(jié)果正確又盡量少計(jì)算，會(huì)讓查詢優(yōu)化器變得非常復(fù)雜热押。對(duì)于b這種情況西傀，與a類似，都要走M(jìn)R桶癣，另外一方面，子查詢中帶limit本身用法比較少見(jiàn)娘锁，所以也沒(méi)有優(yōu)化牙寞。

推薦使用:

(?這些語(yǔ)句會(huì)直接讀取HDFS文件，不走M(jìn)R莫秆，會(huì)很快)

select * from taba? limit 1;

?select a from taba partition(p) limit 1;

2.2使用分區(qū)/列修剪

? ? ? 做好列裁剪和filter操作间雀，尤其是只讀取需要的分區(qū)。支持分區(qū)自動(dòng)修剪镊屎，因此惹挟，一般情況下，只要分區(qū)字段在where子句中缝驳，TDW就會(huì)自動(dòng)過(guò)濾掉不需要的分區(qū)连锯。但是，如果分區(qū)字段位于in以及其他函數(shù)中用狱，那么分區(qū)自動(dòng)修剪將失效运怖。另外，between夏伊、or摇展、and都支持分區(qū)自動(dòng)修剪。

推薦使用:

(tdbank_imp_date 為表的分區(qū)字段,對(duì)分區(qū)字段不要使用函數(shù)溺忧，不要用substr)：?

where tdbank_imp_date?in('2018030100','2018030101','2018030102')?

where tdbank_imp_date='2018030100'

where tdbank_imp_date between? '2018030100'??and '2018043023'

2.3禁止出現(xiàn)笛卡爾積

笛卡爾積只有1個(gè)reduce任務(wù)(一個(gè)整的大文件)咏连，會(huì)導(dǎo)致計(jì)算超慢盯孙，甚至可能計(jì)算不出來(lái)或者導(dǎo)致節(jié)點(diǎn)掛掉。

以下3種形式的SQL會(huì)導(dǎo)致笛卡爾積：

select * from gbk祟滴，utf8?where?gbk.key= utf8.key?and gbk.key > 10;

select * from gbk?join?utf8?where?gbk.key= utf8.key?and gbk.key > 10;

?tablea?join?tableb?join?tablec?join?...?on?tablea.col1 = tableb.col2 and ...

推薦使用:

(1) select * from gbk?join?utf8?on gbk.key= utf8.key?where gbk.key > 10;

(2) tablea?join?tableb?on?（tablea.col1 = tableb.col2 and ...?）?join?tablec?on?...join?...?on?...

(3) select * from?

(select * from gbk where gbk.key>10) gbk?

join?

(select * from utf8)

on gbk.key=utf8.key?

2.4表關(guān)聯(lián)的優(yōu)化

2.4.1有小表且數(shù)據(jù)條數(shù)不超過(guò)2w行

?推薦使用Map Join

map join的必要條件：

?????? a.參與連接的小表的行數(shù)振惰，以不超過(guò)2萬(wàn)條為宜。

?????? b.連接類型是inner join踱启、right outer join（小表不能是右表）报账、left outer join（小表不能是左表）、left semi join埠偿。

使用方法示例：

例如(其中pv是小表透罢，會(huì)把pv表生成hash表，壓縮)：

????? SELECT /*+ MAPJOIN(pv) */ ??

??????????????????? pv.pageid, u.age ????????????????????????????????

????? FROM page_view pv

???????????????? JOIN user u

???????????????? ON (pv.userid = u.userid);

注：當(dāng)大表存在數(shù)據(jù)傾斜時(shí)冠蒋，如果小表符合map join的要求羽圃，使用map join會(huì)極大加速計(jì)算。

2.4.2小表連接大表

? ? 將較大的表放在join操作符的右邊抖剿，這樣生成的查詢計(jì)劃效率較高朽寞，執(zhí)行速度快，不容易出錯(cuò)斩郎；

? ?在Join 操作的Reduce 階段脑融，位于Join 操作符左邊的表的內(nèi)容會(huì)首先被加載進(jìn)內(nèi)存（容器滿后存入硬盤(pán)），然后對(duì)右側(cè)表進(jìn)行流式處理缩宜，將條目少的表放在左邊肘迎，可以有效減少發(fā)生磁盤(pán)IO和OOM 錯(cuò)誤的幾率。

推薦使用：

將較大的表放在join操作符的右邊??

2.5數(shù)據(jù)傾斜的優(yōu)化

? ? ?常見(jiàn)的數(shù)據(jù)傾斜問(wèn)題锻煌，一般發(fā)生在group by?或者join操作上妓布，表現(xiàn)為一個(gè)或幾個(gè)reduce一直沒(méi)辦法做完，原因是key分布不均宋梧，某個(gè)或某幾個(gè)key的數(shù)據(jù)特別大匣沼。可以對(duì)key的數(shù)據(jù)量排序來(lái)檢驗(yàn)是否有數(shù)據(jù)傾斜問(wèn)題：

SELECT? key1,key2, count(1) as cnt

FROM test_tatble

GROUP BY key1,key2

ORDER BY cnt DESC LIMIT 50

2.5.1有小表使用MAPJOIN

當(dāng)需要join的表有一個(gè)小表時(shí)就很適合用內(nèi)存計(jì)算來(lái)完成捂龄，也即使用MAPJOIN释涛。

2.5.2 去除數(shù)據(jù)傾斜數(shù)據(jù)，數(shù)據(jù)為臟數(shù)據(jù)

特別留意關(guān)聯(lián)的key 里面有大量0跺讯、空值枢贿；做過(guò)濾 ，然后再distinct輸出

2.5.3單獨(dú)計(jì)算導(dǎo)致數(shù)據(jù)傾斜的key再合并數(shù)據(jù)

根據(jù)key值拆開(kāi)成2個(gè)集合刀脏，然后再union all 起來(lái)?

2.5.4 groupby時(shí)的數(shù)據(jù)傾斜局荚，參數(shù)設(shè)置

set hive.groupby.skewindata=true;

{:problematic sql}

set hive.groupby.skewindata=false;

查詢計(jì)劃會(huì)生成兩個(gè)MR Job，其中在第一個(gè)MR Job中，Map的輸出結(jié)果會(huì)隨機(jī)分配到Reduce端耀态，每個(gè)Reduce做部分聚合計(jì)算轮傍，然后輸出結(jié)果，從而達(dá)到負(fù)載均衡的目的首装；在第二MR Job中创夜，根據(jù)預(yù)處理的數(shù)據(jù)結(jié)果按照分組 Key 將數(shù)據(jù)分配到相應(yīng)的Reduce任務(wù)中，完成最終的聚合計(jì)算仙逻。

2.5.5設(shè)置reduce數(shù)驰吓，使得reducekey分散

通過(guò)設(shè)置reduce任務(wù)數(shù)提高并行度來(lái)加速執(zhí)行：

set mapred.reduce.tasks=N; //執(zhí)行語(yǔ)句之前

set mapred.reduce.tasks=-1; //執(zhí)行語(yǔ)句之后恢復(fù)原狀

注意：請(qǐng)合理設(shè)置N的大小，最好設(shè)置為上述參數(shù)的大小系奉。一定不要超過(guò)999檬贰！

2.5.6 將數(shù)據(jù)傾斜的key隨機(jī)化

將空值key轉(zhuǎn)換成一個(gè)字符串加上隨機(jī)數(shù)，從而將傾斜的數(shù)據(jù)分到不同的Reduce任務(wù)上

select*fromtable1 a left join table2 b on case when a.vopenid is null then concat('random',rand()) else a.vopenid end = b.vopenid;

2.6先聚合后連接

這個(gè)原則很簡(jiǎn)單缺亮，因?yàn)閖oin key可能存在傾斜翁涤，因此，只要可能萌踱，最好先對(duì)join key進(jìn)行處理一下再進(jìn)行join操作葵礼，避免數(shù)據(jù)傾斜。

一般來(lái)說(shuō)并鸵，join后面會(huì)跟著一些聚合函數(shù)操作鸳粉，這個(gè)原則是盡量將聚合操作提前做，使得在做join的時(shí)候join key可以是單一的园担。

聚合有數(shù)據(jù)壓縮的作用赁严。“先聚合粉铐，后連接”可以減少聚合和連接時(shí)的數(shù)據(jù)量

2.8開(kāi)啟并行執(zhí)行

? ?支持并行執(zhí)行機(jī)制，以下功能都可以通過(guò)并行執(zhí)行加速：union all卤档、join（參與join的是復(fù)雜查詢）蝙泼、cube、rollup劝枣、groupingsets汤踏、with等√蛱冢可見(jiàn)溪胶，很多常用的功能都可以加速。默認(rèn)該并行開(kāi)關(guān)是關(guān)閉的稳诚，若需要并行執(zhí)行哗脖，可以設(shè)置?set hive.exec.parallel=true;打開(kāi)開(kāi)關(guān)，但消耗資源成本會(huì)增加。

2.9任務(wù)分析常用語(yǔ)句

（1）想知道分區(qū)修剪是否起作用么才避？

答案：用explain語(yǔ)句吧橱夭，看看要讀取哪些目錄就知道了。另外桑逝，通過(guò)這個(gè)語(yǔ)句的執(zhí)行結(jié)果棘劣，還可以檢查你的查詢計(jì)劃是否合理蝗蛙。

例句：

Explain select t.col1,t.col3 from dbname::tablename t where t.ftime=‘20130104’

（2）show rowcount的作用

Show rowcount dbname::tablename //顯示整個(gè)表有多少行

通過(guò)這個(gè)命令可以知道一個(gè)表有多少行記錄玄妈，有了它的幫助就可以在連接時(shí)基本保證小表連接大表，也可以知道是否適合使用map join敢课。

Show rowcount extended dbname::tablename? //按分區(qū)顯示每個(gè)分區(qū)有多少行

這個(gè)命令更有用的形式是show rowcount extended tablename寡喝，這個(gè)命令可以按分區(qū)來(lái)顯示每個(gè)分區(qū)有多少行記錄糙俗。

注意：本命令和下一條命令只對(duì)結(jié)構(gòu)化存儲(chǔ)文件生效。如對(duì)文本文件執(zhí)行該命令將報(bào)錯(cuò)拘荡。

（3）show tablesize的作用

Show tablesize dbname::tablename

通過(guò)這個(gè)命令可以知道一個(gè)表有多大臼节，單位是字節(jié)。

擴(kuò)展形式：

Show tablesize extended dbname::tablename

說(shuō)明：

通過(guò)該命令的結(jié)果珊皿，可以估算出大約需要多少個(gè)map任務(wù)网缝，現(xiàn)在一般256MB/512MB一個(gè)map任務(wù)。如果你想知道join的時(shí)候需要多少個(gè)map任務(wù)蟋定，只需要把每個(gè)表需要的map任務(wù)數(shù)求和就可以了粉臊。

需要的map數(shù)太多可不是好事，通常map數(shù)超過(guò)1萬(wàn)就是較大的任務(wù)了驶兜。否則扼仲，你需要耐心等待。

2.10臨時(shí)表的應(yīng)用

多次需要用到的數(shù)據(jù)抄淑，最好使用臨時(shí)表保存起來(lái)

總結(jié)：

用集合的思維分解問(wèn)題 屠凶， 用同類型key做關(guān)聯(lián)；

能少join就盡量少join肆资，想辦法實(shí)現(xiàn)數(shù)據(jù)復(fù)用 矗愧；?

盡最大能力限制子查詢輸入/輸出的數(shù)據(jù)量 ；

盡量避免udf函數(shù)的使用郑原，盡量利用內(nèi)存機(jī)制唉韭；

遇到數(shù)據(jù)傾斜，先清除臟數(shù)據(jù)犯犁，再做優(yōu)化属愤；

3.常見(jiàn)sql的標(biāo)準(zhǔn)寫(xiě)法

(1) 日志數(shù)據(jù)提取

需要注意分區(qū)/列修剪

select filed1,filed2,filed3 from tableA where tdbank_imp_date between '2018092700' and '2018092723' and ....

(2) 去重統(tǒng)計(jì)

先distinct 減少輸出的數(shù)據(jù)量

select count(*) from (

select distinct? openid from? tablelog where?tdbank_imp_date between '2018092700' and '2018092723' and ....

)

(3) 查詢最近一個(gè)月既玩了A游戲也玩了B游戲的用戶數(shù)量

能一個(gè)查詢搞定的 盡量一個(gè)查詢搞定

select count(suin) from iplat group by suin having (count(case when vgameappid='xxx'? then 1 end )>0 and count(case when vgameappid='yyy' then 1 end)>0);

參考資料：

https://blog.csdn.net/LW_GHY/article/details/51469753?utm_source=copy

http://km.oa.com/group/2430/articles/show/127863