/elasticsearch-sql

Use SQL to query Elasticsearch

Primary LanguageJavaApache License 2.0Apache-2.0

Elasticsearch-SQL

build status

1.7.6 1.7.6 Build Status 2.0.0 2.0.0 Build Status 2.1.0 2.1.0 Build Status 2.1.1 2.1.1 Build Status 2.1.2 2.1.2 Build Status 2.2.0 2.2.0 Build Status
2.2.1 2.2.1 Build Status 2.3.0 2.3.0 Build Status 2.3.1 2.3.1 Build Status 2.3.2 2.3.2 Build Status 2.3.3 2.3.3 Build Status 2.3.4 2.3.4 Build Status
2.3.5 2.3.5 Build Status 2.4.0 2.4.0 Build Status 2.4.1 2.4.1 Build Status 2.4.2 2.4.2 Build Status 2.4.3 2.4.3 Build Status 2.4.4 2.4.4 Build Status
2.4.5 2.4.5 Build Status 2.4.6 2.4.6 Build Status 5.0.1 5.0.1 Build Status 5.1.1 5.1.1 Build Status 5.1.2 5.1.2 Build Status 5.2.0 5.2.0 Build Status
5.2.1 5.2.1 Build Status 5.2.2 5.2.2 Build Status 5.3.0 5.3.0 Build Status 5.3.1 5.3.1 Build Status 5.3.2 5.3.2 Build Status 5.3.3 5.3.3 Build Status
5.4.0 5.4.0 Build Status 5.4.1 5.4.1 Build Status 5.4.2 5.4.2 Build Status 5.4.3 5.4.3 Build Status 5.5.0 5.5.0 Build Status 5.5.1 5.5.1 Build Status
5.5.2 5.5.2 Build Status 5.5.3 5.5.3 Build Status 5.6.0 5.6.0 Build Status 5.6.1 5.6.1 Build Status 5.6.2 5.6.2 Build Status 5.6.3 5.6.3 Build Status
5.6.4 5.6.4 Build Status 5.6.5 5.6.5 Build Status 6.0.0 6.0.0 Build Status 6.0.1 6.0.1 Build Status 6.1.0 6.1.0 Build Status 6.1.1 6.1.1 Build Status

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

Check out our wiki!

explain example

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

Web frontend overview

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
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

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 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

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