SQL

Open Distro for Elasticsearch SQL lets you write queries in SQL rather than the Elasticsearch query domain-specific language (DSL). If you’re already familiar with SQL and don’t want to learn the query DSL, this feature is a great option.

To use the feature, send requests to the _opendistro/_sql URI. You can use a request parameter or the request body (recommended).

GET https://<host>:<port>/_opendistro/_sql?sql=select * from my-index limit 50
POST https://<host>:<port>/_opendistro/_sql
{
  "query": "SELECT * FROM my-index LIMIT 50"
}

You can query multiple indices by listing them or using wildcards:

POST _opendistro/_sql
{
  "query": "SELECT * FROM my-index1,myindex2,myindex3 LIMIT 50"
}

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

For a sample curl command, try:

curl -XPOST https://localhost:9200/_opendistro/_sql -u admin:admin -k -d '{"query": "SELECT * FROM kibana_sample_data_flights LIMIT 10"}' -H 'Content-Type: application/json'

By default, queries return JSON, but you can also return data in CSV format:

POST _opendistro/_sql?format=csv
{
  "query": "SELECT * FROM my-index LIMIT 50"
}

When you return data in CSV format, each row corresponds to a document, and each column corresponds to a field. Conceptually, you might find it useful to think of each Elasticsearch index as a database table.

User interfaces

You can test queries using Dev Tools in Kibana (https://<host>:5601).

Troubleshoot queries

The most common error is the dreaded null pointer exception, which can occur during parsing errors or when using the wrong HTTP method (POST vs. GET and vice versa). The POST method and HTTP request body offer the most consistent results:

POST _opendistro/_sql
{
  "query": "SELECT * FROM my-index WHERE ['name.firstname']='saanvi' LIMIT 5"
}

If a query isn’t behaving the way you expect, use the _explain API to see the translated query, which you can then troubleshoot. For most operations, _explain returns Elasticsearch query DSL. For UNION, MINUS, and JOIN, it returns something more akin to a SQL execution plan.

Sample request

POST _opendistro/_sql/_explain
{
  "query": "SELECT * FROM * LIMIT  50"
}

Sample response

{"from":0,"size":50}

Table of contents