Now here's where it gets fun :)
First, I do want to show you that (given sufficient coffee) there is a set
theoretic approach to your first question that allows you to solve it with just
one map-reduce job (a single cogroup) and not two (a cogroup followed by a
group). Consider two sets, A and B where |A| is the number of elements in A and
|B| is the number of elements in B.
Let |AUB| be the size of the set union of A and B. Note, Pig does not have a
set union operator. The UNION operator in Pig is a misnomer. Plus, you cant use
it in a nested projection which is frustrating...
Let |A^B| be the size of the set intersection of A and B. (The number of
elements that are in BOTH A and B.
What you're technically after is |A^B|. However, since Pig does not have a set
intersection operator, and I'm assuming writing a UDF is out of the question
for you, we can be a bit more clever. As it turns out Pig has a DIFF operator.
It takes two bags (basically sets although duplicate elements are allowed) and
returns all the elements that are in either bag but NOT in both. Notice:
|AUB| = |A^B| + |DIFF(A,B)| and
|AUB| = |A| + |B| - |A^B| therefor
|A^B| = 1/2*( |A| + |B| - |DIFF(A,B)| )
All of which we can compute with native Pig :)
So:
A = load 'file1.txt' as (q:chararray, d:chararray);
B = load 'file2.txt' as (q:chararray, d:chararray);
counts = foreach (cogroup A by q, B by q) {
a_size = COUNT(A); -- |A|
b_size = COUNT(B); -- |B|
diff_size = COUNT(DIFF(A,B)); -- |DIFF(A,B)
match_size = (a_size + b_size - diff_size)/2l; -- 1/2*(|A| + |B| -
|DIFF(A,B)|) = |A intersect B|
generate
group as q,
match_size;
};
dump counts;
Alright, back to your other issue of adding the matching elements. Again, if
you were up for it, you could simply write a set intersection udf and be done
with it. Otherwise, here's what I came up with:
A = load 'file1.txt' as (q:chararray, d:chararray);
B = load 'file2.txt' as (q:chararray, d:chararray);
counts = foreach (cogroup A by (q,d), B by (q,d)) {
num_matches = MIN(TOBAG(COUNT(A), COUNT(B)));
generate
flatten(group) as (q,d),
num_matches as num_matches;
};
all_matches = foreach (group counts by q) {
match_set = filter counts by num_matches > 0;
match_set = match_set.d;
generate
group as q,
SUM(counts.num_matches) as total_matches,
match_set as match_set;
};
dump all_matches;
(q1,2,{(d1),(d2)})
(q2,0,{})
(q3,0,{})
The empty curly braces indicate bags that contain no tuples.
--jacob
@thedatachef
On Jun 21, 2013, at 6:14 AM, Siddhi Borkar wrote:
> Thanks a lot the solution worked fine. Is it possible also to display the
> comma separated matching d's?
>
> For ex
> (q1,2, {d1,d2})
> (q2,0)
> (q3,0)
>
> -----Original Message-----
> From: Chris Hokamp [mailto:[email protected]]
> Sent: Friday, June 21, 2013 1:52 AM
> To: [email protected]; Barclay Dunn
> Subject: Re: comparing two files using pig
>
> Z
>
>
> Sent from Samsung Mobile
>
> -------- Original message --------
> From: Jacob Perkins <[email protected]>
> Date: 20/06/2013 20:30 (GMT+00:00)
> To: Barclay Dunn <[email protected]>
> Cc: [email protected]
> Subject: Re: comparing two files using pig
>
> I did not read you original post clearly enough. I didn't realize both the d
> AND the q had to match. It's only slightly more complex, just add the d
> column to the cogroup statement and sum the number of matches:
>
> A = load 'file1.txt' as (q:chararray, d:chararray); B = load 'file2.txt' as
> (q:chararray, d:chararray);
>
> counts = foreach (cogroup A by (q,d), B by (q,d)) {
> num_matches = MIN(TOBAG(COUNT(A), COUNT(B)));
> generate
> flatten(group) as (q,d),
> num_matches as num_matches;
> };
>
> all_matches = foreach (group counts by q) generate group as q,
> SUM(counts.num_matches) as total_matches;
>
> dump all_matches;
>
> (q1,2)
> (q2,0)
> (q3,0)
>
> --jacob
> @thedatachef
>
> On 06/20/2013 02:06 PM, Barclay Dunn wrote:
>> Jacob,
>>
>> If I run that code with an added row in file2.txt, e.g.,
>>
>> $ cat file2.txt
>> q1 d1
>> q1 d2
>> q3 d3
>> q2 d4
>>
>> This gives me mistaken results, i.e.,
>>
>> (q1,2)
>> (q2,1)
>> (q3,0)
>>
>>
>> I am new at this so I apologize for the ponderous pace of the
>> following. It can no doubt be shortened. But it gets the correct
>> results with either data set.
>>
>> set io.sort.mb 10; -- avoid java.lang.OutOfMemoryError: Java
>> heap space (execmode: -x local)
>>
>> A = LOAD '../../../input/file1.txt' using PigStorage(' ') as
>> (aa:chararray, ab:chararray); B = LOAD '../../../input/file2.txt'
>> using PigStorage(' ') as (ba:chararray, bb:chararray);
>>
>> C = UNION A, B;
>> D = COGROUP C by ($0, $1);
>>
>> F = FOREACH D GENERATE FLATTEN($0), COUNT($1);
>>
>> G0 = FILTER F BY $2 > 1; -- any that match
>> G1 = FILTER F BY $2 < 2; -- any that don't match
>>
>> H0 = GROUP G0 BY $0;
>> H1 = GROUP G1 BY $0;
>>
>>
>> J0 = FOREACH H0 GENERATE $0, COUNT($1);
>> J1 = FOREACH H1 GENERATE $0, 0;
>>
>> K = UNION J0, J1;
>>
>> DUMP K;
>> /*
>> (q2,0)
>> (q3,0)
>> (q1,2)
>> */
>>
>>
>> Barclay Dunn
>>
>>
>> On 6/20/13 10:07 AM, Jacob Perkins wrote:
>>> Hi,
>>>
>>> This should just be a simple cogroup.
>>>
>>> A = load 'file1.txt' as (q:chararray, d:chararray); B = load
>>> 'file2.txt' as (q:chararray, d:chararray);
>>>
>>> counts = foreach (cogroup A by q, B by q) {
>>> num_matches = MIN(TOBAG(COUNT(A), COUNT(B)));
>>> generate
>>> group as q,
>>> num_matches as num_matches;
>>> };
>>>
>>> dump counts;
>>>
>>> (q1,2)
>>> (q2,0)
>>> (q3,0)
>>>
>>> --jacob
>>> @thedatachef
>>>
>>> On Jun 20, 2013, at 4:00 AM, Siddhi Borkar wrote:
>>>
>>>> Hi,
>>>>
>>>> I have a problem statement where in I have to compare two files and get
>>>> the count of matching attributes.
>>>>
>>>> For ex:
>>>> File 1: file1.txt
>>>>
>>>> q1 d1
>>>> q1 d2
>>>> q2 d3
>>>> q2 d1
>>>>
>>>> File 2: file2.txt
>>>> q1 d1
>>>> q1 d2
>>>> q3 d3
>>>>
>>>> Now what I need is for each distinct q the count of matching d's
>>>>
>>>> For ex, the output should be
>>>> q1 2 (q1 d1 and q1 d2 are matching in both
>>>> the files hence count is 2)
>>>> q2 0 (has no d's matching)
>>>> q3 0
>>>>
>>>> Any idea how this can be achieved?
>>>>
>>>> Thnx in advance
>>>>
>>>> -Sid
>>>>
>>>>
>>>>
>>>> DISCLAIMER
>>>> ==========
>>>> This e-mail may contain privileged and confidential information which is
>>>> the property of Persistent Systems Ltd. It is intended only for the use of
>>>> the individual or entity to which it is addressed. If you are not the
>>>> intended recipient, you are not authorized to read, retain, copy, print,
>>>> distribute or use this message. If you have received this communication in
>>>> error, please notify the sender and delete all copies of this message.
>>>> Persistent Systems Ltd. does not accept any liability for virus infected
>>>> mails.
>>
>
>
> DISCLAIMER
> ==========
> This e-mail may contain privileged and confidential information which is the
> property of Persistent Systems Ltd. It is intended only for the use of the
> individual or entity to which it is addressed. If you are not the intended
> recipient, you are not authorized to read, retain, copy, print, distribute or
> use this message. If you have received this communication in error, please
> notify the sender and delete all copies of this message. Persistent Systems
> Ltd. does not accept any liability for virus infected mails.
