Querying your Amazon Elasticsearch Service data with SQL

You can use SQL to query your Amazon Elasticsearch Service (Amazon ES), rather than using the JSON-based Elasticsearch query DSL. Querying with SQL is useful if you're already familiar with the language or want to integrate your domain with an application that uses it.

Use the following table to find the version of the SQL plugin that's supported by each Elasticsearch version. Each SQL plugin version corresponds to an Open Distro for Elasticsearch version.

Elasticsearch version	SQL plugin version	Notable features
7.1	1.1.0
7.3	1.3.0	Multiple string and number operators
7.4	1.4.0	Add additional date/time functions, support IF, IFNULL, and ISNULL functions
7.7	1.8.0
7.8	1.9.0
7.9	1.11.0	Add additional date/time functions, ORDER BY keyword
7.10	1.13.0	NULL FIRST and LAST for window functions, CAST() function, SHOW and DESCRIBE commands

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

Sample call

To query your data with 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
  }
}

SQL 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.10	1.13.0
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, Microsoft Excel, and Power BI to the SQL plugin. For information on downloading and using the JAR file, see the SQL repository on GitHub.