SQL CLI

Elasticsearch ships with a script to run the SQL CLI in its bin directory:

  1. $ ./bin/elasticsearch-sql-cli

You can pass the URL of the Elasticsearch instance to connect to as the first parameter:

  1. $ ./bin/elasticsearch-sql-cli https://some.server:9200

If security is enabled on your cluster, you can pass the username and password in the form username:password@host_name:port to the SQL CLI:

  1. $ ./bin/elasticsearch-sql-cli https://sql_user:strongpassword@some.server:9200

Once the CLI is running you can use any query that Elasticsearch supports:

  1. sql> SELECT * FROM library WHERE page_count > 500 ORDER BY page_count DESC;
  2. author | name | page_count | release_date
  3. -----------------+--------------------+---------------+---------------
  4. Peter F. Hamilton|Pandora's Star |768 |1078185600000
  5. Vernor Vinge |A Fire Upon the Deep|613 |707356800000
  6. Frank Herbert |Dune |604 |-144720000000
  7. Alastair Reynolds|Revelation Space |585 |953078400000
  8. James S.A. Corey |Leviathan Wakes |561 |1306972800000

The jar containing the SQL CLI is a stand alone Java application and the scripts just launch it. You can move it around to other machines without having to install Elasticsearch on them. Without the already provided script files, you can use a command similar to the following to start the SQL CLI:

  1. $ ./java -jar [PATH_TO_CLI_JAR]/elasticsearch-sql-cli-[VERSION].jar https://some.server:9200

or

  1. $ ./java -cp [PATH_TO_CLI_JAR]/elasticsearch-sql-cli-[VERSION].jar org.elasticsearch.xpack.sql.cli.Cli https://some.server:9200

The jar name will be different for each Elasticsearch version (for example elasticsearch-sql-cli-7.3.2.jar), thus the generic VERSION specified in the example above. Furthermore, if not running the command from the folder where the SQL CLI jar resides, you’d have to provide the full path, as well.