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
Note
This documentation describes version compatibility between OpenSearch Service and various versions
of the SQL plugin, as well as the JDBC and ODBC driver. See the open source OpenSearch
documentation
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 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
2.13.0 | 2.13.0.0 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
2.11.0 | 2.11.0.0 |
Add support for PPL language and queries |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
2.9.0 | 2.9.0.0 |
Add Spark connector, and support table and PromQL functions |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
2.7.0 | 2.7.0.0 |
Add |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
2.5.0 | 2.5.0.0 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
2.3.0 | 2.3.0.0 |
Add |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
1.3.0 | 1.3.0.0 |
Support default query limit size, and IN clause to select from within a value list |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
1.2.0 | 1.2.0.0 |
Add new protocol for visualization response format |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
1.1.0 |
1.1.0.0 |
Support match function as filter in SQL and PPL |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
1.0.0 | 1.0.0.0 |
Support querying a data stream |
Open Distro for Elasticsearch | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Elasticsearch version | SQL plugin version | Notable features | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
7.10 | 1.13.0 |
NULL FIRST and LAST for window functions, CAST() function, SHOW and DESCRIBE commands | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
7.9 | 1.11.0 |
Add additional date/time functions, ORDER BY keyword | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
7.8 | 1.9.0 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
7.7 | 1.8.0 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
7.3 |
1.3.0 |
Multiple string and number operators | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
7.1 | 1.1.0 |
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_cluster/settings
path, not the
plugin settings path (_plugins/_query/settings
):
PUT _cluster/settings { "transient" : { "plugins.sql.enabled" : true } }
For legacy Elasticsearch domains, replace plugins
with
opendistro
:
PUT _cluster/settings { "transient" : { "opendistro.sql.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 download the driver, click here
The following tables summarize version compatibility for the driver.
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 Microsoft Excel
You can download an example working driver file on the OpenSearch artifacts page