hadoop pig : join on a condition (ex. tab1.COL1 LIKE (%tab2.col2%) )

Question

How to implement a join on a condition in PIG? SQL equivalent Examples:

       select * from tab1, tab2 where instr(t1.col1,t2.col1 ) > 1 ;
       select * from tab1, tab2 where f(t1.col1) =f(t2.col1)  ;

Thank you very much. Filippo

Answer 1

As of now pig supports only Inner Joins,Outer Joins and Full Joins. second Join example can be implemented in Pig, not the other one. Below is an example.

tab1 = LOAD 'file1' using PigStorage('|') using (col1:chararray,col2:chararray);
tab2 = LOAD 'file2' using PigStorage('|') using (col1:chararray,col2:chararray);
result = JOIN tab1 by col1, tab2 by col1;

Answer 2

Try this.

1.

Cross_Table = CROSS tab1, tab2;
Filter_Table = FILTER Cross_Table BY NOT(STARTSWITH(tab1::col1, tab2::col1));

2.

Join_Table = JOIN tab1 BY f(col1) INNER JOIN, tab2 BY f(col1);