這篇文章將為大家詳細(xì)講解有關(guān)Hive中join如何優(yōu)化，小編覺得挺實(shí)用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

創(chuàng)新互聯(lián)總部坐落于成都市區(qū)，致力網(wǎng)站建設(shè)服務(wù)有成都做網(wǎng)站、成都網(wǎng)站建設(shè)、網(wǎng)絡(luò)營銷策劃、網(wǎng)頁設(shè)計(jì)、網(wǎng)站維護(hù)、公眾號搭建、小程序開發(fā)、軟件開發(fā)等為企業(yè)提供一整套的信息化建設(shè)解決方案。創(chuàng)造真正意義上的網(wǎng)站建設(shè)，為互聯(lián)網(wǎng)品牌在互動行銷領(lǐng)域創(chuàng)造價值而不懈努力！

1、需求

需求我做了簡化，很簡單，兩張表做個 join，求指定城市，每天的 pv，用傳統(tǒng)的 RDBMS SQL 寫出來就這樣的：

SELECT t.statdate,
       c.cname,
       count(t.cookieid)
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON (t.area1= c.cname
                                OR t.area2 =c.cname
                                OR t.area3 = c.cname)
WHERE t.statdate>='20140818' and t.statdate<='20140824'
  AND platform='pc'
GROUP BY t.statdate,
         c.cname;

怎么樣？根據(jù) SQL 看懂需求沒問題吧？

2、非等值 join 問題

然后把這條 SQL 貼到 hive 中去執(zhí)行，然后你會發(fā)現(xiàn)報(bào)錯了：

FAILED: SemanticException [Error 10019]: Line 5:32 OR not supported in JOIN currently 'cname'

這是因?yàn)?hive 受限于 MapReduce 算法模型，只支持 equi-joins（等值 join），要實(shí)現(xiàn)上述的非等值 join，你可以采用笛卡兒積（ full Cartesian product ）來實(shí)現(xiàn)：

SELECT t.statdate,
       c.cname,
       count(t.cookieid)
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t
WHERE t.statdate>='20140818'
  AND t.statdate<='20140824'
  AND platform='pc'
  AND (t.area1= c.cname
       OR t.area2 =c.cname
       OR t.area3 = c.cname)
GROUP BY t.statdate,
         c.cname;

然后再拿著這條語句執(zhí)行下。

3、優(yōu)化：reduce side join VS Cartesian product

如果你真的把這條語句放到 Hive 上執(zhí)行，然后恰好你有張表還非常大，那么恭喜你。。。集群管理員估計(jì)會找你的麻煩了。。。

友情提示：笛卡兒積這種語句在 Hive 下慎用，大數(shù)據(jù)場景下的 m * n 映射結(jié)果你懂的。。。對此，Hive 特意提供了一個環(huán)境變量：hive.mapred.mode=strict; 防止笛卡兒積的執(zhí)行：

FAILED: SemanticException [Error 10052]: In strict mode, cartesian product is not allowed. If you really want to perform the operation, set hive.mapred.mode=nonstrict

從 2 中的觀察得知我們在 on 后面跟 join 條件，走的是 reduce side join，如果你在 where 后跟則是走 Cartesian product，但是這里單條 sql 又沒法實(shí)現(xiàn) reduce side join，還有沒有其它辦法呢？

4、改寫非等值 join：union all

既然不允許非等值 join，那我們換一下思路，多個子查詢 union all，然后匯總：

SELECT dt,
       name,
       count(cid)
FROM
  (SELECT t.statdate dt,
          c.cname name,
          t.cookieid cid
   FROM tmpdb.city c
   JOIN ecdata.ext_trackflow t ON t.area1 =c.cname
   WHERE t.statdate>='20140818'
     AND t.statdate<='20140824'
     AND platform='pc'
   UNION ALL SELECT t.statdate dt,
                    c.cname name,
                    t.cookieid cid
   FROM tmpdb.city c
   JOIN ecdata.ext_trackflow t ON t.area2 =c.cname
   WHERE t.statdate>='20140818'
     AND t.statdate<='20140824'
     AND platform='pc'
   UNION ALL SELECT t.statdate dt,
                    c.cname name,
                    t.cookieid cid
   FROM tmpdb.city c
   JOIN ecdata.ext_trackflow t ON t.area3 =c.cname
   WHERE t.statdate>='20140818'
     AND t.statdate<='20140824'
     AND platform='pc') tmp_trackflow
GROUP BY dt,
         name;

5、優(yōu)化：map side join

上述語句走的是 reduce side join，從我們的需求及業(yè)務(wù)得知，tmpdb.city 是一張字典表，數(shù)據(jù)量很小，因此我們可以試試把上述的語句改寫成 mapjoin：

SELECT dt,
       name,
       count(cid)
FROM
  (SELECT /*+ MAPJOIN(c) */ t.statdate dt,
                            c.cname name,
                            t.cookieid cid
   FROM tmpdb.city c
   JOIN ecdata.ext_trackflow t ON t.area1 =c.cname
   WHERE t.statdate>='20140818'
     AND t.statdate<='20140824'
     AND platform='pc'
   UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
                                      c.cname name,
                                      t.cookieid cid
   FROM tmpdb.city c
   JOIN ecdata.ext_trackflow t ON t.area2 =c.cname
   WHERE t.statdate>='20140818'
     AND t.statdate<='20140824'
     AND platform='pc'
   UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
                                      c.cname name,
                                      t.cookieid cid
   FROM tmpdb.city c
   JOIN ecdata.ext_trackflow t ON t.area3 =c.cname
   WHERE t.statdate>='20140818'
     AND t.statdate<='20140824'
     AND platform='pc') tmp_trackflow
