Querying your Amazon OpenSearch Service data with SQL - Amazon OpenSearch Service
Sample callNotes and differencesSQL WorkbenchSQL CLIJDBC driverODBC driver

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. SQL support is available on domains running OpenSearch or Elasticsearch 6.5 or higher.

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 for information about the syntax for basic and complex queries, functions, metadata queries, and aggregate functions.

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 datasource API
2.5.0 2.5.0.0

2.3.0 2.3.0.0

Add maketime and makedate datetime functions
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. In OpenSearch Service, use the _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. For more information, see the GitHub repository.

The following tables summarize version compatibility for the driver.

OpenSearch
OpenSearch version JDBC driver version
2.13 1.1.0.1
2.11 1.1.0.1
2.9 1.1.0.1
2.7 1.1.0.1
2.5 1.1.0.1
2.3 1.1.0.1
1.3 1.1.0.1
1.2 1.1.0.1
1.1 1.1.0.1
1.0 1.1.0.1
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 Microsoft Excel to the SQL plugin.

You can download an example working driver file on the OpenSearch artifacts page. For information about installing the driver, see the SQL repository on GitHub.