Elasticsearch now supports SQL query.

SpringBoot e-commerce project mall (40K + STAR) address: github.com/macrozheng/…

Abstract

When using Elasticsearch, you will occasionally use Query DSL in Kibana to Query data. Elasticsearch now supports SQL queries (since version 6.3). This is the only way for Elasticsearch to use SQL queries.

Introduction to the

Elasticsearch SQL is an X-pack component that allows real-time EXECUTION of SQL-like queries against Elasticsearch. Any client can use SQL to natively search and aggregate data from Elasticsearch, whether using the REST interface, command line, or JDBC. You can think of Elasticsearch SQL as a translator that translates SQL into Query DSL.

Elasticsearch SQL has the following features:

• Native support: Elasticsearch SQL is built specifically for Elasticsearch.
• No extra parts: Query Elasticsearch without additional hardware, processor, runtime environment or dependent libraries. Elasticsearch SQL runs directly within Elasticsearch.
• Lightweight and efficient: Instead of abstracting its search capabilities, Elasticsearch SQL embraces and accepts SQL for full-text search, running full-text search in real time in a concise way.

Pre-school preparation

Elasticsearch and Kibana have been installed as version 7.6.2 of Elasticsearch. For details, see Elasticsearch Quick Start. .

After the installation is complete, import the test data into Kibana. The data address is github.com/macrozheng/…

To do this, run the following command in Kibana Dev Tools:

First SQL query

We use SQL to query the first 10 records, you can use the format parameter to control the format of the returned results, TXT stands for text format, looks more straight view, default json format.

Enter the following command in the Kibana Console:

POST /_sql? format=txt { "query": "SELECT account_number,address,age,balance FROM account LIMIT 10" }Copy the code

The following information is displayed:

Convert SQL to DSL

When we need to use the Query DSL, we can also use SQL first and then Translate it through the Translate API.

For example, we translate the following query statement:

POST /_sql/translate
{
  "query": "SELECT account_number,address,age,balance FROM account WHERE age>32 LIMIT 10"
}
Copy the code

The final result of Query DSL is as follows.

A mix of SQL and DSL

We can also use a mixture of SQL and Query DSL, for example, to set filtering conditions.

For example, to query the records whose age is between 30 and 35, use the following statement:

POST /_sql? format=txt { "query": "SELECT account_number,address,age,balance FROM account", "filter": { "range": { "age": { "gte" : 30, "lte" : 35 } } }, "fetch_size": 10 }Copy the code

The query results are displayed as follows:

Mapping between SQL and ES

SQL	ES	describe
column	field	The fields of the tables in the database correspond to properties in ES
row	document	Row records in database tables correspond to documents in ES
table	index	The tables in the database correspond to indexes in ES

Common SQL operations

grammar

The syntax of SQL query in ES is basically the same as that in database, and the specific format is as follows:

SELECT select_expr [, ...]
[ FROM table_name ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT [ count ] ]
[ PIVOT ( aggregation_expr FOR column IN ( value[[AS] alias ] [, ...] )))Copy the code

WHERE

You can use the WHERE statement to set query conditions, for example, to query records whose state field is VA. The query statement is as follows:

POST /_sql? format=txt { "query": "SELECT account_number,address,age,balance,state FROM account WHERE state='VA' LIMIT 10 " }Copy the code

The query results are displayed as follows:

GROUP BY

We can use the GROUP BY statement to GROUP data and count the number of GROUP records, maximum age, average balance and other information. The query statement is as follows.

POST /_sql? format=txt { "query": "SELECT state,COUNT(*),MAX(age),AVG(balance) FROM account GROUP BY state LIMIT 10" }Copy the code

HAVING

We can use the HAVING statement to perform secondary filtering of the group data, such as filtering the group data with more than 15 records, as shown in the following query.

POST /_sql? format=txt { "query": "SELECT state,COUNT(*),MAX(age),AVG(balance) FROM account GROUP BY state HAVING COUNT(*)>15 LIMIT 10" }Copy the code

ORDER BY

We can use the ORDER BY statement to sort the data, such as BY the balance field from highest to lowest, as shown in the query statement.

POST /_sql? format=txt { "query": "SELECT account_number,address,age,balance,state FROM account ORDER BY balance DESC LIMIT 10 " }Copy the code

DESCRIBE

We can use the DESCRIBE statement to see which fields are in the table (index in ES), for example, to view the fields in the Account table. The query statement is as follows.

POST /_sql? format=txt { "query": "DESCRIBE account" }Copy the code

SHOW TABLES

We can use SHOW TABLES to view all TABLES (indexes in ES).

POST /_sql? format=txt { "query": "SHOW TABLES" }Copy the code

Supported functions

Using SQL to query data in ES, you can use not only some SQL functions, but also some ES specific functions.

Query supported functions

We can use the SHOW FUNCTIONS statement to see all supported FUNCTIONS, such as the following statement to search for all FUNCTIONS with a DATE field.

POST /_sql? format=txt { "query": "SHOW FUNCTIONS LIKE '%DATE%'" }Copy the code

Full-text search function

The full-text search function is unique to ES. When MATCH or QUERY is used, the full-text search function is enabled. The SCORE function can be used to calculate the search SCORE.

MATCH()

Use the MATCH function to query the record containing Street in address.

POST /_sql? format=txt { "query": "SELECT account_number,address,age,balance,SCORE() FROM account WHERE MATCH(address,'Street') LIMIT 10" }Copy the code

QUERY()

Use the QUERY function to QUERY the record containing Street in address.

POST /_sql? format=txt { "query": "SELECT account_number,address,age,balance,SCORE() FROM account WHERE QUERY('address:Street') LIMIT 10" }Copy the code

SQL CLI

If you don’t want to use ES SQL with Kibana, you can also use the ES SQL CLI. This command is located in the ES bin directory.

Run the following command to start the SQL CLI:

elasticsearch-sql-cli http://localhost:9200
Copy the code

Then directly enter the SQL command to query, note to add semicolon.

SELECT account_number,address,age,balance FROM account LIMIT 10;
Copy the code

limitations

Using SQL Query ES has some limitations. It is not as powerful as the native Query DSL. Support for nested properties and some functions is not as good, but it is generally sufficient for querying data.

The resources

The official document: www.elastic.co/guide/en/el…

In this paper, making github.com/macrozheng/… Already included, welcome everyone Star!