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 [email protected] +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 <[email protected]> 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 <[email protected]> > 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 <[email protected]> > 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 <[email protected]> > > > 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 > > > > > > > > [email protected] > > > > > > > > > > > > +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 <[email protected]> > > 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 <[email protected] > > > > > >> 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. > > > >> > > > > > > > > > > > > > >
