Vineet, Pig 0.12 > supports the IN clause for filtering
X = FILTER A BY (f1==8) OR (NOT (f2+f3 > f1)) OR (f1 IN (9, 10, 11)); Ryan On Thu, Oct 30, 2014 at 11:09 PM, Vineet Mishra <[email protected]> wrote: > Hi Dan, > > Thanks for your response, although > > FILTER cat_ids BY (category_id == 1); > > is working fine but having multiple IN matches would make the script very > large, so considering my IN clause contains multiple values, such as > > sum_up_count = FILTER cat_ids BY category_id IN (1,2,3,4,5. . . 100); > > will make the query > > D0 = FILTER C BY (category_id == 1 OR category_id == 2 OR category_id == 3 > OR . . . category_id == 100); > > Is there any move around for this, moreover I wanted to return 1 if filter > matches and 0 if not! > > Looking out for your valuable response. > > Thanks! > > > > On Thu, Oct 30, 2014 at 7:16 PM, Dan DeCapria, CivicScience < > [email protected]> wrote: > > > Hi Vineet, > > > > Not entirely sure I'm understanding the problem correctly, but perhaps > the > > error you are getting can be fixed by: > > sum_up_count = FILTER cat_ids BY (category_id == 1); > > > > I think that having a more clear description of your use case and input > > data sets along with your current pig script in development will help us > in > > debugging this better. > > As such, this might not help, but here's a crack at reverse-engineering > > your problem space: > > > > A0 = LOAD '/input_01' USING PigStorage('\t','-noschema') AS (id:int, > > category_id:int, category_name: chararray, meta:chararray); > > A1 = FOREACH A0 GENERATE id, category_id, category_name; > > A = DISTINCT A1; > > > > B0 = LOAD '/input_02' USING PigStorage('\t','-noschema') AS (category_id: > > int, more_meta:chararray); > > B1 = FOREACH B0 GENERATE category_id; > > B = DISTINCT B1; > > > > C0 = JOIN A BY (category_id), B BY (category_id); -- 0,1,2, 3 > > C1 = FOREACH C0 GENERATE $0 AS id, $1 AS category_id, $2 AS > category_name; > > C = DISTINCT C1; > > > > D0 = FILTER C BY (category_id == 1); > > D1 = GROUP D0 BY (category_id, category_name); > > D = FOREACH D1 GENERATE FLATTEN(group) AS (category_id, category_name), > > (int)COUNT(D0) AS cat_id_1_count:int; > > > > > > Hope this helps, -Dan > > > > On Thu, Oct 30, 2014 at 7:10 AM, Vineet Mishra <[email protected]> > > wrote: > > > > > Hi Dan, > > > > > > I am trying to put Filter inside a Foreach, the description of the > > group(on > > > which the FOREACH iteration is happening) is mentioned below. I am > trying > > > to get counts of which all are passing the filter, > > > > > > Describe grp: > > > grp: {group: (a::category_id: int,a::category_name: chararray),joind: > > > {(a::id: int,a::category_id: int, b::category_id: int)}} > > > > > > Script: > > > purified = FOREACH grp { > > > cat_ids_bag = FOREACH joind generate b::category_id; > > > > > > cat_ids = foreach cat_ids_bag generate flatten(category_id); > > > > > > sum_up_count = FILTER cat_ids BY category_id IN (1); > > > } > > > > > > Its throwing error, > > > Syntax error, unexpected symbol at or near 'IN' > > > > > > Looking out for urgent response. > > > Thanks! > > > > > > On Tue, Oct 28, 2014 at 7:49 PM, Dan DeCapria, CivicScience < > > > [email protected]> wrote: > > > > > > > Hi Vineet, > > > > > > > > Expanding upon Lorand's resources, please note this all really > depends > > on > > > > your actual use case. When blocking out code to transform from SQL > to > > > Pig > > > > latin, it's usually a good idea to just flow-chart plan the logical > > > process > > > > of what you want to do - just like you would for SQL queries. Then > > it's > > > > just a matter of optimizing said queries - again, just like you would > > > with > > > > SQL queries on the DBA layer. the 'under-the-hood' optimizations to > MR > > > is > > > > done by Pig. > > > > > > > > Generically, this follows a simple paradigm, ie): > > > > > > > > -- optional runner: nohup pig -p REDUCERS=180 -f > > > /home/hadoop/my_file.pig > > > > 2>&1 > /tmp/my_file.out & > > > > > > > > -- some example configurations, ie) gzip compress the output > > > > SET output.compression.enabled true; > > > > SET output.compression.codec org.apache.hadoop.io.compress.GzipCodec; > > > > --SET default_parallel $REDUCERS; > > > > > > > > A0 = LOAD '/path/to/hdfs/data.dat' USING some.load.func() AS (the > typed > > > > schema); -- loader data source A > > > > A1 = FOREACH A0 GENERATE stuff; -- projection steps > > > > A = FILTER A1 BY (stuff); -- filter prior to JOIN > > > > > > > > B0 = LOAD '/path/to/hdfs/data.dat' USING some.load.func() AS (the > typed > > > > schema); -- loader data source B > > > > B1 = FOREACH B0 GENERATE stuff; -- projection steps > > > > B = FILTER B1 BY (stuff); -- filter prior to JOIN > > > > > > > > C0 = JOIN A BY (pk), B BY (pk) PARALLEL $REDUCERS; -- where size(A) > > > > > size(B), PARALLEL to force use of all MR capacity > > > > C = FOREACH C0 GENERATE stuff; -- re-alias the JOIN step fields to > what > > > you > > > > want, projection > > > > > > > > D0 = GROUP C BY (cks); -- perform your grouping operation > > > > D = FOREACH D0 GENERATE FLATTEN(group) AS (cks), (int)COUNT(C) AS > > > > example_count:int; -- whatever aggregation stats you wanted to > perform > > > wrt > > > > the GROUP BY operation > > > > > > > > STORE D INTO '/path/to/hdfs/storage/file' USING PigStorage(); -- > flat, > > > > tab-delimited file output of typed schema fields from [D]; here I > used > > > > PigStorage() store.func > > > > > > > > Hope this helps, -Dan > > > > > > > > > > > > On Tue, Oct 28, 2014 at 10:09 AM, Lorand Bendig <[email protected]> > > > wrote: > > > > > > > > > Hi Vineet, > > > > > > > > > > I'd recommend you have a look at these excellent resources: > > > > > > > > > > http://hortonworks.com/blog/pig-eye-for-the-sql-guy/ > > > > > http://mortar-public-site-content.s3-website-us-east-1. > > > > > amazonaws.com/Mortar-Pig-Cheat-Sheet.pdf > > > > > http://www.slideshare.net/trihug/practical-pig/11 > > > > > > > > > > --Lorand > > > > > > > > > > > > > > > On 28/10/14 14:34, Vineet Mishra wrote: > > > > > > > > > >> Hi, > > > > >> > > > > >> I was looking out to transform SQL statement which is consisting > of > > > > >> multiple clause in the same query specifically, a JOIN followed by > > > some > > > > >> condition(WHERE) and finally grouping on some fields(GROUP BY). > > > > >> Can I have a link or some briefing which can guide me how can I > > > > implement > > > > >> this k/o of complex SQL statement in PIG. > > > > >> > > > > >> Thanks! > > > > >> > > > > >> > > > > > > > > > > > > > > > > > -- > > > > Dan DeCapria > > > > CivicScience, Inc. > > > > Back-End Data IS/BI/DM/ML Specialist > > > > > > > > > > > > > > > -- > > Dan DeCapria > > CivicScience, Inc. > > Back-End Data IS/BI/DM/ML Specialist > > > -- Ryan Prociuk | Engineering Distributed Data
