How can select columns from two relations after join in pig.

[#1]

Hi ,

am joining two relations(withoutschema) in pig and want to pick particular columns from both relations.

A = load 'data1' using PigStorage(',');($0,$1...$8)

B = load 'data2' using PigStoarge(',');($0..$4);

C = foreach(join A by($1,$2),B by($1,$2)) generate $0,$1,$4,$5,(how to select B relation columns)

[#2]

@jayaprakash gadi please try this.. I haven't tested yet.

C = foreach(join A by($1,$2),B by($1,$2)) generate B.*

[#3]

am able to pick particular columns from relations A & B where relations have schema but if relations doesn't have any schema then it's not working.

[#4]

After join the elements from A are at positions $0 .. $8, the elements from B are at $9 .. $13. Also, observe Performance enhencers: Use types, and Project early and often which in your case means to remove un-needed elements before the join.

[#5]

it worked ..thanks

[#6 · Accepted solution]

@jayaprakash gadi

here's my solution, considering that result of join is sum of all fields, then if you have 10 columns in A and 4 columns in B, your result row will 14, you can cherry pick columns 1, 2,3 from A and 11, 12, 13 from B.

grunt> fs -cat email_list.csv;
1,Christine,Romero,[email protected]
2,Sara,Hansen,[email protected]
3,Albert,Rogers,[email protected]
4,Kimberly,Morrison,[email protected]
5,Eugene,Baker,[email protected]
6,Ann,Alexander,[email protected]
7,Kathleen,Reed,[email protected]
8,Todd,Scott,[email protected]
9,Sharon,Mccoy,[email protected]
10,Evelyn,Rice,[email protected]

grunt> fs -cat gender_list.csv;
1,Christine,Romero,Female
2,Sara,Hansen,Female
3,Albert,Rogers,Male
4,Kimberly,Morrison,Female
5,Eugene,Baker,Male
6,Ann,Alexander,Female
7,Kathleen,Reed,Female
8,Todd,Scott,Male
9,Sharon,Mccoy,Female
10,Evelyn,Rice,Female

grunt> A = load 'email_list.csv' using PigStorage(',');
grunt> B = load 'gender_list.csv' using PigStorage(',');
grunt> C = join A by ($0, $1, $2), B by ($0, $1, $2);
grunt> dump C;

(1,Christine,Romero,[email protected],1,Christine,Romero,Female)
(10,Evelyn,Rice,[email protected],10,Evelyn,Rice,Female)
(2,Sara,Hansen,[email protected],2,Sara,Hansen,Female)
(3,Albert,Rogers,[email protected],3,Albert,Rogers,Male)
(4,Kimberly,Morrison,[email protected],4,Kimberly,Morrison,Female)
(5,Eugene,Baker,[email protected],5,Eugene,Baker,Male)
(6,Ann,Alexander,[email protected],6,Ann,Alexander,Female)
(7,Kathleen,Reed,[email protected],7,Kathleen,Reed,Female)
(8,Todd,Scott,[email protected],8,Todd,Scott,Male)
(9,Sharon,Mccoy,[email protected],9,Sharon,Mccoy,Female)

grunt> D = foreach C generate $0, $1, $2, $3, $7;
grunt> dump D;

(1,Christine,Romero,[email protected],Female)
(10,Evelyn,Rice,[email protected],Female)
(2,Sara,Hansen,[email protected],Female)
(3,Albert,Rogers,[email protected],Male)
(4,Kimberly,Morrison,[email protected],Female)
(5,Eugene,Baker,[email protected],Male)
(6,Ann,Alexander,[email protected],Female)
(7,Kathleen,Reed,[email protected],Female)
(8,Todd,Scott,[email protected],Male)
(9,Sharon,Mccoy,[email protected],Female)