Elasticsearch SQL Access

X-pack is an elastic stack extension that has SQL features to execute the SQL queries against elasticsearch. It returns the queries results in tabular form. These SQL queries execute in a real-time environment. In Elasticsearch 6.3 version, SQL support has been introduced that enables the user to run the queries against the search engine.

For understanding, you can think of Elasticsearch SQL as a translator. A translator can understand both Elasticsearch and SQL. By taking advantage of elasticsearch capabilities, it makes it easy to read and process the data in real-time. The biggest advantage of elasticsearch SQL query is that you don't need to learn the query DSL if you are already familiar with SQL.

We all know that every tool has few advantages along with some disadvantages. Hence, the elasticsearch SQL also has some advantages, which are as follows -

Advantages of Elasticsearch SQL

Elasticsearch SQL has some excellent features that help to keep the mind straight, why you should use it. Following are the advantages of Elasticsearch SQL -

Native Integration

Elasticsearch SQL has native integration that allows to execute the each and every query against the relevant nodes. It efficiently executes these queries according to the underlying storage.

Lightweight and efficient

Elasticsearch SQL is a light-weighted and efficient database tool. It does not abstract elasticsearch and its search capabilities. Although it embraces and exposes SQL to allow proper full-text search in real-time.

No external parts

It is very most useful advantage of elasticsearch SQL as it does not require any additional hardware, processes, or libraries to query the elasticsearch.

Elasticsearch SQL CLI

Elasticsearch provides elasticsearch-sql-cli.bat file, where we can execute SQL queries. It provides access to run SQL queries in elasticsearch. This bat file exists inside the bin folder.

Elasticsearch SQL features

In this chapter, we will demonstrate how we can execute basic SQL queries. Elasticsearch SQL is a rich platform and provides various features that helps to execute the SQL queries. Following are the features of elasticsearch SQL -

• There are several formats such as json, txt, csv, yaml, and more that are used for responding the SQL queries.
• Elasticsearch offers an elasticsearch-sql-cli utility that provide a CLI where we can directly execute the SQL queries.
• It also allows the user to implement the additional query DSL filtering with elasticsearch SQL.

Getting start with Elasticsearch SQL

Let's take an example of index creation with some data to start using Elasticsearch SQL. In this example, we will create an index named college that will consist of course list.

Response

By executing the above query in elasticsearch-head plugin, we will get the response same as the given below -

Running SQL Query

The Elasticsearch SQL query shows the returned data in tabular form. These SQL queries can be executed using SQL REST API. In the below example, we will create a SQL query to fetch the data from an index where you will see how it execute and respond back to the user.

Response

The above query is used to get the records from college index that contains fee is more the 148000. You will get the response same as the below output -

Elasticsearch SQL CMD

Elasticsearch package offers an elasticsearch-sql-cli command prompt to execute the Elasticsearch SQL queries. The elasticsearch-sql-cli is a batch file that exists inside the bin folder. By running this bat file, a SQL command prompt will display where you can execute elasticsearch SQL queries. It will look like the below screenshot:

Screenshot

Here you can get access to execute SQL queries in elasticsearch. So, you do not need to learn query DSL.