Handling duplicate records in PIG Latin

Question

If there are duplicates in the file, the first record should go to valid file and remaining duplicate records should be moved to invalid file using a PIG script.

Below is the scenario.

Input:
Acc|Phone|Name
1234|333-444-5555|XYZ
4567|222-555-1111|ABC
1234|234-123-0000|DEF
9999|123-456-1890|PQR
8734|456-879-1234|QWE
4567|369-258-0147|NNN
1234|987-654-3210|BLS

output: Two files

1. Valid rec:
1234|333-444-5555|XYZ
4567|222-555-1111|ABC
9999|123-456-1890|PQR
8734|456-879-1234|QWE

2. Invalid rec:
1234|234-123-0000|DEF
4567|369-258-0147|NNN
1234|987-654-3210|BLS

Invalid records are not necessarily to be in same order. It can also be like this.

Invalid rec:
1234|234-123-0000|DEF
1234|987-654-3210|BLS
4567|369-258-0147|NNN

Scenario 2: Input:

1234|333-444-5555|XYZ
4567|222-555-1111|ABC
1234|234-123-0000|DEF
9999|123-456-1890|PQR
8734|456-879-1234|QWE
4567|369-258-0147|NNN
1234|087-654-3210|BLS
1234|303-444-5555|XYZ
4567|122-555-1111|ABC
1234|134-123-0000|DEF
9999|123-456-1890|PQR
8734|456-879-1234|QWE
4567|069-258-0147|NNN
1234|086-654-3210|BLS
1234|033-444-5555|XYZ
4567|200-555-1111|ABC
1234|230-123-0000|DEF
9999|023-456-1890|PQR
8734|456-779-1234|QWE
4567|309-258-0147|NNN
1234|007-654-3210|BLS

Good Rec:

1234|333-444-5555|XYZ
4567|222-555-1111|ABC
9999|123-456-1890|PQR
8734|456-879-1234|QWE

Can anyone please suggest some idea. I'm only able to get the first record.

Thanks.

Sivasakthi Jayaraman Sivasakthi Jayaraman · Accepted Answer · 2014-11-28T13:43:49

Can you try this?

input.txt

1234|333-444-5555|XYZ
4567|222-555-1111|ABC
1234|234-123-0000|DEF
9999|123-456-1890|PQR
8734|456-879-1234|QWE
4567|369-258-0147|NNN
1234|987-654-3210|BLS

PigScript:

A =LOAD 'input.txt' USING PigStorage('|') AS (Acc:chararray,Phone:chararray,Name:chararray);
B = RANK A;
C = GROUP B BY Acc;
D = FOREACH C {
                sortInAsc = ORDER B BY rank_A ASC;
                top1 = LIMIT sortInAsc 1;
                GENERATE top1 AS goodRecord,SUBTRACT(B,top1) AS badRecord;
              }

--Flatten the good records
E = FOREACH D GENERATE FLATTEN(goodRecord);

--Get the required columns and skip the rank column(ie,$0)
F = FOREACH E GENERATE $1,$2,$3;
STORE F INTO 'goodrecord' USING PigStorage('|');


--Flatten the bad records
G = FOREACH D GENERATE FLATTEN(badRecord);

--Get the required columns and skip the rank column(ie,$0)
H = FOREACH G GENERATE $1,$2,$3;
STORE H INTO 'badrecord' USING PigStorage('|');

goodrecord Output1:

1234|333-444-5555|XYZ
4567|222-555-1111|ABC
8734|456-879-1234|QWE
9999|123-456-1890|PQR

badrecord Output1:

1234|987-654-3210|BLS
1234|234-123-0000|DEF
4567|369-258-0147|NNN

Scenario2 goodrecord Output:

1234|333-444-5555|XYZ
4567|222-555-1111|ABC
8734|456-879-1234|QWE
9999|123-456-1890|PQR

Scenario2 badrecord Output:

1234|033-444-5555|XYZ
1234|007-654-3210|BLS
1234|230-123-0000|DEF
1234|303-444-5555|XYZ
1234|234-123-0000|DEF
1234|134-123-0000|DEF
1234|086-654-3210|BLS
1234|087-654-3210|BLS
4567|369-258-0147|NNN
4567|309-258-0147|NNN
4567|122-555-1111|ABC
4567|069-258-0147|NNN
4567|200-555-1111|ABC
8734|456-879-1234|QWE
8734|456-779-1234|QWE
9999|123-456-1890|PQR
9999|023-456-1890|PQR

Handling duplicate records in PIG Latin

1 Answers