Elasticsearch is one of the robust solution and analytical search engines for semi-structural and nonstructural, raw data. It is purely a NoSQL search engine that uses queries to extract and search data in JSON format from Elasticsearch indexes. These queries are executed through rest APIs.
However, with Elasticsearch growth, its community introduces the Xpack component that allows users to execute the SQL interface to run limited SQL queries such as “SELECT” query, “SHOW CATALOGS” query, “SHOW COLUMNS” query, “SHOW FUNCTIONS” query, and “SHOW TABLES” query.
This blog will illustrate:
How to Query Elasticsearch with SQL?
Elasticsearch supports limited SQL queries as it is pure NoSQL language and usually works with Rest APIs to search, insert, or update data from Elasticsearch indices. In order to execute SQL queries in Elasticsearch queries, go through the following steps.
Step 1: Run Elasticsearch Engine
First, start the Command Prompt Window’s terminal via the “Startup” menu. Then, navigate to Elasticsearch “bin” directory through the “cd” command. Now, execute “elasticsearch.bat” command:
elasticsearch.bat
Step 2: Access Elasticsearch From Localhost
Utilize the “curl” command along with “localhost:9200” URL to access the Elasticsearch. Also pass the user login credentials using “-u” option to access it from CLI:
In the above command, the “localhost:9200” is the default address where the Elasticsearch engine can be accessible locally, and the “-u” option is utilized to provide the Elasticsearch login credentials such as username and password:
Step 3: Run the SQL with Elasticsearch Query
To use the SQL with Elasticsearch query, first, use the “XPOST” API of elasticsearch. Then, pass the Elasticsearch URL along with the “_sql” document. Now, use the “SELECT” query in JSON format as a value of the “query” variable as shown below:
In the above-mentioned command, the “SELECT” query is utilized to search data from the “employee” index where the “Designation” value is “Technical Author”:
The above output indicates that we have successfully executed the SQL with the Elasticsearch query.
How to Use Elasticsearch SQL CLI to Run SQL Queries?
Elasticsearch provided the “xpack component” that enables us to use the “Elasticsearch-SQL” command line interface where users can directly execute the SQL commands using Elasticsearch APIs and queries.
To use Elasticsearch SQL CLI, follow the given instructions.
Step 1: Run Elasticsearch SQL CLI
Navigate to the Elasticsearch “bin” directory through “cd” command. After that, utilize the “elasticsearch-sql-cli” command along with Elasticsearch account URL that is “http://elastic:<user-password>@localhost:9200”:
In the above command:
- “-c” option is used to allow the initial connection check on startup.
- “-d” option permits the debug logging.
- “-v” option is used to display the verbose output.
The below output shows that we have successfully started the “Elasticsearch SQL” command line interface:
Step 2: Use SQL Queries
Now, directly execute the SQL queries to access or search data from Elasticsearch indices. For instance, we have used the “SELECT” query to access the “employee” index:
In order to access the indexes column or attributes, simply use the below query:
That is all about using query Elasticsearch with SQL.
Conclusion
Elasticsearch is purely a NoSQL search engine but its xpack component permits us to use limited SQL queries via the “Elasticsearch SQL” interface or with a combination of Elasticsearch queries. To use the SQL with Elasticsearch query, first, use the “XPOST” API, and pass the Elasticsearch URL along with the “_sql” document. After that, use the SQL query in JSON format as a value of the “query” variable. Users can also use the Elasticsearch SQL CLI interface using the “Elasticsearch-sql-cli” command. This post has covered the method to use SQL query with Elasticsearch.
