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
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_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
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