You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by Pradeeban Kathiravelu <kk...@gmail.com> on 2016/08/18 21:03:09 UTC
Querying Nested Data in Mongo does not produce the results.
Hi,
I have this simple data in a Mongo database.
{"_id":{"$oid":"56a784b76952647b7b51c562"},"provenance":{"image":{"case_id":100,"subject_id":"TCGA"}}}
When I run
*SELECT camic.provenance.image.case_id caseidFROM mongo.users.`contacts2`
camic*
I get the below.
+---------+
| caseid |
+---------+
| 100 |
+---------+
When I run
*SELECT camic.provenance.image.case_id caseidFROM mongo.users.`contacts2`
camicWHERE caseid > 10;*
I expect the same outcome as above.
However, I get the below (no results).
+---------+
| caseid |
+---------+
+---------+
*tail -f sqlline.log* indicates the below. (see the highlighted line
specifically).
2016-08-18 16:56:07,337 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:foreman]
INFO o.a.drill.exec.work.foreman.Foreman - Query text for query id
2849df17-8fbd-eb57-03c8-cb2181bc81c7: SELECT camic.provenance.image.case_id
caseid
FROM mongo.users.`contacts2` camic
WHERE caseid > 10
2016-08-18 16:56:08,491 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
INFO o.a.d.e.s.m.MongoScanBatchCreator - Number of record readers
initialized : 1
2016-08-18 16:56:08,512 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
INFO o.a.d.e.w.fragment.FragmentExecutor -
2849df17-8fbd-eb57-03c8-cb2181bc81c7:0:0: State change requested
AWAITING_ALLOCATION --> RUNNING
2016-08-18 16:56:08,512 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
INFO o.a.d.e.w.f.FragmentStatusReporter -
2849df17-8fbd-eb57-03c8-cb2181bc81c7:0:0: State to report: RUNNING
2016-08-18 16:56:08,513 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
INFO o.a.d.e.s.mongo.MongoRecordReader - Filters Applied : Document{{}}
2016-08-18 16:56:08,513 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
INFO o.a.d.e.s.mongo.MongoRecordReader - Fields Selected :Document{{_id=0,
caseid=1, provenance=1}}
*2016-08-18 16:56:08,514 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
WARN o.a.d.e.e.ExpressionTreeMaterializer - Unable to find value vector of
path `caseid`, returning null instance.*
2016-08-18 16:56:08,520 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
INFO o.a.d.e.w.fragment.FragmentExecutor -
2849df17-8fbd-eb57-03c8-cb2181bc81c7:0:0: State change requested RUNNING
--> FINISHED
2016-08-18 16:56:08,520 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
INFO o.a.d.e.w.f.FragmentStatusReporter -
2849df17-8fbd-eb57-03c8-cb2181bc81c7:0:0: State to report: FINISHED
Tested with 1.6.0 and 1.7.0. Reported this as DRILL-4855
<https://issues.apache.org/jira/browse/DRILL-4855>
Can someone confirm whether this is a bug, or is it simply I am doing
something that is not supported, or doing something wrong?
Thank you.
Regards,
Pradeeban.
--
Pradeeban Kathiravelu.
PhD Researcher, Erasmus Mundus Joint Doctorate in Distributed Computing,
INESC-ID Lisboa / Instituto Superior Técnico, Universidade de Lisboa,
Portugal.
Biomedical Informatics Software Engineer, Emory University School of
Medicine.
Blog: [Llovizna] http://kkpradeeban.blogspot.com/
LinkedIn: www.linkedin.com/pub/kathiravelu-pradeeban/12/b6a/b03
Re: Querying Nested Data in Mongo does not produce the results.
Posted by Pradeeban Kathiravelu <kk...@gmail.com>.
Thanks Kathleen.
SELECT camic.provenance.image.case_id caseid
from mongo.users.`contacts2` camic
where camic.provenance.image.case_id > 10;
works for me as well.
I have also resolved the bug report that I opened as "Invalid"
Regards,
Pradeeban.
On Thu, Aug 18, 2016 at 5:33 PM, Kathleen Li <kl...@maprtech.com> wrote:
> Hi Prdedeeban,
>
> Please check out the following doc:
> http://drill.apache.org/docs/select-statements/
>
>
> Column Aliases. You cannot reference column aliases in the following
> clauses:
>
> * WHERE
> * GROUP BY
> * HAVING
>
>
> http://drill.apache.org/docs/select-statements/
>
> The following works for me without using column alias:
> 0: jdbc:drill:zk=drill1:5181,drill2:5181,dril> SELECT
> camic.provenance.image.case_id caseid from test camic where
> camic.provenance.image.case_id > 10;
> +---------+
> | caseid |
> +---------+
> | 100.0 |
> +---------+
>
>
>
>
>
>
>
> Kathleen
>
>
>
>
>
>
> On 8/18/16, 2:03 PM, "Pradeeban Kathiravelu" <kk...@gmail.com>
> wrote:
>
> >Hi,
> >I have this simple data in a Mongo database.
> >{"_id":{"$oid":"56a784b76952647b7b51c562"},"provenance":{"
> image":{"case_id":100,"subject_id":"TCGA"}}}
> >
> >When I run
> >
> >*SELECT camic.provenance.image.case_id caseidFROM mongo.users.`contacts2`
> >camic*
> >
> >I get the below.
> >
> >+---------+
> >| caseid |
> >+---------+
> >| 100 |
> >+---------+
> >
> >When I run
> >
> >
> >
> >*SELECT camic.provenance.image.case_id caseidFROM mongo.users.`contacts2`
> >camicWHERE caseid > 10;*
> >I expect the same outcome as above.
> >
> >However, I get the below (no results).
> >
> >+---------+
> >| caseid |
> >+---------+
> >+---------+
> >
> >
> >*tail -f sqlline.log* indicates the below. (see the highlighted line
> >specifically).
> >
> >2016-08-18 16:56:07,337 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:foreman]
> >INFO o.a.drill.exec.work.foreman.Foreman - Query text for query id
> >2849df17-8fbd-eb57-03c8-cb2181bc81c7: SELECT
> camic.provenance.image.case_id
> >caseid
> >FROM mongo.users.`contacts2` camic
> >WHERE caseid > 10
> >2016-08-18 16:56:08,491 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
> >INFO o.a.d.e.s.m.MongoScanBatchCreator - Number of record readers
> >initialized : 1
> >2016-08-18 16:56:08,512 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
> >INFO o.a.d.e.w.fragment.FragmentExecutor -
> >2849df17-8fbd-eb57-03c8-cb2181bc81c7:0:0: State change requested
> >AWAITING_ALLOCATION --> RUNNING
> >2016-08-18 16:56:08,512 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
> >INFO o.a.d.e.w.f.FragmentStatusReporter -
> >2849df17-8fbd-eb57-03c8-cb2181bc81c7:0:0: State to report: RUNNING
> >2016-08-18 16:56:08,513 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
> >INFO o.a.d.e.s.mongo.MongoRecordReader - Filters Applied : Document{{}}
> >2016-08-18 16:56:08,513 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
> >INFO o.a.d.e.s.mongo.MongoRecordReader - Fields Selected
> :Document{{_id=0,
> >caseid=1, provenance=1}}
> >*2016-08-18 16:56:08,514 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
> >WARN o.a.d.e.e.ExpressionTreeMaterializer - Unable to find value vector
> of
> >path `caseid`, returning null instance.*
> >2016-08-18 16:56:08,520 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
> >INFO o.a.d.e.w.fragment.FragmentExecutor -
> >2849df17-8fbd-eb57-03c8-cb2181bc81c7:0:0: State change requested RUNNING
> >--> FINISHED
> >2016-08-18 16:56:08,520 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
> >INFO o.a.d.e.w.f.FragmentStatusReporter -
> >2849df17-8fbd-eb57-03c8-cb2181bc81c7:0:0: State to report: FINISHED
> >
> >
> >
> >Tested with 1.6.0 and 1.7.0. Reported this as DRILL-4855
> ><https://issues.apache.org/jira/browse/DRILL-4855>
> >
> >Can someone confirm whether this is a bug, or is it simply I am doing
> >something that is not supported, or doing something wrong?
> >
> >Thank you.
> >Regards,
> >Pradeeban.
> >
> >--
> >Pradeeban Kathiravelu.
> >PhD Researcher, Erasmus Mundus Joint Doctorate in Distributed Computing,
> >INESC-ID Lisboa / Instituto Superior Técnico, Universidade de Lisboa,
> >Portugal.
> >Biomedical Informatics Software Engineer, Emory University School of
> >Medicine.
> >
> >Blog: [Llovizna] http://kkpradeeban.blogspot.com/
> >LinkedIn: www.linkedin.com/pub/kathiravelu-pradeeban/12/b6a/b03
>
>
--
Pradeeban Kathiravelu.
PhD Researcher, Erasmus Mundus Joint Doctorate in Distributed Computing,
INESC-ID Lisboa / Instituto Superior Técnico, Universidade de Lisboa,
Portugal.
Biomedical Informatics Software Engineer, Emory University School of
Medicine.
Blog: [Llovizna] http://kkpradeeban.blogspot.com/
LinkedIn: www.linkedin.com/pub/kathiravelu-pradeeban/12/b6a/b03
Re: Querying Nested Data in Mongo does not produce the results.
Posted by Kathleen Li <kl...@maprtech.com>.
Hi Prdedeeban,
Please check out the following doc:
http://drill.apache.org/docs/select-statements/
Column Aliases. You cannot reference column aliases in the following clauses:
* WHERE
* GROUP BY
* HAVING
http://drill.apache.org/docs/select-statements/
The following works for me without using column alias:
0: jdbc:drill:zk=drill1:5181,drill2:5181,dril> SELECT camic.provenance.image.case_id caseid from test camic where camic.provenance.image.case_id > 10;
+---------+
| caseid |
+---------+
| 100.0 |
+---------+
Kathleen
On 8/18/16, 2:03 PM, "Pradeeban Kathiravelu" <kk...@gmail.com> wrote:
>Hi,
>I have this simple data in a Mongo database.
>{"_id":{"$oid":"56a784b76952647b7b51c562"},"provenance":{"image":{"case_id":100,"subject_id":"TCGA"}}}
>
>When I run
>
>*SELECT camic.provenance.image.case_id caseidFROM mongo.users.`contacts2`
>camic*
>
>I get the below.
>
>+---------+
>| caseid |
>+---------+
>| 100 |
>+---------+
>
>When I run
>
>
>
>*SELECT camic.provenance.image.case_id caseidFROM mongo.users.`contacts2`
>camicWHERE caseid > 10;*
>I expect the same outcome as above.
>
>However, I get the below (no results).
>
>+---------+
>| caseid |
>+---------+
>+---------+
>
>
>*tail -f sqlline.log* indicates the below. (see the highlighted line
>specifically).
>
>2016-08-18 16:56:07,337 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:foreman]
>INFO o.a.drill.exec.work.foreman.Foreman - Query text for query id
>2849df17-8fbd-eb57-03c8-cb2181bc81c7: SELECT camic.provenance.image.case_id
>caseid
>FROM mongo.users.`contacts2` camic
>WHERE caseid > 10
>2016-08-18 16:56:08,491 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
>INFO o.a.d.e.s.m.MongoScanBatchCreator - Number of record readers
>initialized : 1
>2016-08-18 16:56:08,512 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
>INFO o.a.d.e.w.fragment.FragmentExecutor -
>2849df17-8fbd-eb57-03c8-cb2181bc81c7:0:0: State change requested
>AWAITING_ALLOCATION --> RUNNING
>2016-08-18 16:56:08,512 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
>INFO o.a.d.e.w.f.FragmentStatusReporter -
>2849df17-8fbd-eb57-03c8-cb2181bc81c7:0:0: State to report: RUNNING
>2016-08-18 16:56:08,513 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
>INFO o.a.d.e.s.mongo.MongoRecordReader - Filters Applied : Document{{}}
>2016-08-18 16:56:08,513 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
>INFO o.a.d.e.s.mongo.MongoRecordReader - Fields Selected :Document{{_id=0,
>caseid=1, provenance=1}}
>*2016-08-18 16:56:08,514 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
>WARN o.a.d.e.e.ExpressionTreeMaterializer - Unable to find value vector of
>path `caseid`, returning null instance.*
>2016-08-18 16:56:08,520 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
>INFO o.a.d.e.w.fragment.FragmentExecutor -
>2849df17-8fbd-eb57-03c8-cb2181bc81c7:0:0: State change requested RUNNING
>--> FINISHED
>2016-08-18 16:56:08,520 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0]
>INFO o.a.d.e.w.f.FragmentStatusReporter -
>2849df17-8fbd-eb57-03c8-cb2181bc81c7:0:0: State to report: FINISHED
>
>
>
>Tested with 1.6.0 and 1.7.0. Reported this as DRILL-4855
><https://issues.apache.org/jira/browse/DRILL-4855>
>
>Can someone confirm whether this is a bug, or is it simply I am doing
>something that is not supported, or doing something wrong?
>
>Thank you.
>Regards,
>Pradeeban.
>
>--
>Pradeeban Kathiravelu.
>PhD Researcher, Erasmus Mundus Joint Doctorate in Distributed Computing,
>INESC-ID Lisboa / Instituto Superior Técnico, Universidade de Lisboa,
>Portugal.
>Biomedical Informatics Software Engineer, Emory University School of
>Medicine.
>
>Blog: [Llovizna] http://kkpradeeban.blogspot.com/
>LinkedIn: www.linkedin.com/pub/kathiravelu-pradeeban/12/b6a/b03