Working with Elasticsearch SQL

In the last chapter, we touched upon the major components of the Elastic Stack. We went through the entire range of data processes, starting with Filebeat, Logstash, and Elasticsearch, before finally viewing it in Kibana. Although we only gave a simple example, you can still see that once the details can be handled, the feasibility and extensibility are great. That is why the Beats are so popular and extend to different areas to collect data. In this chapter, we will introduce Elasticsearch SQL. With Elasticsearch SQL, you can access full-text search and easily extend the functionality with a familiar query syntax. You can even see your results in the tabular views. Elasticsearch provides a variety of approaches, such as the REST API interface, the command-line interface, the JDBC (Java Database Connectivity) driver, and the ODBC (Open Database Connectivity...

Overview

The support feature for SQL was delivered with version 6.3. This was due to the popularity of SQL, while many Elasticsearch users were familiar with SQL syntax and the ability to perform SQL-like queries on indexed data had been a longstanding desire. Elasticsearch SQL is an X-Pack component, where X-Pack is an Elastic Stack extension that provides many features, including monitoring, security, and machine learning. In the default installation, Elasticsearch comes with X-Pack. You will recall from the Mapping concepts across SQL and Elasticsearch section of Chapter 1, Overview of Elasticsearch 7, that we constructed a table that describes the terms between SQL and Elasticsearch, comparing column and field, row and document, table and index, and database and cluster instance. You can think of Elasticsearch SQL as a translator from SQL to Elasticsearch. Nonetheless, all...

Getting started

Elasticsearch ships with a script, named elasticsearch-sql-cli, to run the SQL CLI (command-line interface) in its bin directory. Let's use the Elasticsearch Docker container to run the example. Assume that the container ID is 6b3d0fde663d and issue the command, as described in the following code block, to start a bash shell session in the Docker container:

$docker exec -it elasticsearch /bin/bash
[root@6b3d0fde663d elasticsearch]# 

You will get a prompt in response, and now you are working in the Elasticsearch Docker container. Issue the elasticsearch-sql-cli command to run the SQL CLI:

[root@6b3d0fde663d elasticsearch]# bin/elasticsearch-sql-cli
WARNING: sun.reflect.Reflection.getCallerClass is not supported. This will impact performance.

When the command line starts, you will see the Elastic Stack icon and a prompt for input, as shown in the following screenshot...

Elasticsearch SQL language

SQL stands for Structured Query Language, a programming language that is used for the following purposes:

• Defining data (such as create, alter, and drop commands in DDL)
• Manipulating data (such as insert, update, and delete in DML)
• Resolving queries (such as select, describe, and show in DQL)
• Controlling data access (such as grant and revoke in DCL)
• Dealing with the transaction (such as commit and rollback in TCL) for relational databases

Elasticsearch SQL does not have a DCL (Data Control Language) and TCL (Transactional Control Language) in its current version. To understand this language better, we will follow six aspects, including keywords, data types, operators, functions, and lexical structure, to study it.

Reserved keywords

...

Elasticsearch SQL REST API

The SQL REST API accepts a SQL statement in JSON format, executes it, and returns a response. The endpoint of the SQL REST API is shown in the following code block. You should use a parameter query with a SQL statement in the request body:

POST /_sql?format=response_format
{
   "query": "....",
    "parameter_x": parameter_x_value
}

Now, let's use the Kibana console to practice some examples outlined under the Query DSL section of Chapter 6, Search APIs:

1. To use the Kibana console from dev_tools, click on the button with the wrench icon on the left-hand sidebar, as shown in the following screenshot:

2. Then, type the SQL statement to retrieve all the records from the cf_etf index, as described in the following code block, in the left-hand panel and click on the green arrow button in the top-right corner of the panel:

POST...

Elasticsearch SQL JDBC

Elasticsearch's SQL JDBC is a type 4 driver, which means it is a pure Java driver and platform-independent. It connects directly to Elasticsearch and converts JDBC calls to Elasticsearch SQL. According to the support matrix of the official website (https://www.elastic.co/subscriptions#request-info), JDBC support is only available for the platinum license. If the Elasticsearch software is downloaded, or the open source version used, it will be a basic version. Therefore, let's follow the instructions provided in the following section to perform an upgrade.

Upgrading Elasticsearch from a basic to a trial license

To run the sample program, go to the Kibana license manager to upgrade from a basic...

Summary

Please put your hands together! Elasticsearch SQL is easy, right? You should now know what Elasticsearch SQL is. In addition, you are now familiar with the semantics of Elasticsearch SQL. We performed a few SQL commands by using the elasticsearch-sql-cli script. We demonstrated how to use the Kibana DevTools console to perform SQL REST API with different SQL statements and, finally, we provided some programming instructions to set up the Elasticsearch SQL JDBC driver and used the JDBC connection to perform the request and handle the result.

In the next chapter, we will learn about Elasticsearch plugin management to enhance its core functionality. We will focus on the analysis plugins. We will use the ICU Analysis plugin and the Smart Chinese Analysis plugin as examples of enhancing the text analyzer for mixed Chinese-English texts.

...