How to filter minimum distinct values in a group by?

Question

Lets say I have a table with the following columns (A,B,C)

How would I write a pig statement to create a group by on a column( A). And then filter where count(column B > 100) and count of (distinct(column C) > 3) ?

From what I have:

I first removed count where B is less than 100

filter_column = FILTER data by b > 100;

Then did a group by on A:

group_1 = GROUP filter_column by A;

How would I now filter this group_1 where the count of distinct values in column C is > 3 ?

Potential duplicate: https://stackoverflow.com/questions/9247296/select-count-distinct-using-pig-latin — Bart Schuijt, Mar 05 '19 at 14:02

score 0 · Answer 1 · answered Mar 05 '19 at 14:01

To accomplish this, you can first aggregate (count column B and distinct column C) prior to filtering down the values.

dataAggregated = FOREACH (GROUP data BY col_a) {
    uniqC = DISTINCT data.col_c;
    GENERATE
        FLATTEN(group) AS col_a,
        COUNT(data.col_b) AS b_count,
        COUNT(uniqC) AS uniq_c_count; }
;

dataFiltered = FILTER dataAggregated BY b_count > 100 AND uniq_c_count > 3;

Filtering a scalar (column B) will not yield the same results as filtering an aggregation (count column B). If you want the aggregation (count unique column C) to take place for records that meet criterium column B > 100, then you should apply that filter first.

How to filter minimum distinct values in a group by?

1 Answers1