Execute Hive Query with IN clause parameters in parallel

by

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

More Related Contents:

  1. Hive: Best way to do incremetal updates on a main table
  2. Spark: Inconsistent performance number in scaling number of cores
  3. Difference between Hive internal tables and external tables?
  4. How do I output the results of a HiveQL query to CSV?
  5. What is the difference between spark.sql.shuffle.partitions and spark.default.parallelism?
  6. Why is the final reduce step extremely slow in this MapReduce? (HiveQL, HDFS MapReduce)
  7. Hive query performance for high cardinality field
  8. How to update table in Hive 0.13?
  9. Performance optimization strategies of last resort [closed]
  10. HTTP vs HTTPS performance
  11. Is it safe to shallow clone with –depth 1, create commits, and pull updates again?
  12. How are x86 uops scheduled, exactly?
  13. Why is my Rust program slower than the equivalent Java program?
  14. How can I accurately benchmark unaligned access speed on x86_64?
  15. HIVE select count(*) non null returns higher value than select count(*)
  16. How much overhead does SSL impose?
  17. Faster way to initialize arrays via empty matrix multiplication? (Matlab)
  18. When, if ever, is loop unrolling still useful?
  19. Run-length decoding in MATLAB
  20. Sqoop import : composite primary key and textual primary key
  21. x86_64: is IMUL faster than 2x SHL + 2x ADD?
  22. Number of partitions in RDD and performance in Spark
  23. How to convert .txt file to Hadoop’s sequence file format
  24. SUMIFS function in Google Spreadsheet
  25. Optimizing File Cacheing and HTTP2
  26. Performance of using static methods vs instantiating the class containing the methods
  27. Job queue for Hive action in oozie
  28. preallocate list in R
  29. How to analyze golang memory?
  30. How to avoid Query Plan re-compilation when using IEnumerable.Contains in Entity Framework LINQ queries?

Leave a Comment