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 -
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 -
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.
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.
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:
Here you can get access to execute SQL queries in elasticsearch. So, you do not need to learn query DSL.