Re: ElasticSearch. Explicit Definitions, Literals and RelBuilder

[Andrei Sereda]

>
> 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.bei...@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
>> > /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 
>> 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
>>> mm...@uwaterloo.ca
>>>
>>>
>>> Le jeu. 17 mai 2018 à 10:39, Andrei Sereda  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.bei...@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 " in sqlline.
>>
>> --
>> Michael Mior
>> mm...@uwaterloo.ca
>>
>>
>> Le jeu. 17 mai 2018 à 01:26, Andrei Sereda  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 

Re: ElasticSearch. Explicit Definitions, Literals and RelBuilder

[Christian Beikov]

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

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  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
mm...@uwaterloo.ca


Le jeu. 17 mai 2018 à 10:39, Andrei Sereda  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.bei...@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 " in sqlline.

--
Michael Mior
mm...@uwaterloo.ca


Le jeu. 17 mai 2018 à 01:26, Andrei Sereda  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

[Andrei Sereda]

Regarding  (2) Lower-case problem. The issue seems to be in
ElasticsearchFilter.Translator

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  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
> mm...@uwaterloo.ca
>
>
> Le jeu. 17 mai 2018 à 10:39, Andrei Sereda  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.bei...@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 " in sqlline.
> > >>
> > >> --
> > >> Michael Mior
> > >> mm...@uwaterloo.ca
> > >>
> > >>
> > >> Le jeu. 17 mai 2018 à 01:26, Andrei Sereda  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

[Michael Mior]

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
mm...@uwaterloo.ca


Le jeu. 17 mai 2018 à 10:39, Andrei Sereda  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.bei...@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 " in sqlline.
> >>
> >> --
> >> Michael Mior
> >> mm...@uwaterloo.ca
> >>
> >>
> >> Le jeu. 17 mai 2018 à 01:26, Andrei Sereda  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

[Andrei Sereda]

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.bei...@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 " in sqlline.
>>
>> --
>> Michael Mior
>> mm...@uwaterloo.ca
>>
>>
>> Le jeu. 17 mai 2018 à 01:26, Andrei Sereda  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

[Christian Beikov]

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 " in sqlline.

--
Michael Mior
mm...@uwaterloo.ca


Le jeu. 17 mai 2018 à 01:26, Andrei Sereda  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

[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 " in sqlline.

--
Michael Mior
mm...@uwaterloo.ca


Le jeu. 17 mai 2018 à 01:26, Andrei Sereda  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.
>