0: jdbc:drill:> select * from mongo.`connect`.events e where
e.`*`.customerId.`$oid` = '54901607f10c2236769f7b3b' limit 1;
+--+
| |
+--+
+--+
No rows selected (0.646 seconds)
0: jdbc:drill:> select customerId from mongo.`connect`.events e where
e.customerId.`$oid` = '54901607f10c2236769f7b3b' limit 1;
+------------+
| customerId |
+------------+
| {"$oid":"54901607f10c2236769f7b3b"} |
+------------+
1 row selected (0.116 seconds)
Interestingly enough, if I run the second query with "limit 2" (or anything
above 1), it will return the first row then freeze the query.
On Fri, Jan 9, 2015 at 12:07 PM, Jacques Nadeau <jacq...@apache.org> wrote:
> I gave the slightly wrong query. I forgot that you need to prefix table
> name in case of complex paths (which you had done). Try this:
>
> select * from mongo.`connect`.events e where e.`*`.customerId.`$oid` =
> '54901607f10c2236769f7b3b' limit 1;
>
> as well as:
>
> select customerId from mongo.`connect`.events e where e.customerId.`$oid` =
> '54901607f10c2236769f7b3b' limit 1;
>
>
>
> On Thu, Jan 8, 2015 at 5:26 PM, Adam Gilmore <a...@pharmadata.net.au>
> wrote:
>
> > 0: jdbc:drill:> select * from mongo.`connect`.events where
> > `*`.events.customerId.`$oid` = '54901607f10c2236769f7b3b' limit 1;
> > Query failed: Query failed: Failure validating SQL.
> > org.eigenbase.util.EigenbaseContextException: From line 1, column 44 to
> > line 1, column 46: Table '*' not found
> >
> > Error: exception while executing query: Failure while executing query.
> > (state=,code=0)
> > 0: jdbc:drill:> select events from mongo.`connect`.events where
> > events.customerId.`$oid` = '54901607f10c2236769f7b3b' limit 1;
> > +------------+
> > | events |
> > +------------+
> > | null |
> > +------------+
> > 1 row selected (0.261 seconds)
> >
> > Strange results there - I played around with the second query and it
> seems
> > to be able to return anything but * nicely. So you're probably right.
> >
> >
> > Regards,
> >
> >
> > *Adam Gilmore*
> >
> > Director of Technology
> >
> > a...@pharmadata.net.au
> >
> >
> > +61 421 997 655 (Mobile)
> >
> > 1300 733 876 (AU)
> >
> > +617 3171 9902 (Intl)
> >
> >
> > *PharmaData*
> >
> > Data Intelligence Solutions for Pharmacy
> >
> > www.PharmaData.net.au <http://www.pharmadata.net.au/>
> >
> >
> >
> > [image: pharmadata-sig]
> >
> >
> >
> > *Disclaimer*
> >
> > This communication including any attachments may contain information that
> > is either confidential or otherwise protected from disclosure and is
> > intended solely for the use of the intended recipient. If you are not the
> > intended recipient please immediately notify the sender by e-mail and
> > delete the original transmission and its contents. Any unauthorised use,
> > dissemination, forwarding, printing, or copying of this communication
> > including any file attachments is prohibited. The recipient should check
> > this email and any attachments for viruses and other defects. The Company
> > disclaims any liability for loss or damage arising in any way from this
> > communication including any file attachments.
> >
> > On Fri, Jan 9, 2015 at 2:01 AM, Jacques Nadeau <jacq...@apache.org>
> wrote:
> >
> >> I think I know what the problem is. Hanifi recently identified that the
> >> Mongo storage plugin doesn't handle * correctly. Instead of returning a
> >> set of columns for the top level of the mongo record, it returns a
> single
> >> top-level column called star. DRILL-1692 identifies the problem and it
> >> causes a number of issues when using mongo. Can you try each of the
> >> following to see if they work?
> >>
> >> select * from mongo.`connect`.events where `*`.events.customerId.`$oid`
> =
> >> '54901607f10c2236769f7b3b' limit 1;
> >>
> >> OR
> >>
> >> select events from mongo.`connect`.events where
> events.customerId.`$oid` =
> >> '54901607f10c2236769f7b3b' limit 1;
> >>
> >> Thanks,
> >> Jacques
> >>
> >> On Wed, Jan 7, 2015 at 12:44 AM, Adam Gilmore <dragoncu...@gmail.com>
> >> wrote:
> >>
> >> > Unfortunately, that didn't work. I tried:
> >> >
> >> > select * from mongo.`connect`.events where events.customerId.`$oid` =
> >> > '54901607f10c2236769f7b3b' limit 1;
> >> >
> >> > returns no results - but there are definitely rows with that value in
> >> it.
> >> >
> >> > On Wed, Jan 7, 2015 at 2:18 PM, Jacques Nadeau <jacq...@apache.org>
> >> wrote:
> >> >
> >> > > I believe the current Drill MongoDB plugin is trying to work with
> >> > MongoDB's
> >> > > extended JSON [1] at the moment. As such, I think you'd need to
> write
> >> > the
> >> > > filter as myfield.`$oid` = 'id value'
> >> > >
> >> > > http://docs.mongodb.org/manual/reference/mongodb-extended-json/
> >> > >
> >> > > Note that currently Drill uses SQL expressions with dotted notation
> >> > > extensions for filters and doesn't currently support the mongodb
> based
> >> > json
> >> > > object filters.
> >> > >
> >> > > On Tue, Jan 6, 2015 at 3:52 PM, Adam Gilmore <
> a...@pharmadata.net.au>
> >> > > wrote:
> >> > >
> >> > > > Hi Kamesh,
> >> > > >
> >> > > > Unfortunately it's not on _id - it's on another objectId field we
> >> have
> >> > in
> >> > > > our documents. That seems to work fine with _id but with anything
> >> > else,
> >> > > it
> >> > > > returns no results.
> >> > > >
> >> > > > Any thoughts?
> >> > > >
> >> > > >
> >> > > > Regards,
> >> > > >
> >> > > >
> >> > > > *Adam Gilmore*
> >> > > >
> >> > > > Director of Technology
> >> > > >
> >> > > > a...@pharmadata.net.au
> >> > > >
> >> > > >
> >> > > > +61 421 997 655 (Mobile)
> >> > > >
> >> > > > 1300 733 876 (AU)
> >> > > >
> >> > > > +617 3171 9902 (Intl)
> >> > > >
> >> > > >
> >> > > > *PharmaData*
> >> > > >
> >> > > > Data Intelligence Solutions for Pharmacy
> >> > > >
> >> > > > www.PharmaData.net.au <http://www.pharmadata.net.au/>
> >> > > >
> >> > > >
> >> > > >
> >> > > > [image: pharmadata-sig]
> >> > > >
> >> > > >
> >> > > >
> >> > > > *Disclaimer*
> >> > > >
> >> > > > This communication including any attachments may contain
> information
> >> > that
> >> > > > is either confidential or otherwise protected from disclosure and
> is
> >> > > > intended solely for the use of the intended recipient. If you are
> >> not
> >> > the
> >> > > > intended recipient please immediately notify the sender by e-mail
> >> and
> >> > > > delete the original transmission and its contents. Any
> unauthorised
> >> > use,
> >> > > > dissemination, forwarding, printing, or copying of this
> >> communication
> >> > > > including any file attachments is prohibited. The recipient should
> >> > check
> >> > > > this email and any attachments for viruses and other defects. The
> >> > Company
> >> > > > disclaims any liability for loss or damage arising in any way from
> >> this
> >> > > > communication including any file attachments.
> >> > > >
> >> > > > On Wed, Jan 7, 2015 at 2:08 AM, Kamesh <kamesh.had...@gmail.com>
> >> > wrote:
> >> > > >
> >> > > >> Hi Adam,
> >> > > >> Are you trying apply filter using _id?. If so, Mongo storage
> >> plugin
> >> > > >> supports it.
> >> > > >>
> >> > > >> 0: jdbc:drill:zk=localhost:2181> select * from mongo.usda.`zips`
> >> where
> >> > > _id
> >> > > >> = '01077';
> >> > > >> +------------+
> >> > > >> | * |
> >> > > >> +------------+
> >> > > >> | { "city" : "SOUTHWICK" , "loc" : [ -72.770588 , 42.051099] ,
> >> "pop" :
> >> > > >> 7667
> >> > > >> , "state" : "MA"} |
> >> > > >> +------------+
> >> > > >> 1 row selected (4.792 seconds)
> >> > > >>
> >> > > >>
> >> > > >> On Mon, Jan 5, 2015 at 1:45 PM, Adam Gilmore <
> >> dragoncu...@gmail.com>
> >> > > >> wrote:
> >> > > >>
> >> > > >> > Hi all,
> >> > > >> >
> >> > > >> > I'm trying to work out how to filter by an objectId field using
> >> the
> >> > > >> Mongo
> >> > > >> > plugin. I've tried many combinations of = '{$oid: ''id''}'
> etc.
> >> but
> >> > > >> > nothing seems to work.
> >> > > >> >
> >> > > >> > Is this implemented yet? If not, is there a JIRA item for it
> >> > already?
> >> > > >> >
> >> > > >>
> >> > > >>
> >> > > >>
> >> > > >> --
> >> > > >> Kamesh.
> >> > > >>
> >> > > >
> >> > > >
> >> > >
> >> >
> >>
> >
> >
>
