How can handles Exist ,not Exist query on flink

[hagersaleh]

How can handles Exist ,not Exist , all and any in query
Example
SELECT P.PRODUCT_ID,
 P.PRODUCT_NAME
FROM PRODUCTS P
WHERE NOT EXISTS
 (
 SELECT  1
 FROM SALES S
 WHERE S.PRODUCT_ID = P.PRODUCT_ID);

[hagersaleh]

please help

[Fabian Hueske-2]

Hi,

if you decorrelate the subquery, you can execute EXIST or NOT EXIST with a join or co group.

If you search for "subquery decorrelation" you will find a good amount of information.

Note that subquery decorrelation is not possble in general. Queries with subqueries that cannot be decorrelated cannot be executed efficiently by Flink (and other parallel system struggle with that as well).

cheers, Fabian

2015-07-07 16:26 GMT+02:00 hagersaleh <[hidden email]>:

please help



--
View this message in context: http://apache-flink-user-mailing-list-archive.2336050.n4.nabble.com/How-can-handles-Exist-not-Exist-query-on-flink-tp1939p1973.html

Sent from the Apache Flink User Mailing List archive. mailing list archive at Nabble.com.

[hagersaleh]

thanks

[hagersaleh]

I want example on use join or co group for handles Exists or not Exists

[hagersaleh]

please help
I want example

[Michele Bertoni]

ds1.filter(//here selection of query 1)
ds2.filter(//here selection of query 2)

exist
ds1.join(ds2.distinct(id)).where(id).equal(id){ // join by your join key(s) - note the distinct operator, otherwise you will get many line for each input line
(left, right) => left //collect left
}

or

ds1.cogroup(ds2).where(id).equal(id){ //cogroup by your join key(s)
(left : Iterator, right: Iterator, out: Collector) =>
if(right.hasNext) //exist something in right dataset
        while(left.hasNext) //collect all the left
                out.collect(left.next)
}



not exist

ds1.cogroup(ds2).where(id).equal(id){ //cogroup by your join key(s)
(left : Iterator, right: Iterator, out: Collector) =>
if( ! right.hasNext) //nothing exists in right dataset - note the not (exclamation mark) in front
        while(left.hasNext) //collect all the left
                out.collect(left.next)
}




in short you are doing a full-outer-join and keeping only elements with at [ least one(exist) | no(not exist) ] matching element


this is just a sketch written on my smartphone you should re-adapt it to your query
cheers

> Il giorno 16/lug/2015, alle ore 00:44, hagersaleh <[hidden email]> ha scritto:
>
> please help
> I want example
>
>
>
> --
> View this message in context: http://apache-flink-user-mailing-list-archive.2336050.n4.nabble.com/How-can-handles-Exist-not-Exist-query-on-flink-tp1939p2068.html
> Sent from the Apache Flink User Mailing List archive. mailing list archive at Nabble.com.

[hagersaleh]

when write this code display error
 no interface expected here public static class MyCoGrouper extends CoGroupFunction<Customer,Orders,Result> {

ExecutionEnvironment env = ExecutionEnvironment.getExecutionEnvironment();

        DataSet<Customer> customers = getCustomerDataSet(env,mask,l,map);

        DataSet<Orders> orders= getOrdersDataSet(env,maskorder,l1,maporder);

        DataSet<Result> result = customers.coGroup(orders).where(0).equalTo(1).with
            (new CoGroupFunction<Customer,Orders,Result>() {

        @Override
        public void coGroup(Iterator<Customer> leftElements,
                            Iterator<Orders> rightElements,
                           Collector<Result> out) throws Exception {

                 if(rightElements.hasNext()) {
           while(leftElements.hasNext()) {

        out.collect(new Result(leftElements.next().f0,leftElements.next().f1,rightElements.next().f0
               ,rightElements.next().f1,rightElements.next().f2));

        }
        }

        }
        });