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
***@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.
Post by Jacques NadeauI 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
Post by Adam Gilmoreselect * from mongo.`connect`.events where events.customerId.`$oid` =
'54901607f10c2236769f7b3b' limit 1;
returns no results - but there are definitely rows with that value in it.
Post by Jacques NadeauI believe the current Drill MongoDB plugin is trying to work with
MongoDB's
Post by Jacques Nadeauextended JSON [1] at the moment. As such, I think you'd need to write
the
Post by Jacques Nadeaufilter 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
Post by Jacques Nadeauobject filters.
Post by Adam GilmoreHi Kamesh,
Unfortunately it's not on _id - it's on another objectId field we
have
Post by Adam Gilmorein
Post by Jacques NadeauPost by Adam Gilmoreour documents. That seems to work fine with _id but with anything
else,
Post by Jacques Nadeauit
Post by Adam Gilmorereturns no results.
Any thoughts?
Regards,
*Adam Gilmore*
Director of Technology
+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
Post by Jacques NadeauPost by Adam Gilmoreis either confidential or otherwise protected from disclosure and is
intended solely for the use of the intended recipient. If you are not
the
Post by Jacques NadeauPost by Adam Gilmoreintended recipient please immediately notify the sender by e-mail and
delete the original transmission and its contents. Any unauthorised
use,
Post by Jacques NadeauPost by Adam Gilmoredissemination, forwarding, printing, or copying of this communication
including any file attachments is prohibited. The recipient should
check
Post by Jacques NadeauPost by Adam Gilmorethis email and any attachments for viruses and other defects. The
Company
Post by Jacques NadeauPost by Adam Gilmoredisclaims any liability for loss or damage arising in any way from
this
Post by Adam GilmorePost by Jacques NadeauPost by Adam Gilmorecommunication including any file attachments.
Post by KameshHi 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
Post by Adam GilmorePost by Jacques Nadeau_id
Post by Adam GilmorePost by Kamesh= '01077';
+------------+
| * |
+------------+
| { "city" : "SOUTHWICK" , "loc" : [ -72.770588 , 42.051099] ,
7667
, "state" : "MA"} |
+------------+
1 row selected (4.792 seconds)
Post by Adam GilmoreHi all,
I'm trying to work out how to filter by an objectId field using
the
but