Elastic Search adapter not identifying columns

[Shashwat Kumar <sh...@gmail.com>]

Hi,

I am trying to use calcite elasticsearch adapter via sqlline. I am
successfully able to connect to elastic search and able to run
*select * from zips.*

However I am not able to get specific fields in sql such has
*select "id" from "zips";*

It gives following error
*Error: Error while executing SQL "select "id" from "zips"": From line 1,
column 8 to line 1, column 11: Column 'id' not found in any table
(state=,code=0)*

Similarly the columns are not identified in *where* condition. I see that
its returning only one column as *_MAPS *and whole json document as one row.
Does adapter not support columns for elasticsearch?

-- 
Regards
Shashwat Kumar

Re: Elastic Search adapter not identifying columns

[Andrei Sereda <an...@sereda.cc>]

Hi Kumar,

Currently elastic adapter doesn't automatically import document mappings
<https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping.html>.
You need to either:

1) Manually define view
<https://github.com/apache/calcite/blob/master/elasticsearch/src/test/java/org/apache/calcite/adapter/elasticsearch/ElasticSearchAdapterTest.java#L101>
on the top of zips. "create view zips_view as select _MAP['id'] as "id"
from zips"
2) Use raw meta-column _MAP doesn't have any schema. "select * from zips
where _MAP['id'] in (1, 2, 3)"

We're planning to add support for elastic document mappings at some point.

Regards,
Andrei.


On Mon, Oct 22, 2018 at 6:49 AM Shashwat Kumar <sh...@gmail.com>
wrote:

> Hi,
>
> I am trying to use calcite elasticsearch adapter via sqlline. I am
> successfully able to connect to elastic search and able to run
> *select * from zips.*
>
> However I am not able to get specific fields in sql such has
> *select "id" from "zips";*
>
> It gives following error
> *Error: Error while executing SQL "select "id" from "zips"": From line 1,
> column 8 to line 1, column 11: Column 'id' not found in any table
> (state=,code=0)*
>
> Similarly the columns are not identified in *where* condition. I see that
> its returning only one column as *_MAPS *and whole json document as one
> row.
> Does adapter not support columns for elasticsearch?
>
> --
> Regards
> Shashwat Kumar
>