GROUP BY dt,
         name;

6、優(yōu)化無極限：開啟 parallel 和 控制 reduce 個數(shù)

上述語句執(zhí)行時，你可以看到執(zhí)行計(jì)劃和狀態(tài)信息，以及結(jié)合你的 union all 語句可知，三個 union 語句之間沒有依賴關(guān)系，其實(shí)是可以并行執(zhí)行的：

explain SQL...
...
STAGE DEPENDENCIES:
  Stage-11 is a root stage
  Stage-1 depends on stages: Stage-11
  Stage-2 depends on stages: Stage-1
  Stage-3 depends on stages: Stage-2, Stage-6, Stage-9
  Stage-12 is a root stage
  Stage-5 depends on stages: Stage-12
  Stage-6 depends on stages: Stage-5
  Stage-13 is a root stage
  Stage-8 depends on stages: Stage-13
  Stage-9 depends on stages: Stage-8
  Stage-0 is a root stage
...

我們在 SQL 前加上如下環(huán)境變量選項(xiàng)：

set mapred.reduce.tasks=60;
set hive.exec.parallel=true;

讓執(zhí)行計(jì)劃中的 Stage-11、Stage-12、Stage-13 并行執(zhí)行，并控制好 reduce task 個數(shù)。

完整的語句如下：

hive -e "
SET mapred.reduce.tasks=60;


SET hive.exec.parallel=TRUE;


SELECT dt,
       name,
       count(cid)
FROM
  (SELECT /*+ MAPJOIN(c) */ t.statdate dt,
                            c.cname name,
                            t.cookieid cid
   FROM tmpdb.city c
   JOIN ecdata.ext_trackflow t ON t.area1 =c.cname
   WHERE t.statdate>='20140818'
     AND t.statdate<='20140824'
     AND platform='pc'
   UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
                                      c.cname name,
                                      t.cookieid cid
   FROM tmpdb.city c
   JOIN ecdata.ext_trackflow t ON t.area2 =c.cname
   WHERE t.statdate>='20140818'
     AND t.statdate<='20140824'
     AND platform='pc'
   UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
                                      c.cname name,
                                      t.cookieid cid
   FROM tmpdb.city c
   JOIN ecdata.ext_trackflow t ON t.area3 =c.cname
   WHERE t.statdate>='20140818'
     AND t.statdate<='20140824'
     AND platform='pc') tmp_trackflow
GROUP BY dt,
         name;

" > a1.txt

最后的優(yōu)化效果是：2 中的語句三個小時沒出結(jié)果。。。5 比 4 快 8 倍左右，6 比 5 快 2 倍左右，最終 10min 出結(jié)果。

7、最后的問題：

在 6 的語句執(zhí)行的時候你會發(fā)現(xiàn)，其掃描了 三遍 源文件。而 hive 本身是對 union all 的 join 做了優(yōu)化的，當(dāng)多個 union all 子查詢同一張表時，只掃描一次源文件，但這里為什么會三個子查詢各掃描一次呢？

可能是這里的 union all 子查詢使用了 join 的緣故，導(dǎo)致 hive 的 union all 執(zhí)行計(jì)劃優(yōu)化失效了。

8、關(guān)于 hive 中的 笛卡爾集（ full Cartesian product ）

在JION接連查詢中沒有ON連接key，而通過WHERE條件語句會產(chǎn)生笛卡爾集。
Hive本身是不支持笛卡爾集的，不能用select T1.*, T2.* from table1, table2這種語法。但有時候確實(shí)需要用到笛卡爾集的時候，可以用下面的語法來實(shí)現(xiàn)同樣的效果：
select T1.*, T2.* from table1 T1 join table2 T2 where 1=1;
注意在Hive的Strict模式下不能用這種語法，因?yàn)檫@樣會產(chǎn)生笛卡爾集，而這種模式禁止產(chǎn)生笛卡爾集。需要先用set hive.mapred.mode=nonstrict;設(shè)為非strict模式就可以用了，或者將where改為on連接。
select T1.*, T2.* from table1 T1 join table2 T2 on  T1.id=T2.id;

9、關(guān)于Strict Mode

Hive中的嚴(yán)格模式可以防止用戶發(fā)出（可以有問題）的查詢無意中造成不良的影響。 將hive.mapred.mode設(shè)置成strict可以禁止三種類型的查詢：
1）、在一個分區(qū)表上，如果沒有在WHERE條件中指明具體的分區(qū)，那么這是不允許的，換句話說，不允許在分區(qū)表上全表掃描。這種限制的原因是分區(qū)表通常會持非常大的數(shù)據(jù)集并且可能數(shù)據(jù)增長迅速，對這樣的一個大表做全表掃描會消耗大量資源，必須要再WHERE過濾條件中具體指明分區(qū)才可以執(zhí)行成功的查詢。
2）、第二種是禁止執(zhí)行有ORDER BY的排序要求但沒有LIMIT語句的HiveQL查詢。因?yàn)镺RDER BY全局查詢會導(dǎo)致有一個單一的reducer對所有的查詢結(jié)果排序，如果對大數(shù)據(jù)集做排序，這將導(dǎo)致不可預(yù)期的執(zhí)行時間，必須要加上limit條件才可以執(zhí)行成功的查詢。
3）、第三種是禁止產(chǎn)生笛卡爾集。在JION接連查詢中沒有ON連接key而通過WHERE條件語句會產(chǎn)生笛卡爾集，需要改為JOIN...ON語句。

關(guān)于“Hive中join如何優(yōu)化”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，使各位可以學(xué)到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。