Can I tell a few columns from a pig?

Question

I have a usecase in which I need to count a different number of two fields.

Example:

x = LOAD 'testdata' using PigStorage('^A') as (a,b,c,d); y = GROUP x BY a; z = FOREACH y { **bc = DISTINCT xb,xc;** dd = DISTINCT xd; GENERATE FLATTEN(group) as (a), COUNT(bc), COUNT(dd); }; 

Answer 1

You were very close. The key is not to apply DISTINCT to two fields, but instead to apply it to one composite field that you create:

 x = LOAD 'testdata' using PigStorage('^A') as (a,b,c,d); x2 = FOREACH x GENERATE a, TOTUPLE(b,c) AS bc, d y = GROUP x2 BY a; z = FOREACH y { bc = DISTINCT x2.bc; dd = DISTINCT x2.d; GENERATE FLATTEN(group) AS (a), COUNT(bc), COUNT(dd); }; 

Answer 2

IMHO, there is no trivial way (e.g. GROUP(DISTINCT a) in MySQL), so you need to split the table to do two counts for each row.

 x = LOAD 'testdata' using PigStorage('^A') as (a,b,c,d); w1 = FOREACH x GENERATE a, CONCAT(b,c) AS bc; w2 = FOREACH x GENERATE a, d; v1 = DISTINCT w1; v2 = DISTINCT w2; u1 = GROUP v1 BY a; u2 = GROUP v2 BY a; t1 = FOREACH u1 GENERATE group AS a, COUNT(v1.bc); t2 = FOREACH u2 GENERATE group AS a, COUNT(v2.d); s = JOIN t1 BY a, t2 BY a; 

UDF can greatly simplify this.