Left outer join in HIVE causing an error

Question

I am trying to perform a left outer join on 2 tables in Hive. The join condition here involves a LIKE operator. This is the query that I am trying to execute:

SELECT *
FROM a 
LEFT OUTER JOIN 
    (SELECT * 
     FROM b  
     WHERE class = 'ex') ON b.mo LIKE CONCAT(a.device_name ,'%')
                         AND a.time_stamp BETWEEN to_utc_timestamp(from_unixtime(cast(b.first_time AS BIGINT) - 660), "z")
                                          AND to_utc_timestamp(from_unixtime(cast(b.first_time AS BIGINT) + 60),"z")

When I execute this query, I get an error

Both left and right aliases encountered in JOIN ''%''

The aim here to perform a left outer join on the tables a and b on the given conditions i.e. based on the device name and time_stamp.

If someone could help me out with this, I would really appreciate it.

Vamsi Prabhala Vamsi Prabhala · Accepted Answer · 2017-08-14T14:13:34

like isn't supported in the join conditions. One way around this is to specify the condition in the where clause.

SELECT * --better specify columns needed instead of *
FROM a 
LEFT OUTER JOIN (SELECT * from b  WHERE class = 'ex') b ON 1=1
WHERE b.mo LIKE CONCAT(a.device_name ,'%')
AND a.time_stamp 
BETWEEN to_utc_timestamp(from_unixtime(cast(b.first_time AS BIGINT) - 660),"z")
AND to_utc_timestamp(from_unixtime(cast(b.first_time AS BIGINT) + 60),"z")

But the problem with this approach is, the join turns to an inner join as you are filtering the right table (b in this case).

Left outer join in HIVE causing an error

1 Answers