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. 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. > > > >> > > > > > > > > > > > > > >
