Querying your Amazon OpenSearch Service data with SQL - Amazon OpenSearch Service (successor to Amazon Elasticsearch Service)

Querying your Amazon OpenSearch Service data with SQL

You can use SQL to query your Amazon OpenSearch Service, rather than using the JSON-based OpenSearch 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 OpenSearch and Elasticsearch version.

OpenSearch
OpenSearch version SQL plugin version Notable features
1.0.0 1.0.0.0

Support querying a data stream

Open Distro for Elasticsearch
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 OpenSearch or Elasticsearch 6.5 or higher. Full documentation of the SQL plugin is available in the OpenSearch documentation.

Sample call

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

POST domain-endpoint/_plugins/_sql { "query": "SELECT * FROM my-index LIMIT 50" }
Note

If your domain is running Elasticsearch rather than OpenSearch, the format is _opendistro/_sql.

Notes and differences

Calls to _plugins/_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 OpenSearch Service.

The OpenSearch SQL plugin includes many tunable settings, but on OpenSearch Service, use the _plugins/_sql/settings path (_opendistro/_sql_settings for Elasticsearch domains) rather than the standard _cluster/settings path:

PUT _plugins/_sql/settings { "persistent": { "plugins.sql.cursor.enabled": true } }

SQL Workbench

The SQL Workbench is an OpenSearch Dashboards 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 Query Workbench.

SQL CLI

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

JDBC driver

The Java Database Connectivity (JDBC) driver lets you integrate OpenSearch Service domains with your favorite business intelligence (BI) applications. To get started, see the GitHub repository. The following tables summarize version compatibility for the driver. The Open Distro for Elasticsearch driver is available for download here. OpenSearch drivers are not yet available for download.

OpenSearch
OpenSearch version JDBC driver version
1.0 1.0.0.0
Open Distro for Elasticsearch
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. The Open Distro for Elasticsearch driver is available for download here. OpenSearch drivers are not yet available for download. For information about installing the driver, see the SQL repository on GitHub.