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 <
dan.decap...@civicscience.com> 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 <clearmido...@gmail.com>
> 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 <
> > dan.decap...@civicscience.com> 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 <lben...@gmail.com>
> > 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
>
