You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Andrei Sereda <an...@sereda.cc> on 2018/05/17 05:26:12 UTC
ElasticSearch. Explicit Definitions, Literals and RelBuilder
Hello Calcite Devs,
I have some questions about ES adapter and custom predicates / projections
in Calcite. Your help is much appreciated.
1) All ES examples use a view (ZIPS
<https://github.com/apache/calcite/blob/master/elasticsearch5/src/test/resources/elasticsearch-zips-model.json>)
which does explicit type cast, name alias and dictionary access (via _MAP)
for each field. If such view is not defined beforehand, making ad-hoc
queries becomes tedious. Is there a way to make it more user-friendly (eg.
using existing ES mapping) ?
Example: select cast(_MAP['city'] AS varchar(20)) AS \"city\" from ...
Why some adapters require explicit definition (eg. Mongo / ES) while others
don't (eg. Geode)
2) When not using explicit casting (or field alias) query literals are
converted to lower case:
SQL: select * from "elastic" where _MAP['Foo'] = 'BAR' (note upper-case)
ES Query: { "term": { "foo" : "bar" }} (note lower-case)
This is less intuitive. Is there a way to switch it off (customize) ?
3) How to build the following query using Algebra Builder (RelBuilder)
select * from "elastic" where _MAP['Foo'] = 'Bar'
I presume one has to use SqlItemOperator but I couldn't get it to work. Is
this related to (1) and (2) ?
Regards,
Andrei.
Re: ElasticSearch. Explicit Definitions, Literals and RelBuilder
Posted by Andrei Sereda <an...@sereda.cc>.
>
> Seems like an easy fix. Would you mind creating the PR? :)
https://github.com/apache/calcite/pull/692
Any chance to get a patch release ?
On Thu, May 17, 2018 at 12:32 PM, Christian Beikov <
christian.beikov@gmail.com> wrote:
> I honestly don't know the reason for that. I mainly just reused the
> original implementation which was comitted here:
> https://github.com/apache/calcite/commit/f3caf13b9f1cd92f95d
> cf27716466bf2133e1ed7#diff-3ebf1b729d2fdd03596ff01088977e63
>
> Seems like an easy fix. Would you mind creating the PR? :)
>
>
> Mit freundlichen Grüßen,
> ------------------------------------------------------------------------
> *Christian Beikov*
> Am 17.05.2018 um 17:43 schrieb Andrei Sereda:
>
>> Regarding (2) Lower-case problem. The issue seems to be in
>> ElasticsearchFilter.Translator
>> <https://github.com/apache/calcite/blob/master/core/src/main
>> /java/org/apache/calcite/adapter/elasticsearch/Elasticsearch
>> Filter.java#L109>
>>
>> which
>> can be easily fixed :
>>
>> return "\"query\" : " + builder.toJsonString(map).replaceAll("\\s+", "")
>> .toLowerCase(Locale.ROOT);
>> Any reason for such explicit toLowerCase() transformation ?
>>
>>
>> On Thu, May 17, 2018 at 11:14 AM, Michael Mior <mm...@uwaterloo.ca>
>> wrote:
>>
>> I'm not too familiar with the RelBuilder API myself unfortunately. I think
>>> for your ???, you want builder.field(0) although builder.field("_MAP")
>>> may
>>> also work.
>>>
>>> --
>>> Michael Mior
>>> mmior@uwaterloo.ca
>>>
>>>
>>> Le jeu. 17 mai 2018 à 10:39, Andrei Sereda <an...@sereda.cc> a écrit :
>>>
>>> Hi Michael and Christian,
>>>>
>>>> Thanks for your replies.
>>>>
>>>> Regarding (3) RelBuilder below is the query plan. I'm not sure how to
>>>> express element =(ITEM($0, 'Foo') using API.
>>>>
>>>> Query Plan
>>>> 0: jdbc:calcite:model=target/test-classes/mod> explain plan for select
>>>>
>>> *
>>>
>>>> from "elasticsearch_raw"."table" where _MAP['Foo'] = 'BAR';
>>>>
>>>> | ElasticsearchToEnumerableConverter
>>>> ElasticsearchFilter(condition=[=(ITEM($0, 'Foo'), 'BAR')])
>>>> ElasticsearchTableScan(table=[[elasticsearch_raw,table]])
>>>>
>>>>
>>>> API
>>>> // Using RelBuilder API to construct simple ES query
>>>> // select * from "elasticsearch_raw"."table" where _MAP['Foo'] = 'BAR';
>>>> // use RexInputRef here ?
>>>> RexNode item = builder.call(SqlStdOperatorTable.ITEM, ???,
>>>> builder.literal("Foo"));
>>>> final RelNode node = builder
>>>> .scan("elasticsearch_raw", "table")
>>>> .filter(builder.call(SqlStdOperatorTable.EQUALS, item,
>>>> builder.literal("BAR")))
>>>> .build();
>>>>
>>>> Regards,
>>>> Andrei.
>>>>
>>>>
>>>>
>>>>
>>>> On Thu, May 17, 2018 at 9:16 AM, Christian Beikov <
>>>> christian.beikov@gmail.com> wrote:
>>>>
>>>> 1) I wasn't aware of an API to retrieve a schema. We could definitely
>>>>>
>>>> use
>>>
>>>> that to avoid the need for views.
>>>>>
>>>>> 2) I agree, seems like a bug.
>>>>>
>>>>>
>>>>> Mit freundlichen Grüßen,
>>>>> ------------------------------------------------------------
>>>>>
>>>> ------------
>>>
>>>> *Christian Beikov*
>>>>>
>>>>> Am 17.05.2018 um 13:51 schrieb Michael Mior:
>>>>>
>>>>> 1) I'm not too familiar with ES so I didn't realize you could define
>>>>>> mappings. I don't see any obvious reason why we couldn't use those
>>>>>> assuming
>>>>>> they're exposed via the ES API.
>>>>>>
>>>>>> 2) I was not aware of this and not sure whether it was intentional. It
>>>>>> seems like a bug to me though.
>>>>>>
>>>>>> 3) Whenever you want to see how to build a particular query, you may
>>>>>>
>>>>> find
>>>>
>>>>> it helpful to run "EXPLAIN PLAN FOR <query>" in sqlline.
>>>>>>
>>>>>> --
>>>>>> Michael Mior
>>>>>> mmior@uwaterloo.ca
>>>>>>
>>>>>>
>>>>>> Le jeu. 17 mai 2018 à 01:26, Andrei Sereda <an...@sereda.cc> a
>>>>>>
>>>>> écrit :
>>>
>>>> Hello Calcite Devs,
>>>>>>
>>>>>>> I have some questions about ES adapter and custom predicates /
>>>>>>> projections
>>>>>>> in Calcite. Your help is much appreciated.
>>>>>>>
>>>>>>> 1) All ES examples use a view (ZIPS
>>>>>>> <
>>>>>>> https://github.com/apache/calcite/blob/master/elasticsearch5
>>>>>>> /src/test/resources/elasticsearch-zips-model.json
>>>>>>>
>>>>>>> )
>>>>>>>>
>>>>>>>> which does explicit type cast, name alias and dictionary access
>>>>>>> (via
>>>>>>> _MAP)
>>>>>>> for each field. If such view is not defined beforehand, making ad-hoc
>>>>>>> queries becomes tedious. Is there a way to make it more user-friendly
>>>>>>> (eg.
>>>>>>> using existing ES mapping) ?
>>>>>>>
>>>>>>> Example: select cast(_MAP['city'] AS varchar(20)) AS \"city\" from
>>>>>>>
>>>>>> ...
>>>
>>>> Why some adapters require explicit definition (eg. Mongo / ES) while
>>>>>>> others
>>>>>>> don't (eg. Geode)
>>>>>>>
>>>>>>> 2) When not using explicit casting (or field alias) query literals
>>>>>>>
>>>>>> are
>>>
>>>> converted to lower case:
>>>>>>>
>>>>>>> SQL: select * from "elastic" where _MAP['Foo'] = 'BAR' (note
>>>>>>>
>>>>>> upper-case)
>>>>
>>>>> ES Query: { "term": { "foo" : "bar" }} (note lower-case)
>>>>>>>
>>>>>>> This is less intuitive. Is there a way to switch it off (customize) ?
>>>>>>>
>>>>>>> 3) How to build the following query using Algebra Builder
>>>>>>>
>>>>>> (RelBuilder)
>>>
>>>> select * from "elastic" where _MAP['Foo'] = 'Bar'
>>>>>>>
>>>>>>> I presume one has to use SqlItemOperator but I couldn't get it to
>>>>>>>
>>>>>> work.
>>>
>>>> Is
>>>>>>> this related to (1) and (2) ?
>>>>>>>
>>>>>>> Regards,
>>>>>>> Andrei.
>>>>>>>
>>>>>>>
>>>>>>>
>
Re: ElasticSearch. Explicit Definitions, Literals and RelBuilder
Posted by Christian Beikov <ch...@gmail.com>.
I honestly don't know the reason for that. I mainly just reused the
original implementation which was comitted here:
https://github.com/apache/calcite/commit/f3caf13b9f1cd92f95dcf27716466bf2133e1ed7#diff-3ebf1b729d2fdd03596ff01088977e63
Seems like an easy fix. Would you mind creating the PR? :)
Mit freundlichen Grüßen,
------------------------------------------------------------------------
*Christian Beikov*
Am 17.05.2018 um 17:43 schrieb Andrei Sereda:
> Regarding (2) Lower-case problem. The issue seems to be in
> ElasticsearchFilter.Translator
> <https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/adapter/elasticsearch/ElasticsearchFilter.java#L109>
> which
> can be easily fixed :
>
> return "\"query\" : " + builder.toJsonString(map).replaceAll("\\s+", "")
> .toLowerCase(Locale.ROOT);
> Any reason for such explicit toLowerCase() transformation ?
>
>
> On Thu, May 17, 2018 at 11:14 AM, Michael Mior <mm...@uwaterloo.ca> wrote:
>
>> I'm not too familiar with the RelBuilder API myself unfortunately. I think
>> for your ???, you want builder.field(0) although builder.field("_MAP") may
>> also work.
>>
>> --
>> Michael Mior
>> mmior@uwaterloo.ca
>>
>>
>> Le jeu. 17 mai 2018 à 10:39, Andrei Sereda <an...@sereda.cc> a écrit :
>>
>>> Hi Michael and Christian,
>>>
>>> Thanks for your replies.
>>>
>>> Regarding (3) RelBuilder below is the query plan. I'm not sure how to
>>> express element =(ITEM($0, 'Foo') using API.
>>>
>>> Query Plan
>>> 0: jdbc:calcite:model=target/test-classes/mod> explain plan for select
>> *
>>> from "elasticsearch_raw"."table" where _MAP['Foo'] = 'BAR';
>>>
>>> | ElasticsearchToEnumerableConverter
>>> ElasticsearchFilter(condition=[=(ITEM($0, 'Foo'), 'BAR')])
>>> ElasticsearchTableScan(table=[[elasticsearch_raw,table]])
>>>
>>>
>>> API
>>> // Using RelBuilder API to construct simple ES query
>>> // select * from "elasticsearch_raw"."table" where _MAP['Foo'] = 'BAR';
>>> // use RexInputRef here ?
>>> RexNode item = builder.call(SqlStdOperatorTable.ITEM, ???,
>>> builder.literal("Foo"));
>>> final RelNode node = builder
>>> .scan("elasticsearch_raw", "table")
>>> .filter(builder.call(SqlStdOperatorTable.EQUALS, item,
>>> builder.literal("BAR")))
>>> .build();
>>>
>>> Regards,
>>> Andrei.
>>>
>>>
>>>
>>>
>>> On Thu, May 17, 2018 at 9:16 AM, Christian Beikov <
>>> christian.beikov@gmail.com> wrote:
>>>
>>>> 1) I wasn't aware of an API to retrieve a schema. We could definitely
>> use
>>>> that to avoid the need for views.
>>>>
>>>> 2) I agree, seems like a bug.
>>>>
>>>>
>>>> Mit freundlichen Grüßen,
>>>> ------------------------------------------------------------
>> ------------
>>>> *Christian Beikov*
>>>>
>>>> Am 17.05.2018 um 13:51 schrieb Michael Mior:
>>>>
>>>>> 1) I'm not too familiar with ES so I didn't realize you could define
>>>>> mappings. I don't see any obvious reason why we couldn't use those
>>>>> assuming
>>>>> they're exposed via the ES API.
>>>>>
>>>>> 2) I was not aware of this and not sure whether it was intentional. It
>>>>> seems like a bug to me though.
>>>>>
>>>>> 3) Whenever you want to see how to build a particular query, you may
>>> find
>>>>> it helpful to run "EXPLAIN PLAN FOR <query>" in sqlline.
>>>>>
>>>>> --
>>>>> Michael Mior
>>>>> mmior@uwaterloo.ca
>>>>>
>>>>>
>>>>> Le jeu. 17 mai 2018 à 01:26, Andrei Sereda <an...@sereda.cc> a
>> écrit :
>>>>> Hello Calcite Devs,
>>>>>> I have some questions about ES adapter and custom predicates /
>>>>>> projections
>>>>>> in Calcite. Your help is much appreciated.
>>>>>>
>>>>>> 1) All ES examples use a view (ZIPS
>>>>>> <
>>>>>> https://github.com/apache/calcite/blob/master/elasticsearch5
>>>>>> /src/test/resources/elasticsearch-zips-model.json
>>>>>>
>>>>>>> )
>>>>>>>
>>>>>> which does explicit type cast, name alias and dictionary access (via
>>>>>> _MAP)
>>>>>> for each field. If such view is not defined beforehand, making ad-hoc
>>>>>> queries becomes tedious. Is there a way to make it more user-friendly
>>>>>> (eg.
>>>>>> using existing ES mapping) ?
>>>>>>
>>>>>> Example: select cast(_MAP['city'] AS varchar(20)) AS \"city\" from
>> ...
>>>>>> Why some adapters require explicit definition (eg. Mongo / ES) while
>>>>>> others
>>>>>> don't (eg. Geode)
>>>>>>
>>>>>> 2) When not using explicit casting (or field alias) query literals
>> are
>>>>>> converted to lower case:
>>>>>>
>>>>>> SQL: select * from "elastic" where _MAP['Foo'] = 'BAR' (note
>>> upper-case)
>>>>>> ES Query: { "term": { "foo" : "bar" }} (note lower-case)
>>>>>>
>>>>>> This is less intuitive. Is there a way to switch it off (customize) ?
>>>>>>
>>>>>> 3) How to build the following query using Algebra Builder
>> (RelBuilder)
>>>>>> select * from "elastic" where _MAP['Foo'] = 'Bar'
>>>>>>
>>>>>> I presume one has to use SqlItemOperator but I couldn't get it to
>> work.
>>>>>> Is
>>>>>> this related to (1) and (2) ?
>>>>>>
>>>>>> Regards,
>>>>>> Andrei.
>>>>>>
>>>>>>
Re: ElasticSearch. Explicit Definitions, Literals and RelBuilder
Posted by Andrei Sereda <an...@sereda.cc>.
Regarding (2) Lower-case problem. The issue seems to be in
ElasticsearchFilter.Translator
<https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/adapter/elasticsearch/ElasticsearchFilter.java#L109>
which
can be easily fixed :
return "\"query\" : " + builder.toJsonString(map).replaceAll("\\s+", "")
.toLowerCase(Locale.ROOT);
Any reason for such explicit toLowerCase() transformation ?
On Thu, May 17, 2018 at 11:14 AM, Michael Mior <mm...@uwaterloo.ca> wrote:
> I'm not too familiar with the RelBuilder API myself unfortunately. I think
> for your ???, you want builder.field(0) although builder.field("_MAP") may
> also work.
>
> --
> Michael Mior
> mmior@uwaterloo.ca
>
>
> Le jeu. 17 mai 2018 à 10:39, Andrei Sereda <an...@sereda.cc> a écrit :
>
> > Hi Michael and Christian,
> >
> > Thanks for your replies.
> >
> > Regarding (3) RelBuilder below is the query plan. I'm not sure how to
> > express element =(ITEM($0, 'Foo') using API.
> >
> > Query Plan
> > 0: jdbc:calcite:model=target/test-classes/mod> explain plan for select
> *
> > from "elasticsearch_raw"."table" where _MAP['Foo'] = 'BAR';
> >
> > | ElasticsearchToEnumerableConverter
> > ElasticsearchFilter(condition=[=(ITEM($0, 'Foo'), 'BAR')])
> > ElasticsearchTableScan(table=[[elasticsearch_raw,table]])
> >
> >
> > API
> > // Using RelBuilder API to construct simple ES query
> > // select * from "elasticsearch_raw"."table" where _MAP['Foo'] = 'BAR';
> > // use RexInputRef here ?
> > RexNode item = builder.call(SqlStdOperatorTable.ITEM, ???,
> > builder.literal("Foo"));
> > final RelNode node = builder
> > .scan("elasticsearch_raw", "table")
> > .filter(builder.call(SqlStdOperatorTable.EQUALS, item,
> > builder.literal("BAR")))
> > .build();
> >
> > Regards,
> > Andrei.
> >
> >
> >
> >
> > On Thu, May 17, 2018 at 9:16 AM, Christian Beikov <
> > christian.beikov@gmail.com> wrote:
> >
> > > 1) I wasn't aware of an API to retrieve a schema. We could definitely
> use
> > > that to avoid the need for views.
> > >
> > > 2) I agree, seems like a bug.
> > >
> > >
> > > Mit freundlichen Grüßen,
> > > ------------------------------------------------------------
> ------------
> > > *Christian Beikov*
> > >
> > > Am 17.05.2018 um 13:51 schrieb Michael Mior:
> > >
> > >> 1) I'm not too familiar with ES so I didn't realize you could define
> > >> mappings. I don't see any obvious reason why we couldn't use those
> > >> assuming
> > >> they're exposed via the ES API.
> > >>
> > >> 2) I was not aware of this and not sure whether it was intentional. It
> > >> seems like a bug to me though.
> > >>
> > >> 3) Whenever you want to see how to build a particular query, you may
> > find
> > >> it helpful to run "EXPLAIN PLAN FOR <query>" in sqlline.
> > >>
> > >> --
> > >> Michael Mior
> > >> mmior@uwaterloo.ca
> > >>
> > >>
> > >> Le jeu. 17 mai 2018 à 01:26, Andrei Sereda <an...@sereda.cc> a
> écrit :
> > >>
> > >> Hello Calcite Devs,
> > >>>
> > >>> I have some questions about ES adapter and custom predicates /
> > >>> projections
> > >>> in Calcite. Your help is much appreciated.
> > >>>
> > >>> 1) All ES examples use a view (ZIPS
> > >>> <
> > >>> https://github.com/apache/calcite/blob/master/elasticsearch5
> > >>> /src/test/resources/elasticsearch-zips-model.json
> > >>>
> > >>>> )
> > >>>>
> > >>> which does explicit type cast, name alias and dictionary access (via
> > >>> _MAP)
> > >>> for each field. If such view is not defined beforehand, making ad-hoc
> > >>> queries becomes tedious. Is there a way to make it more user-friendly
> > >>> (eg.
> > >>> using existing ES mapping) ?
> > >>>
> > >>> Example: select cast(_MAP['city'] AS varchar(20)) AS \"city\" from
> ...
> > >>>
> > >>> Why some adapters require explicit definition (eg. Mongo / ES) while
> > >>> others
> > >>> don't (eg. Geode)
> > >>>
> > >>> 2) When not using explicit casting (or field alias) query literals
> are
> > >>> converted to lower case:
> > >>>
> > >>> SQL: select * from "elastic" where _MAP['Foo'] = 'BAR' (note
> > upper-case)
> > >>> ES Query: { "term": { "foo" : "bar" }} (note lower-case)
> > >>>
> > >>> This is less intuitive. Is there a way to switch it off (customize) ?
> > >>>
> > >>> 3) How to build the following query using Algebra Builder
> (RelBuilder)
> > >>>
> > >>> select * from "elastic" where _MAP['Foo'] = 'Bar'
> > >>>
> > >>> I presume one has to use SqlItemOperator but I couldn't get it to
> work.
> > >>> Is
> > >>> this related to (1) and (2) ?
> > >>>
> > >>> Regards,
> > >>> Andrei.
> > >>>
> > >>>
> > >
> >
>
Re: ElasticSearch. Explicit Definitions, Literals and RelBuilder
Posted by Michael Mior <mm...@uwaterloo.ca>.
I'm not too familiar with the RelBuilder API myself unfortunately. I think
for your ???, you want builder.field(0) although builder.field("_MAP") may
also work.
--
Michael Mior
mmior@uwaterloo.ca
Le jeu. 17 mai 2018 à 10:39, Andrei Sereda <an...@sereda.cc> a écrit :
> Hi Michael and Christian,
>
> Thanks for your replies.
>
> Regarding (3) RelBuilder below is the query plan. I'm not sure how to
> express element =(ITEM($0, 'Foo') using API.
>
> Query Plan
> 0: jdbc:calcite:model=target/test-classes/mod> explain plan for select *
> from "elasticsearch_raw"."table" where _MAP['Foo'] = 'BAR';
>
> | ElasticsearchToEnumerableConverter
> ElasticsearchFilter(condition=[=(ITEM($0, 'Foo'), 'BAR')])
> ElasticsearchTableScan(table=[[elasticsearch_raw,table]])
>
>
> API
> // Using RelBuilder API to construct simple ES query
> // select * from "elasticsearch_raw"."table" where _MAP['Foo'] = 'BAR';
> // use RexInputRef here ?
> RexNode item = builder.call(SqlStdOperatorTable.ITEM, ???,
> builder.literal("Foo"));
> final RelNode node = builder
> .scan("elasticsearch_raw", "table")
> .filter(builder.call(SqlStdOperatorTable.EQUALS, item,
> builder.literal("BAR")))
> .build();
>
> Regards,
> Andrei.
>
>
>
>
> On Thu, May 17, 2018 at 9:16 AM, Christian Beikov <
> christian.beikov@gmail.com> wrote:
>
> > 1) I wasn't aware of an API to retrieve a schema. We could definitely use
> > that to avoid the need for views.
> >
> > 2) I agree, seems like a bug.
> >
> >
> > Mit freundlichen Grüßen,
> > ------------------------------------------------------------------------
> > *Christian Beikov*
> >
> > Am 17.05.2018 um 13:51 schrieb Michael Mior:
> >
> >> 1) I'm not too familiar with ES so I didn't realize you could define
> >> mappings. I don't see any obvious reason why we couldn't use those
> >> assuming
> >> they're exposed via the ES API.
> >>
> >> 2) I was not aware of this and not sure whether it was intentional. It
> >> seems like a bug to me though.
> >>
> >> 3) Whenever you want to see how to build a particular query, you may
> find
> >> it helpful to run "EXPLAIN PLAN FOR <query>" in sqlline.
> >>
> >> --
> >> Michael Mior
> >> mmior@uwaterloo.ca
> >>
> >>
> >> Le jeu. 17 mai 2018 à 01:26, Andrei Sereda <an...@sereda.cc> a écrit :
> >>
> >> Hello Calcite Devs,
> >>>
> >>> I have some questions about ES adapter and custom predicates /
> >>> projections
> >>> in Calcite. Your help is much appreciated.
> >>>
> >>> 1) All ES examples use a view (ZIPS
> >>> <
> >>> https://github.com/apache/calcite/blob/master/elasticsearch5
> >>> /src/test/resources/elasticsearch-zips-model.json
> >>>
> >>>> )
> >>>>
> >>> which does explicit type cast, name alias and dictionary access (via
> >>> _MAP)
> >>> for each field. If such view is not defined beforehand, making ad-hoc
> >>> queries becomes tedious. Is there a way to make it more user-friendly
> >>> (eg.
> >>> using existing ES mapping) ?
> >>>
> >>> Example: select cast(_MAP['city'] AS varchar(20)) AS \"city\" from ...
> >>>
> >>> Why some adapters require explicit definition (eg. Mongo / ES) while
> >>> others
> >>> don't (eg. Geode)
> >>>
> >>> 2) When not using explicit casting (or field alias) query literals are
> >>> converted to lower case:
> >>>
> >>> SQL: select * from "elastic" where _MAP['Foo'] = 'BAR' (note
> upper-case)
> >>> ES Query: { "term": { "foo" : "bar" }} (note lower-case)
> >>>
> >>> This is less intuitive. Is there a way to switch it off (customize) ?
> >>>
> >>> 3) How to build the following query using Algebra Builder (RelBuilder)
> >>>
> >>> select * from "elastic" where _MAP['Foo'] = 'Bar'
> >>>
> >>> I presume one has to use SqlItemOperator but I couldn't get it to work.
> >>> Is
> >>> this related to (1) and (2) ?
> >>>
> >>> Regards,
> >>> Andrei.
> >>>
> >>>
> >
>
Re: ElasticSearch. Explicit Definitions, Literals and RelBuilder
Posted by Andrei Sereda <an...@sereda.cc>.
Hi Michael and Christian,
Thanks for your replies.
Regarding (3) RelBuilder below is the query plan. I'm not sure how to
express element =(ITEM($0, 'Foo') using API.
Query Plan
0: jdbc:calcite:model=target/test-classes/mod> explain plan for select *
from "elasticsearch_raw"."table" where _MAP['Foo'] = 'BAR';
| ElasticsearchToEnumerableConverter
ElasticsearchFilter(condition=[=(ITEM($0, 'Foo'), 'BAR')])
ElasticsearchTableScan(table=[[elasticsearch_raw,table]])
API
// Using RelBuilder API to construct simple ES query
// select * from "elasticsearch_raw"."table" where _MAP['Foo'] = 'BAR';
// use RexInputRef here ?
RexNode item = builder.call(SqlStdOperatorTable.ITEM, ???,
builder.literal("Foo"));
final RelNode node = builder
.scan("elasticsearch_raw", "table")
.filter(builder.call(SqlStdOperatorTable.EQUALS, item,
builder.literal("BAR")))
.build();
Regards,
Andrei.
On Thu, May 17, 2018 at 9:16 AM, Christian Beikov <
christian.beikov@gmail.com> wrote:
> 1) I wasn't aware of an API to retrieve a schema. We could definitely use
> that to avoid the need for views.
>
> 2) I agree, seems like a bug.
>
>
> Mit freundlichen Grüßen,
> ------------------------------------------------------------------------
> *Christian Beikov*
>
> Am 17.05.2018 um 13:51 schrieb Michael Mior:
>
>> 1) I'm not too familiar with ES so I didn't realize you could define
>> mappings. I don't see any obvious reason why we couldn't use those
>> assuming
>> they're exposed via the ES API.
>>
>> 2) I was not aware of this and not sure whether it was intentional. It
>> seems like a bug to me though.
>>
>> 3) Whenever you want to see how to build a particular query, you may find
>> it helpful to run "EXPLAIN PLAN FOR <query>" in sqlline.
>>
>> --
>> Michael Mior
>> mmior@uwaterloo.ca
>>
>>
>> Le jeu. 17 mai 2018 à 01:26, Andrei Sereda <an...@sereda.cc> a écrit :
>>
>> Hello Calcite Devs,
>>>
>>> I have some questions about ES adapter and custom predicates /
>>> projections
>>> in Calcite. Your help is much appreciated.
>>>
>>> 1) All ES examples use a view (ZIPS
>>> <
>>> https://github.com/apache/calcite/blob/master/elasticsearch5
>>> /src/test/resources/elasticsearch-zips-model.json
>>>
>>>> )
>>>>
>>> which does explicit type cast, name alias and dictionary access (via
>>> _MAP)
>>> for each field. If such view is not defined beforehand, making ad-hoc
>>> queries becomes tedious. Is there a way to make it more user-friendly
>>> (eg.
>>> using existing ES mapping) ?
>>>
>>> Example: select cast(_MAP['city'] AS varchar(20)) AS \"city\" from ...
>>>
>>> Why some adapters require explicit definition (eg. Mongo / ES) while
>>> others
>>> don't (eg. Geode)
>>>
>>> 2) When not using explicit casting (or field alias) query literals are
>>> converted to lower case:
>>>
>>> SQL: select * from "elastic" where _MAP['Foo'] = 'BAR' (note upper-case)
>>> ES Query: { "term": { "foo" : "bar" }} (note lower-case)
>>>
>>> This is less intuitive. Is there a way to switch it off (customize) ?
>>>
>>> 3) How to build the following query using Algebra Builder (RelBuilder)
>>>
>>> select * from "elastic" where _MAP['Foo'] = 'Bar'
>>>
>>> I presume one has to use SqlItemOperator but I couldn't get it to work.
>>> Is
>>> this related to (1) and (2) ?
>>>
>>> Regards,
>>> Andrei.
>>>
>>>
>
Re: ElasticSearch. Explicit Definitions, Literals and RelBuilder
Posted by Christian Beikov <ch...@gmail.com>.
1) I wasn't aware of an API to retrieve a schema. We could definitely
use that to avoid the need for views.
2) I agree, seems like a bug.
Mit freundlichen Grüßen,
------------------------------------------------------------------------
*Christian Beikov*
Am 17.05.2018 um 13:51 schrieb Michael Mior:
> 1) I'm not too familiar with ES so I didn't realize you could define
> mappings. I don't see any obvious reason why we couldn't use those assuming
> they're exposed via the ES API.
>
> 2) I was not aware of this and not sure whether it was intentional. It
> seems like a bug to me though.
>
> 3) Whenever you want to see how to build a particular query, you may find
> it helpful to run "EXPLAIN PLAN FOR <query>" in sqlline.
>
> --
> Michael Mior
> mmior@uwaterloo.ca
>
>
> Le jeu. 17 mai 2018 à 01:26, Andrei Sereda <an...@sereda.cc> a écrit :
>
>> Hello Calcite Devs,
>>
>> I have some questions about ES adapter and custom predicates / projections
>> in Calcite. Your help is much appreciated.
>>
>> 1) All ES examples use a view (ZIPS
>> <
>> https://github.com/apache/calcite/blob/master/elasticsearch5/src/test/resources/elasticsearch-zips-model.json
>>> )
>> which does explicit type cast, name alias and dictionary access (via _MAP)
>> for each field. If such view is not defined beforehand, making ad-hoc
>> queries becomes tedious. Is there a way to make it more user-friendly (eg.
>> using existing ES mapping) ?
>>
>> Example: select cast(_MAP['city'] AS varchar(20)) AS \"city\" from ...
>>
>> Why some adapters require explicit definition (eg. Mongo / ES) while others
>> don't (eg. Geode)
>>
>> 2) When not using explicit casting (or field alias) query literals are
>> converted to lower case:
>>
>> SQL: select * from "elastic" where _MAP['Foo'] = 'BAR' (note upper-case)
>> ES Query: { "term": { "foo" : "bar" }} (note lower-case)
>>
>> This is less intuitive. Is there a way to switch it off (customize) ?
>>
>> 3) How to build the following query using Algebra Builder (RelBuilder)
>>
>> select * from "elastic" where _MAP['Foo'] = 'Bar'
>>
>> I presume one has to use SqlItemOperator but I couldn't get it to work. Is
>> this related to (1) and (2) ?
>>
>> Regards,
>> Andrei.
>>
Re: ElasticSearch. Explicit Definitions, Literals and RelBuilder
Posted by Michael Mior <mm...@uwaterloo.ca>.
1) I'm not too familiar with ES so I didn't realize you could define
mappings. I don't see any obvious reason why we couldn't use those assuming
they're exposed via the ES API.
2) I was not aware of this and not sure whether it was intentional. It
seems like a bug to me though.
3) Whenever you want to see how to build a particular query, you may find
it helpful to run "EXPLAIN PLAN FOR <query>" in sqlline.
--
Michael Mior
mmior@uwaterloo.ca
Le jeu. 17 mai 2018 à 01:26, Andrei Sereda <an...@sereda.cc> a écrit :
> Hello Calcite Devs,
>
> I have some questions about ES adapter and custom predicates / projections
> in Calcite. Your help is much appreciated.
>
> 1) All ES examples use a view (ZIPS
> <
> https://github.com/apache/calcite/blob/master/elasticsearch5/src/test/resources/elasticsearch-zips-model.json
> >)
> which does explicit type cast, name alias and dictionary access (via _MAP)
> for each field. If such view is not defined beforehand, making ad-hoc
> queries becomes tedious. Is there a way to make it more user-friendly (eg.
> using existing ES mapping) ?
>
> Example: select cast(_MAP['city'] AS varchar(20)) AS \"city\" from ...
>
> Why some adapters require explicit definition (eg. Mongo / ES) while others
> don't (eg. Geode)
>
> 2) When not using explicit casting (or field alias) query literals are
> converted to lower case:
>
> SQL: select * from "elastic" where _MAP['Foo'] = 'BAR' (note upper-case)
> ES Query: { "term": { "foo" : "bar" }} (note lower-case)
>
> This is less intuitive. Is there a way to switch it off (customize) ?
>
> 3) How to build the following query using Algebra Builder (RelBuilder)
>
> select * from "elastic" where _MAP['Foo'] = 'Bar'
>
> I presume one has to use SqlItemOperator but I couldn't get it to work. Is
> this related to (1) and (2) ?
>
> Regards,
> Andrei.
>