Hive Query Optimization

[Soupam Mandal <so...@gmail.com>]

0

We have 7 tables and each table is partitioned by record_date.There is a
query which involves inner join with all these tables and join is based on
consumer_id. The join involves multiple partition join. Currently querying
1 week data takes very long time around 20-30 mins. We want to optimize
this query. The root cause of this slowness is the data shuffling and map
reduce.Each table contains around 8TB data on compression. Table is
compressed with ORC-Zlib.

We tried bucketing along with the partitioning. So we bucketed each
partition of the table into 100 buckets based on the consumer_id as
consumer_id is the joining key. When there is a query involving one
partition there is improvement in the query perf as it uses merge join. But
whenever there is a query involving multiple partitions the query plan
starts shuffling data and no improvements in query perf. We are using hive
tez engine and vectorization is enabled.Can anyone suggest what approach we
should follow when there is query with multiple tables join and multiple
partitions?