Search code examples
apache-pig

Filtering inside a FOREACH block by a condition value calculated inside the different blocks in PIG script

I have 2 datasets, and I need to find matching records which match records from dataset 1 to dataset 2, as such :

dataset 1 = [sourceID, details, key]
1, details1, 1111
2, details2, 1112
3, details3, 1113
4, details4, 1114
...

dataset2 = [key1, key2, number]
1111,1112,3
1111,1114,1
1112,1113,11
 ...

output: 
1, details1, 1111, 2, details2, 1112, 3
1, details1,1111, 4, details4, 1114, 1
2, details2, 1112, 3, details3, 11
....

I tried as follows:

a = foreach dataset1 {
        b = filter dataset2 by dataset1.key1 matches dataset1.key;
        c = filter dataset2 by datset1.key2 matches dataset1.key; 
        generate b, c;
    };

Any help would be great, please.

Many thanks.

Solution

  • Run two joins?

    B = join dataset1 by key, dataset2 by key1;
C = join dataset1 by key, B by key2;