How to Join Two tables in HIVE.

Question

I have two tables A and B that both have the following structure.

// Table A

Name    Age    actualdate    no

// Table B

City    sdate  edate         id

I wish to obtain all the fields from A and B using JOIN where, id = no and sdate <= actualdate and edate >= actualdate.

I tried as below using where clause, but it's not working.

select v3.*, t3.* from A v3
JOIN
B t3
where v3.id = t3.no and
      v3.sdate <= t3.actualdate and
      v3.edate >= t3.actualdate
limit 1;

Using On clause :

select v3.*, t3.* from A v3
    JOIN
    B t3
    ON ( v3.id = t3.no and
          v3.sdate <= t3.actualdate and
          v3.edate >= t3.actualdate )
    limit 1;

Unfortunately, Hive doesn't support equijoin. Is there a way to achieve the above using joins?

Answer 1

Instead of using the <= and >= conditions inside the ON clause, you can use WHERE to do the filtering.

select v3.*, t3.* from A t3
JOIN
B v3
ON ( v3.id = t3.no)
WHERE v3.sdate <= t3.actualdate and
      v3.edate >= t3.actualdate
limit 1;

Note: your t3 and v3 aliases were interchanged.