Good afternoon, please tell me whether the output will be the same for these two situations? What are the alternatives if you need to use three or more different joins inside a single hive query.
from a
join b on a.key=b.key
join c on a.key=c.key
left join u on a.key=u.key
_______
from a
join b on a.key=b.key
left join u on a.key=u.key
join c on a.key=c.key
The result should be the same but performance may be different. Joins can duplicate or reduce the number of rows.
All kind of joins can duplicate rows (except left semi join, which may or may not reduce the number of rows) if the join key is not unique. This is normal behavior and can be done intentionally. Each subsequent join after the duplicating one will process more rows and this will definitely affect performance.
Check that join key is unique in the table you are joining with to eliminate duplication.
Inner join can also reduce the number of rows (like left-semi-join) if keys are not matching. Subsequent joins will process less rows and this will improve performance. At the same time inner join can duplicate rows(see above).
Joins which reduce the dataset should be done before other joins if you want to improve performance. Filter in join ON condition if possible because it more efficient than WHERE clause, which is being applied after all joins. Joins duplicating rows(intentionally) should be executed last.
Normally CBO decides which joins should be done first based on statistics. Alternatively you can explicitly group joins into subqueries to make sure that joins reducing the dataset are being executed first. For example like this:
from
(--Do inner joins first to reduce the dataset before left join
--The order of Inner joins also matters, most restrictive one
--should be performed first, you can add more subqueries to make the order explicit
select a.key, ...
from a
join b on a.key=b.key
join c on a.key=c.key
)s --subquery with inner joins will be executed before left join
left join u on s.key=u.key
Also Map joins can be combined and executed on single vertex. Read this answer.
Yes, they are equivalent, although the results may not be in the same order in the result set. And if you used select *, then the columns would be in a different order.
The reason is a little subtle -- the outer joined table is not used anywhere else in the FROM clause. So, you don't have to worry about NULL values from non-matching rows.
As a general rule, I order joins in the FROM clause starting with inner joins and followed by outer joins. The clause becomes quite difficult to accurately follow when you start mixing join types. So, I recommend:
from a join
b
on a.key = b.key join
c
on a.key = c.key left join
u
on a.key = u.key
