Elasticsearch-SQL

build status

1.7.6 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
2.3.5 2.4.0 2.4.1 2.4.2 2.4.3 2.4.4
2.4.5 2.4.6 5.0.1 5.1.1 5.1.2 5.2.0
5.2.1 5.2.2 5.3.0 5.3.1 5.3.2 5.3.3
5.4.0 5.4.1 5.4.2 5.4.3 5.5.0 5.5.1
5.5.2 5.5.3 5.6.0 5.6.1 5.6.2 5.6.3
5.6.4 5.6.5 5.6.6 5.6.7 5.6.8 5.6.9
5.6.10 6.0.0 6.0.1 6.1.0 6.1.1 6.1.2
6.1.3 6.1.4 6.2.0 6.2.1 6.2.2 6.2.3
6.2.4 6.3.0 6.3.1

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

Check out our wiki!

explain example

you can visite : http://essql.nlpcn.org/ , it is a sample example for explain

Web frontend overview

SETUP

Install as plugin: Versions

elasticsearch version	latest version	remarks	branch
1.x	1.7.6	tested against elastic 1.7.6	elastic1.x
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.1	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
2.3.5	2.3.5.0	delete commands not supported	elastic2.3.5
2.4.0	2.4.0.1	delete commands not supported	elastic2.4.0
2.4.1	2.4.1.0	delete commands not supported	elastic2.4.1
2.4.2	2.4.2.1	delete commands not supported	elastic2.4.2
2.4.3	2.4.3.0	delete commands not supported	elastic2.4.3
2.4.4	2.4.4.0	delete commands not supported	elastic2.4.4
2.4.5	2.4.5.0	delete commands not supported	elastic2.4.5
2.4.6	2.4.6.0	delete commands not supported	elastic2.4.6
5.0.1	5.0.1.0	delete commands not supported	elastic5.0.1
5.1.1	5.1.1.0	delete commands not supported	elastic5.1.1
5.1.2	5.1.2.0	delete commands not supported	elastic5.1.2
5.2.0	5.2.0.0	delete commands not supported	elastic5.2.0
5.2.1	5.2.1.0	delete commands not supported	elastic5.2.1
5.2.2	5.2.2.0	delete commands not supported	elastic5.2.2
5.3.0	5.3.0.0	delete commands not supported	elastic5.3.0
5.3.1	5.3.1.0	delete commands not supported	elastic5.3.1
5.3.2	5.3.2.0	delete commands not supported	elastic5.3.2
5.3.3	5.3.3.0	delete commands not supported	elastic5.3.3
5.4.0	5.4.0.0	delete commands not supported	elastic5.4.0
5.4.1	5.4.1.0	delete commands not supported	elastic5.4.1
5.4.2	5.4.2.0	delete commands not supported	elastic5.4.2
5.4.3	5.4.3.0	delete commands not supported	elastic5.4.3
5.5.0	5.5.0.1	delete commands not supported	elastic5.5.0
5.5.1	5.5.1.0	delete commands not supported	elastic5.5.1
5.5.2	5.5.2.0	delete commands not supported	elastic5.5.2
5.5.3	5.5.3.0	delete commands not supported	elastic5.5.3
5.6.0	5.6.0.0	delete commands not supported	elastic5.6.0
5.6.1	5.6.1.0	delete commands not supported	elastic5.6.1
5.6.2	5.6.2.0	delete commands not supported	elastic5.6.2
5.6.3	5.6.3.0	delete commands not supported	elastic5.6.3
5.6.4	5.6.4.0	delete commands not supported	elastic5.6.4
5.6.5	5.6.5.0	delete commands not supported	elastic5.6.5
5.6.6	5.6.6.0		elastic5.6.6
5.6.7	5.6.7.0		elastic5.6.7
5.6.8	5.6.8.0		elastic5.6.8
5.6.9	5.6.9.0		elastic5.6.9
5.6.10	5.6.10.0		elastic5.6.10
6.0.0	6.0.0.0		elastic6.0.0
6.0.1	6.0.1.0		elastic6.0.1
6.1.0	6.1.0.0		elastic6.1.0
6.1.1	6.1.1.0		elastic6.1.1
6.1.2	6.1.2.0		elastic6.1.2
6.1.3	6.1.3.0		elastic6.1.3
6.1.4	6.1.4.0		elastic6.1.4
6.2.0	6.2.0.0		elastic6.2.0
6.2.1	6.2.1.0		elastic6.2.1
6.2.2	6.2.2.0		elastic6.2.2
6.2.3	6.2.3.0		elastic6.2.3
6.2.4	6.2.4.0		elastic6.2.4
6.3.0	6.3.0.0		elastic6.3.0
6.3.1	6.3.1.0		elastic6.3.1

