Pig joins using replicated(hands on example explanation)
-------------------------------------salrec = load '/home/horton/Desktop/Sales.csv' using PigStorage(',')
AS
(custid:long, prod_id:long, qty_pur:int, pur_date:datetime, sale_id:long);
custrec = load '/home/horton/Desktop/Customer.csv' using PigStorage(',')
AS
(fn:chararray, ln:chararray, status:chararray, ph:chararray, custid1:long, add:chararray);
pd_rec = load '/home/horton/Desktop/Product.csv' using PigStorage(',')
AS
(pdname:chararray, pddesc:chararray, pdcat:chararray, pdgty:long, pdid:long, pkwith:chararray);
slrec_pdrec = join salrec by prod_id full, pd_rec by pdid ;
crs_rec = join custrec by custid1 left outer,slrec_pdrec by custid USING 'replicated';
STORE crs_rec into '/home/horton/Desktop/pigc23' using PigStorage();
replicated : large on left, small on right . Small in memory .(only left outer join is supported)
replicated joins can be done for three way joins too, as long as none of the "outer join" is specified in the statement written.
sample output:
John Wade A 416-776-9281 2938 Bigcity|12345
Franklin Josephs I 905-560-9887 2913 Mediumcity|67890
Elizabeth Reynolds A 647-908-8865 2891 Smallcity|98765
Arthur Gibbons A 416-238-7765 400 Bigcity|12345
Lisa Scott I 416-824-8866 402 Bigcity|12345
Cliff Bowden A 905-994-9982 3772 Mediumcity|67890
Jean Dexter A 416-774-2339 210 Bigcity|12345
Roger Vickers A 647-232-9987 234 Smallcity|98765
Hubert Lexter A 416-972-2348 109 Bigcity|12345
Jack Merdec A 905-216-0989 54 Mediumcity|67890
Jovan Melcic A 905-324-4456 92 Mediumcity|67890
Melanie Ilyenko I 416-988-7723 404 Bigcity|12345
Jillian Panelo A 905-498-8872 12 Mediumcity|67890
Michael Junielle A 416-209-9987 43 Bigcity|12345
Rodson Mello A 416-996-7654 220 Bigcity|12345
Erick Jansen A 905-498-8211 93 Mediumcity|67890
Grant Getnet A 416-298-3445 332 Bigcity|12345
Roscoe Banhent A 647-829-0223 338 Smallcity|98765
Allen Serghert A 416-344-0992 324 Bigcity|12345
John Merdec I 416-922-2331 55 Bigcity|12345
Franklin Melcic A 905-561-2330 647 Mediumcity|67890 647 45628 2 2012-01-24T00:00:00.000+05:30 34859 500 GB HD J 500 GB HD Panther Brand HD 200 45628 atacable:manual
Elizabeth Ilyenko A 416-354-2778 102 Bigcity|12345 102 98243 1 2012-01-24T00:00:00.000+05:30 34851 09K Video Video Card Tiger Brand 84F1 Video 5 98243 manual:game
Arthur Panelo A 905-276-9987 227 Mediumcity|67890 227 77623 1 2012-01-24T00:00:00.000+05:30 34852 J Case 1500 Computer Case Jargon Brand Style 1500 Case 20 77623 fans:manual:screws
Lisa Junielle A 416-352-9992 323 Bigcity|12345 323 99207 2 2012-01-24T00:00:00.000+05:30 34853 84G1 Video Video Card Jargon Brand 84F1 Video 14 99207 dvd:manual:hdmicable
Cliff Mello A 416-210-9997 47 Bigcity|12345 47 92387 1 2012-01-24T00:00:00.000+05:30 34854 J Power 300W Power Supply Jargon Brand 300 Watts Power 28 92387 cables:screws
Jean Jansen A 905-244-4588 431 Mediumcity|67890 431 87659 1 2012-01-24T00:00:00.000+05:30 34855 4 GB Memory E 4 GB Memory ECC Ram 1000 87659 manual:heatsink
Roger Getnet I 416-309-9982 24 Bigcity|12345 24 45641 5 2012-01-24T00:00:00.000+05:30 34856 500 GB HD T 500 GB HD Tiger Brand HD 498 45641 satacable:manual
Hubert Banhent A 416-526-8888 19 Bigcity|12345 19 88734 7 2012-01-24T00:00:00.000+05:30 34857 DVD J INT DVD Jargon Brand Internal Optical 23 88734 manual
Gerhart Serghert A 905-561-9234 773 Mediumcity|67890 773 45628 10 2012-01-24T00:00:00.000+05:30 34860 500 GB HD J 500 GB HD Panther Brand HD 200 45628 atacable:manual
Gerhart Serghert A 905-561-9234 773 Mediumcity|67890 773 45641 4 2012-01-24T00:00:00.000+05:30 34858 500 GB HD T 500 GB HD Tiger Brand HD 498 45641 satacable:manual
Lambert Givens A 416-209-8223 587 Bigcity|12345 587 87634 1 2012-01-09T00:00:00.000+05:30 34823 16 GB Memory E 16 GB Memory ECC Ram 238 87634 manual
Panelo Wade I 905-298-9992 922 Mediumcity|67890 922 77623 3 2012-01-09T00:00:00.000+05:30 34827 J Case 1500 Computer Case Jargon Brand Style 1500 Case 20 77623 fans:manual:screws
Panelo Wade I 905-298-9992 922 Mediumcity|67890 922 88734 24 2012-01-09T00:00:00.000+05:30 34828 DVD J INT DVD Jargon Brand Internal Optical 23 88734 manual
Panelo Wade I 905-298-9992 922 Mediumcity|67890 922 88734 1 2012-01-09T00:00:00.000+05:30 34824 DVD J INT DVD Jargon Brand Internal Optical 23 88734 manual
Junielle Josephs A 905-737-9088 433 Mediumcity|67890 433 99207 2 2012-01-09T00:00:00.000+05:30 34825 84G1 Video Video Card Jargon Brand 84F1 Video 14 99207 dvd:manual:hdmicable
Mello Reynolds A 905-245-4431 64 Mediumcity|67890 64 77624 17 2012-01-09T00:00:00.000+05:30 34833 J Case 1501 Computer Case Jargon Brand Style 1501 Case 18 77624 fans:manual:screws
Mello Reynolds A 905-245-4431 64 Mediumcity|67890 64 92387 4 2012-01-24T00:00:00.000+05:30 34847 J Power 300W Power Supply Jargon Brand 300 Watts Power 28 92387 cables:screws
Mello Reynolds A 905-245-4431 64 Mediumcity|67890 64 98243 1 2012-01-09T00:00:00.000+05:30 34826 09K Video Video Card Tiger Brand 84F1 Video 5 98243 manual:game
Jansen Gibbons A 416-298-3881 895 Bigcity|12345 895 88211 31 2012-01-09T00:00:00.000+05:30 34834 T Case 4332 Computer Case Tiger Brand Style 4332 Case 7 88211 fans:manual:screws:watercooler
Jansen Gibbons A 416-298-3881 895 Bigcity|12345 895 282009 7 2012-01-24T00:00:00.000+05:30 34848 93H772 MB Motherboard H772 CPU MB 15 282009 cables:screws
Getnet Scott A 905-309-8221 1993 Mediumcity|67890 1993 92387 3 2012-01-24T00:00:00.000+05:30 34849 J Power 300W Power Supply Jargon Brand 300 Watts Power 28 92387 cables:screws
Getnet Scott A 905-309-8221 1993 Mediumcity|67890 1993 92387 2 2012-01-09T00:00:00.000+05:30 34835 J Power 300W Power Supply Jargon Brand 300 Watts Power 28 92387 cables:screws
Banhent Bowden A 416-989-0223 720 Bigcity|12345 720 99207 1 2012-01-24T00:00:00.000+05:30 34850 84G1 Video Video Card Jargon Brand 84F1 Video 14 99207 dvd:manual:hdmicable
Banhent Bowden A 416-989-0223 720 Bigcity|12345 720 282009 2 2012-01-09T00:00:00.000+05:30 34836 93H772 MB Motherboard H772 CPU MB 15 282009 cables:screws
Serghert Dexter A 416-823-0991 830 Bigcity|12345 830 282299 1 2012-01-09T00:00:00.000+05:30 34837 94F991 MB Motherboard F991 CPU MB 19 282299
Merdec Vickers I 416-298-0908 176 Bigcity|12345 176 77623 1 2012-01-09T00:00:00.000+05:30 34838 J Case 1500 Computer Case Jargon Brand Style 1500 Case 20 77623 fans:manual:screws
Melcic Lexter A 416-823-4443 128 Bigcity|12345 128 88734 4 2012-01-09T00:00:00.000+05:30 34839 DVD J INT DVD Jargon Brand Internal Optical 23 88734 manual
Ilyenko Merdec A 416-293-8771 97 Bigcity|12345 97 99202 1 2012-01-09T00:00:00.000+05:30 34840 93G Video Video Card Jargon Brand 93G Video 80 99202 dvd:manual:game
Roscoe Morris A 905-455-3221 322 Mediumcity|67890 322 99202 6 2012-01-09T00:00:00.000+05:30 34841 93G Video Video Card Jargon Brand 93G Video 80 99202 dvd:manual:game
Allen Jaskobec A 416-299-0202 7 Bigcity|12345 7 98243 1 2012-01-24T00:00:00.000+05:30 34842 09K Video Video Card Tiger Brand 84F1 Video 5 98243 manual:game
John Jarkin A 416-622-0991 11 Bigcity|12345 11 77623 2 2012-01-24T00:00:00.000+05:30 34843 J Case 1500 Computer Case Jargon Brand Style 1500 Case 20 77623 fans:manual:screws
Franklin Drill A 647-309-2331 37 Smallcity|98765
Elizabeth Metzer A 416-322-9001 85 Bigcity|12345
Arthur Balgne A 905-311-1211 331 Mediumcity|67890 331 282009 2 2012-01-09T00:00:00.000+05:30 34829 93H772 MB Motherboard H772 CPU MB 15 282009 cables:screws
Lisa Hetzer A 416-980-3229 482 Bigcity|12345 482 87634 1 2012-01-09T00:00:00.000+05:30 34830 16 GB Memory E 16 GB Memory ECC Ram 238 87634 manual
Lisa Hetzer A 416-980-3229 482 Bigcity|12345 482 88734 1 2012-01-24T00:00:00.000+05:30 34844 DVD J INT DVD Jargon Brand Internal Optical 23 88734 manual
Cliff Brandson I 416-559-0223 3221 Bigcity|12345 3221 92387 15 2012-01-09T00:00:00.000+05:30 34831 J Power 300W Power Supply Jargon Brand 300 Watts Power 28 92387 cables:screws
Cliff Brandson I 416-559-0223 3221 Bigcity|12345 3221 282009 1 2012-01-24T00:00:00.000+05:30 34845 93H772 MB Motherboard H772 CPU MB 15 282009 cables:screws
Jean Kulinski A 905-409-8823 452 Mediumcity|67890 452 99202 23 2012-01-24T00:00:00.000+05:30 34846 93G Video Video Card Jargon Brand 93G Video 80 99202 dvd:manual:game
Jean Kulinski A 905-409-8823 452 Mediumcity|67890 452 282299 2 2012-01-09T00:00:00.000+05:30 34832 94F991 MB Motherboard F991 CPU MB 19 282299
Roger Marjory A 647-290-6776 557 Smallcity|98765
Hubert Mentz A 416-577-8233 312 Bigcity|12345
No comments:
Post a Comment