SQL Support for Amazon Elasticsearch Service

SQL support for Amazon Elasticsearch Service (Amazon ES) lets you query your data using SQL rather than the JSON-based Elasticsearch query DSL. This feature is useful if you're already familiar with SQL or want to integrate your domain with an application that uses SQL.

SQL support is available on domains running Elasticsearch 6.5 or higher. Full documentation is available in the Open Distro for Elasticsearch documentation.

Sample Call

To query your data using SQL, send HTTP requests to _opendistro/_sql using the following format:

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

Notes and Differences

Calls to _opendistro/_sql include index names in the request body, so they have the same access policy considerations as the bulk, mget, and msearch operations. As always, follow the principle of least privilege when you grant permissions to API operations.

For security considerations related to using SQL with fine-grained access control, see Fine-Grained Access Control in Amazon Elasticsearch Service.

The Open Distro for Elasticsearch SQL plugin includes many tuneable settings, but on Amazon ES, use the _opendistro/_sql/settings path rather than the standard _cluster/settings path:

PUT _opendistro/_sql/settings
{
  "persistent": {
    "opendistro.sql.cursor.enabled": true
  }
}

Workbench

The SQL Workbench is a Kibana user interface that lets you run on-demand SQL queries, translate SQL into its REST equivalent, and view and save results as text, JSON, JDBC, or CSV. For more information, see Workbench.

SQL CLI

The SQL CLI is a standalone Python application that you can launch with the odfesql command. For steps to install, configure, and use, see SQL CLI.

JDBC Driver

The Java Database Connectivity (JDBC) driver lets you integrate Amazon ES domains with your favorite business intelligence (BI) applications. To get started, see the GitHub repository. The following table summarizes version compatibility for the driver.

Elasticsearch Version	JDBC Driver Version
7.9	1.11.0
7.8	1.9.0
7.7	1.8.0
7.4	1.4.0
7.1	1.0.0
6.8	0.9.0
6.7	0.9.0
6.5	0.9.0

ODBC Driver

The Open Database Connectivity (ODBC) driver is a read-only ODBC driver for Windows and macOS that lets you connect business intelligence and data visualization applications like Tableau and Microsoft Excel to the SQL plugin. For information on downloading and using the JAR file, see the SQL repository on GitHub.