Hive中join如何優化

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

1、需求

需求我做了簡化，很簡單，兩張表做個 join，求指定城市，每天的 pv，用傳統的 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;

怎么樣？根據 SQL 看懂需求沒問題吧？

2、非等值 join 問題

然后把這條 SQL 貼到 hive 中去執行，然后你會發現報錯了：

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

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

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;

然后再拿著這條語句執行下。

3、優化：reduce side join VS Cartesian product

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

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

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 又沒法實現 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、優化：map side join

上述語句走的是 reduce side join，從我們的需求及業務得知，tmpdb.city 是一張字典表，數據量很小，因此我們可以試試把上述的語句改寫成 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、優化無極限：開啟 parallel 和 控制 reduce 個數

上述語句執行時，你可以看到執行計劃和狀態信息，以及結合你的 union all 語句可知，三個 union 語句之間沒有依賴關系，其實是可以并行執行的：

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 前加上如下環境變量選項：

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

讓執行計劃中的 Stage-11、Stage-12、Stage-13 并行執行，并控制好 reduce task 個數。

完整的語句如下：

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

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

7、最后的問題：

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

可能是這里的 union all 子查詢使用了 join 的緣故，導致 hive 的 union all 執行計劃優化失效了。

8、關于 hive 中的 笛卡爾集（ full Cartesian product ）

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

9、關于Strict Mode

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

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