tez | lightweight Trainer for PyTorch. It also comes | Machine Learning library

Please check my answer to this same issue here Enable hive parallel processing

That post explains what you need to do to enable parallel processing.

For example, when using tez, you need to use the tez.name.queue={your queue name} in order to fully utilize it, this enables parallelism in yarn.

For spark, you need to specify the --queue {your queue name} when launching pyspark, or when submitting jobs using the spark_submit.

In order to use the above, you need to have queues set up in yarn using the hive.server2.tez.default.queues, parameter that you need to set up with the list of default queues for tez. It is important to note that you can create the queues and not list them as default, by doing that you need need to call out the queue manually all the time and the queries are not going to get into any default queue.

Percentage of physical CPU allocated for all containers on a node 80%

Number of virtual cores 8

https://docs.microsoft.com/en-us/azure/hdinsight/hdinsight-hadoop-hive-out-of-memory-error-oom

But, I think there are some cases which might explain why this feature is still configurable;

• You may want to disable it for workaround purpose. For example, this hive ticket is still unresolved and when tez.am.container.reuse.enabled=false the problematic query works fine. If my production case is critical, instead of being completely blocked, I may prefer running my jobs without re-using the containers.
• The property may conflict with some other properties, and based on your priority, you may wanna give up on performance. For example in Configure Tez Container Reuse doc, there is a warning which says;

Do not use the tez.queue.name configuration parameter because it sets all Tez jobs to run on one particular queue.

• As a last item, I saw another warning on this doc;

Enabling this parameter improves performance by avoiding the memory overhead of reallocating container resources for every task. However, disable this parameter if the tasks contain memory leaks or use static variables.

1. Removed the records which had no value i.e. order_id=''
2. Performed the JOIN in batches rather than doing all in one go
3. Referred the below for setting certain hive properties:

hive properties

• set hive.auto.convert.join = false;
• set hive.exec.parallel=true;
• set hive.exec.compress.output=true;
• set hive.exec.parallel=true;
• set hive.cbo.enable=true;
• set hive.compute.query.using.stats=true;
• set hive.stats.fetch.column.stats=true;
• set hive.stats.fetch.partition.stats=true;