Shulou(Shulou.com)06/01 Report--

This article shows you how to use the plug-in Elasticsearch-sql to query Elasticsearch with SQL, the content is concise and easy to understand, it can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Elasticsearch query language (DSL) is really difficult to write, but the query function is strange, filter/query/match/agg/geo is various, no matter you encapsulate JSON or python/java api, it is very inconvenient.

Recently found a plug-in, Elasticsearch-SQL can use sql query Elasticsearch, feel that this wheel is really well built.

Project address of Elasticsearch-sql: https://github.com/NLPchina/elasticsearch-sql

1. Introduction

Functions implemented by Elasticsearch-sql:

1) plug-in installation

2) SQL query

3) queries beyond SQL

4) support for JDBC mode

2. Plug-in installation

The installation method is similar to that of elasticsearch-head:

The es version we use is 2.1.1. If you are using a different version, you can find support in https://github.com/NLPchina/elasticsearch-sql.

$cd ~ / elasticsearch-2.1.1

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

If successful, the command line prints the following:

[bigdata-dw@bigdata-arch-client10 es2.1.1] $. / bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.1.1.1/elasticsearch-sql-2.1.1.1.zip

-> Installing from https://github.com/NLPchina/elasticsearch-sql/releases/download/2.1.1.1/elasticsearch-sql-2.1.1.1.zip...

Trying https://github.com/NLPchina/elasticsearch-sql/releases/download/2.1.1.1/elasticsearch-sql-2.1.1.1.zip...

Downloading. . . .DONE

Verifying https://github.com/NLPchina/elasticsearch-sql/releases/download/2.1.1.1/elasticsearch-sql-2.1.1.1.zip checksums if available...

NOTE: Unable to verify checksum for downloaded plugin (unable to find .SHA1 or .MD5 file to verify)

Installed sql into / home/bigdata-dw/es2.1.1/plugins/sql

3. SQL query

After the installation is successful, we can query ES through sql.

Es-sql also provides a web page, which is accessed by http://10.93.18.34:9200/_plugin/sql/ (if you use head, then your head access should be http://10.93.18.34:9200/_plugin/head/)

The ip and port here are the hosts and http ports where you install es.

The page visited is like this

So you now have two ways to execute your SQL:

1) enter your sql directly in the search box. (don't write ";" at the end of the line in my version, otherwise you won't be able to parse SQL.)

2) request via http, such as

Curl-XPOST http://10.93.18.34:8049/_sql-d'SELECT * FROM audit where dDelay=-2053867461'

You will receive a return in json format

{

"took": 2

"timed_out": false

"_ shards": {

"total": 5

"successful": 5

"failed": 0

}

"hits": {

"total": 1

"max_score": 12.549262

"hits": [{

"_ index": "audit"

"_ type": "kafka"

"_ id": "AVzzK-h_V9seINxbZ2Ox"

"_ score": 12.549262

"_ source": {

"timestamp": "1498726500000"

"dCount": 680008

"dDelay":-2053867461

"cDelay": 0

"clanName": "DJ_elk_common"

"checkTime": 1498728360063

"cCount": 0

"pCount": 680008

"topicName": "DJ_elk_common_clean"

"pDelay": 370356423

}

}]

}

}

Let's briefly talk about the four types of sql writing methods:

1) query

SELECT * FROM bank WHERE age > 30 AND gender ='m'

2) aggregation

Select COUNT (*), SUM (age), MIN (age) as m, MAX (age), AVG (age)

FROM bank GROUP BY gender ORDER BY SUM (age), m DESC

3) delete

DELETE FROM bank WHERE age > 30 AND gender ='m'

4) geo

SELECT * FROM locations WHERE GEO_BOUNDING_BOX (fieldname,100.0,1.0,101,0.0)

5) index+type needs to be specified

SELECT * FROM indexName/type

6) how to specify a route

Select / *! ROUTINGS (salary) * / sum (count) from index where type= "salary"

4. Support for JDBC

The above query method is either to enter sql directly on web or to request through http. Elasticsearch-sql also supports programming through jdbc.

The above content is to use SQL to query Elasticsearch plug-in Elasticsearch-sql how to use, have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.