Elastic SQL

[_baba]

Hi, I'm not able to execute sql queries from kibana on AWS managed Elasticsearch, however I can execute it via REST call. Can someone help me identify what's the root cause of this?

Query:

POST /_sql
{
"query": "SELECT * FROM Table_Name WHERE ((filed1= 'Value1') AND (field2 = 'value2'))",
"fetch_size": 5
}

Error output: {"Message":"Your request: '/_sql' is not allowed."}

ES version: v 7.10.2

[RabBit_BR (andre.coelho)]

Hi @_baba

Try this:

POST _opendistro/_sql?
{
 "query": "SELECT * FROM Table_Name WHERE ((filed1= 'Value1') AND (field2 = 'value2'))",
"fetch_size": 5
}

This doc have more details.

[system]

OpenSearch/OpenDistro are AWS run products and differ from the original Elasticsearch and Kibana products that Elastic builds and maintains. You may need to contact them directly for further assistance.

(This is an automated response from your friendly Elastic bot. Please report this post if you have any suggestions or concerns )

[_baba]

Thanks for your response, it works from kibana.

Can you please help me with the builder that we can use from the REST API (java).
I tried both QueryStringQueryBuilder, and SimpleQueryStringBuilder but these convert the query to query_string below which I'm not looking for.
{
"query": {
"simple_query_string": {

  "query" : "SELECT * FROM Table_Name WHERE ((filed1= 'Value1') AND (field2 = 'value2'))",
  "fields"  : []
}

}
}

I have also gone through this doc to find a suitable builder with no luck.

Any lead would be appreciated.

[Christian_Dahlqvist]

You are using a third party plugin that is not core Elasticsearch. None of the official clients will therefore have any built in support for this.

[_baba]

Thanks for details. Can you suggest any querybuilder for Elasticsearch SQL core library, we can migrate from 3rd party plugin to core ES.

POST _sql/
{
"query": "SELECT * FROM Table_Name WHERE ((filed1= 'Value1') AND (field2 = 'value2'))",
",
"fetch_size": 5
}

[dadoonet (David Pilato)]

You would have to move from OpenSearch to Elasticsearch actually. Those are 2 different products.

You can easily run Elasticsearch on AWS either from the marketplace or better from cloud.elastic.co. Then you will have access to the full Elasticsearch experience.