Drill WITH clause syntax
Team Is this a valid Drill syntax (i.e 2 tables in with clause). WITH X1 AS (SELECT city, AVG(review_count) AS city_reviews_avg FROM `business.json` GROUP BY city), X2 AS (SELECT X1.city, X1.city_reviews_avg, MAX(X1.city_reviews_avg) OVER () AS city_reviews_avg_max FROM X1) SELECT X2.city, X2.city_reviews_avg FROM X2 WHERE X2.city_reviews_avg_max = X2.city_reviews_avg; I am hitting this error. *Error: SYSTEM ERROR: NoSuchFieldError: constants* A single table specification in WITH clause works fine. -Neeraja
Re: Drill WITH clause syntax
Is it something related to the json file you used? I tried the following query, replacing "business.json' with cp.`employee.json`. It works fine. (no row returned, which is expected). 0: jdbc:drill:zk=local> WITH X1 . . . . . . . . . . . > AS . . . . . . . . . . . > (SELECT city, . . . . . . . . . . . > AVG(review_count) AS city_reviews_avg . . . . . . . . . . . > FROM cp.`employee.json` . . . . . . . . . . . > GROUP BY city), . . . . . . . . . . . > X2 . . . . . . . . . . . > AS . . . . . . . . . . . > (SELECT X1.city, X1.city_reviews_avg, . . . . . . . . . . . > MAX(X1.city_reviews_avg) . . . . . . . . . . . > OVER () AS city_reviews_avg_max . . . . . . . . . . . > FROM X1) . . . . . . . . . . . > SELECT X2.city, X2.city_reviews_avg . . . . . . . . . . . > FROM X2 . . . . . . . . . . . > WHERE X2.city_reviews_avg_max = X2.city_reviews_avg; +---+---+ | city | city_reviews_avg | +---+---+ +---+---+ No rows selected (3.462 seconds) On Wed, Sep 23, 2015 at 4:23 PM, Bob Rumsbywrote: > Based on our docs, multiple table references are valid, and this is > consistent with other systems where WITH is supported in queries. However, > maybe the problem is the reference to X1 in the definition of X2? > > Bob > > On Wed, Sep 23, 2015 at 3:41 PM, Neeraja Rentachintala < > nrentachint...@maprtech.com> wrote: > >> Team >> Is this a valid Drill syntax (i.e 2 tables in with clause). >> >> WITH X1 >> AS >> (SELECT city, >> AVG(review_count) AS city_reviews_avg >> FROM `business.json` >> GROUP BY city), >> X2 >> AS >> (SELECT X1.city, X1.city_reviews_avg, >> MAX(X1.city_reviews_avg) >> OVER () AS city_reviews_avg_max >> FROM X1) >> SELECT X2.city, X2.city_reviews_avg >> FROM X2 >> WHERE X2.city_reviews_avg_max = X2.city_reviews_avg; >> >> I am hitting this error. *Error: SYSTEM ERROR: NoSuchFieldError: constants* >> >> A single table specification in WITH clause works fine. >> >> -Neeraja >>
Re: Drill WITH clause syntax
Works for me. WITH X1 AS (SELECT city, AVG(review_count) AS city_reviews_avg FROM `yelp_academic_dataset_business.json` GROUP BY city), X2 AS (SELECT X1.city, X1.city_reviews_avg, MAX(X1.city_reviews_avg) OVER () AS city_reviews_avg_max FROM X1) SELECT X2.city, X2.city_reviews_avg FROM X2 WHERE X2.city_reviews_avg_max = X2.city_reviews_avg; ++---+ |city| city_reviews_avg | ++---+ | Summerlin | 128.0 | ++---+ 1 row selected (0.87 seconds) On Wed, Sep 23, 2015 at 4:27 PM, Jinfeng Niwrote: > Is it something related to the json file you used? > > I tried the following query, replacing "business.json' with > cp.`employee.json`. It works fine. (no row returned, which is > expected). > > 0: jdbc:drill:zk=local> WITH X1 > . . . . . . . . . . . > AS > . . . . . . . . . . . > (SELECT city, > . . . . . . . . . . . > AVG(review_count) AS city_reviews_avg > . . . . . . . . . . . > FROM cp.`employee.json` > . . . . . . . . . . . > GROUP BY city), > . . . . . . . . . . . > X2 > . . . . . . . . . . . > AS > . . . . . . . . . . . > (SELECT X1.city, X1.city_reviews_avg, > . . . . . . . . . . . > MAX(X1.city_reviews_avg) > . . . . . . . . . . . > OVER () AS city_reviews_avg_max > . . . . . . . . . . . > FROM X1) > . . . . . . . . . . . > SELECT X2.city, X2.city_reviews_avg > . . . . . . . . . . . > FROM X2 > . . . . . . . . . . . > WHERE X2.city_reviews_avg_max = > X2.city_reviews_avg; > +---+---+ > | city | city_reviews_avg | > +---+---+ > +---+---+ > No rows selected (3.462 seconds) > > > On Wed, Sep 23, 2015 at 4:23 PM, Bob Rumsby wrote: > > Based on our docs, multiple table references are valid, and this is > > consistent with other systems where WITH is supported in queries. > However, > > maybe the problem is the reference to X1 in the definition of X2? > > > > Bob > > > > On Wed, Sep 23, 2015 at 3:41 PM, Neeraja Rentachintala < > > nrentachint...@maprtech.com> wrote: > > > >> Team > >> Is this a valid Drill syntax (i.e 2 tables in with clause). > >> > >> WITH X1 > >> AS > >> (SELECT city, > >> AVG(review_count) AS city_reviews_avg > >> FROM `business.json` > >> GROUP BY city), > >> X2 > >> AS > >> (SELECT X1.city, X1.city_reviews_avg, > >> MAX(X1.city_reviews_avg) > >> OVER () AS city_reviews_avg_max > >> FROM X1) > >> SELECT X2.city, X2.city_reviews_avg > >> FROM X2 > >> WHERE X2.city_reviews_avg_max = X2.city_reviews_avg; > >> > >> I am hitting this error. *Error: SYSTEM ERROR: NoSuchFieldError: > constants* > >> > >> A single table specification in WITH clause works fine. > >> > >> -Neeraja > >> >
Re: Drill WITH clause syntax
I am hitting this error. *Error: SYSTEM ERROR: NoSuchFieldError: constants* That looks like a low-level Java error caused by a version mismatch, as if one class is referring to a member field named "constants" in another class, which had the field when the first class was compiled, but field doesn't actually exist in the version of the other class that got loaded. Daniel Neeraja Rentachintala wrote: Team Is this a valid Drill syntax (i.e 2 tables in with clause). WITH X1 AS (SELECT city, AVG(review_count) AS city_reviews_avg FROM `business.json` GROUP BY city), X2 AS (SELECT X1.city, X1.city_reviews_avg, MAX(X1.city_reviews_avg) OVER () AS city_reviews_avg_max FROM X1) SELECT X2.city, X2.city_reviews_avg FROM X2 WHERE X2.city_reviews_avg_max = X2.city_reviews_avg; I am hitting this error. *Error: SYSTEM ERROR: NoSuchFieldError: constants* A single table specification in WITH clause works fine. -Neeraja -- Daniel Barclay MapR Technologies