OSDir


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: ElasticSearch. Explicit Definitions, Literals and RelBuilder


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