java es sql groupby_es插件--[elasticsearch-sql]

Elasticsearch-SQL

build status

1.X  2.0.0  2.1.0  2.1.1  2.1.2  2.2.0  2.2.1  2.3.0  2.3.1  2.3.2  2.3.3  2.3.4 

Query elasticsearch using familiar SQL syntax. You can also use ES

functions in SQL.

Check out

our

explain example

you can visite : http://www.nlpcn.org:9999/web/ ,

it is a sample example for explain

Web frontend

overview

SETUP

Install as plugin:

Versions

elasticsearch version

latest version

remarks

branch

1.X

1.4.9

tested against elastic 1.4-1.6

master

2.0.0

2.0.4

delete commands not supported

elastic2.0

2.1.0

2.1.0.2

delete commands not supported

elastic2.1

2.1.1

2.1.1.1

delete commands not supported

elastic2.1.1

2.1.2

2.1.2.0

delete commands not supported

elastic2.1.2

2.2.0

2.2.0.1

delete commands not supported

elastic2.2.0

2.2.1

2.2.1.0

delete commands not supported

elastic2.2.1

2.3.0

2.3.0.0

delete commands not supported

elastic2.3.0

2.3.1

2.3.1.0

delete commands not supported

elastic2.3.1

2.3.2

2.3.2.0

delete commands not supported

elastic2.3.2

2.3.3

2.3.3.0

delete commands not supported

elastic2.3.3

2.3.4

2.3.4.0

delete commands not supported

elastic2.3.4

Elasticsearch 1.X

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

Elasticsearch 2.0.0

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

Elasticsearch 2.1.0

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

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

Elasticsearch 2.1.2

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

Elasticsearch 2.2.0

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

Elasticsearch 2.2.1

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

Elasticsearch 2.3.0

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

Elasticsearch 2.3.1

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

Elasticsearch 2.3.2

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

Elasticsearch 2.3.3

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

Elasticsearch 2.3.4

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

After doing this, you need to restart the Elasticsearch server.

Otherwise you may get errors like Invalid

index name [sql], must not start with ''];

","status":400}.

Basic Usage

Visit The elasticsearch-sql web

front end:

http://localhost:9200/_plugin/sql/

Simple query

http://localhost:9200/_sql?sql=select * from indexName limit 10

Explain SQL to elasticsearch

query DSL

http://localhost:9200/_sql/_explain?sql=select * from indexName limit 10

SQL Usage

Query

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

Aggregation

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

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

Delete

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

Beyond sql

Search

SELECT address FROM bank WHERE address = matchQuery('880 Holmes Lane') ORDER BY _score DESC LIMIT 3

Aggregations

range age group 20-25,25-30,30-35,35-40

SELECT COUNT(age) FROM bank GROUP BY range(age, 20,25,30,35,40)

range date group by day

SELECT online FROM online GROUP BY date_histogram(field='insert_time','interval'='1d')

range date group by your config

SELECT online FROM online GROUP BY date_range(field='insert_time','format'='yyyy-MM-dd' ,'2014-08-18','2014-08-17','now-8d','now-7d','now-6d','now')

ES

Geographic

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

Select type

SELECT * FROM indexName/type

SQL Features

SQL Select

SQL

Delete

SQL

Where

SQL Order

By

SQL Group

By

SQL AND

& OR

SQL

Like

SQL COUNT

distinct

SQL In

SQL

Between

SQL

Aliases

SQL Not

Null

SQL(ES)

Date

SQL

avg()

SQL

count()

SQL

last()

SQL

max()

SQL

min()

SQL

sum()

SQL

Nulls

SQL

isnull()

SQL

now()

Beyond sql

features

ES TopHits

ES

MISSING

ES

STATS

ES

GEO_INTERSECTS

ES

GEO_BOUNDING_BOX

ES

GEO_DISTANCE

ES

GEOHASH_GRID aggregation