performance - Execute Hive Query with IN clause parameters in parallel

Question

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.

Answer 1

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