Workaround for non-equi left join in the hive

vikash11

Non-equi joins are not supported by Hive: The popular work around is to relocate the join condition to the where clause, which works great when you want an inner join. What about a left join, though?
One of these examples given here is supposed we have an orderLineItem table, and we need to link to a ProductPrice table that includes a productID, price & a date period for which the price applies. We'd want to connect this where ProductID=ProductID and OrderDate are between the start and end dates. I still want to display all orderLineItems if a productID or a suitable date range does not match.
If I implement the normal solution and shift the non-equi filter to the where clause, it becomes an inner join. In the examples above, SQL tinker, and below, I have a product ID that isn't in the lookup.
Given that hive does not accept non-equi-joins, How is a left non-equivalence achieved?
Tables:

CREATE TABLE OrderLineItem(
  LineItemIDId int IDENTITY(1,1),
  OrderID int  NOT NULL,
  ProductID int NOT NULL,
  OrderDate Date
);
 
 
CREATE TABLE ProductPrice(
  ProductID int,
  Cost float,
  startDate  Date,
  EndDate  Date
 
 
);

fcioanca

Hi @vikash11

Is your post related to a specific course? Or a specific forum category?

Regards,

Flavia