An example of hive join Optimization

Because hive is very different from traditional relational databases in business scenarios and underlying technical architecture, some skills in the traditional database field may no longer be applied to hive. The article on the optimization, principles, and applications of hive is also described in the previous sections. However, most of them tend to be at the theoretical level. This article will introduce an example, step by step from the instance to deepen the understanding and awareness of hive optimization.

1. Requirements

I have simplified the requirements, and it is very simple. I want to join two tables to find the PV of a specified city and every day, and write it out using the traditional rdbms SQL:

SELECT t.statdate,       c.cname,       count(t.cookieid)FROM tmpdb.city cJOIN 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;

How is it? Is it okay to understand the requirements based on SQL? 2. Non-equivalent join

Then paste this SQL statement into hive for execution, and then you will find an error:

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

This is because hive is limited by the mapreduce Algorithm Model and only supports equi-Joins (equivalent join). To implement the above non-equivalent join, you can use the full Cartesian product to implement it:

SELECT t.statdate,       c.cname,       count(t.cookieid)FROM tmpdb.city cJOIN ecdata.ext_trackflow tWHERE 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;

Then execute the statement. 3. Optimization: reduce side join vs Cartesian Product

If you really put this statement on hive for execution, and you happen to have a very large table, congratulations... The Cluster Administrator may find you in trouble...

Friendly reminder: You should be cautious when using the dequeer product statement in hive. You can understand the M * n ing results in Big Data scenarios... In this regard, hive provides an environment variable: hive. mapred. mode = strict; to prevent the execution of the flute product:

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

We learned from the observations in 2 that we followed the join condition after on and followed the reduce side join condition. If you followed the WHERE clause, the Cartesian product is used, however, a single SQL statement cannot implement reduce side join. Is there any other way? 4. Rewrite non-equivalent join: Union all

Since non-equivalent join is not allowed, let's take a look at the problem. Multiple subqueries are union all, and then the summary is as follows:

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_trackflowGROUP BY dt,         name;

5. Optimization: Map side join

The preceding statement uses reduce side join. We know from our needs and business that tmpdb. City is a dictionary table with a small amount of data. Therefore, we can try to rewrite the preceding statement to 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_trackflowGROUP BY dt,         name;

6. Unlimited optimization: Enable parallel and control reduce count

When the preceding statement is executed, you can see the execution plan and status information. Combined with your union all statement, we can see that there is no dependency between the three Union statements. In fact, they can be executed in parallel:

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...

Add the following environment variable options before SQL:

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

Execute stage-11, stage-12, and stage-13 in parallel in the execution plan, and control the number of reduce tasks.

The complete statement is as follows:

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_trackflowGROUP BY dt,         name;" > a1.txt

The final optimization result is: the statement in 2 has no results in three hours... 5 is about 8 times faster than 4, and 6 is about 2 times faster than 5.

7. Last question:

When executing Statement 6, you will find that the source file is scanned three times. Hive itself optimizes the join of Union all. When multiple Union all subqueries on the same table, only the source file is scanned once, but why does the three subqueries Scan each time?

This may be because join is used in the Union all subquery, which invalidates the optimization of the Union all execution plan of hive.

If you have a better optimization solution, please leave a message to us.

8. refer:

[1] hive query-joining two tables on three joining conditions with or operator

Http://stackoverflow.com/questions/16272804/hive-query-joining-two-tables-on-three-joining-conditions-with-or-operator

[2] languagemanual joinoptimization

Https://cwiki.apache.org/confluence/display/Hive/LanguageManual+JoinOptimization

[3] hive execution plan

Http://yychao.iteye.com/blog/1749562

[4] hive SQL parsing/execution plan Generation Process Analysis

Http://yanbohappy.sinaapp.com /? P = 265

[5] SQL Performance Optimization in data warehouse (hive)

Http://www.zihou.me/html/2014/02/12/9207.html

[6] hive optimization and execution principles

Http://www.smartcitychina.cn/upload/2014-01/14012015376829.pdf

[7] hive job optimization Summary

Http://my.oschina.net/yangzhiyuan/blog/262910

An example of hive join Optimization