Hive Multiple Joins Very Slow

Asked May 05 '18 at 06:52

Active May 05 '18 at 06:52

Viewed 645 times

I have Table A with 60 million records and Table B with 20 million records and I am joining those tables based on key columns . (inner join on a.id=b.id)

I am able to fetch the results within 10 minutes . After that for validating another column in Table C (I am joining the result generated from Table A and Table B ) with Table C(30 million records) and joining the column b with column c.(inner join on b.prfl_id=c.prfl_id)

But the query is very slow and it is running for more than 30 minutes and even then result is not generated. Any suggestion to get the results fast. Thanks in advance for your suggestion .

Regards,

Saravanan

asked May 05 '18 at 06:52

saravanatn

What is the result size of first join in terms of rows? – Jagrut Sharma May 05 '18 at 07:11
Also, what is the format of the underlying data? – Jagrut Sharma May 05 '18 at 07:20
Result size of 1st join is lakhs of data and I am not sure of exact count ... – saravanatn May 05 '18 at 08:03
Format is delimited text format. – saravanatn May 05 '18 at 08:03
1

Convert tables to a more efficient format like orc. Then, try implementing a partitioning strategy and rewriting queries to leverage the partitions. Consider denormalizing the data to avoid expensive joins, since Hive is not a traditional RDBMS. – Jagrut Sharma May 05 '18 at 08:28
1

Try leveraging a map-side join. This can help speed things up if one table is quite small compared to the other. – Jagrut Sharma May 05 '18 at 08:31
1

1. Check joins do not duplicate data (check join keys unique where they should be). 2. Increase parallelism and use vectorizing. See this answer please: https://stackoverflow.com/a/48487306/2700344 – leftjoin May 07 '18 at 07:28

Hive Multiple Joins Very Slow

0 Answers0