[Hive] Hive Tuning: Making tasks run in parallel

Business background

The EXTRACT_TRFC_PAGE_KPI hive SQL is as follows:

 set Mapred.job.queue.name=pms; set hive. exec. reducers. Max=8; Set mapred.reduce.tasks=8; set mapred.job.name=extract_trfc_page_kpi;InsertOverwriteTablePMS.EXTRACT_TRFC_PAGE_KPI Partition (ds=' $yesterday ')Select distinctPAGE_TYPE_ID, PV, UV,' $yesterday 'Update_time from(--For PC, H5SelectPAGE_TYPE_ID,sum(PV) asPvsum(UV) asUv fromDw.rpt_trfc_page_kpiwhereDS =' $yesterday '  andStat_type =1     Group  bypage_type_idUnion  All--PC Search Page Special handlingSelect         5  asPAGE_TYPE_ID,sum(PV) asPvsum(UV) asUv fromDw.rpt_trfc_page_kpiwhereDS =' $yesterday '  andStat_type =1  andpage_type_idinch(Wuyi, the)Union  All--For appsSelectA.PAGE_TYPE_ID,sum(PV) asPvsum(UV) asUv fromDw.rpt_trfc_page_kpi A Left outer Join(Select distinctPAGE_TYPE_ID, old_page_type_id fromTandem.mobile_backend_page_url_rulewhereIs_delete =0) b on(a.page_type_id = b.old_page_type_id)whereA.ds =' $yesterday '  andStat_type =1     Group  bya.page_type_id) T;

There are two union all operations in the SQL above, and it takes 20 minutes to execute them sequentially.

Optimization strategy

Parsing the above SQL, where there is no direct correlation between the three query operations before and after union all, so there is no need for sequential execution, so the idea of optimization is to have these three query operations executed in parallel, and Hive provides the following parameters for parallel operation of the job:

// 开启任务并行执行set hive.exec.parallel=true;// 同一个sql允许并行任务的最大线程数set hive.exec.parallel.thread.number=8;

programme I

Add the above two hive parameters when executing SQL, such as:

 set Mapred.job.queue.name=pms; set hive. exec. reducers. Max=8; Set mapred.reduce.tasks=8; set hive. exec. parallel=true; set hive. exec. Parallel.thread. Number =8;  set mapred.job.name=extract_trfc_page_kpi;InsertOverwriteTablePMS.EXTRACT_TRFC_PAGE_KPI Partition (ds=' $yesterday ')Select distinctPAGE_TYPE_ID, PV, UV,' $yesterday 'Update_time from(--For PC, H5SelectPAGE_TYPE_ID,sum(PV) asPvsum(UV) asUv fromDw.rpt_trfc_page_kpiwhereDS =' $yesterday '  andStat_type =1     Group  bypage_type_idUnion  All--PC Search Page Special handlingSelect         5  asPAGE_TYPE_ID,sum(PV) asPvsum(UV) asUv fromDw.rpt_trfc_page_kpiwhereDS =' $yesterday '  andStat_type =1  andpage_type_idinch(Wuyi, the)Union  All--For appsSelectA.PAGE_TYPE_ID,sum(PV) asPvsum(UV) asUv fromDw.rpt_trfc_page_kpi A Left outer Join(Select distinctPAGE_TYPE_ID, old_page_type_id fromTandem.mobile_backend_page_url_rulewhereIs_delete =0) b on(a.page_type_id = b.old_page_type_id)whereA.ds =' $yesterday '  andStat_type =1     Group  bya.page_type_id) T;

Scenario Two

Set up in Hive-site.xml to view the configuration parameters for the current version of hive:

Hive>Set-V;... hive. exec. Orc. Zerocopy=falsehive. exec. Parallel=falsehive. exec. Parallel. Thread. number=8Hive. exec. Perf. Logger=org. Apache. Hadoop. Hive. QL. Log. PerfloggerHive. exec. Rcfile. Use. Explicit. Header=truehive. exec. Rcfile. Use. Sync. Cache=truehive. exec. reducers. Bytes. Per. Reducer=1000000000Hive. exec. reducers. Max=999Hive. exec. RowOffset=falsehive. exec. ScratchDir=/tmp/hive-pmshive. exec. Script. Allow. Partial. Consumption=falsehive. exec. Script. Maxerrsize=100000Hive. exec. Script. Trust=falsehive. exec. Show. Job. Failure. Debug. Info=true ...

These parameters are configured in $hive_home/conf/hive-site.xml and are now included in this configuration file:

<property >    <name>Hive.exec.parallel</name>    <value>True</value></Property ><property >    <name>Hive.exec.parallel.thread.number</name>    <value>16</value></Property >

Restart Hive and see that the parameters you have just configured are in effect:

Hive>Set-V;... hive. exec. Orc. Skip. Corrupt. Data=falsehive. exec. Orc. Zerocopy=falsehive. exec. Parallel=truehive. exec. Parallel. Thread. number= -Hive. exec. Perf. Logger=org. Apache. Hadoop. Hive. QL. Log. PerfloggerHive. exec. Rcfile. Use. Explicit. Header=truehive. exec. Rcfile. Use. Sync. Cache=truehive. exec. reducers. Bytes. Per. Reducer=1000000000Hive. exec. reducers. Max=999Hive. exec. RowOffset=falsehive. exec. ScratchDir=/tmp/hive-pmshive. exec. Script. Allow. Partial. Consumption=false ...

Conclusion

After testing, adding these two parameters, the Extract_trfc_page_kpi script execution time took 20 minutes, optimized to take 3 minutes.

[Hive] Hive Tuning: Making tasks run in parallel