Join Table By String Matching In Hive Or Impala Or Pig

I have two tables A and B, where B is huge (20 million by 300) and A is of moderate size (300k by 10). A contains one column that is address and B contains 3 columns that can be pu

Solution 1:

You can use join with equality conditions only, but - You can cross join and filter.

select      A.id, B.Tax
from        A crossjoin B
where       concat(' ',A.Address,' ') like concat('% ',cast(B.Number as string),' %')
        and concat(' ',A.Address,' ') like concat('% ',B.Street_name,' %')
;

---

Demo

hive>createtable A (id int,Address string);
OK
hive>createtable B (number int,Street_name string,Street_suffix string,tax decimal(12,2));
OK
hive>insertinto A values (233,'123 Main St');
Query ID = ...
OK
hive>insertinto B values (123,'Main','Street',320.2);
Query ID = ...
OK
hive>select      A.id, B.Tax
    >from        A crossjoin B
    >where       concat(' ',A.Address,' ') like concat('% ',cast(B.Number as string),' %')
    >and concat(' ',A.Address,' ') like concat('% ',B.Street_name,' %')
    > ;
Warning: Map Join MAPJOIN[8][bigTable=b] in task 'Stage-3:MAPRED'is a cross product
Query ID = ...
OK
233320.2
hive>

Solution 2:

Hive join have limitation you can use join with equality conditions only.

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins

If you could logically explode A.Address column in such away that it matches table B column format then you can use join on that column ....

Solution 3:

First of all, JOINs in hive only work with equality conditions

Refer here

So you can try the where condition with like and concat operation like below

selectA.id, B.TaxfromA,BwhereA.AddressLIKECONCAT('%',cast(B.Number as string),'%') 
               ANDA.AddressLIKECONCAT('%',B.Street_name,'%')