Shulou(Shulou.com)05/31 Report--

What is the usage of Elasticsearch SQL? for this question, this article introduces the corresponding analysis and answer in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

The editor will introduce the use of Elasticsearch SQL in different versions, summarize the methods and operations commonly used in practice, and give several specific examples.

I. the usage of ES-SQL in 5.x

In Elasticsearch 5.x, the SQL feature has not been integrated into the Elasticsearch source code, and you need to download a third-party plug-in before using it. The configuration process is as follows:

1. Installation of ES-SQL depends on node npm

After ES-SQL version 5.x, installation depends on node and npm. Install node and npm first, and check the installation of node and npm after installation. The command is as follows:

Yum-y install nodejs npm node-v npm-v

two。 Download ES-SQL and install

Then change to the root directory of ES and execute the following command to download and install the ES-SQL plug-in:

. / bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.6.30/.elasticsearch-sql-5.6.3.0.zip

Offline package installation can be performed:

. / bin/elasticsearch-plugin install file:/elasticsearch-sql-5.6.3.0.zip

3. Restart the ES service

After performing the above three steps, you can explore the data using SQL, taking the usage in kibana as an example:

II. 6.4 Elasticsearch SQL usage

1 data types supported by Elasticsearch SQL

First, let's take a look at the correspondence between Elasticsearch SQL and standard SQL data types:

2 how to use Elasticsearch SQL

Elasticsearch SQL supports three kinds of client: REST Interface, command-line,JDBC

2.1 REST Interface

It is recommended to test in kibana first (you can execute more than one SQL at a time), and then copy the query to the project for testing after the query has passed.

6.3 + Elasticsearch SQL has a very practical function, that is, you can use translate api to translate SQL statements into ES DSL sentences, which is blessed for students who have a headache in learning DSL.

2.2 command-line

How to enter the command line interface:

. / elasticsearch-sql-cli IP:PORT (ports of native ip and es)

The interface after entering is as follows:

The command line is generally used as an SQL test.

2.3 JDBC

This component is a charging component in X-Pack. Interested students can refer to the official document: https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-jdbc.html

3 commonly used SQL statements

Note: query a single index name must be quoted with "", otherwise an error will be reported.

* View all the indexes of the current user: "SHOW TABLES;"

Precisely query an index: "SHOW TABLES LIKE 'indexname';"

Some indexes are queried by wildcards: "SHOW TABLES LIKE';"

* View an index structure: "DESCRIBE table;" or "DESC table;"

Both of the above commands are aliases for the "SHOW COLUMNS [FROM | IN]? table" command

* View function: "SHOW FUNCTIONS [LIKE? pattern?]?"

Precisely query a function:

Wildcards query some functions:

View all functions:

Common aggregate functions:

SELECT MIN (value_1) min, MAX (value_1) max, AVG (value_1) avg,SUM (value_1) sum,COUNT (*) count,COUNT (DISTINCT value_1) dictinct_count FROM "micloud_es_sink_zhouyongbo_test-2018.10.19"

The syntax of the SELECT statement is sorted as follows:

