How to query AWS ElasticSearch with SQL

Question

AWS documentation here indicates SQL querying is supported: https://aws.amazon.com/about-aws/whats-new/2019/05/amazon-elasticsearch-service-sql-support/

This page shows the syntax: https://docs.aws.amazon.com/elasticsearch-service/latest/developerguide/sql-support.html

POST elasticsearch_domain/_opendistro/_sql
{
  "query": "SELECT * FROM my-index LIMIT 50"
}

Running the following command in the Kibana Dev Tools

POST my_domain/_opendistro/_sql
{
  "query": "SELECT * FROM my-index LIMIT 50"
}

Gives the error

{
  "error": {
    "root_cause": [
      {
        "type": "invalid_type_name_exception",
        "reason": "Document mapping type name can't start with '_', found: [_opendistro]"
      }
    ],
    "type": "invalid_type_name_exception",
    "reason": "Document mapping type name can't start with '_', found: [_opendistro]"
  },
  "status": 400
}

My ElasticSearch version is 6.7.

mischva11 · Accepted Answer · 2020-03-04 13:03:25Z

1

Since your elasticsearch is driven by AWS and you are using Kibana Dev tools, you could get your query results by following way

POST _opendistro/_sql
{
  "query": "SELECT * FROM your_index "
}

You dont need to enter domain name. It worked for me.

edited Mar 4, 2020 at 13:03

mischva11

2,9563 gold badges20 silver badges35 bronze badges

answered Mar 4, 2020 at 12:40

Shivam Chaurasia

273 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to query AWS ElasticSearch with SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related