3

I am having a Hive query like the one below:

select a.x as column from table1 a where a.y in (<long comma-separated list of parameters>)
union all
select b.x as column from table2 b where b.y in (<long comma-separated list of parameters>)

I have set hive.exec.parallel as true which is helping me achieve parallelism between the two queries between union all.

But, my IN clause has many comma separated values and each value is taken once in 1 job and then the next value. This is actually getting executed sequentially.

Is there any hive parameter which if enabled can help me fetch data parallelly for the parameters in the IN clause?

Currently, the solution I am having is fire the select query with = multiple times instead of one IN clause.

leftjoin
  • 36,950
  • 8
  • 57
  • 116
vijayinani
  • 2,548
  • 2
  • 26
  • 48

1 Answers1

2

There is no need to read the same data many times in separate queries to achieve better parallelism. Tune proper mapper and reducer parallelism for the same.

First of all, enable PPD with vectorizing, use CBO and Tez:

SET hive.optimize.ppd=true;
SET hive.optimize.ppd.storage=true;
SET hive.vectorized.execution.enabled=true;
SET hive.vectorized.execution.reduce.enabled = true;
SET hive.cbo.enable=true;
set hive.stats.autogather=true;
set hive.compute.query.using.stats=true;
set hive.stats.fetch.partition.stats=true;
set hive.execution.engine=tez;
SET hive.stats.fetch.column.stats=true;
SET hive.tez.auto.reducer.parallelism=true;

Example settings for Mappers on Tez:

set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
set tez.grouping.max-size=32000000;
set tez.grouping.min-size=32000;

Example settings for Mappers if you decide to run on MR instead of Tez:

set mapreduce.input.fileinputformat.split.minsize=32000; 
set mapreduce.input.fileinputformat.split.maxsize=32000000;

--example settings for reducers: 

set hive.exec.reducers.bytes.per.reducer=32000000; --decrease this to increase the number of reducers, increase to reduce parallelism

Play with these settings. Success criteria is more mappers/reducers and your map and reduce stages are running faster.

Read this article for better understanding of how to tune Tez: https://community.hortonworks.com/articles/14309/demystify-tez-tuning-step-by-step.html

leftjoin
  • 36,950
  • 8
  • 57
  • 116
  • Thanks for the answer. hive.vectorized.execution.enabled cannot be used as my data is not in ORC format. It is in Avro format. Check https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties – vijayinani Jan 28 '18 at 15:42
  • hive.cbo.enable cannot be used as I have hive version 0.13.1 – vijayinani Jan 28 '18 at 15:43
  • Tez is not present in Cloudera 5.3.3 :( – vijayinani Jan 28 '18 at 15:43
  • @vijayinani Use mapper settings for MR then. And config for reducers works both for Tez and MR. – leftjoin Jan 28 '18 at 16:05
  • @leftjoin, Thanks for this answer, am running query to remove duplicates (240Million records), is there any way to decide what value should we use for - mapreduce.input.fileinputformat.split.maxsize and hive.exec.reducers.bytes.per.reducer – Vijiy Jun 08 '20 at 02:30
  • @Vijiy Experimentally. chose best configuration based on performance. It depends on your cluster capacity also. If too many mappers(reducers) started, they may waiting in queue. Also it depends on the task they are doing. – leftjoin Jun 08 '20 at 07:22