SELECT select_expr [,...] [FROM table_name] [WHERE condition] [GROUP BY grouping_element [,...]] [HAVING condition] [ORDER BY expression [ASC | DESC] [,...] [LIMIT [count]]

* limit the number of returned data: "limit"

SELECT * FROM "micloud_es_sink_zhouyongbo_test-2018.10.19" limit 10

Note that the limit in SQL has higher priority than that in fetch_size. For example, the following example returns 5:

{"query": "SELECT * FROM" micloud_es_sink_zhouyongbo_test-2018.10.19 "limit 5", "fetch_size": 10}

* sort: "order by + field name + asc/desc"

SELECT * FROM "micloud_es_sink_zhouyongbo_test-2018.10.19" ORDER BY value_1 asc/desc

Sort by multiple fields:

Select city cvaluevalue1 + 1 vp from "micloud_es_sink_zhouyongbo_test-2018.10.19" group by cMagnevp order by c desc,vp asc

* WHERE queries based on conditions:

WHERE is followed by ES complex data types:

SELECT first_name FROM index WHERE first_name.raw = 'John'

WHERE is followed by several query criteria:

SELECT * FROM micloud_es_sink_zhouyongbo_test* where city=' Beijing 'and value_1=8 ORDER BY value_1 desc

* group by grouping query:

Group queries based on individual fields:

Select city,count (city) as count_city,sum (value_1) as count_value_1 from "micloud_es_sink_zhouyongbo_test-2018.10.19" group by city

Group queries based on multiple fields:

Select city,count (city) count_city,sum (value_1) count_value_1 from "micloud_es_sink_zhouyongbo_test-2018.10.19" group by city,value_1

For longer fields, you can also declare an alias for the field and make a grouping query for the alias. The "as" of the alias declaration can be omitted:

Select city ccent count (city) count_city,sum (value_1) count_value_1 from "micloud_es_sink_zhouyongbo_test-2018.10.19" group by ccent value1

You can also calculate a field and then group the query according to the calculated results:

Select city ccenture 1 + 1 vp from "micloud_es_sink_zhouyongbo_test-2018.10.19" group by cpene VP

* HAVING filters the grouping results (the ES-SQL engine also calculates HAVING statements after grouping):

Select city ccent count (*) count from "micloud_es_sink_zhouyongbo_test-2018.10.19" group by c having count > 53834

* query nesting types:

Select * from zhouyongbo_test04 where love.kaishu=' Lu Gong'

* query multiple indexes with wildcards:

Note that the index to be queried must have the same mapping, otherwise there will be the following error:

Summary of commonly used methods and operations:

* comparison operation:

Equality (=)

Select * from "micloud_es_sink_zhouyongbo_test-2018.10.19" where value_1 = 6 limit 5

Inequality (or! = or

Select * from "micloud_es_sink_zhouyongbo_test-2018.10.19" where value_1 6 limit 5

Comparison (= 6 limit 5

BETWEEN

Select * from "micloud_es_sink_zhouyongbo_test-2018.10.19" where value_1 between 6 and 8 limit 5

IS NULL/IS NOT NULL

Select * from "micloud_es_sink_zhouyongbo_test-2018.10.19" where value_1 is not NULL limit 5

* logical operation:

AND

Select * from "micloud_es_sink_zhouyongbo_test-2018.10.19" where value_1 > 5 and value_1

< 7 limit 5; OR select * from "micloud_es_sink_zhouyongbo_test-2018.10.19" where value_1 = 5 or value_1 = 7 limit 5; NOT select * from "micloud_es_sink_zhouyongbo_test-2018.10.19" where not value_1 >

5 limit 5

* Mathematical operations:

Add (+)

Select 1 + 1 as x

Subtract (infix -)

Select 1-1 as x

Negate (unary -)

Select-1 as x

Multiply (*)

Select 6 * 6 as x

Divide (/)

Select 30 / 5 as x

Modulo or Reminder (%)

Select 30 7 as x

* Mathematical functions: (divided into general functions and trigonometric functions):

General function:

ABS: find the absolute value of a number

Select ABS (value_1) from "micloud_es_sink_zhouyongbo_test-2018.10.19" limit 5

CBRT: find the cube root of a number and return double

Select value_1 vCBRT (value_1) cbrt from "micloud_es_sink_zhouyongbo_test-2018.10.19" limit 5

CEIL: returns a minimum integer greater than or equal to the specified expression (double)

Select value_1 vmae CEIL (value_1) from "micloud_es_sink_zhouyongbo_test-2018.10.19" limit 5

CEILING: equivalent to CEIL

Select value_1 vCEILING (value_1) from "micloud_es_sink_zhouyongbo_test-2018.10.19" limit 5

E: returns the natural constant e (2.718281828459045)

Select value_1,E (value_1) from "micloud_es_sink_zhouyongbo_test-2018.10.19" limit 5

ROUND: rounding down to individual bits

Select ROUND (- 3.14)

FLOOR: round down

Select FLOOR (3.14)

LOG: calculate the natural logarithm with a base of 2

Select LOG (4)

LOG10: calculate the natural logarithm with a base of 10

Select LOG10 (100)

SQRT: find the square root of a nonnegative real number

Select SQRT (9)

EXP: this function returns the value of the X power of e (the base of the natural logarithm)

Select EXP (3)

EXPM1: returns e x-1

Select EXPM1 (3)

Trigonometric function:

DEGREES: returns the conversion of X from radians to values

Select DEGREES (x)

RADIANS: returns the value of X converted from degrees to radians

Select RADIANS (x)

SIN: returns the sine of X

Select SIN (x)

COS: returns X, where the X value is the cosine in radians

Select COS (Angle)

TAN: returns the parameter X, which represents the tangent value in radians

Select TAN (Angle)

ASIN: returns the arcsine of X. The value of X must be in the range of-1 to 1. Return NULL.

Select ASIN (x)

ACOS: returns the arcsine of X. The value of X must be in the range of-1 to 1, otherwise NULL will be returned.

Select ACOS (x)

ATAN: returns the inverse tangent of X

Select ATAN (x)

SINH: returns the hyperbolic sine of X

Select SINH (x)

COSH: returns the hyperbolic cosine of X

Select COSH (x)

* date and time processing methods:

YEAR:

SELECT YEAR (CAST ('2018-10-23T16 AS TIMESTAMP 59Z' AS TIMESTAMP)) AS year

MONTH_OF_YEAR () or MONTH ():

SELECT MONTH (CAST ('2018-10-23T16 AS TIMESTAMP 59Z' AS TIMESTAMP)) AS month

WEEK_OF_YEAR () or WEEK ():

SELECT WEEK (CAST ('2018-10-23T16 AS TIMESTAMP 59Z' AS TIMESTAMP)) AS week

DAY_OF_YEAR () or DOY (), the effect is the same as EXTRACT (

SELECT DOY (CAST ('2018-10-23T16 AS TIMESTAMP 59Z' AS TIMESTAMP)) AS day

DAY_OF_MONTH (), DOM (), or DAY ():

SELECT DAY (CAST ('2018-10-23T16 AS TIMESTAMP 59Z' AS TIMESTAMP)) AS day

DAY_OF_WEEK () or DOW ():

SELECT DOW (CAST ('2018-10-23T16 AS TIMESTAMP 59Z' AS TIMESTAMP)) AS day

HOUR_OF_DAY () or HOUR ():

SELECT HOUR (CAST ('2018-10-23T16 AS TIMESTAMP 59Z' AS TIMESTAMP)) AS hour

MINUTE_OF_DAY ():

SELECT MINUTE_OF_DAY (CAST ('2018-10-23T16 AS TIMESTAMP 59Z' AS TIMESTAMP)) AS minute

MINUTE_OF_HOUR () or MINUTE ():

SELECT MINUTE (CAST ('2018-10-23T16 AS TIMESTAMP 59Z' AS TIMESTAMP)) AS minute

SECOND_OF_MINUTE () or SECOND ():

SELECT SECOND (CAST ('2018-10-23T16 AS TIMESTAMP 59Z' AS TIMESTAMP)) AS second

If the query requirements are not satisfied after optimizing the SQL statement, you can mix SQL and DSL. ES will first query according to SQL, and then query the execution result of SQL based on DSL statement. Here is a small example:

POST / _ xpack/sql?format=txt {"query": "SELECT * FROM library ORDER BY page_count DESC", "filter": {"range": {"page_count": {"gte": 100,200}, "fetch_size": 5}

This query will first query according to the SQL after "query", then filter the query results with the execution "filter" and "fetch_size" DSL syntax, and then return the final result.

This is the end of the answer to the question about the use of Elasticsearch SQL. I hope the above content can be of some help to you. If you still have a lot of doubts to solve, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.