Hive Optimization Techniques in Hadoop 2.x

Enable the below Properties in hive SQL for large volumes of data:

SET hive.execution.engine = tez;
SET mapreduce.framework.name=yarn-tez;
SET tez.queue.name=SIU;
SET hive.vectorized.execution.enabled=true;
SET hive.auto.convert.join=true;
SET hive.compute.query.using.stats = true;
SET hive.stats.fetch.column.stats = true;
SET hive.stats.fetch.partition.stats = true;
SET hive.cbo.enable = true;
SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.parallel=true;
SET hive.exec.mode.local.auto=true;
SET hive.exec.reducers.bytes.per.reducer=1000000000; (Depends on your total size of all tables in the hql)
SET hive.mapjoin.smalltable.filesize=1000000000;
SET hive.auto.convert.join.noconditionaltask.size=25000000; (Depends on Map Memory Capacity)
SET hive.hadoop.supports.splittable.combineinputformat=true;
SET hive.mapjoin.optimized.keys=true;
SET hive.mapjoin.lazy.hashtable=true;
SET hive.exec.parallel.thread.number=16;
SET hive.merge.mapfiles=true;
SET hive.merge.mapredfiles=true;
SET hive.optimize.skewjoin=true;
SET hive.optimize.bucketmapjoin=true;
SET hive.mapred.supports.subdirectories=true;
SET mapred.input.dir.recursive=true;
SET mapreduce.job.reduces=-1;
SET hive.exec.compress.intermediate=true;
SET hive.exec.compress.output=true;
SET tez.runtime.intermediate-input.is-compressed=true;
SET tez.runtime.intermediate-output.should-compress=true;