Elasticsearch 1.x

./bin/plugin -u https://github.com/NLPchina/elasticsearch-sql/releases/download/1.7.6/elasticsearch-sql-1.7.6.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.1/elasticsearch-sql-2.3.1.1.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

Elasticsearch 2.3.5

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

Elasticsearch 2.4.0

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

Elasticsearch 2.4.1

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

Elasticsearch 2.4.2

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

Elasticsearch 2.4.3

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

Elasticsearch 2.4.4

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

Elasticsearch 2.4.5

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

Elasticsearch 2.4.6

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

Elasticsearch 5.0.1

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

Elasticsearch 5.1.1

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

Elasticsearch 5.1.2

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

Elasticsearch 5.2.0

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

Elasticsearch 5.2.1

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

Elasticsearch 5.2.2

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

Elasticsearch 5.3.0

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

Elasticsearch 5.3.1

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

Elasticsearch 5.3.2

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

Elasticsearch 5.3.3

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

Elasticsearch 5.4.0

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

Elasticsearch 5.4.1

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

Elasticsearch 5.4.2

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

Elasticsearch 5.4.3

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

Elasticsearch 5.5.0

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

Elasticsearch 5.5.1

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

Elasticsearch 5.5.2

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

Elasticsearch 5.5.3

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

Elasticsearch 5.6.0

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

Elasticsearch 5.6.1

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

Elasticsearch 5.6.2

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

Elasticsearch 5.6.3

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

Elasticsearch 5.6.4

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

Elasticsearch 5.6.5

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

Elasticsearch 5.6.6

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

Elasticsearch 5.6.7

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

Elasticsearch 5.6.8

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

Elasticsearch 5.6.9

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

Elasticsearch 5.6.10

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

Elasticsearch 6.0.0

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

Elasticsearch 6.0.1

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

Elasticsearch 6.1.0

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

Elasticsearch 6.1.1

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

Elasticsearch 6.1.2

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

Elasticsearch 6.1.3

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

Elasticsearch 6.1.4

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

Elasticsearch 6.2.0

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

Elasticsearch 6.2.1

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

Elasticsearch 6.2.2

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

Elasticsearch 6.2.3

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

Elasticsearch 6.2.4

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

Elasticsearch 6.3.0

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

Elasticsearch 6.3.1

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/6.3.1.0/elasticsearch-sql-6.3.1.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

On elasticsearch 1.x / 2.x, visit the elasticsearch-sql web front-end:

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

On elasticsearch 5.x/6.x, download and extract site.

Then start the web front-end like this:

cd site-server
npm install express --save
node node-server.js

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()
SQL floor
SQL split
SQL trim
SQL log
SQL log10
SQL substring
SQL round
SQL sqrt
SQL concat_ws
SQL union and minus

JDBC Support (Experimental feature)

Check details : JDBC Support

Beyond sql features

ES TopHits
ES MISSING
ES STATS
ES GEO_INTERSECTS
ES GEO_BOUNDING_BOX
ES GEO_DISTANCE
ES GEOHASH_GRID aggregation

Files

README.md

Latest commit

History