Interactive command-line interface (CLI) for executing PPL (Piped Processing Language) and SQL queries against OpenSearch clusters. Supports secure and insecure endpoints, AWS SigV4 authentication, autocomplete, syntax highlighting, configurable output formats (Table, JSON, CSV), and saved query history. Easily toggle language modes, SQL plugin versions, and vertical display formatting - all from a single terminal session.

The SQL CLI component in OpenSearch is a stand-alone Python application and can be launched by a 'wake' word opensearchsql.

Users can test their existing queries against newer OpenSearch SQL plug-in versions before upgrading their OpenSearch clusters.

For example, user is currently using version 2.19 may want to validate query compatibility with version 3.1 first.

By using this CLI tool, they can:

• Load and run SQL 3.1 logic locally, without upgrading their OpenSearch cluster.
• Verify that their current queries execute as expected under the new SQL engine.
• Avoid potential breaking changes and reduce the need for rollback in production.

This CLI acts as a safe testing environment, allowing smooth transitions between versions with confidence.

Test and build

• Autocomplete for SQL and PPL
• Syntax highlighting
• Formatted output
  • Table
  • JSON
  • CSV
• Field names displayed with color
• Horizontal display for table format
  • Vertical display automatically used when output is too wide
  • Toggle vertical mode on/off with -v
• Connect to OpenSearch
  • Works with or without OpenSearch security enabled
  • Supports Amazon OpenSearch Service domains
• Query operations
  • Execute queries
  • Explain plans
  • Save and load queries
• SQL plugin version selection
• Command history
  • src/main/python/opensearchsql_cli/.cli_history
• Configuration file
  • src/main/python/opensearchsql_cli/config/config_file.yaml
• SQL plug-in connection log
  • src/main/java/sql_library.log
• Gradle log
  • build.log

Unlike plugins which use 4-digit version number. SQl-CLI uses x.x.x as version number same as other python packages in OpenSearch family. As a client for OpenSearch SQL, it has independent release. SQL-CLI should be compatible to all OpenSearch SQL versions. However since the codebase is in a monorepo, so we'll cut and name sql-cli release branch and tags differently. E.g.

release branch: sql-cli-1.0
release tag: sql-cli-v1.0.0 

Launch your local OpenSearch instance and make sure you have the OpenSearch SQL plugin installed.

To install the SQL CLI:

1. We suggest you install and activate a python3 virtual environment to avoid changing your local environment:

   pip install virtualenv
   virtualenv venv
   cd venv
   source ./bin/activate
   

2. Install the CLI:

   pip3 install opensearchsql
   

   The SQL CLI only works with Python 3, since Python 2 is no longer maintained since 01/01/2020. See https://pythonclock.org/

3. To launch the CLI, run:

   opensearchsql 
   

   By default, the opensearchsql command connects to http://localhost:9200.

• Language: PPL
• Endpoint: http://localhost:9200
• Output Format: Table
• SQL Plugin Version: 3.1.0.0

• The default protocol is HTTP with port number 9200.
• If using HTTPS without specifying a port, port 443 is used by default.

# Start with all defaults
opensearchsql

# Use secure endpoint with credentials
opensearchsql -e https://localhost:9200 -u admin:password -k

# Use AWS SigV4 connection
opensearchsql --aws-auth amazon.com

# Use SQL and JSON output
opensearchsql -l sql -f json

# Load specific plugin version
opensearchsql -v 2.19

• SQL Version: v3.1.0.0
• Language: PPL or SQL
• Format: TABLE, JSON, or CSV

To use a different OpenSearch SQL plug-in version, restart the CLI with

opensearchsql -v <version number>

When you first launch the SQL CLI, a configuration file is automatically created at main/opensearchsql_cli/config/config_file (for MacOS and Linux), the configuration is auto-loaded thereafter.

You can also configure the following connection properties:

⚠️ Security Warning: Passwords stored in this file are not encrypted. Consider using -u username:password instead for sensitive environments.

Note: PPL Calcite result is limited by QUERY_SIZE_LIMIT number

For a list of all available configurations, see config.yaml.

1. Save the sample accounts test data file.

2. Index the sample data.

   curl -H "Content-Type: application/x-ndjson" -POST https://localhost:9200/data/_bulk -u admin:< Admin password > --insecure --data-binary "@accounts.json"
   

3. Run a simple SQL/PPL command in OpenSearch SQL CLI:

   # PPL
   source=accounts
   # SQL
   SELECT * FROM accounts

The CLI supports all types of query that OpenSearch PPL/SQL supports. Refer to OpenSearch SQL basic usage documentation.

This project has adopted an Open Source Code of Conduct.

If you discover a potential security issue in this project we ask that you notify AWS/Amazon Security via our vulnerability reporting page. Please do not create a public GitHub issue for security bugs you report.

See the LICENSE file for our project's licensing. We will ask you to confirm the licensing of your contribution.

Copyright OpenSearch Contributors. See NOTICE for details.